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

# LAB: Star + SCD-2 Build on w7-retail (DuckDB, 90 min) > **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) ```sql -- 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) | Criterion | Weight | Pass bar | |---|---|---| | Grain correctly declared and enforced | 25% | checks.sql all green | | Star layout (facts vs dims classified) | 25% | conformed keys, no descriptive text in fact | | SCD-2 history correct (no overwrites) | 30% | re-run with changed tier adds version, keeps old | | Star-vs-snowflake defense | 20% | explicit choice with join-cost reasoning |