The Second Copy

A due-diligence questionnaire asked one line: state the recovery point objective and recovery time objective for the analytics platform, and attach evidence of the most recent successful restore test. The first two parts had answers, from a security-review slide eighteen months old: RPO 24 hours, RTO 8 hours. The third part did not.

The reasoning behind the slide is the reasoning this series has repeated since lesson 02, and it is good reasoning: the warehouse is derived. All 220 models are functions of raw files we keep, so if the warehouse burns down we re-run the functions. The 8 hours came from a real measurement — a dbt build --full-refresh on the staging project in November 2024, which took 6 h 40, rounded up.

So somebody booked three days and ran it.

One of six performed without challenges, and it was the one everybody was already sure about.

mart_gmv was dropped at 09:14 and restored at 09:18 from a timestamp six minutes before the drop. One statement, no data loss, no decision to make.

CREATE OR REPLACE TABLE `bs.mart_gmv` AS
SELECT * FROM `bs.mart_gmv`
  FOR SYSTEM_TIME AS OF TIMESTAMP('2026-09-15 09:08:00 UTC');

Strength. Time travel is excellent at the thing it is for: no configuration, no job, no storage decision, no forecasting of which tables matter.

Area for improvement. This is also the entire content of what most organisations mean by “we have backups”, and it is the scenario that almost never happens. Of the 120 data incidents logged in the trailing year, 37 were found while a correct version was still inside the seven-day window — 30.83% of them, carrying 3.85% of the damage.

Reference (read from the vendor’s own pages). BigQuery time travel: default 7 days, configurable from a minimum of two days to a maximum of seven. Then a further 7 days of fail-safe storage, which you cannot query — “To recover data from fail-safe storage, contact Cloud Customer Care.” A table snapshot can be taken “of a table as it was at any time in the past seven days”, and no further back.

Nothing in the platform computed this, and once computed it turned out to be a different quantity from the one the slide promised.

RPO is a database word: if the system fails now, how much recent work is gone? A derived warehouse has a second, larger quantity with no standard name. Call it the reproducible horizon: the earliest date from which an object can be reconstructed from things that still exist.

It is computable statically, with no data at all. Walk up the lineage graph to every raw table an object descends from; ask how far back each source can still answer; take the latest of those floors, because you need all the inputs, not one. A horizon is a maximum over ancestors — which is why one short-retention source contaminates everything downstream, and a report is downstream of almost everything.

Where the horizon comes from

1,247 days promised ÷ 7 days retained = 178.1×.

The four seven-day windows

The platform’s entire recovery envelope is seven days, in four independent places, and not one of them was chosen:

• Kafka log.retention.hours — DEFAULT_RETENTION_MS = 24 * 7 * 60 * 60 * 1000L (604,800,000 ms). Its size limit, LOG_RETENTION_BYTES_DEFAULT, is -1 — unlimited — so the one that bites is time.
• BigQuery time travel — 7 days by default, and 7 is also the maximum.
• BigQuery table snapshots — can only be taken of the last 7 days.
• Cloud Storage soft delete — “The default retention duration is 7 days and you can customize the retention duration to anywhere between 7 to 90 days.”

Seven days is the right default for an operational mistake, because operational mistakes are noticed in minutes. It has nothing to do with how long it takes to notice a number is wrong.

The horizon is worse for the things people read. The 30 models behind a served object have a median horizon of 7 days and a mean of 48.1; the other 190 have a median of 90 and a mean of 195.9 — 70.0% of the first group is stuck at seven days against 34.7% of the second. A report joins across subject areas, so it inherits the worst retention in the platform.

Not attempted to completion: five of the eight sources cannot supply the period the runbook describes. But the exercise produced a job log, so the 2,006 slot-hours of a full rebuild are measured, and the rest is scheduling arithmetic over the real graph.

