Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. What is a role-playing dimension?

Data modeling · Extra High-Value

What is a role-playing dimension?

Mediummodel-27
role-playing dimensiondim_datealiases

Question

What is a role-playing dimension?

Solution

A role-playing dimension is one physical dimension reused in multiple roles via different foreign keys on the fact.

The classic is dim_date:

fct_orders
  order_date_key   --> dim_date  (role: order date)
  ship_date_key    --> dim_date  (role: ship date)
  delivery_date_key--> dim_date  (role: delivery date)

In SQL / BI you alias the same table three times:

FROM fct_orders f
JOIN dim_date order_dt ON f.order_date_key = order_dt.date_key
JOIN dim_date ship_dt  ON f.ship_date_key  = ship_dt.date_key

Other examples

  • dim_airport as origin vs destination
  • dim_employee as seller vs support agent
  • dim_geography as billing vs shipping address

Why it helps

One maintained calendar (fiscal flags, holidays) serves every date role. You do not copy dim_date three times.

Interview tip

> "Same dimension, multiple roles, multiple FK columns, aliases in queries."

PreviousNext