All writingData engineering · 7 min read

A pipeline is only as good as its worst backfill

Daily runs prove almost nothing. The real test is the morning you find six weeks of wrong data and have to rebuild history while everyone is still reading it.

Apache IcebergdbtDagsterorchestrationDatabricksData engineeringFintechHealthcareSaaS

The pipeline had been green for eleven months. Then someone in finance noticed that a currency conversion had been applied twice for a subset of transactions since a config change in March, and the question became: how do we fix six weeks of history while the business keeps reading these tables every hour?

This is the moment that separates pipelines that were designed from pipelines that merely accumulated. Daily incremental runs are the easy case. Every mature data platform eventually faces a reprocessing event, and the shape of that event is decided long before it happens, by choices that looked like implementation details at the time.

We see this often enough in the Week 1 audit that it has become a standing question. Not "does the pipeline run", but "show me the last time you reprocessed history, and tell me what broke". The answer is usually a story about a weekend.

Idempotency is not a nice-to-have, it is the whole design

A pipeline step is idempotent when running it twice produces the same result as running it once. That sounds obvious until you look at what most production code actually does. An INSERT with no key discipline is not idempotent. A step that reads "everything since the last watermark" and then advances the watermark as a side effect is not idempotent, because rerunning it does nothing at all. A step that computes a running total by adding to yesterday's value is emphatically not idempotent, and it is also unrecoverable, because the error compounds forward and you cannot rebuild any single day in isolation.

The test is simple and worth applying to every step you own. If I run this task for 14 March, twice, with the same inputs, do I get one correct partition or two overlapping copies? If the answer is not obviously the first one, you do not have a backfill strategy. You have a hope.

The partition is the unit of recovery

Whatever you can rerun independently is your unit of recovery, and in nearly every case that should be a time partition, usually a day. This has consequences that reach back into how you write the transformation. A daily partition can only be rebuilt in isolation if the logic for that day depends solely on data belonging to that day, plus reference data that is either stable or versioned.

The two things that quietly destroy this property are late-arriving facts and mutable dimensions. If an event timestamped 14 March lands in your system on 22 March, then rebuilding the 14 March partition on 20 March and again on 25 March produces two different answers, and both are correct as of their run time. That is fine, as long as you decided it deliberately and the downstream consumers know which convention they are reading. It is not fine when it surfaces during an incident and nobody can say which number is the real one.

Dimensions are the sharper edge. If a customer moved from the Enterprise tier to Mid-Market in June, and your dimension table simply overwrites the tier, then backfilling January today will attribute January revenue to Mid-Market. The pipeline will run cleanly. The numbers will be wrong, and wrong in a way that nobody catches for a quarter. This is the entire argument for slowly changing dimensions of type 2, and it is why we treat them as a backfill requirement rather than a modelling nicety. A type 2 dimension carries valid_from and valid_to on each version of the row, so a historical rerun can join to the version that was true at the time rather than the version that is true now.

Two timelines of the same rebuild. In the first, an overwritten dimension holds one Mid-Market row covering all of history, and January facts join to it, so January revenue is attributed to Mid-Market. In the second, a type 2 dimension holds an Enterprise version valid to June and a Mid-Market version valid from June, and January facts join to the Enterprise version.
Nothing about the facts changed between these two reruns. The only difference is whether the dimension remembers what was true in January.

A backfill does not only reprocess your facts. It re-joins them to a present-day world, and if your dimensions have no memory, the past gets rewritten in the image of today.

The pattern, stated plainly

Append-only makes you brave

There is a reason the strongest pipelines we work on keep an immutable raw layer. Land the source data exactly as received, partitioned by ingestion time, and never update it. Every transformation downstream reads from that layer and writes somewhere else.

The value shows up precisely in the bad morning. If the raw layer is intact, a logic error is a compute problem, and compute is cheap relative to a lost quarter of history. You fix the transformation, rerun the affected partitions, and the truth is recoverable because you never destroyed the inputs. If instead your pipeline transforms on ingest and keeps only the result, then a bug in that transformation has silently deleted evidence, and the only remedy is to ask the source system for six weeks of history, which in the case of an API with a 30 day retention window is a conversation that ends badly.

  • Raw, append-only Source payloads as received, partitioned by ingestion time, never mutated. This is your insurance policy.
  • Cleaned and conformed Typed, deduplicated, keys resolved. Fully derivable from raw, therefore fully rebuildable.
  • Modelled marts Business logic, aggregations, the tables people actually query. The most likely place for the bug, and the easiest layer to rebuild if the two below it are honest.
