Backfills and the Cost of Being Wrong

“Accountants don’t use erasers or they go to jail. All entries in a ledger remain in the ledger.”
— Pat Helland, “Immutability Changes Everything” (2015)

Design every pipeline on the assumption that it will be wrong, and that you will need to correct its history. That means four properties from the start: jobs that can be re-run without side effects, writes that replace a defined slice of data, transforms whose output depends only on their inputs, and raw source data kept long enough to recompute from. Funding these up front is cheap. Retrofitting them after a restatement is not.

The usual reasoning runs the other way. Correctness is treated as a testing problem: catch errors before they land, and the need to rewrite history goes away. Testing matters, but it rests on an assumption that does not hold. Data is wrong for reasons tests cannot see in advance: a definition changes, a source sends corrected records three weeks late, a currency table was wrong upstream. The question is not whether you will need to correct the past. It is how long it will take and who will trust the result.

The same bug, two outcomes

Picture a Series B company that finds a bug in its revenue model. Refunds in one currency were converted at the wrong rate, and monthly recurring revenue has been slightly overstated for four months. Two board decks used the figure.

Fixing the code takes an hour. What happens next depends on decisions made long before.

In one version of this company, the data engineer changes the logic, re-runs the pipeline for the four affected months, and by the afternoon every dashboard and downstream table shows corrected history. The CFO receives a short note explaining the restatement, with the old and new figures side by side.

In the other version, re-running a month appends a second copy of every row. The transformation reads today's exchange rates, so recomputing last June produces a number that was never true in June. The raw refund records were never kept, and the billing API only returns the last 90 days. Someone writes a one-off UPDATE against the reporting table, gets it slightly wrong, and the board deck carries a footnote for a year.

The bug was identical. The cost was not. In the first company it was an afternoon and a memo. In the second it was a week of senior time, a number the finance team no longer trusts, and a conversation with the board that nobody wanted to have.

Re-running is the normal case

Teams tend to treat a backfill as an emergency procedure. It is routine. You will re-run historical data when you fix a bug, when the business changes a definition, when a source delivers late or corrected records, when you add a column and want it populated for the past, and when an upstream outage leaves a gap. Orchestrators build this in for a reason: Apache Airflow's documentation on DAG runs treats catch-up and backfill as first-class operations, not workarounds. A pipeline that can only move forward is a pipeline that can never be corrected.

So the design question for every pipeline is this: if we discovered today that the last six months were wrong, how would we fix them, and how long would it take? If the honest answer is "we couldn't" or "it depends who's around", the pipeline is not finished, however green its tests are.

The case against, heard fairly

The objection from a busy team is reasonable. Re-runnable design takes more thought, more storage and sometimes more compute. Keeping raw data costs money every month. Partitioned, deterministic jobs are slower to write than a script that appends whatever arrived since yesterday. When the company is small and the roadmap is full, why pay now for a correction that may never come?

Two answers. First, the correction will come; the only uncertainty is when. Second, the price of these properties is lowest at the start and rises with every table built on top. Making an append-only job idempotent on day one is an hour of thought. Making it idempotent after three years, with forty downstream models depending on its quirks, is a project. The trade-off is a small, predictable cost now against a large, unpredictable one later, and the later one tends to arrive at the worst moment, in front of a board or an auditor.

What makes a pipeline safe to re-run

None of this is new. Maxime Beauchemin's essay "Functional Data Engineering" made the case for treating batch jobs like pure functions in 2018. What follows is the practical version.

Idempotent jobs. Running a job twice for the same input must leave the same result as running it once. That rules out blind appends. A job either replaces what it owns or merges on a stable key. If a retry after a timeout can double-count revenue, every operational hiccup becomes a data quality incident.

Partitioned writes. Give each run a slice it owns, usually a day or an hour of event time, and have it overwrite that slice completely. Re-running June 14th then means recomputing June 14th and swapping it in, without touching the 13th or the 15th. This is what turns a four-month backfill into a loop over 120 independent, parallel jobs instead of one risky operation on the whole table.

