Six Copies, One Question

The weekly commercial review at a second-hand fashion marketplace. The question is the simplest one the company asks: what was premium net GMV last week? Last week is Monday 31 August to Sunday 6 September 2026.

Eleven minutes of argument, an agreement to “get to the bottom of it”, and a ticket closed on Wednesday as could not reproduce — because by Wednesday the dashboard and the warehouse agree again.

Every one of those four numbers is a correct sum of the rows in front of the person saying it. No bug, no failed job, no wrong row, no red test. The warehouse is right and irrelevant: nobody in the room is reading the warehouse. They are reading copies of it, and the copies are of different ages.

Three scheduled jobs sit between the warehouse and the screen, each owned by a different tool and each set by hand at some point:

• 05:30 — the warehouse rebuilds mart_category_daily. A rebuild on the morning of day D contains every complete day up to D−1.
• 03:15 — the BI server refreshes the cat_perf extract from that mart.
• 00:20 — a cache-warming subscription runs every dashboard tile so the first person in does not wait.

The extract refreshes 2 h 15 min before the mart it reads is rebuilt, so it always reads yesterday’s mart — the extract always contains data through D−2. The cache warms three hours before the extract, so warmed tiles are a further day back at D−3, and a reader at 10:04 (inside the 12-hour TTL) gets those.

The jobs fire 00:20 → 03:15 → 05:30. The data has to travel 05:30 → 03:15 → 00:20. Nothing runs late; each job reads the output of the run before last.

What hides it: the dashboard’s freshness stamp reads Last refreshed today at 03:15 — 6 h 49 min old, green, and entirely true. It is the age of the copy, not the age of the data. The two differ by more than a day, every day, and nothing on the screen says so.

Three distinct numbers, because two copies share a vintage. Three people agreeing is not corroboration when all three are reading the same copy.

The frozen CSV: its most recent complete week (17–23 Aug) reads €664 406.50 in the file and €601 786.38 in the warehouse today — +10.41%, because 13 more days of returns have posted against those sales since the export. The file has not changed. The world has.

A copy that is always a day behind is invisible in almost every view: month-to-date loses one day of thirty, a 90-day trend one point of ninety. The weekly view is different, because a week has a boundary. On a Monday, “last complete week” ends on Sunday — the one day the copy does not have. From Tuesday the whole week is inside D−2 and the tile is fine.

Sunday’s share of a year of premium gross GMV, by weekday:

It is worse per category: Outerwear takes 20.55% of its week on Sunday, Suits 11.02%.

Across 358 days, the dashboard’s figure for “last complete week” minus the warehouse’s figure for the same week:

The finding that changes the diagnosis: the same copy, same query and same week produce an error of −16.89% on a Monday and +1.18% on every other day — not a smaller error, an error with the opposite sign, because from Tuesday the copy has all seven days of sales and one day fewer of returns booked against them. That is why it was never reproduced: anyone who checks on Tuesday finds the dashboard slightly high and concludes there is nothing wrong.

The category team keeps a spreadsheet; every Monday at about ten to ten somebody copies the week’s figure off the dashboard into the next row. It is the series the board pack quotes. All 52 rows are therefore missing their Sunday.

• Across the 49 settled weeks the row sits −7.34% below the warehouse, sd only 0.35 pp.
• Rows within 1% of the warehouse: 0 of 52.
• The three most recent rows read −7.11%, −9.71%, −16.42% — the freshest number in the file is 2.24× further from the truth than the settled ones, because its returns have not arrived yet.

The steadiness is two errors of opposite sign settling at a constant. Indexed on the week’s gross premium GMV = 100:

Dropping Sunday costs 17.3% of the week; not yet knowing the returns gives 12.1% of it back. They do not cancel — they settle at 7.34%, and the residue is stable enough that the series looks healthy.

The law. A systematic bias is invisible in every comparison the series makes with itself — growth, trend, ranking, mix, share — and fully present in every comparison it makes with anything else: plan, budget, a contract, last year’s audited figure, another team’s tool. Ratios divide the bias out. Absolutes do not.

Which is exactly where it lands, because the one thing this series is used for is the plan.

In the meeting’s own week, Dresses flips: plan €78 610.22, dashboard €76 726.20 (under by €1 884.02), warehouse €93 422.31 (over by €14 812.09). Seven categories are classified the same way by both numbers; one is not.

