Building dimensions

This track mints keys with row_number() OVER (ORDER BY natural_key) - perfect for a full rebuild, because the whole dimension is created in one statement and numbering is deterministic. Production warehouses that load incrementally use the same idea with different machinery:

• Sequences / IDENTITY columns: the database hands out the next integer as each new row arrives - the incremental-load version of row_number(). DuckDB has CREATE SEQUENCE; Snowflake and friends have AUTOINCREMENT.
• Hash keys: some teams hash the natural key instead, so the key is computable without a lookup. Trade-offs galore; know the pattern exists.
• The invariant that matters: whatever mints it, the surrogate key is meaningless. Nobody parses it, nobody predicts it, nothing breaks when the business changes.

Craft means knowing the exceptions. dim_date's key is the date itself - natural, and correct, because a calendar date never changes meaning, never collides, and needs no versions. A tiny static lookup (country codes) can also skip the ceremony. The rule of thumb: surrogate keys for dimensions that describe things that change (customers, products, employees); natural keys where the domain itself is immutable. Decide per dimension, out loud.

generate_series(start, end, INTERVAL 1 DAY) emits every date in the range; the SELECT around it decorates each day with attributes. This is the exact table the star setup has been handing you:

CREATE TABLE dim_date AS
SELECT CAST(gs AS DATE)               AS date_key,
       EXTRACT(year FROM gs)          AS year,
       EXTRACT(month FROM gs)         AS month,
       strftime(gs, '%b')             AS month_name,
       EXTRACT(dow FROM gs)           AS day_of_week,
       EXTRACT(dow FROM gs) IN (0, 6) AS is_weekend
FROM generate_series(DATE '2025-11-01', DATE '2026-06-30',
                     INTERVAL 1 DAY) t(gs);

SELECT * FROM dim_date
WHERE is_weekend
LIMIT 6;

What belongs on it: anything the business says about a day. Year, quarter, month, weekday, weekend - tonight. Fiscal periods, holidays, promo windows - the moment the business defines them, they get a column here and only here.

You could write strftime(order_date, '%b') in every query forever. Three reasons the join beats the function:

• Consistency: one analyst's month starts Monday, another's week is ISO, a third forgets the year boundary. A shared dim_date means one definition, everywhere, forever.
• Business logic lives once: "fiscal year starts in February" is a column update on one table - not a hunt through 200 dashboard queries.
• Joins beat functions for pruning: engines can skip whole chunks of a fact table when you filter on a joined date attribute - but a function wrapped around a column often defeats that skipping. b8 measures this for real.

Conformed dimensions are free tonight (one team, one browser tab). At company scale they cost governance: someone must own dim_customer, arbitrate what "active customer" means, and stop the marketing team from quietly forking their own copy with a friendlier definition. That ownership conversation is the leader track's a3 - this session is its boardroom twin made concrete in SQL.

Finance thinks in quarters, so the calendar should too. Derive it from the month with a CASE (strftime has no quarter code, and writing the logic yourself means you can move the fiscal year-end later):

CREATE TABLE dim_date AS
SELECT CAST(gs AS DATE)               AS date_key,
       EXTRACT(year FROM gs)          AS year,
       EXTRACT(month FROM gs)         AS month,
       strftime(gs, '%b')             AS month_name,
       CASE WHEN EXTRACT(month FROM gs) <= 3 THEN 'Q1'
            WHEN EXTRACT(month FROM gs) <= 6 THEN 'Q2'
            WHEN EXTRACT(month FROM gs) <= 9 THEN 'Q3'
            ELSE 'Q4' END             AS quarter,
       EXTRACT(dow FROM gs) IN (0, 6) AS is_weekend
FROM generate_series(DATE '2025-11-01', DATE '2026-06-30',
                     INTERVAL 1 DAY) t(gs);

SELECT quarter, count(*) AS days,
       min(date_key) AS first_day,
       max(date_key) AS last_day
FROM dim_date
GROUP BY quarter
ORDER BY first_day;

Dimensions earn their keep by carrying derived attributes the business thinks in. Nobody asks "revenue where price is 16.00" - they ask "how do budget items do vs premium". Band the price once, here:

CREATE TABLE dim_product AS
SELECT row_number() OVER (ORDER BY product_id) AS product_key,
       product_id, name, category, price, roast,
       CASE WHEN price < 15 THEN 'under 15'
            WHEN price < 25 THEN '15 to 24'
            ELSE '25 and up' END AS price_band
FROM stg_products;

SELECT price_band, count(*) AS products,
       ROUND(AVG(price), 2) AS avg_price
FROM dim_product
GROUP BY price_band
ORDER BY avg_price;

The scenario: Daybreak acquires a wholesale arm with its own ordering system. Its database also numbers customers from 1 - so both sources contain a customer_id 7, and they are different people. Marketing wants one warehouse, one dim_customer, one set of dashboards. What breaks if you join on customer_id, and what does the conformed dimension have to do instead?

The debrief: joining on the natural key silently merges the two number-sevens into one phantom customer - their orders sum together, their cities fight, and no error is ever thrown. The conformed dim_customer fixes it structurally: each person gets their own surrogate key, and the dimension carries two lineage columns (source_system, source_customer_id) instead of pretending one ID rules both worlds. Staging maps each source's IDs to the shared dimension during loads (the b6 lookup step). This is Part 1's collision escape and Part 3's conformance discipline meeting in one table - and it is why acquisitions are where warehouse teams earn their salary.