Dimension Patterns: Conformed, Role-Playing, Degenerate, Junk

Why conformed dimensions hold a warehouse together

An enterprise warehouse is many fact tables at once: fact_orders, fact_returns, fact_web_sessions. If each one invents its own idea of "customer" or "date", no two marts slice the same way and cross-mart numbers never reconcile. A conformed dimension fixes this: it is one shared table, like a single dim_date or dim_customer, that every fact joins to. Because they all reference the same rows, "revenue by month" from orders and "returns by month" from returns line up exactly. Kimball calls the grid of which facts share which conformed dimensions the bus matrix, and it is the backbone of a coherent warehouse.

Four more patterns show up on that same schema, and interviewers name them directly.

Four patterns on one schema

Role-playing dimension. One physical dimension joined more than once under different meanings. An order has an order date and a ship date. You do not build two date tables. You join the single dim_date twice, once for the order role and once for the ship role, and each alias contributes its own columns.

Degenerate dimension. A dimension attribute with no dimension table. An order_number or invoice_number is a genuine attribute of a sale, but it has nothing to describe (no name, no category), so you store it directly on the fact and skip the dim table. The dimension has degenerated to a bare key sitting on the fact.

Junk dimension. Several low-cardinality flags, like is_gift, is_expedited, and is_first_order, folded into one small table of the distinct combinations that actually occur. The fact then carries a single flag_key instead of three separate boolean columns or three tiny foreign keys.

Factless fact table. A fact with no measures. It records that an event or relationship happened, like "student attended class" or "promotion covered product". There is nothing to sum, so you answer questions with COUNT(*) over the rows.

Worked example: role-playing dim_date

SELECT
  f.order_key,
  order_d.month AS order_month,
  ship_d.month  AS ship_month
FROM fact_orders f
JOIN dim_date order_d ON order_d.date_key = f.order_date_key
JOIN dim_date ship_d  ON ship_d.date_key  = f.ship_date_key;

Order 500 was placed in month 1 and shipped in month 2. The same dim_date supplies both months; the aliases order_d and ship_d are what give the two roles distinct meaning.

Pitfalls

• Duplicating a shared dimension. Building a separate dim_ship_date next to dim_order_date forks one conformed dimension into two. Now a fiscal-calendar fix has to be applied twice and the copies drift apart. Join the single dim_date twice instead.
• A tiny dimension per flag. Giving is_gift, is_expedited, and is_first_order each its own dimension table (dimension explosion) bloats the schema with three one-column joins. Fold them into one junk dimension of the combinations that occur.

Interview nuance: conformed dimensions are what let two independently built marts be compared at all, so when asked "how do you make revenue and returns slice consistently by month", the answer is a single conformed dim_date that both facts join, not two lookalike date tables.