Week 5 · Model it in SQL › Day 30

Day 30. Dimensions done right (keys, dates, SCD2)

1. Why this matters

"What was the Double Stack's price when this order happened?", "Which region was store R104 in last quarter, before the re-org?". Answering those correctly needs history in dimensions, the topic that most trips up developers in their first BI job.

2. Surrogate keys and unknown members

• Facts reference dimensions by a surrogate key (menu_item_key int), not the natural code.
• Every dimension has an unknown row (key -1, name "Unknown") and optionally not applicable (-2). Facts whose code doesn't match yet point to -1 instead of being dropped, and the DQ report counts them.

3. The date dimension

One row per date, generated (never loaded from a source), covering every date facts could have plus a few years ahead.

-- DuckDB / Postgres flavour
create table dim_date as
select d::date as date_key, year(d) as year, quarter(d) as quarter, month(d) as month,
       strftime(d, '%b') as month_name, isodow(d) as iso_dow, strftime(d, '%a') as dow_name,
       week(d) as iso_week, isodow(d) in (6, 7) as is_weekend,
       -- fiscal year starting 1 May (set per company)
       case when month(d) >= 5 then year(d) + 1 else year(d) end as fiscal_year,
       (d - interval 364 day)::date as same_weekday_last_year              -- 52 weeks back: same weekday
from generate_series(date '2024-01-01', date '2028-12-31', interval 1 day) t(d);

Add a holiday table (Ontario statutory holidays, long weekends, local events like festival weeks) and join it in. Hospitality and QSR performance is unreadable without it.

4. Slowly changing dimensions

-- point-in-time join: the menu item version valid when the order happened
select f.order_id, f.business_date, m.item_name, m.price as list_price_at_order
from fct_order_lines f
join dim_menu_item m
  on m.menu_item_id = f.menu_item_id
 and f.order_ts >= m.valid_from and f.order_ts < m.valid_to;      -- half-open interval

Better: resolve the right menu_item_key at load time and store it on the fact. Then reports join on the key only, and they're simple and fast.