• 20.06 h — 100 slots, production paused. Also exactly 2,006 ÷ 100, because at this width the reservation is busy from the first minute to the last and the dependency graph costs nothing. It only begins to bind above 300 slots.
• 69.95 h (2.91 days) — sharing the reservation with the company.
• 3.56 h — the floor at any capacity. Below it the rebuild is waiting for source systems: 2.08 h for one analytics vendor’s export endpoint serving a day per request, 0.51 h for the payments provider at ten paged requests a second.
• 8 h — the runbook. A correct measurement of a staging project holding 4.2% of production’s rows, i.e. of a system 23.8× smaller.

Three levers, measured — two of them lose

A wide thread pool destroys the restore priority. At the configured threads: 16 there are almost always fewer ready models than threads, so everything runnable runs and the queue order is decoration. Turn the pool down to two and the same reordering is worth 15.92 hours. The two settings are one setting, and nobody owns it: threads lives in a profile file and restore priority lives in nobody’s file at all. Fair sharing conserves slot-seconds, so the total never moves in any of the twelve runs.

Selective restore is the one scheduling lever that pays, and it pays less than it should: 127 of 234 objects (54.3%) are ancestors of at least one tier-1 answer, though only 20.6% of the work. There is no version of this platform in which the eight answers the company watches hourly depend on a small, separable part of it — lesson 28’s finding, in the DAG instead of the org chart.

Eleven inputs are not data the company collected; they are decisions the company made, typed in by people. Six are in git and came back in 42 seconds. 5 exist nowhere but inside the warehouse — 23,966 rows, 3.718 MiB — and were not recoverable by any procedure: the erasure ledger (lesson 14), the identity-bridge overrides (lesson 19), the manual price corrections (lesson 14), the wholesale lot allocations and the plan targets (both in a Google Sheet with one owner).

14 of 18 served objects read at least one of them, including 8 of 8 tier-1 answers, and 54 of the 234 objects.

The cheapest data you own is the only data you cannot re-derive. The 32.576 TiB of tables are a function and can be recomputed while the inputs exist. These 4.26 MiB are not a function of anything — 0.0000125% of the platform’s bytes, a ratio of 8,026,535 to one — and a nightly versioned export of all eleven costs $0.36 a year.

A warehouse is rarely lost. It is regularly wrong, and the recovery question is not “where is the copy” but “was there a moment when this table was right, and is that moment still reachable”.

Two things have to be true. First, a correct prior state has to exist: for 38 of the 120 incidents it does not, and no retention setting of any length helps — a constant (lesson 11) was always wrong, a referential error (lesson 19) is wrong in a correspondence no table stores, an unsettled figure (lesson 26) had not finished happening. Those carry 27.12% of the damage.

Second, the moment must still be inside a window. Median detection: 2.65 h when a monitor found it (26 of 120), 17.33 days when a person did (94 of 120). 55.00% of incidents were older than seven days when they surfaced, 44.17% older than fourteen, 21.67% older than thirty-five.

Replication recovers nothing — not a low number, zero, structurally: a replica is byte-faithful by design and propagates a wrong value as promptly and durably as a right one. The ceiling leaves 19 incidents and 15.41% of the damage untouched, because what is missing there was never bytes.

A snapshot can only be taken of the last seven days, so a 35-day snapshot history is not something you can create in an emergency — it is something you either already have or do not.

Rebuilding from source runs today’s code over yesterday’s data. The models have been fixed 19 times in 40.97 months (out of 4,118 merges touching model SQL): vouchers out of GMV, wholesale lots in, returns moved to the refund date, the returns window 14 → 30 days, cancelled orders excluded. Every one was an improvement.

Three things fall out of that table. The 90-day replay is the dangerous one, because it passes: -0.18% clears any materiality threshold while 32 models change underneath it. 6 of the 19 changes move GMV by exactly €0.00 and still change what the tables say, so a money-only check scores them as no-ops. And the error is not monotone in the age of the window — it flips sign at the date one large definitional change landed, so “the further back, the more wrong” is not a law you can lean on.

