---
title: "Role-playing dimension"
description: "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."
canonical: "https://motherduck.com/glossary/role-playing-dimension/"
related:
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "SHOW roles and membership | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/show-roles/"
  - title: "Data types | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/data-types/"
---

# 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

```sql
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.