The 4 Types of Dimensions (Data Modeling 101)

Most dimensions in a star schema are straightforward: one table that describes a customer, a product or a location, joined to a fact table on a key. The four types that raise questions are role-playing, conformed, junk and degenerate dimensions, and they split into two groups. Role-playing and conformed dimensions reuse one dimension, either several times inside the same fact table or across many fact tables. Junk and degenerate dimensions handle an attribute that is too small to deserve its own table, by bundling several low cardinality flags into one dimension or by leaving the value on the fact table itself.

Facts and dimensions, briefly

Building a data warehouse is rarely straightforward. Every company has its own nuances and business logic to account for, and dimensions are where a lot of that shows up.

A quick refresher before the four types. In a star schema:

• The fact table is the core business action you're trying to measure. Something that happened, with numeric measures you want to aggregate.
• Dimensions provide the context around that action. Who was the customer, what was the product, when did it happen.

You typically start with a fact table and build dimensions around it. The four types below cover the situations that come up once that basic structure is in place and the model starts to grow.

1. Role-playing dimension

One table, joined three times. A role-playing dimension is one dimension table that gets joined to the same fact table multiple times, playing a different role each time. The classic example is dates.

Say you have an orders fact with several dates on it: the order date, the ship date, the delivery date. They're all dates, but you don't need three separate date dimensions to handle them.

You build one central dim_date and join it back on each key with a different alias.

What the query looks like

Looking at the query makes it clearer than any description.

select
    o.order_id,
    od.full_date      as order_date,
    sd.full_date      as ship_date,
    dd.full_date      as delivery_date,
    o.order_amount
from fct_orders o
left join dim_date od on o.order_date_key    = od.date_key
left join dim_date sd on o.ship_date_key     = sd.date_key
left join dim_date dd on o.delivery_date_key = dd.date_key

Why one table is better than three

Three keys, three joins, one table. The benefits:

• Storage: you're not recreating the same table three times.
• Consistency: a date is a date regardless of the role it's playing.

You'll run into this outside of calendar dates too, but dates are the example you'll see most often. When you see a dimension rejoined to the same fact under different aliases, that's a role-playing dimension.

2. Conformed dimension

A conformed dimension is a single dimension that is shared and consistent across multiple fact tables. This one is extremely common, and most teams already have one without using the term.

Customer is the obvious example

Sometimes a dimension is only ever relevant to one fact. A lot of the time it's reused, and customer is the obvious example.

Say you're building three fact tables:

• fct_sales
• fct_tickets
• fct_emails

Each one has metrics you'll want to summarize or filter by customer, or by an attribute of the customer like region or segment. All three join to the same central dim_customer. That's what makes it conformed.

It doesn't have to be customer. Products, locations, anything that spans more than one business process qualifies.

Why it matters

This is what makes cross-process questions possible. Looking at one customer across sales, support tickets and email engagement only works if all three facts agree on who that customer is.

Without a conformed dimension, every mart ends up with its own definition of a customer and the join breaks.

If you find yourself building the same customer dimension for every fact, you're repeating yourself and opening the door to conflicts. Centralize it.

3. Junk dimension

The first two types are well understood by most teams. The next two are edge cases, and they're the ones I think are more valuable to have in your toolbox.

A junk dimension bundles small, low cardinality flags or columns together into one isolated dimension.

What cardinality means

Cardinality is the number of distinct values a column can hold.

• An ID column might have thousands or millions of distinct values. That's high cardinality.
• A yes or no flag has two. That's low cardinality.

The problem: three tiny flags

Take an orders fact with three descriptive columns:

• is_gift: two values, yes or no.
• payment_method: five values.
• order_channel: three values.

They're descriptions, not measures, so they don't really belong on the fact. The first instinct is to give each its own dimension. That gets you:

• Three tiny tables with essentially one flag each
• Three keys on the fact
• Three relationships to manage

And you're only doing it because you think you're supposed to.

The alternative: one table of combinations

Since each column has so few options, you can work out every combination, give each one a unique key, and store that single key on the fact table.

Two gift values times five payment methods times three channels is thirty rows. One table, one key, one join.

Why do it this way? It keeps the fact table narrow and the model organized. Instead of three keys and three joins for three small flags, you have one relationship to manage.

It's an edge case, and like everything in modeling it has nuance. It's worth knowing it exists for the next time you're staring at a handful of flags wondering where they go.

4. Degenerate dimension

The naming is a little funny, but a degenerate dimension is a dimension attribute that lives directly in the fact table.

Generally a fact table is reserved for keys and metrics. You don't want many descriptions in there, because the line between fact and dimension starts to blur.

You do it when there's nothing else to describe. Say you're building fct_order_line_items or fct_orders. Customer, product, date and location are already handled by their own dimensions. What's left is the order number.

Why not just build dim_orders?

You could create a dim_orders with the order number in it. That's technically possible, but it would sit at the same or very nearly the same grain as the fact table. You'd be doubling your storage to hold one column that already exists.

Instead you put the order number on the fact, right after the keys, as a descriptive value. Order number and invoice number are the classic cases.

If there's no category, no hierarchy and no other attribute worth storing, it's perfectly acceptable to leave it there.

What leaving it on the fact saves

A dimension where the only real column is the key is wasteful. Leaving it on the fact saves the SQL, saves the storage, and simplifies the model.

What matters here, and the reason for walking through all four, is that this is a thinking exercise. You go through the process as an engineer, talk it through with your team and with the business, and model around what makes sense for you.

A mental shortcut

The four types split into two groups.

Reusing a dimension: role-playing and conformed

It's the same table either way, you're just using it differently.

• Role-playing reuses it inside one fact table, joined two, three or four times.
• Conformed reuses it across multiple fact tables, like dim_customer serving every fact that has a customer on it.

Too small for its own table: junk and degenerate

• Junk combines several low cardinality columns into one table.
• Degenerate puts the single value directly on the fact.

When you hit one of these situations, ask which group you're in. Am I reusing something that exists, or am I deciding where a small attribute belongs? That narrows it to two options, and the examples above usually settle the rest.

Related reading

• The 3 Types of Facts
• The Struggle of Data Modeling (Facts vs Dimensions)
• Do You Need a Date Dimension?
• How to Track History in a Data Warehouse

Final takeaway

These four come up in nearly every star schema I review with a client team. Usually the first two are already in place without anyone naming them, and the second two are the ones that resolve a debate about where some leftover column should go.