The practical consequence: no figure this company has published can be reproduced after seven days. Not because the data is gone — because the function has moved. Lesson 26 hit the same wall from the other side, where the inputs were still arriving. The fix is the same in both and it is not a longer retention window: write the answer down, with the ids of the things it was computed from, at the moment it is published.

One accidental mercy: the window the sources can supply is seven days long, and no value-producing merge landed in the last seven days, so the replay that is possible is also exact to the cent. The two failures cancel. This is not a design.

A fair scheduler divides capacity among running jobs (lesson 27), so the rebuild takes a share proportional to how many jobs it submits — sixteen, at the configured threads.

• Overnight, with 6 production jobs running, the rebuild takes 72.7 of 100 slots and the nightly batch runs 3.67× slower.
• In the 08:00–11:00 peak, with 48 jobs running, the rebuild gets 31.2 slots and every interactive query slows by 1.33×.

The rebuild is 2.33× faster at night and 2.76× more disruptive at night. No setting resolves that; it is the same scarcity from two sides. What resolves it is not sharing the pool: a separate autoscaling reservation runs the same rebuild in 5.21 hours and touches production not at all, for $108.33 of slot time.

Every plan that says “we would replay from raw” is also, silently, a plan to run the biggest job in the company’s history during the worst week in the company’s history, in the same pool as everything the company needs in order to understand what is happening to it.

The landing zone is 10.681 GiB a day, 13,319.5 GiB over the platform’s life. The lifecycle rule that deletes it at ninety days was added in a storage-cost review.

Keeping the entire history costs less than keeping ninety days of it. Archive is 16.67× cheaper per byte and this company has 1,247 days of history; 1,247 < 16.67 × 90, so archiving everything and never deleting it is $39.00 a year cheaper than the ninety-day Standard window. The sensible version — thirty days hot, the rest archived — costs $33.36 a year more and takes the replay horizon from 90 days to 1,247.

The rule generalises: archiving all of your history beats keeping a window of it in Standard for as long as your history is shorter than 16.67 times your window — at a ninety-day window, 1,500 days, or 4.11 years. Two caveats: Archive has a 365-day minimum storage duration, and a full replay would incur $649.96 of retrieval at $0.05/GiB, one time.

The whole improvement plan costs $561.04 a year in infrastructure plus about a fortnight of engineering. It was not skipped because of the money. It was skipped because nobody had converted a belief into a number, and a belief has no line in the budget.

The check that would have caught all of this, for nothing

Every retention figure in this report is a configuration value. Not one required reading a row of data, which is why the check costs nothing and why no observability tool sells it: there is no metric to scrape.

-- The alarm: a config read, not a data read. $0.00 a year.
with recursive lineage(served_id, node) as (
        select served_id, "table" from served_tables
    union
        select l.served_id, e.parent
        from   lineage l join edges e on e.child = l.node
)
select   l.served_id,
         max(m.available_from)                                     as rebuildable_from,
         date_diff('day', max(m.available_from), current_date) + 1  as reproducible_days,
         arg_max(m.name, m.available_from)                          as binding_source
from     lineage l
join     models m on m.name = l.node
where    m.layer = 'raw'
group by l.served_id
order by reproducible_days;

Run it in CI. Fail the build when a source’s retention falls below the history the object in front of it claims to serve. It compares no two numbers, so it has no false positives to tune away. Its limit, stated: it tells you the horizon and the moment the horizon changes. It cannot tell you whether the rebuild would produce the same values, and it cannot tell you whether anyone would think to use it. That is what the canary and the exercise are for.

