All Checks Passed

A one-line refactor reverts Lesson 09’s as-of join to a natural-key join → fan-out: one sale matches every historical version of its brand. In production fct_sales is incremental, so only the merge-day slice inflated: 744 duplicate sale_ids, 879 extra rows, +12.83% GMV by discovery. But CI’s own --defer build is from-scratch against all history → 16,921 duplicate keys — 22× the production damage — and CI called it green.

Three CI knobs, eight combinations, only 1 catches it — and the 7 misses need three different fixes: – Selection state:modified (4 tests, none a grain test) vs state:modified+ (80 tests, 10 models incl. fct_sales where the unique test lives). Testing the changed model ≠ testing what it breaks. – Test data dev seed (2,303 rows, one version per brand → fan-out cannot occur) vs --defer prod (carries the history that triggers it). – Severity a two-year-old +severity: warn on the fct_ folder → the grain failure is a non-blocking WARN. (56 tests = 25.3% are warn.) Only state:modified+ and deferred-prod and severity:error turns the build RED.

• Composition failure. Total GMV +12.8%, but mass share overstated +7.2pp (15.3%→22.4%) while luxury GMV is identical to the cent (€271,061, and 675,263,437 to the cent over all days) — because no luxury brand was ever demoted, so no phantom luxury row exists. The headline number an exec watches is untouched; the mix that drives merchandising is wrong. Checked, not assumed.
• Both live instruments embarrass themselves. The Lesson 06 band catches it a day late (day 236, min/max envelope hides a +11% day under weekday peaks). The grain test did fire — as one of 14 WARNs on a night whose mean is 12.6±3.1 (z = +0.44; 91 of 235 prior nights had ≥14). A muted test is a log line nobody reads.
• Detection ladder (all rungs work; they differ only in € exposed): P0 data-diff / P1 state:modified+ --defer grain / N2 severity:error (skips 8 marts) all €0 before or at merge; N3 band €13,648 (day 237); X5 daily Stripe reconciliation €6,562 (day 236); D monthly reconciliation €62,826 (day 245 — what happened).

11 constant · 12 drift · 13 unwatched · 14 reversible · 15 unreproduced: passes every test because no test environment reproduces the trigger — here production’s history (an SCD dim with >1 version/key), and every cheap CI environment carries a history-less snapshot. Generalises to any bug needing real scale, skew, duplicate keys or elapsed time. Fix is never “more tests” — it is run the tests you have where the data is shaped like production.

Data-diff in the PR · slim CI state:modified+ --defer · grain tests always severity:error · dev→staging→prod with a real-shaped (historied) staging clone, not a 3-day seed · budget WARNs to near-zero · daily (not monthly) external reconciliation.

If a test cannot see production’s shape, it is a green light wired to nothing.

09 (as-of join / SCD2 dim_brand — this bug reverts it) · 06 (band, alert fatigue → now structural) · 13 (boundary reconciliation vs the payment processor) · 08 (accepted_values, contracts) · 11 (three layers). Visual identity: CI / pull-request build-status page — purple Merged pill, green “All checks have passed” banner, the cube as a truth table, monospace build log; Geist + Geist Mono + Source Serif 4.