What is a role-playing dimension, and how do you join one date dimension three times?

## The situation Almost every interesting fact has more than one date attached. An order line was ordered on one day, promised for another, shipped on a third and paid on a fourth. A claim has an incident date, a filing date and a settlement date. If each of those got its own dimension table you would maintain four copies of the same calendar, and they would drift: someone patches a fiscal-period label in one and forgets the others, and two reports that both say "Q3" disagree. ## The pattern Build one date dimension. Put several foreign keys in the fact table, one per role, and join the same physical table once per key: ```sql CREATE TABLE fact_order_line ( order_date_key INTEGER NOT NULL, ship_date_key INTEGER NOT NULL, due_date_key INTEGER NOT NULL, product_key INTEGER NOT NULL, quantity INTEGER NOT NULL, extended_amount DECIMAL(18,2) NOT NULL ); SELECT o.calendar_year AS order_year, s.calendar_year AS ship_year, SUM(f.extended_amount) AS revenue FROM fact_order_line f JOIN dim_date o ON o.date_key = f.order_date_key JOIN dim_date s ON s.date_key = f.ship_date_key GROUP BY o.calendar_year, s.calendar_year; ``` The table is joined three times; each join is a role. Nothing is duplicated on disk, the calendar is loaded once, and a fix to a holiday flag or a fiscal-period label is instantly correct in every role. ## Why you usually publish views Raw aliases work fine in hand-written SQL but are hostile to everyone else. Three aliased copies of `dim_date` all expose `calendar_year`, `month_name`, `is_holiday`; in a report, on a dashboard or in a shared query, a plain `year` is ambiguous and an analyst can pick the wrong one without any error being raised. The standard remedy is a thin view per role with role-prefixed column names: ```sql CREATE VIEW dim_ship_date AS SELECT date_key AS ship_date_key, full_date AS ship_date, calendar_year AS ship_year, month_name AS ship_month_name, fiscal_quarter AS ship_fiscal_quarter FROM dim_date; ``` A view costs nothing to store, is defined once, and inherits every change to the base table. The naming, not the view mechanism, is the point: `ship_year` cannot be mistaken for `order_year`. The alternative — physically copying the dimension per role — is what you are being tested on rejecting. Copies must be loaded, reconciled and corrected N times, and the first time one of them lags a load the report labelled "by ship month" stops agreeing with the report labelled "by order month" for reasons no one can find. ## It is not only dates Any dimension can play roles. In a sales fact, `dim_employee` can be joined as the salesperson and again as the approving manager. In a flight fact, `dim_airport` plays origin and destination. In a transfer fact, `dim_account` plays source and target. The reasoning is identical: one physical table, one definition of "what an airport is", several foreign keys with role-specific names. ## Where it goes wrong - **The wrong role in a report.** This is the dominant real-world failure. Revenue joined on `ship_date_key` but labelled "revenue by order month" is silently wrong — the numbers are plausible, the total over all time is right, and only the monthly shape is off. Role-prefixed naming is the cheapest defence; a test that reconciles a metric across two roles is the next one. - **Nullable roles.** Not every order has shipped. A null `ship_date_key` breaks an inner join and silently drops rows, so warehouses conventionally reserve a special row in the date dimension (for example key 0, labelled "Not yet shipped" or "Unknown") and point unshipped rows at it. Then the join is total and the missing case is visible as a labelled bucket rather than as absent rows. - **Attributes that differ per role.** Occasionally a role wants an attribute the others do not — a "days late" style calculation belongs on the fact, not in a role view. Keep the role views thin renames of the shared table; the moment a role view starts adding role-specific logic, you have begun forking the dimension. - **Deciding which role is the default.** A mart usually names one role as the primary time axis for its metrics. Decide it explicitly and document it, because half of the "the numbers don't match" tickets come from two teams having assumed different defaults. ## What an interviewer is checking That you say "one table, several joins, one alias or view per role" rather than "four date tables", and that you can name the ambiguity problem the role naming solves. It is a vocabulary shibboleth: the term itself is short, but the follow-up about how unshipped rows join, or which role a metric defaults to, is where judgment shows.