1. “What is the retention of every source we read, and where is that written down?” Not the warehouse’s — the sources’. If the answer needs three teams and a vendor, that is the finding.
2. “Pick our most-watched dashboard. What is the earliest date we could rebuild it from scratch?” A specific date. Computable from the lineage graph in an afternoon.
3. “Which of our tables are the only copy of a human decision?” Erasure ledgers, manual merges, price corrections, plan targets, anything with “override” in it. Ask where copy two is.
4. “How old was the last defect we found, and is that inside our recovery window?” Compare median detection to retention in the same unit.
5. “If we replayed last quarter from raw, would we get last quarter’s numbers?” Somebody will say yes. Ask how many merges changed a value-producing rule in that quarter.
6. “When we do this, whose queries get slower, and for how long?”
7. “When did we last try it?” An untested restore is a belief. Ask for the date, not the runbook.

1. Run the generator (gen.py, published with the artifact): it writes figures.json and eleven CSVs, and every figure in the lesson comes out of it.
2. Load edges.csv, models.csv and served_tables.csv into DuckDB and run the recursive horizon query above. You should get seven days for fourteen objects and ninety for four.
3. Break one source and watch it spread: remove "S3" from raw_customers_cdc‘s source list, re-run, and see how many objects move from 90 days to 7. One topic setting.
4. Count how many models are ancestors of a tier-1 served object. Ours is 127 of 234. Ask whether “restore the important things first” is meaningful at that ratio.
5. Then do it for one real dashboard. List the sources it descends from; find each one’s retention in the broker config, the bucket lifecycle rule, the vendor’s API docs, the backup schedule. Take the shortest. That is your reproducible horizon.

An untested restore is a belief, not a capability. This company held a reasonable, well-argued belief — a derived warehouse does not need backups, it needs its inputs — and the belief was correct in structure and false in fact, because the inputs are kept by seven other systems whose retention nobody had added up. Three days of exercise turned one slide into six numbers, and the expensive one was not the clock: it was that the answer to “can we replay?” is “7 days of 1,247”.

Three quantities, and only one of them has a name:

• RPO — how much recent work is lost. Well defined, and about the operational database.
• RTO — how long until service resumes. Well defined, and here it is not one number but eighteen, because the dependency graph decides who comes back first and was not built with that in mind.
• The reproducible horizon — the earliest date an answer can be reconstructed from things that still exist. No standard name, no tool that computes it, computable statically in an afternoon, and 178.1× away from what the company believed.

The twentieth failure shape: the irrecoverable

Nineteen lessons of failure shapes, every one of them something being wrong. Lesson 23 reached a failure where nothing in the data was wrong; lesson 24 one where no answer was wrong. This is the first shape that is not a defect in the present tense at all. Every row is correct, every test passes, every backup job succeeded last night, nothing is stale or missing or contended. The defect exists only in a future where something is lost, and the unit of failure is a retention window in somebody else’s system.

Its signature is that it is invisible to every instrument by construction, because there is nothing wrong to observe. The eighteen earlier shapes were about a test failing to notice something; lesson 20’s plural was about there being no assertion to write. This one has nothing to assert about: the present state is perfect and the liability is counterfactual. Which makes it the only shape whose sole detector is an exercise — not a query, not a monitor, not a contract. You find it by trying, and the whole of its cost is that trying feels optional right up until it is not.

Vocabulary

• RPO / RTO — recovery point objective (how much data you lose) and recovery time objective (how long until you are serving again). Both assume a copy exists.
• Reproducible horizon — for a derived object, the earliest date from which it can be rebuilt from surviving sources. A maximum over its ancestors’ floors.
• Restore vs replay (re-derivation) — a restore returns the artefact; a replay returns what today’s code makes of the surviving inputs. The same only while the code stands still.
• Time travel / fail-safe — a queryable window into a table’s recent past (BigQuery: 7 days, max 7), then a non-queryable one recoverable only by the vendor’s support (a further 7).
• Object lifecycle management — bucket rules that delete or re-class objects by age. Changes take up to 24 hours to take effect and there is no dry run.
• Storage class / minimum storage duration — Standard, Nearline, Coldline, Archive: 0 / 30 / 90 / 365-day minimums, and a retrieval fee that rises as the storage price falls.
• Critical path — the longest chain of dependencies; the floor on a rebuild no capacity crosses.
• Selective restore — rebuilding a named slice. Useful exactly to the extent that the slice is small, which for tier-1 answers it is not.
• Tabletop vs functional exercise — talking through the plan, versus running it. The first cannot produce any of the numbers in this report.

