Role-playing dimension
A role-playing dimension is a single physical dimension table, most often a date dimension, that is joined into a fact table multiple times to represent different roles, such as order date, ship date, and delivery date.
Overview
Some dimensions are useful in more than one context within the same fact table. A dim_date table might need to represent an order's placement date, its ship date, and its delivery date all at once. Rather than building three separate physical date dimension tables — which would need to be kept in sync and would break the benefits of a conformed dimension — you build one dim_date table and join it into the fact table multiple times, once per foreign key, aliasing each join to give it a distinct role.
Example
Copy code
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY,
full_date DATE,
quarter VARCHAR
);
CREATE TABLE fact_orders (
order_id INTEGER PRIMARY KEY,
order_date_key INTEGER REFERENCES dim_date(date_key),
ship_date_key INTEGER REFERENCES dim_date(date_key),
delivery_date_key INTEGER REFERENCES dim_date(date_key),
revenue DECIMAL(10,2)
);
SELECT
order_date.quarter AS order_quarter,
ship_date.full_date AS ship_date,
delivery_date.full_date AS delivery_date,
f.revenue
FROM fact_orders f
JOIN dim_date AS order_date ON order_date.date_key = f.order_date_key
JOIN dim_date AS ship_date ON ship_date.date_key = f.ship_date_key
JOIN dim_date AS delivery_date ON delivery_date.date_key = f.delivery_date_key;
Each aliased join (order_date, ship_date, delivery_date) is the same underlying dim_date table playing a different role. BI tools typically expose these as separate logical dimensions ("Order Date", "Ship Date") even though there's only one physical table behind them, often via views that pre-alias each role.
DuckDB angle
Role-playing dimensions are handled with ordinary SQL table aliasing, as shown above — DuckDB has no special syntax needed. If a BI tool needs each role exposed as its own named object, you can create lightweight CREATE VIEW ship_date_dim AS SELECT * FROM dim_date style views over the single physical table in DuckDB or MotherDuck, one per role, without duplicating any data.
Related terms
A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.
Conformed dimension →A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.
Degenerate dimension →A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.
Junk dimension →A junk dimension is a single dimension table that groups together several low-cardinality, otherwise-unrelated flags and indicators, to avoid cluttering a fact table with many tiny separate dimension keys.
DATE_TRUNC →DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data.
Star schema →A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.
FAQS
That would require maintaining multiple copies of essentially the same data and would break the conformed-dimension principle of one shared, consistent date table. Aliasing a single dim_date table into multiple joins achieves the same result without duplication.