A three-layer diagram showing raw append-only partitions at the bottom feeding a cleaned layer feeding marts, with a backfill arrow re-running only the affected date range through the upper two layers while the raw layer stays untouched
A backfill should touch the derived layers only. If it has to touch raw, the raw layer was never raw.

What snapshot isolation actually buys you

The hardest part of a large backfill is not the reprocessing. It is that the business is still reading the table while you do it. Rebuild forty partitions sequentially against a plain directory of files and, for the hour that takes, every dashboard query sees a table that is part old and part new. Someone screenshots a number mid-rebuild and you will be explaining it in a meeting next week.

This is the concrete argument for a table format with atomic commits. Apache Iceberg, and the equivalent transactional layer under Databricks, maintain a metadata pointer to the current snapshot of the table. Readers resolve that pointer once at query start and read a consistent set of files. A writer can stage a rewrite of many partitions and commit them in a single atomic swap of the pointer, so readers see either the entire old state or the entire new state, never a mixture. Time travel falls out of the same mechanism: the old snapshots remain addressable, which means you can compare before and after, and you can roll back if the backfill itself was wrong.

A strip of forty partitions being rewritten in place, with the boundary between the rebuilt partitions and the ones not yet rebuilt sitting part way along, so any query during the rebuild reads part old and part new. Below it, the same strip after a single commit, where every partition changes at once and the old snapshot stays addressable.
The hour of the rebuild is the exposure. An atomic commit removes the mixed state rather than shortening it.

How to actually run one

Sequencing matters more than throughput. The instinct is to blast every partition in parallel and finish fast, which maximises the window in which downstream tables disagree with their sources and tends to saturate the warehouse that everyone else is trying to use.

  1. 01
    Reproduce on one partition firstPick a single day where you can point to a row you know is wrong. Rebuild it in a separate schema. If you cannot explain the delta on one day, you do not yet understand the bug and a forty-day rerun will simply produce forty days of a different wrongness.
  2. 02
    Bound the blast radius explicitlyWrite down the exact date range and the exact set of tables. Use lineage to find every downstream model that depends on them, including the ones owned by another team who will not hear about this otherwise.
  3. 03
    Rebuild upstream to downstream, in orderReprocess the cleaned layer for the range, verify it, then the marts, then the aggregates. Rebuilding a mart against an uncorrected upstream just bakes the bug in again with a fresh timestamp.
  4. 04
    Commit visibly, onceStage the corrected partitions and swap them in atomically. Where the platform cannot do that, build into a shadow table and rename. Half-rebuilt is the state you are paying to avoid.
  5. 05
    Reconcile against something outside the pipelineRow counts prove very little. Compare a handful of aggregate totals against the source system, or against a finance-owned number, for both the corrected range and a control period you did not touch. The control period is what tells you the fix did not spread.
  6. 06
    Tell people, in writing, before they find outWhich tables, which dates, what changed, and which saved reports will now show different figures. A number that moves without explanation costs more trust than the original bug did.
The sequence we use for a production reprocessing event, in roughly this order every time.

The orchestration layer should make most of this boring. Partition-aware orchestrators, Dagster among them, model each partition as an addressable thing with its own materialisation state, so a backfill is a first-class operation with a visible progress record rather than a shell loop someone runs from a laptop and then has to babysit. That last detail matters more than it sounds. A backfill run from a terminal session has no record, no retry semantics, and no way for the next person to know it happened.

Where this advice is wrong

Not every pipeline deserves this. If you are moving a few million rows a day into a single warehouse and the business tolerates a morning of stale figures, then a full rebuild from source is simpler, cheaper to reason about, and removes the entire class of problems described above. Full refresh is an excellent strategy right up until it is not, and the honest answer for a lot of teams is that they adopted incremental processing for elegance rather than because a rebuild had actually stopped fitting in the window. Measure it before you commit to the harder design.

The reverse case is worth naming too. In regulated settings, particularly fintech and healthcare, silently correcting history may be the wrong move regardless of how clean your mechanics are. If a figure was reported to a regulator or shown on a patient record, the requirement is often to record a correction alongside the original rather than to make the original disappear. That is a compliance decision, not an engineering one, and it needs to be asked before the rebuild rather than after.

The general principle holds anyway. Assume you will one day discover that six weeks of output was wrong, and ask what your current design would force you to do about it. If the honest answer involves a weekend, a spreadsheet, and a conversation about what the numbers used to say, the work to fix that is cheaper now than it will be in the middle of the incident.

Find out what your worst backfill would cost you

The Week 1 audit takes two calls and a fixed fee. We trace one pipeline end to end and tell you plainly which steps could be rerun safely today, which would produce a different answer than they did last month, and what it would take to make a six-week correction a routine operation rather than a weekend. You keep the one-pager whether or not we go further.

Contact us →More writing →