Backfilling a pipeline
A Socratic walk-through of backfilling a pipeline — reasoned out one step at a time, not lectured.
The question we started with
THE QUESTION #Why is re-running yesterday's job harder than running today's?
A pipeline runs every night and has done for a year. Tonight's run is routine — it takes eleven minutes and nobody thinks about it. Then a bug is found in a calculation, and the request comes: re-run the last ninety days with the fix.
The instinct is that this should be ninety routine runs. It is the same code, the same job, just pointed at older dates. And yet everyone with pipeline experience winces at the request, and they are right to. Something about running yesterday's job today is genuinely different from running today's job today. What is it?
Reasoning it through
REASONING #Try to say precisely what a nightly run is. It reads some inputs, computes, and writes an output for a date. Now ask: when it ran a year ago, was it reading the same inputs it would read now?
Consider a job that joins order events against a customer table to attribute revenue by region. The order events for last March are still last March's — they are historical facts. But the customer table is not historical; it is current. A customer who was in the Berlin region last March may be recorded in the Munich region now. So a re-run of last March's job today reads today's customer table and produces a different answer from the one it produced then — not because of the bug fix, but because the world moved underneath a dimension that has no memory.
That is the first thing that makes a backfill hard, and it is not a bug in the job. It is a property of the inputs: some are frozen, some are live, and the job's code makes no distinction between them.
Now a second question. When tonight's job writes its output, what does it do with what was already there? For a normal run, nothing was there. For a backfill, the answer is already present and possibly already consumed. If the job appends, you now have last March counted twice. If it overwrites the whole table, you have destroyed the other eighty-nine days you were not re-running. The only safe answer is that the job must replace exactly the slice it owns — delete-and-insert for that partition, or an atomic partition swap. A job that only ever ran forwards was never forced to have that property, so it usually does not.
Third: does the job depend on anything about when it runs? A job that calls the equivalent of "today" anywhere — to name an output, to bound a query, to compute an age — produces different results when replayed, because "today" has moved. This is why orchestrators such as Airflow hand each run a logical date rather than letting the code look at the clock, and why using the wall clock inside a task is treated as an outright error rather than a style preference.
Fourth, and easy to miss: the ordering assumptions. A job that maintains a running balance by adding today's change to yesterday's total cannot be re-run for a single day in the middle. Fixing March the fifteenth means every day after it is now wrong. The re-run is not ninety independent jobs; it is a chain, and its length is the whole tail.
Finally, the mundane one that causes the outage: ninety days at once is not that load spread over ninety nights, it is all of it now, competing with tonight's production run.
Put those together and the answer is that a forward-running pipeline is quietly allowed to depend on the present — on current dimensions, on an empty output, on the clock, on its own previous run. Running forwards, those dependencies are always satisfied, so they never announce themselves. A backfill is the first time the pipeline is asked to be a function of its inputs rather than a sequence of events, and every place where it is not one becomes visible at the same moment.
The analogy
THE ANALOGY #Think of a ship's log versus a diary written from memory. Each night at sea the officer records position, weather and heading — and because the entry is written in the moment, it captures what was true then. Now ask that officer, a year later, to rewrite the entries for last March because a compass error has been discovered. The heading correction is straightforward arithmetic. But the crew list has changed, the ports have been renamed, the officer's memory of the weather is now yesterday's memory, and if any entry read "three days out from the last port", correcting one page silently invalidates every page after it. The rewrite is not the same act as the original writing, even with the same pen and the same format.
the ship's officer cannot recover last March's crew list at all, whereas a data platform can — if it kept slowly changing dimensions with validity dates, the historical join is recoverable, which is precisely why that design exists.
Clarifying the model
THE MODEL #The most useful correction is that a backfill is not an operation you perform; it is a capability you either built in or did not. The properties that make it possible — idempotent partition-scoped writes, no wall-clock reads, historically-resolvable dimensions, no dependence on the previous run — are all decisions taken while writing the forward-running job, when there is no visible reason to take them. The cost of omitting them is invisible for a year and then arrives all at once, under time pressure.
Two smaller refinements. First, "just re-run it" hides a genuine decision about which historical answer you want. Recomputing last March against today's customer regions gives you a restated history; recomputing it against the regions as they stood then gives you a corrected original. Both are legitimate, they are different numbers, and nobody downstream can tell which they were given unless someone says.
Second, backfills are not only for bug fixes: adding a column, onboarding a source with history, or changing a definition all produce the same request. Treating it as an exceptional event rather than a routine one is part of why it stays fragile.
A picture of it
THE PICTURE #How to readEach box is the condition of one date's partition, not of the pipeline. The spine down to Complete is the routine forward path everybody plans for. The interesting move is Complete to Stale: nothing about that partition changed, only the world around it did, which is why staleness arrives without any run failing. From Stale, the backfill re-enters a state the partition has already occupied, and the two edges into Corrupt name the two ways it goes wrong — both properties the forward path never had to possess. The note is why a backfill is rarely one partition.
What became clearer
WHAT CLEARED #Running forwards, a pipeline is allowed to be a sequence of events, each quietly leaning on the present moment. Running backwards, it must be a function — same inputs, same output, regardless of when it is invoked. Backfilling is hard because it is the first request that tests whether the job was ever that, and the properties it needs cost almost nothing to build in and a great deal to retrofit.
Where to go next
ONWARD #- How slowly changing dimensions with validity intervals make a historical join reproducible.
- What atomic partition replacement looks like in different table formats.
- How to bound a large backfill so it does not starve the production schedule.
Key terms
TERMS #| Term | What it means |
|---|---|
| Backfill | re-running a pipeline over historical periods to produce or correct their outputs. |
| Idempotency | the property that running a job twice for the same period leaves the same result as running it once. |
| Partition | the slice of an output a single run owns, usually a date, which is what a safe run replaces in whole. |
| Logical date | the period a run is for, supplied by the orchestrator, as distinct from the wall-clock time it executes. |
| Slowly changing dimension | a reference table that records validity intervals so an attribute can be resolved as it stood at a past date. |
Every term the collection defines is gathered in the glossary.