A5 is the rung worth staring at: the obvious fix (reconcile and allow slack) fires on 269 of the 306 days when nothing is wrong, because on those days the copy is legitimately ~1.2% high. The real failure and the noise are the same mechanism seen on different days, so no threshold separates them.

A6 works because it compares no two numbers at all — it compares the window the tile claims to show with the dates the copy actually contains. A structural question with a yes/no answer: no threshold to tune, no false positives to live with.

-- A6 in full. One column, one partition, the 10 MB minimum.
declare week_end date default
  date_trunc(current_date(), WEEK(MONDAY)) - 1;

select week_end,
       max(order_date)              as data_through,
       max(order_date) >= week_end  as window_is_complete
from `bi_extracts.cat_perf`;

Both are inside BigQuery’s 1 TiB-per-month free allowance, so the actual bill either way is $0.00. The extract is 1.30× cheaper in bytes and the whole difference is $0.62 a year. Nobody built this to save money; the money was never the reason and never could have been at this volume. The latency was real — the ticket records a p95 tile load of 11.4 s live against 0.6 s on the extract — and it is the only justification that survives arithmetic.

A change that bundles a real reason (latency) with an unmeasured one (cost) gets reviewed on the bundle — Lesson 16’s bundled failure. Here the bundle also carried a third thing nobody named at all: a second copy of the data with its own clock.

The extract was defined, reasonably, as “everything the category team might want to filter on”, so it carries buyer_email_hash, buyer_plz, buyer_id, order_id at order-item grain. In the warehouse those columns are governed. In the extract they are a file.

Erasure runs the other way. Of 204 erasure requests granted in the window, the extract — rebuilt from scratch every night — inherits every deletion within about a day: 1 subject is still in today’s extract, and only because the extract is a day behind on deletions too. The CSV on eleven laptops still holds 7 people erased from the warehouse, and will hold them forever.

The generalisation. The one copy that is accidentally compliant is the one rebuilt from nothing every night. Lesson 14 showed a full refresh undoing a hand-run deletion; here a full refresh performs it. The difference is not the refresh — it is whether the correction lives in the data the pipeline reads. A copy that is never rebuilt can never be corrected, by anyone, for any reason.

Hours from a correction committed at 05:31 on a Monday to the moment each copy carries it (pure schedule arithmetic):

The engineer fixes it in twenty minutes and reports it fixed. The director sees the fix seven days later; the eleven people with the CSV never do. In between, everybody is comparing a corrected number to an uncorrected one and calling it a data quality problem.

The fix ladder

1. Our main dashboard says “last refreshed”. Does it anywhere say data through? What are the two values right now?
2. List the jobs between the source table and the screen, with their times. Are they in the right order, or did each get its time set independently?
3. Is any of them triggered by the thing it reads finishing, rather than by a clock?
4. If I fix a number this morning, when does the weekly pack show the fixed version? Say the date.
5. Which of our numbers are compared to a plan, a contract or last year’s audited figure — rather than to themselves? Those are the only ones where a steady bias costs money.
6. What is in the exports people downloaded last quarter, and who has them?

1. Draw the chain. Most-opened dashboard; every copy between the source table and a human eye (most teams find four to six), with the schedule next to each.
2. Do the arithmetic, not the tooltip. For each hop work out what data_through must be, given that a job reads whatever existed when it started. Compare with what the tool displays.
3. Run the one query. select max(<date column>) from <whatever the dashboard reads>, at 09:00 on a Monday. If it is not yesterday, you have found it.
4. Price the boundary. For your busiest weekly tile, compute what share of the week falls on the day the copy does not have. Ours is 17.30%.
5. Write rung A6 and schedule it thirty minutes before the meeting that reads the number.
6. Reproduce the lesson. The artifact’s appendix carries a self-contained standard-library generator; change extract_through to read the same morning’s mart and watch which figures move and which do not.

The fifteenth failure shape: the detached. The artefact people act on is a copy that was correct when it was taken and is no longer connected to the system that produces it. Every guarantee the platform makes — a test, a masking rule, a correction, an erasure, a definition change — stays true of the table and becomes silently false of the thing being read. No row is wrong, so no predicate over rows can fail; the error is in the vintage, which no copy records and no test can see.