A teaching scenario. Every number comes from one deterministic generator with no random seed — 27 top-level keys, 1,522 scalar values in figures.json, md5-stable across runs.

1. Retention constants read from source, not blogs. Kafka’s DEFAULT_RETENTION_MS = 24 * 7 * 60 * 60 * 1000L and LOG_RETENTION_BYTES_DEFAULT = -1L curled out of LogConfig.java / ServerLogConfigs.java; the 7-day time travel window and its 2–7 range, the 7-day fail-safe, the 7-day snapshot reach and the 7-day Cloud Storage soft-delete default quoted from the vendor’s own pages, as are the four storage-class prices, minimum durations, retrieval fees and the $0.054 slot-hour. The four-window coincidence is the finding; asserted from memory it would have been worthless.
2. The rebuild’s seconds are scheduling arithmetic over measured work, not a performance model. Durations come from an event-driven simulation of the real graph in which running jobs divide the reservation fairly, each capped at its observed width. No throughput model was fitted; the extract phase enters as explicit stated rates.
3. Two of the three obvious fixes were measured and lost. “Restore the important things first” was planned as the free win; it is worth 0.000 hours at the configured thread width, and the reason is a better lesson than the win would have been. Raising threads moves the total by 0.000 hours across six settings.
4. A round number was checked before it was published. The seven-day replay differs by exactly €0.00 and 0 models — structural, because the most recent value-producing merge is dated 19 Aug 2026. The first draft of the recovery ladder’s last rung came out at exactly 100%, which was an artifact (it credited a replay with fixing incidents whose defect is not in the bytes); corrected, the ceiling is 84.17% / 84.59%.
5. The all-18-at-7-days version was rejected as a strawman. An earlier wiring made every served object bind on the same source — true, but it reads as rigged. Giving two CDC topics a landing-zone archive (because a compliance project happened to ask for one) produced the honest 14/4 spread and a better finding: the only sources with more than a week of history are the ones an unrelated project archived.
6. The cost punchline is arithmetic with a stated condition, which is published with the claim: it stops being true past 4.11 years of history.
7. The rebuild at 100 slots is capacity-bound, and the arithmetic says so twice. 2,006 ÷ 100 = 20.06 h, and the schedule came out at 20.06 h. The first draft claimed the graph made the rebuild slower than the parallel bound; the numbers said otherwise and the claim was deleted.
8. The round-trip diff caught a real non-determinism. Extracting the generator from the rendered page and running it in a clean directory gave six differing values — the name of the source binding an object’s horizon when several bind it on the same date, tie-broken by iterating a Python set, whose string hashing is salted per process (lesson 27’s hash() trap in a new coat). Sorting before the tie-break fixed it; the check now reports 1,522 scalar values compared, 0 differing, generator byte-identical.
9. Damage is counted in object-hours — hours wrong × objects affected × a tier weight — the unit lesson 28 used, so the two ladders are comparable. No euro figure is attached to a wrong answer.
10. The palette was validated before any chart code was written. #1a63b5 / #b4551f / #6b4fa8 / #2e7d4f light, #3d86d9 / #cf7333 / #8a74d4 / #2f9e69 dark: all six checks pass in both themes against each theme’s own surface.
11. The published SQL was re-run. All eleven CSVs loaded into DuckDB and the nine queries this report leans on asserted against figures.json: 132 checks, 132 matched, 0 failed.

Sources read: apache/kafka LogConfig.java and ServerLogConfigs.java · BigQuery time travel, table snapshots and pricing docs · Cloud Storage soft delete, object lifecycle management and pricing docs · the HSEEP after-action report / improvement plan template, for the rating scale and the field names.