LAB: Star + SCD-2 Build on w7-retail (DuckDB)

Outcome: Shipped modeled marts in DuckDB: a star schema plus a working SCD Type-2 dimension with effective_from, effective_to, is_current. Dataset: w7-retail (2k-row CSV, OLTP orders extract — grain: one row per order line item).

1. Brief

Using the w7-retail OLTP extract, (a) declare the grain, (b) build fct_order_lines plus dim_customer / dim_product / dim_date, and (c) implement SCD-2 versioning on dim_customer so address/tier changes keep history. All work runs locally in DuckDB — no cloud account needed.

2. Required deliverables (single .zip)

1. marts.sql — DDL + transforms producing the star (fact + 3 dims).
2. scd2_customer.sql — SCD-2 load for dim_customer (new / changed / expired rows).
3. checks.sql — the deterministic checks from section 4 (must all pass).
4. design.md — grain statement + star-vs-snowflake choice (<= 1 page).

3. Starter template (do not remove the -- CHECK: markers)

-- marts.sql (starter): fill each TODO; keep every '-- CHECK:' line.
CREATE OR REPLACE TABLE dim_customer AS
SELECT
    -- TODO: deterministic surrogate key, e.g. md5(customer_id || '|' || valid_from)
    CAST(NULL AS VARCHAR) AS customer_key, -- CHECK: surrogate-key-present
    customer_id, email, tier
FROM raw_customers; -- CHECK: scd2-columns (effective_from, effective_to, is_current)

CREATE OR REPLACE TABLE fct_order_lines AS
SELECT CAST(NULL AS VARCHAR) AS order_line_key -- CHECK: fact-grain (one row per line)
FROM raw_order_lines;

4. Deterministic structural checks (run before AI review)

• surrogate-key-present: no NULL customer_key; uniqueness = 100%.
• scd2-columns: effective_from, effective_to, is_current exist; exactly one is_current = true row per customer_id.
• fact-grain: COUNT(*) = COUNT(DISTINCT (order_id, line_item_id)).
• referential-integrity: every fact customer_key exists in the dimension.

5. AI-review rubric (plan 32)

0. Dataset Setup

Fetch the w7-retail OLTP extract (2k-row CSV, grain: one row per order line item) into data/w7_retail.csv before starting the lab:

python backend/scripts/fixtures/make_w7_retail_fixture.py
mkdir -p data && cp backend/data/fixtures/w7_retail_orders.csv data/w7_retail.csv

Or generate it inline in Python (stdlib only, seed 42 — see backend/scripts/fixtures/make_w7_retail_fixture.py). Customers 42, 108, 156, 210, 280 carry ground-truth SCD-2 tier/state transitions across distinct months; all other customers hold constant attributes.