Types of Dimensions

Concepts covered: Dimension Tables

A fact_sales row says customer_sk = 8827414, quantity = 2, line_amount = 189.98. dim_customer turns the key into Priya Natarajan in Denver, Gold tier, acquired through a referral in 2023. Every WHERE clause and every GROUP BY an analyst writes lands on a dimension column, so the set of dimensions and their attributes decides which questions the warehouse answers without a rebuild.

Conformed dimensions

A conformed dimension is 1 physical table shared by several fact tables, with 1 set of keys and 1 definition per attribute. dim_customer is referenced by fact_sales, fact_returns and fact_support_tickets, so a cross-process question, such as customers who bought the Cascade 65 pack and opened a ticket within 30 days, is a join through the shared key. Before Ridgeline conformed it, support derived region from the ticket's time zone and sales from the shipping address, and the 2 regional dashboards disagreed by 11 percent.

Role-playing dimensions

A role-playing dimension is 1 physical table referenced more than once by the same fact, each reference in a different role. fact_order_fulfilment carries placed_date_sk, shipped_date_sk and delivered_date_sk, all pointing at dim_date. The table is loaded once; the query aliases it once per role, and the alias is the only thing that differs between orders by placed month and orders by delivered month.

SELECT d_placed.month_name AS placed_month,
       d_deliv.month_name  AS delivered_month,
       COUNT(*)            AS orders
FROM fact_order_fulfilment f
JOIN dim_date d_placed ON d_placed.date_sk = f.placed_date_sk
JOIN dim_date d_deliv  ON d_deliv.date_sk  = f.delivered_date_sk
WHERE d_placed.calendar_year = 2026
GROUP BY 1, 2;

Junk dimensions

An order carries flags that belong to no customer or product: is_gift, is_expedited, gift_wrap and a payment_method with 4 values. Left on the fact they are 4 columns across 840 million rows, and every new flag is an ALTER TABLE on the largest table in the warehouse. A junk dimension collects them: dim_order_flags holds every combination, 2 x 2 x 2 x 4 = 32 rows, and fact_sales carries 1 order_flags_sk as a fifth key. A new boolean flag doubles it to 64 rows and leaves the fact untouched.

Degenerate dimensions

order_id sits on fact_sales with no dimension table behind it. A dim_order would hold 1 column, the id, and joining 840 million rows to it to fetch a value they already carry gains nothing. An identifier with no attributes of its own stays on the fact as a degenerate dimension, used to group line items back into their orders.

The date dimension

dim_date has 1 row per calendar day, 36,525 of them, generated once for 100 years. Its columns are everything a report groups or filters by: day_of_week, month_name, quarter, fiscal_year, fiscal_quarter, is_weekend, is_holiday. Ridgeline's fiscal year begins on 1 February, so a calendar EXTRACT(QUARTER ...) is wrong for every finance report, and dim_date.fiscal_quarter holds the answer once. Before the table existed, 4 teams wrote their own fiscal quarter logic and 2 disagreed about which year 31 January belongs to.

Each new attribute forces the question of where it lives, and the answer follows from what it describes and which attributes travel with it.

Marketing adds a coupon_code to orders. A coupon has a discount_pct, a campaign_name and a validity window, and 2,300 coupons have been issued so far. Where does the coupon belong in the star?

• On fact_sales as a degenerate dimension, since it is an identifier.
• As a fifth column in dim_order_flags.
• A new dim_coupon with 1 row per coupon, and a coupon_sk on fact_sales.
• As columns on dim_customer, recording the coupon each customer used.

About this lesson

This section is part of the Star Schemas lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.