Drill 8 — The Backfill That Rewrote History

Theme: Data Disasters · Format: 20-min drill · Difficulty: core

The story

A well-meaning cleanup normalizes status codes across a core table — retroactively, in place. Historical rows now show the final status semantics, not what was true at the time. Months later, a new model backtests spectacularly and everyone celebrates.

⏸ Stop here and ask the room: connect the cleanup to the celebration.

Run it — facilitator script

Timing: 2 min story · 10 min questions · 3 min reveal · 5 min transfer.

What did the backfill destroy?

Listen for: Point-in-time truth — the record of what was knowable when. The table still answers "what is the status now," but it can no longer answer "what did we know at decision time," and that second question is the one every backtest depends on. Weak signal: "Some old data got changed" — treating it as a values problem on specific rows rather than the loss of a global property (reconstructability of the past) that every downstream consumer relied on.

Why does the new model look so good offline?

Listen for: Label leakage — the normalized statuses encode information that only settled after outcomes, so the backtest quietly hands the model the answer key; a spectacular offline lift that evaporates in production is the classic leakage signature, and should itself be treated as a red flag. Weak signal: "Overfitting" as a catch-all — this isn't a variance problem you fix with regularization; the training data literally contains the future.

How would you even discover this, months later?

Listen for: The offline–online gap as a tripwire — a large backtest win triggers a data-provenance review before anyone presents it; audit logs on writes to historical partitions; diffing old snapshots or backups against current rows to prove the past was edited. Weak signal: "Debug the model harder" — the bug isn't in the model, and no amount of model-side tooling will ever see an edit that happened in the warehouse months earlier.

The reveal

The "cleaned" statuses encode information that only existed after outcomes — every backtest now leaks the future. The model flops in production; tracing the gap back to an undocumented in-place backfill takes a month.

Why this happens — the mechanics

Databases default to mutable: UPDATE is a first-class verb, and nothing distinguishes a table that records history from a table that records current state. Status codes are especially treacherous because their semantics converge over time — an account's status settles into its final meaning only after the outcome resolves. Normalize old rows to today's semantics and you've stamped post-outcome knowledge onto pre-outcome records. Any model trained on those rows is being shown, at training time, information that arrives only after the moment it's supposed to predict.

Organizationally, the backfill looks like virtue, not risk. Inconsistent status codes are visible tech debt; cleaning them is exactly the kind of initiative that gets praised in a sprint review. Code changes go through review, staging, and rollback plans — but a data change of equivalent blast radius often ships as a single SQL statement with no ticket, because change management for data isn't a discipline most teams have built. The engineer who ran it and the team backtesting months later may never meet.

The failure is invisible twice over. First, the edit itself announces nothing — no deploy, no diff, no failed job. Second, and worse, the symptom is good news: the contaminated backtest produces a metrics improvement, and teams investigate disappointments, not celebrations. The month-long trace happens only after production performance forces the question — and by then, every other backtest run against that table in the interim is suspect too.

Diagrams

Problem — how the failure unfolds:

sequenceDiagram
    autonumber
    participant DE as Data engineer
    participant HT as History table
    participant BT as Backtest
    participant PR as Production
    DE->>HT: Cleanup normalizes status<br/>codes in place
    Note over HT: Rows overwritten with final<br/>semantics - point-in-time<br/>truth destroyed (silent)
    BT->>HT: New model backtests<br/>on rewritten history
    HT-->>BT: Statuses that encode<br/>the future (silent)
    Note over BT: Spectacular offline<br/>metrics - team celebrates
    BT->>PR: Model ships
    Note over PR: Model flops - the offline<br/>edge was leaked future
    Note over DE,PR: Month-long trace back to an<br/>undocumented backfill

Solution — where the guardrails go:

flowchart TD
    A["Cleanup normalizes status<br/>codes"] --> B["Attempted UPDATE on<br/>historical table"]
    B --> C["Append-only store rejects<br/>the edit"]
    C --> D["Correction recorded as new<br/>event with its own<br/>timestamp"]
    D --> E["Backtests reconstruct<br/>truth as of decision time"]
    E --> F["Leak-free offline metrics"]
    B -.-> G["Alarm fires: write to<br/>history attempted"]

What good looks like

Revoke routine UPDATE and DELETE rights on historical fact tables; the exception path is break-glass access that fires an audit alarm every time it's used.
Corrections are appends: a new event with its own timestamp, a reason code, and a pointer to the row it corrects — current-state views resolve "latest," history stays intact.
Bitemporal design on decision-critical tables: store both valid time (when it was true) and transaction time (when we recorded it), so any query can be replayed as of any moment.
Training sets and backtests built only through a point-in-time join framework — never ad hoc SQL against live tables.
An automated alarm on any write touching partitions older than N days (start with N = 7), routed to the data-platform owner.
A backtest sanity gate: offline lift above a set threshold (say, five points of AUC — Area Under the Curve, a standard ranking-quality metric) requires a leakage review before the result is presented anywhere.
Data changes ride the same change management as code: ticket, review, rollback plan, and an announcement to downstream consumers.

The lesson

History is append-only: corrections are new events with their own timestamps, never edits. If anyone can UPDATE the past, every backtest in the company is on borrowed time — and data changes deserve the same change management as code deploys.

Named concepts: immutability, event sourcing, point-in-time correctness, change management for data.

Real-world echoes

This class of failure almost always stays private — an in-place backfill produces no headline, only a model that mysteriously flops months later. That's precisely what makes it dangerous: nobody reads about anyone else's version, so every team discovers it from scratch.

Transfer

Who has UPDATE rights on our historical tables? Has anyone used them this quarter — and would we know?

Make it a ticket: pull the actual grants on our three most decision-critical historical tables, list every role with write access, and wire an alarm on writes to closed partitions — due before the next training run.

Drill 10 — The Contaminated Year — the other way the future leaks into training, and what to do once you find it.
Drill 9 — The Merge That Couldn't Be Unmade — destructive writes without lineage, applied to entities instead of history.