Deterministic transforms. The output should depend only on the input data and the code version. The usual offenders are hidden clocks and mutable lookups. A transform that reads the current date inside its logic, or joins to "the customer's current plan", gives a different answer every time you run it for the same day. Pass the logical date in as a parameter. Join to dimensions as they were at the time, using snapshots or Kimball's type 2 slowly changing dimensions, not as they are now. Exchange rates, price lists and org hierarchies are the classic traps.

Raw inputs you can return to. You can only recompute what you still have. Land source data in raw form, append-only and immutable, before transforming it, and keep it for as long as you might need to restate. Many SaaS APIs forget history and production databases overwrite rows in place. If your only copy of last year is the already-transformed table, you cannot correct a transformation bug. This is the one habit worth insisting on even in a 30-person company's first data platform.

Versioned logic. Keep transformation code in version control and record which version produced which partition. When numbers change, you need to say why: new data or new logic. Code promotion discipline matters here as much as in application code, which is the argument of promoting the same artifact through every environment.

Defensive pipeline code catches bad data on the way in. These properties cover what happens after something got through anyway.

Bug fix or definition change: a leadership call

One distinction belongs with leadership, not the data team. A bug fix should usually restate history: the old number was wrong, and leaving it in place means continuing to report something false. A definition change is different. If "active customer" moves from 30 days of usage to 28, the old series was not wrong; it measured something else. Sometimes you restate, sometimes the new definition starts on a date and both series are kept with a clear break.

Someone with authority over the metric should make that call, and it should be written down alongside the metric itself. When the data team makes it alone, two things happen. Either every change is restated silently and leaders stop recognizing their own trend lines, or nothing is restated and charts accumulate unexplained steps. Both erode the confidence that makes a metric useful, the risk described in how accurate numbers still mislead. Agreeing the rule in advance with the owners of upstream sources, through data contracts, removes most of the argument.

The organizational cost of history you cannot correct

The technical cost is a few bad days for the data team. The organizational cost is larger and slower.

When history cannot be corrected, known errors become permanent. People learn that the warehouse number is "roughly right" and stop relying on it. Finance builds a shadow spreadsheet; sales operations builds another. The trust that lineage and governance are supposed to earn erodes one footnote at a time.

It also freezes the team. If any change to a model can only apply going forward, every improvement creates a discontinuity in the chart, and engineers start avoiding improvements. Fixes pile up as "we'll do it in the next rebuild", which never comes. Each correction that does happen is manual surgery, done under time pressure by the one person who understands the tables, with no review.

We would not accept a code deployment that cannot be rolled back. A pipeline that cannot be re-run carries the same risk, and it belongs in the same conversation about data debt.

What good looks like, and what bad looks like

Good: a backfill is a documented command with a date range. The team knows roughly how long a full rebuild takes and what it costs in compute, and has run one recently. Corrections arrive with a short note to the people who use the numbers. Restatements of published figures follow an agreed approval.

Bad: the backfill procedure is a wiki page last edited two years ago. Re-running needs someone to delete rows by hand first. Nobody is sure whether the source still has the data. The fastest way to fix history is a hand-written UPDATE.

How the call changes by stage

Seed and Series A. The easiest way to be idempotent is to rebuild everything from raw data every night. Full refreshes are deterministic, trivially re-runnable and cheap at small volumes. The one thing to get right from day one is keeping the raw data.

Series B. Volumes grow and full rebuilds become slow or expensive, so teams switch to incremental loads. This is where re-runnability usually breaks: incremental logic that appends, depends on run order or reads "since last run" state. Make partition ownership and deterministic inputs part of the review for every incremental model, and run a full rebuild in a test environment often enough to know it still works.

Later stage. When data drives invoices, financial reporting or customer-facing figures, re-running becomes a governed act. You still need to do it in hours. You also need a change log, an approver and a way to tell the people who already saw the old number.

Ask the team this at your next data or leadership review, without warning: "If we found a bug in the revenue model today, how long until history is correct, and who would approve the restatement?" An answer in hours, with a name attached, means you are fine. An answer that starts with "well, it depends" tells you where to invest next.