Cleaned Eight Times

Two slides in one board meeting, both labelled premium-tier GMV, August, both pulled from the warehouse that morning. Board deck: €2,336,146.73. Finance close: €2,319,895.35. Gap €16,251.38 (0.70%) — close enough that the room spends eleven minutes on it and moves on. Neither is right; the data says €2,307,800.01.

Behind both slides: one table, bronze.listing_sold_raw, 69,130 rows over 92 days from three feeds (own app, partner API, wholesaler CSV). No cleaned layer. Every report cleans the raw table on the way past, in its own SQL.

Five rules × eight reports = 27 separate implementations. A price-parser fix must be found in 7 places and added to the eighth.

Feed shares: A 61.4% of rows / 48.6% of GMV / 12.0% premium · B 24.8 / 25.2 / 18.6 · C 13.8 / 26.1 / 45.8 — the messiest feed carries the premium money.

Also: silver removes 1.92% of rows and rewrites the values of 38.2%. Row-count reconciliation between bronze and silver proves almost nothing.

August premium GMV, sorted:

Spread €822,378.78; excluding the catastrophic one, €36,452.22. 7 distinct answers from 8 reports.

Three results worth more than the spread: – Rule count does not predict accuracy. corr(rules implemented, |error|) = −0.203. The 2-rule Exec sheet is 20× more accurate than the 3-rule Ops tile. R1 alone owns 95.1% of the error surface. – The rule that moves no money breaks every breakdown. R3 changes totals by exactly €0.00 and is the difference between 7 and 19 distinct condition values. Two reports return the identical euro figure to the cent while disagreeing about the world. – Nobody is deliberately wrong. Eight competent people fixed what they noticed; the architecture gave them eight places to fix five problems.

Every report band-tested against its own weekly history (weeks of 8 Jun – 26 Jul), scored on the four full weeks of August:

• Ops daily tile (34% wrong): 4/4 — the best score in the company.
• silver (correct): 3/4.
• Marketing tier report (1.25% wrong): 2/4 — worse than the truth.

Mechanism: dropping feed C removed 26.1% of the volume and raised the Ops tile’s relative variance, so its own tolerance band is 1.61× wider (13.96 pp vs 8.69 pp indexed). Being wrong made its monitor less sensitive.

A report can only be tested against its own history — and its history was produced by the same wrong code. A systematic cleaning error is not a change, so nothing that looks for change can see it.

This is the structural break from Lessons 06/08/09/10: those were events (before/after), which every drift, threshold, freshness and anomaly test is built to find. Duplicated cleaning logic produces a constant. Totals reconcile to within 1.6% across the seven non-catastrophic reports — signed off as a rounding difference. So the fix cannot be a test; it has to be that there is only one number to test.

Where each rule belongs: R1, R2, R4 → silver. R3 → silver (translation), but “which grades are sellable” → gold. R5 → silver stamps event_date; gold owns the calendar. “Premium tier” → gold, only gold.

If two reasonable people could disagree about it, it belongs in gold. If they couldn’t, silver. If it hasn’t been decided yet, bronze — untouched.

The wholesaler’s retry path re-serialises the payload: "1.284,19" comes back as "1284.19", " b " as "B". SELECT DISTINCT * therefore collapses 252/252 byte-identical feed-A retries and 0/220 feed-C ones — it catches the duplicates that cost nothing and misses the ones that cost €19,483.92 in August, 80.3% of it inside the premium tier that is 18.2% of rows and 100% of the slide.

Dedup can only run after values are canonical, because until then two records of the same event are not equal. The ordering runs the other way too: quarantine before dedup and the same reject is counted twice — which is why the waterfall uses disjoint buckets (2 rows are both a test seller and an unparseable price; subtract each bucket independently and the arithmetic stops reconciling).

69,130 bronze → −472 dupes → −491 test sellers → −366 quarantined → 67,801 silver → 276 rows in gold.gmv_daily_tier (245.7× smaller; Lesson 10’s lever #1).

Rejects go to silver.listing_sold_quarantine with the failing rule and raw payload — the money is still missing from every report, but visibly, as a number somebody owns. The 190 rows with an unmapped condition code ("AA") keep their (valid) price and point at an UNKNOWN member of the dimension (Lesson 05) rather than being dropped.

Three schemas named bronze/silver/gold with no rule about what may enter each · cleaning in bronze (you can never replay) · business logic in silver (every consumer needs an exception, so they go back to raw) · “just this once” reads of bronze (cleaning site 28) · a silver per team (silver_finance = bronze with better manners) · gold on gold on gold · no quarantine (rejects vanish into WHERE) · no contract at the bronze→silver boundary (Lesson 08 drift walks straight through) · skipping gold (every tile re-derives the metric and re-scans the big table — Lesson 10).

1. Which table does every report read? (More than one name, or a raw table, and you have this problem.)
2. Where is the price parser — the file, not the approach? Count the places. That count is your 27.
3. What happens to a row we cannot parse? What was last month’s quarantined euro total?
4. Do we deduplicate before or after normalising? What does the retry path do to the payload?
5. Name a business definition that lives in silver. (There will be one.)
6. If “premium tier” changed tomorrow, how many objects change? One is the goal.

Run assay.py (stdlib only, deterministic) → reproduce €2,307,800.01 and the seven answers. Load bronze into DuckDB as-is; write exactly two SQL files, silver.sql (all five rules) and gold_gmv_daily_tier.sql — with the rule that gold_*.sql may not contain CAST, CASE, DISTINCT or TEST. Prove notebook = gold mart to the cent. Add the quarantine table (366 rows) and the UNKNOWN member (190). Then break the dedup in silver on purpose and watch exactly €19,483.92 of phantom revenue appear in every downstream number at once — one mistake, one place, one fix.

medallion architecture · bronze / silver / gold · raw zone · conformed vocabulary · grain · business key · quarantine table · unknown member · idempotent dedup · layer contract · single source of truth · cleaning site

All figures from assay.py — standard library, no RNG, no seed, deterministic integer hashing — shipped in full as an appendix inside the artifact. Verified by extracting the source back out of the rendered HTML, running it, and diffing 729 figure keys against the published values: zero differences.