Dimensions — Conformed, Junk, and Role-Playing

Beyond the basic dimension table, three Kimball-named patterns recur in every serious warehouse. If you know them, you'll spot them — and design with them when needed.

Conformed dimensions

A conformed dimension is a dimension shared across multiple facts. Every fact joining to it uses the same dimension table and gets the same definitions.

Example: dim_customer is used by fct_orders, fct_returns, fct_subscriptions, fct_support_tickets. All four can be sliced by the same customer attributes — country, segment, signup_date — and the answers will line up.

fct_orders ──join──┐
                   │
fct_returns ──join─┼─→  dim_customer
                   │
fct_support_tickets┘

Why this matters:

• Cross-fact comparison — "orders vs returns by customer segment" assumes both facts agree on what customer.segment means.
• Single source of truth — if marketing and finance disagree on customer segmentation, they're not arguing about facts but about the conformed dimension definition. That's a much shorter argument.
• Schema clarity — analysts learn one customer model and reuse it everywhere.

The opposite anti-pattern: each fact has its own copy of customer attributes. Marketing's fact has customer_tier; finance's has customer_grade. Both are computed from the same raw data, both disagree. Conformed dimensions prevent this.

Junk dimensions

Junk dimensions consolidate small, low-cardinality flags into one dimension instead of polluting the fact with many tiny columns.

Without a junk dimension:

fct_orders:
    order_id, ..., is_gift, gift_wrapping, is_express_delivery,
    is_first_order, is_corporate_purchase, payment_method, ...

A dozen boolean/short-string columns. Each is its own column on every row.

With a junk dimension:

fct_orders:
    order_id, ..., order_flags_key  -- FK to dim_order_flags

dim_order_flags:
    order_flags_key, is_gift, gift_wrapping, is_express_delivery,
    is_first_order, is_corporate_purchase, payment_method
    (one row per unique combination of flag values — typically a small table)

Most facts use 50-200 unique combinations of flags out of theoretically thousands. The junk dimension has 200 rows, each with the unique combination. Fact rows reference one key.

Benefits:

• Cleaner fact table.
• Easier to filter ("orders with gift wrap" = dim_order_flags.gift_wrapping = TRUE).
• New flag additions don't require a fact-table schema change.

When NOT to use junk dimensions:

• For high-cardinality categorical fields (don't junk-dim a free-text column).
• When the flags are truly independent and rarely correlate.
• When you have only 1-2 flags and a dedicated dimension feels overkill.

Role-playing dimensions

A role-playing dimension is one dimension reused in multiple roles within the same fact.

Example: dim_date joined to fct_orders three times — as order date, ship date, return date.

SELECT
    od.year, od.month,  -- order date
    sd.year, sd.month,  -- ship date
    rd.year, rd.month   -- return date
FROM fct_orders fo
JOIN dim_date od ON od.date_key = fo.order_date_key
JOIN dim_date sd ON sd.date_key = fo.ship_date_key
JOIN dim_date rd ON rd.date_key = fo.return_date_key;

Same dim_date table, three different join contexts.

Best practice: create database views to make each role explicit:

CREATE VIEW dim_order_date  AS SELECT * FROM dim_date;
CREATE VIEW dim_ship_date   AS SELECT * FROM dim_date;
CREATE VIEW dim_return_date AS SELECT * FROM dim_date;

Now the SQL is clearer:

JOIN dim_order_date  od ON od.date_key = fo.order_date_key
JOIN dim_ship_date   sd ON sd.date_key = fo.ship_date_key
JOIN dim_return_date rd ON rd.date_key = fo.return_date_key

Same data, but BI tools and analysts see three distinct dimensions in the model browser.

Other common role-playing dimensions:

• dim_geography as ship-to, bill-to, and supplier address.
• dim_user as creator, assignee, and reviewer of a task.
• dim_currency as transaction currency and reporting currency.

Degenerate dimensions

A degenerate dimension is a fact-table attribute that would normally be a dimension but has no other attributes — so there's no point creating a separate table.

Examples: order_id, invoice_number, transaction_id.

These belong on the fact row directly, used for traceability and joining to the source system. Don't create dim_order_id with just an order_id column — there's nothing else to put there.

Common dimension design mistakes

1. Per-fact dimensions — each fact has its own customer table. Cross-fact comparison breaks. Use conformed.
2. No junk dimension; 20 flag columns on the fact — wide fact, awkward filters.
3. Role-playing dimensions joined without aliases — query is unreadable. Create views for clarity.
4. Treating a degenerate dimension as a real dimension — wasted table, extra join, no benefit.

Takeaway

Four named patterns: conformed (shared across facts), junk (consolidate flags), role-playing (same dim, different roles), degenerate (fact-only). Recognize them in inherited warehouses; apply them in new designs.