Conformed Dimensions & Mart Design

Conformed dimensions

A conformed dimension is the same dimension definition reused across multiple fact tables and marts — same columns, same surrogate keys, same business rules.

dim_customer (conformed) / \ fct_orders fct_support_tickets

Why it matters:

• Executives compare metrics across departments without conflicting definitions of "customer"
• ETL runs once; many marts consume the same dim_customer
• Interview signal: you think in enterprise bus architecture, not one-off tables

How to conform in practice:

1. Build dim_customer in a shared schema or dbt models/core/
2. Enforce single owner team (often analytics engineering or platform DE)
3. Version changes via data contracts — downstream marts break if grain or keys change

Link to Data Quality & Governance for contracts and lineage.

Role-playing dimensions

One physical dimension table referenced multiple times in a fact with different semantic roles.

In dbt, use ref() with aliases or separate views (dim_date_order, dim_date_ship) over the same base table.

Fact table types (Kimball)

Type	Grain	Example
Transaction	One business event	fct_clicks, fct_payments
Periodic snapshot	Entity state at regular intervals	fct_inventory_daily (semi-additive qty)
Accumulating snapshot	Pipeline with milestones	fct_order_fulfillment (ordered → shipped → delivered dates on one row)

Pick the type from how the business asks questions:

• "How many orders yesterday?" → transaction fact
• "What was inventory on hand each night?" → periodic snapshot
• "Average days from order to delivery?" → accumulating snapshot

Bridge tables (many-to-many)

When a fact grain doesn't resolve the many-to-many — e.g. students and classes — use a bridge between fact and dimension:

fct_attendance → bridge_student_class → dim_student → dim_class

Bridge rows carry allocation weights when aggregating (e.g. split credit 50/50 across two classes). Mention this if the interviewer gives a ambiguous grain.

Bus matrix (design tool)

Rows = business processes (facts), columns = conformed dimensions. A checkmark shows which dimensions attach to which facts.

	Date	Customer	Product	Store
Orders	✓	✓	✓	✓
Returns	✓	✓	✓
Web sessions	✓	✓

Use this in interviews to show structured thinking before writing SQL.

Kimball vs Inmon (one paragraph)

	Kimball (dimensional)	Inmon (enterprise warehouse)
Approach	Bottom-up marts, conformed dims	Top-down normalized EDW, then marts
Typical at	Modern cloud warehouses, dbt shops	Legacy enterprise DW teams
Interview	Default answer for DE loops	Name it; explain you still deliver star marts on top

Most FAANG-style lakehouses are Kimball at the gold layer regardless of silver normalization.

SCD: theory → implementation

SCD	Business need	Where in this track
Type 1	Correct current value, no history	Silver merge overwrite
Type 2	Full history of changes	Silver valid_from/valid_to + surrogate key
Type 3	Previous value only	Rare; extra column on dimension

Theory lives here; merge logic and dbt snapshots live in Medallion & Incremental Processing.

dbt mart patterns

models/ core/ — conformed dimensions (dim_customer, dim_date) marts/ finance/ — fct_revenue, fct_refunds product/ — fct_events, fct_sessions

• core models are contracts — tested for uniqueness and referential integrity
• Mart facts ref() core dimensions, never re-derive customer logic inline
• Document grain in schema.yml descriptions — interviewers love explicit grain statements

Link to dbt & the Transform Layer for materializations and incremental facts.

Common interview scenarios

E-commerce revenue mart

• Grain: order line item
• Facts: fct_order_items
• Dimensions: customer, product, date (order), date (ship, role-play), promo
• Pitfall: double-counting revenue when joining to promotions bridge

Subscription SaaS

• Transaction fact for payments
• Periodic snapshot for active_subscribers by day (semi-additive)
• SCD2 on dim_account for plan tier changes

Marketplace (two-sided)

• Separate facts for buyer and seller actions OR shared dim_user with role attribute
• Conformed dim_user prevents duplicate user definitions

Interview answer template

"I'd start with a bus matrix. dim_customer and dim_date are conformed in core. fct_orders is line-item grain with additive revenue. Returns get their own transaction fact sharing the same dimensions. Customer address history is SCD2 in core — facts always join on customer_sk valid at order time (or use current dimension if the question allows). dbt tests enforce grain uniqueness on order_item_id."