Module 5: Model it in SQL · Lesson 5.2 · about 40 min
Dimensions done right: keys, dates and SCD2
Surrogate keys, unknown members, a generated date dimension and SCD2 history, with point-in-time joins and tests that versions cover time with no gaps.
Module 5 · about 40 minutes · read first: 5.1. You’ll be able to give every dimension surrogate keys and an unknown member, generate a date dimension with a fiscal year and a “same weekday last year” column, build an SCD2 dimension from daily snapshots, and test that its versions cover time with no gaps or overlaps.
1. Why this matters
“What was the Classic Burger’s price when this order was placed?” “Which region was store R104 in last quarter, before the reorganisation?” Neither question can be answered from a dimension that simply overwrites old values. History in dimensions is the topic that trips up most people in their first BI job, and it is entirely the pipeline’s job to get right: a dashboard cannot rebuild history that the warehouse threw away.
The Power BI course shows how a report uses a date table in A proper date table. Here you build that table, and the history-keeping dimensions around it, in the warehouse.
2. Surrogate keys and unknown members
- Facts point at dimensions with a surrogate key: a plain number the warehouse makes up (
menu_item_key int), not the code from the source (menu_item_id = 'BB-0042'). Source codes get reused, renamed and merged; your key never changes meaning. - Every dimension has an unknown row (key −1, name “Unknown”) and, if useful, a not applicable row (−2). A fact whose code does not match yet points at −1 instead of being dropped. The data quality report from Lesson 2.6 counts those rows each day.
A surrogate key is like a library’s own barcode on a book: the publisher may reprint the book with a new ISBN, but the barcode on the shelf copy stays the same.
3. The date dimension
One row per date, generated (never loaded from a source), covering every date a fact could have plus a few years ahead.
-- DuckDB 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,
case when month(d) >= 5 then year(d) + 1 else year(d) end as fiscal_year, -- fiscal year starts 1 May
(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);
Then join in a small holiday table you type yourself: public holidays where your stores are, long weekends, local festival weeks. Restaurant and hotel numbers are unreadable without it (“why did Monday drop 30 %?” “It was a holiday.”).
same_weekday_last_year matters because a Saturday should be compared with a Saturday. 364 days is exactly 52 weeks.
4. Slowly changing dimensions
| Type | What happens on a change | Use for |
|---|---|---|
| SCD 0 | Never changes | Original values: date opened |
| SCD 1 | Overwrite the old value | Corrections: a typo in a name |
| SCD 2 | Add a new row with valid_from, valid_to, is_current |
Anything you report history by: price, region, brand, manager |
| SCD 3 | Keep a “previous value” column | One-off reorganisations where people want old and new side by side |
An SCD2 dimension is like a passport history: when you renew, the old passport is not rewritten, it is stamped “valid until” and a new one starts. Any stamp in your travel history belongs to exactly one passport.
Building it from daily snapshots in SQL:
with snap as (
select menu_item_id, snapshot_date, item_name, price,
md5(concat_ws('|', price, category)) as tracked_hash -- only the SCD2 columns
from menu_snapshots
),
changes as (
select *, lag(tracked_hash) over (partition by menu_item_id order by snapshot_date) as prev_hash
from snap
),
starts as (
select * from changes where prev_hash is null or prev_hash <> tracked_hash -- first row or a real change
)
select row_number() over (order by menu_item_id, snapshot_date) as menu_item_key,
menu_item_id, item_name, price,
snapshot_date as valid_from,
coalesce(lead(snapshot_date) over (partition by menu_item_id order by snapshot_date), date '9999-12-31') as valid_to,
lead(snapshot_date) over (partition by menu_item_id order by snapshot_date) is null as is_current
from starts;
(In practice, keep keys stable across rebuilds by building the dimension incrementally or with a dbt snapshot, Lesson 5.4; row_number() here is for learning.)
5. Point-in-time joins, and doing them once
-- the 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
Better still: resolve the right menu_item_key at load time and store it on the fact row. Reports then join on the key alone, which is simple and fast, and nobody can get the date logic wrong in a dashboard.
Maths and principle
Principle: the versions of one natural key must tile time: no gaps, no overlaps.
Equation: for versions of key , sorted by start,
With half-open intervals , every instant belongs to exactly one version.
Analogy: cutting a ribbon into pieces. Every point of the ribbon is on exactly one piece. Closed intervals would put the cut point on two pieces; a gap leaves a bit of ribbon on none (and the facts there become orphans).
Try it: versions [1 Jan, 1 Mar) and [1 Mar, ∞). Which one does an order at 1 March 00:00:00 match? (Only the second.)
- Surrogate key
- A number the warehouse makes up for each dimension row; facts store it instead of the source code.
- Unknown member
- The row with key −1 that catches facts whose code has no match yet, so nothing is dropped.
- SCD2
- On a change, close the current row and add a new one, so history stays as it was.
- Half-open interval
- [valid_from, valid_to): includes the start, excludes the end, so boundaries belong to one side only.
- Physical meaning
- Finance asks why last year's West region revenue dropped in this year's report. With region as SCD1, every store that moved was silently relabelled and history was rewritten. With SCD2, last year still shows the stores where they were, and the reorganisation shows up only from the day it happened.
“Make every column SCD2, just to be safe.” That explodes row counts and confuses people (“why are there three Classic Burgers?”). Ask the business attribute by attribute which ones need history. A typical answer: price, region, brand and manager are SCD2; names and descriptions are SCD1. Keep is_current so a slicer can show only today’s names while facts keep their historical keys.
6. Try it
- Generate
dim_datefor 2024 to 2028 with a fiscal year starting 1 May, ISO week andsame_weekday_last_year. Type a holidays table for two years and join it in. - Write a small Python script that changes six menu items’ prices on random dates and writes daily snapshots
reference/menu_snapshots/<date>.csv. - Build
dim_menu_itemas SCD2 from the snapshots with the SQL above. - Write four tests as queries that must return zero rows: overlapping versions, gaps between versions, items with more or fewer than one current row, and a missing unknown member.
- Resolve
menu_item_keyonfct_order_linesat load time. For one item whose price changed, compare revenue at list price with revenue at actual price paid.
What to check: all four tests return zero rows; an order at the exact change time matches one version; the unknown member exists and the data quality report counts facts pointing at it. Commit as feat(model): date dimension, holidays, SCD2 menu dimension with tests.
New words: surrogate key, natural key, unknown member, date dimension, SCD 0, 1, 2, 3, half-open interval, point-in-time join.
- Facts store surrogate keys; every dimension has an unknown member (−1) so no fact is dropped.
- Generate the date dimension, with fiscal year, holidays and same weekday last year (364 days back).
- SCD1 overwrites, SCD2 adds a row per change with half-open validity, SCD3 keeps a “previous” column.
- Test that SCD2 versions tile time and that exactly one row per item is current. Resolve the version key at load time.
Check yourself
Sources
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, Chapter 5 (slowly changing dimension techniques) and Chapter 3 (the date dimension)
- DuckDB documentation: date functions and generate_series https://duckdb.org/docs/
- dbt documentation: snapshots https://docs.getdbt.com/docs/build/snapshots
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.