The collection so far: event (06–10) · constant (11) · drift (12) · unwatched (13) · reversible (14) · unreproduced (15) · bundled (16) · transient (17) · extremal (18) · referential (19) · plural (20) · remote (21) · absent (22) · permitted (23) · detached (24). Fourteen is the reversible — a correct fix undone by the next scheduled run. This one is its mirror: the fix is never undone, it simply never travels.

Four sentences to keep

• “Last refreshed” is the age of the copy. “Data through” is the age of the data. Only one is what the reader needs, and it is almost never the one on the screen.
• A copy that is always n periods behind is invisible in every view except the one with a boundary — and the boundary views are the ones with meetings attached.
• A steady bias divides out of every ratio and survives in every absolute. Growth, mix and ranking all look fine while the plan comparison is wrong 13% of the time.
• If a copy is never rebuilt, it can never be corrected. Give every copy either a refresh or an expiry — one of the two, chosen on purpose.

• Extract — a materialised copy of a query result held inside the BI tool (Tableau .hyper, Power BI import mode, a PDT that has left the warehouse). Fast, and outside every control the warehouse has.
• Result cache — reusing the bytes of a previous identical query for a TTL. Cheap and correct, as long as the TTL is shorter than the time it takes the answer to change.
• Cache warming — a scheduled job that runs the dashboard’s queries so the first human does not wait. It fixes latency and pins a vintage.
• Data through / as-of — the newest event date the artefact actually contains. The number that belongs on the screen.
• Last refreshed — when the copy was written. A property of the job, not of the data.
• Lag vs staleness — lag is how far behind the copy is; staleness is whether anyone is told. A constant, declared lag is a design; an undeclared one is this lesson.
• Restatement — a figure changing after publication because late facts (here, returns) attach to an earlier date. Lesson 26’s subject.
• Exposure — a declaration that a named dashboard, export or app consumes a given model; the only way the last mile appears in a lineage graph.
• The last mile — everything between the warehouse and a human decision. Usually owned by nobody, instrumented by nobody, and the only part anyone reads.

A deterministic simulation (modular arithmetic, no random seed, no clock) of 434 days of a second-hand fashion marketplace: eight categories in two price tiers, per-category weekday profiles, seasonality, a steady growth trend, a week-level campaign swing and a day-level wobble, with returns arriving on a fixed 21-day delay profile so the value of any given day keeps falling for three weeks. Money in integer cents throughout. 434 days × 8 categories × 2 tiers = 6 944 cells, 2 288 276 order items.

• The three published answers were re-derived by loading all 2 288 276 items into DuckDB and running the published query once per data_through; all three matched the Python to the cent. The structural check (A6) was re-run in SQL over the calendar and returned 52 fires, 0 of them on a non-Monday.
• Costs use BigQuery’s documented rule — charged on the size of each column’s data type, rounded up, minimum 10 MB per table per query — at the on-demand list price of $6.25/TiB with the first 1 TiB a month free. INT64, FLOAT64 and NUMERIC confirmed at 8/8/16 bytes; DATE and TIMESTAMP taken at 8 and STRING at 2 + UTF-8 length. Wall-clock latency (11.4 s / 0.6 s) is an observation from the scene and is used in no calculation.
• The appendix generator was extracted from the rendered HTML, run in a clean directory, and diffed key-by-key: 18 top-level keys, 1 289 scalar values, 0 differences.
• Three things came out other than planned and were kept: the error changes sign between Monday and the rest of the week; the category ranking does not move at all (0 positions, top three unchanged); and a 1% reconciliation tolerance fires on 269 of 306 clean days, which killed the first draft’s recommended alarm.

-- the same query, three data_through values, three answers
select sum(item_price_cents) as net_gmv_cents
from fct_order_item
where tier = 'premium'
  and order_date between date '2026-08-31' and date '2026-09-06'
  and order_date <= date :data_through                           -- days this copy holds
  and (returned_at is null or returned_at > date :data_through); -- returns it has seen

-- 2026-09-06  warehouse    66,717,750   (the answer)
-- 2026-09-05  extract      55,761,156   (-16.42%)
-- 2026-09-04  warm cache   45,680,220   (-31.53%)

Builds on Lesson 07 (sensors vs schedules), Lesson 10 (the frequency lever), Lesson 13 (count equality is not set equality), Lesson 14 (the reversible), Lesson 16 (the bundled), Lesson 20 (definitions) and Lesson 21 (exposures and lineage).