What Backfills Break
Duplicated periods, overwritten current data, a saturated warehouse and a source knocked over by its own history — the four ways a correction becomes an incident.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
A backfill re-ran a range that was already populated. Every task succeeded. What is now wrong, and which check would have said so?
The on-call engineer who will be asked why revenue for last March doubled overnight, and the analyst who has to decide whether the number on their slide is now the old one, the new one, or the sum of both. They need to know what the operation could have touched, not what it was meant to touch.
The blast radius is measured in partitions, not rows: a backfill either replaced a (dataset, period) cell correctly, added a second copy of it, or overwrote a cell it was not asked to touch. Every risk in this lesson is one of those three at some partition.
Assume the risk of a backfill is that it fails. Watch the task status, retry anything red, and treat a fully green run as a completed correction. This is exactly how the operation is monitored in most platforms, and it detects precisely the failure mode that matters least.
The re-run appended. The period is present twice, so every additive measure over it is roughly doubled, and the run was green from end to end — the model in src/de/sim/pipeline.ts reproduces exactly this, and the check that moves is uniqueness, not completeness (Deduplication).
- The re-run appended. The period is present twice, so every additive measure over it is roughly doubled, and the run was green from end to end — the model in
src/de/sim/pipeline.tsreproduces exactly this, and the check that moves is uniqueness, not completeness (Deduplication). - The range included today. The backfill computed the current partition from a snapshot taken hours ago, published it over the complete version the daily run had already written, and the day now looks quiet.
- The backfill ran 180 partitions in parallel against the same warehouse the hourly pipelines use. Nothing failed; everything queued, and the freshness SLO on six unrelated datasets was missed (The Freshness SLO).
- The recompute re-extracted from the operational database because the raw layer did not go back far enough, and six months of full-table scans against a production replica caused replication lag and an application incident (Workload Isolation).
- A dependent mart was not rebuilt, so the fact table now holds corrected numbers and the mart holds the old ones. Two dashboards showing "revenue" disagree, and the one with the wrong number is the one the executive uses (Data Marts).
- The backfill was run twice because the first attempt looked like it had failed. It had not; it had timed out on reporting. The range is now present three times.
What is actually happening
- An append and a replace are the same shape of write to an orchestrator and opposite operations to the data. The orchestrator observes a process that exited zero; it has no concept of what was already in the target (The Pipeline Succeeded. The Data Is Wrong.).
- Duplication from a backfill is invisible to a completeness check, because nothing is missing, and invisible to a freshness check, because everything is new. It moves uniqueness on the business key and it moves distribution, and if neither test exists it moves nothing anyone is watching (Data Tests).
- The current partition is dangerous because it is the only one that is still changing. Every other partition in a historical range is closed; today is being written by another process on another schedule, and two writers with no coordination is the whole problem (Atomic Publish).
- The compute risk is a queueing effect rather than a failure. A backfill submits months of work into a system whose concurrency was sized for a day, and the visible symptom lands on whatever pipeline was unlucky enough to be scheduled during it (Queueing: Why Systems Get Slow Before They Get Broken in Observability covers why the damage is non-linear).
- The source risk exists because a backfill is the one operation that reads history at full width. Extracting six months from an operational database is a different workload from the incremental extract that normally runs, and the source has never been sized for it (OLTP vs OLAP).
- Every one of these is a consequence of the same missing property: the pipeline is not idempotent, and re-running is therefore not a repair but a second execution with its own effects (Idempotent Data Pipelines).
Four ways a green backfill leaves you worse off
Each row below is a real operational failure with a specific trigger, a specific symptom and a specific first thing to check. What they share is that the orchestrator reported success in every case, so none of them starts with an alert — they start with a person.
Read the cause column carefully. In three of four rows the cause is a property the pipeline never had rather than a mistake made during the run. That is why the response for those rows is a design change rather than a corrective action: you cannot carefully append your way out of not being idempotent.
The fourth row is different in kind. Compute saturation is not a data correctness problem at all; it is a capacity problem that a data operation caused, and it is included because it is the one that pages someone at three in the morning while the backfill is still going fine.
| Trigger | Symptom | Cause | Response |
|---|---|---|---|
A populated range is re-run by a pipeline that writes with INSERT. | Every additive measure over the range is inflated; row counts per partition have roughly doubled; the shape of the series is unchanged, which makes it look like growth. | The write is not idempotent. Re-running is not a repair — it is a second execution whose effects add to the first (Idempotent Data Pipelines). | Roll back to the pre-backfill snapshot if the format has one. Then convert the write to a merge on the business key or a partition replacement before attempting the range again (Upserts and Merges). |
| The backfill range is specified as "from March until now". | Today's partition shrinks. The current day looks like a slow day and is investigated as a business question a week later. | The current partition is being written concurrently by the scheduled pipeline. The backfill computed it from an older snapshot and published over a more complete version. | Re-run the normal daily pipeline for that partition from raw. Then bound every backfill at yesterday in code, not in the runbook. |
| A six-month range is submitted with high parallelism into the shared warehouse. | Unrelated hourly pipelines miss their freshness SLOs. Nothing has failed; everything is queued behind the backfill. | Concurrency was sized for the daily shape. A backfill is months of that shape submitted at once, and queueing delay grows disproportionately as utilisation approaches capacity (Saturation: The Reading Utilization Cannot Give You). | Cap the backfill's concurrency or move it to isolated compute, and re-run it outside the window that matters. Record the incident against the backfill so the next one is planned rather than discovered. |
| The raw layer only retains ninety days, so the recompute re-extracts from the operational database. | Replication lag climbs on the read replica; the application starts serving stale reads; the data incident becomes a product incident. | A full-width historical extract is an analytical workload against a system designed for point lookups, and no one sized the replica for it (OLTP vs OLAP). | Stop the extract. Rebuild from raw where possible, or export once to object storage and recompute from there — one scan instead of one hundred and eighty. |
What the correction can reach
Before running a backfill, the useful question is not "what am I changing" but "what can this operation reach". The answer is a chain, and every node in it holds something that the republish can corrupt in a different way.
The chain also explains why a correction that stops at the fact table is not finished. Each node downstream of the change holds its own materialised copy of the answer, on its own refresh schedule, and until each is rebuilt the platform is genuinely serving two different values for the same metric (Impact Analysis).
The last two nodes are the ones lineage tooling usually cannot see: an extract someone scheduled into a spreadsheet, and a model whose training set was pulled once. Both will keep the old numbers indefinitely, and neither appears in any graph the platform can generate (Data Lineage).
- Raw event files for the range
holds The immutable record of what arrived, which the recompute reads.
could corrupt Nothing, if the backfill only reads it. A backfill that "cleans" raw in place destroys the only thing that made the correction possible (The Raw Landing Zone).
↑ reads from - Staging model for the range
holds Cleaned, typed, deduplicated rows at order grain.
could corrupt Recomputing with today's parser over an older payload shape, quietly nulling columns that had values (Schema Evolution).
↑ reads from - `fct_orders`, the target
holds One row per order for the range, currently wrong and being read.
could corrupt Duplication if appended; loss if the range overruns into the current partition; silent alteration outside the range if the predicate is wrong.
↑ reads from - `revenue_daily` mart
holds Pre-aggregated revenue per country-day, derived from the target.
could corrupt Nothing by itself — and it keeps the old numbers until rebuilt, so it disagrees with its own parent for at least one refresh interval (Data Marts).
↑ reads from - BI dashboards and their caches
holds The number people actually look at, plus whatever filters the BI layer adds.
could corrupt Serving a cached pre-correction value after the correction, which is indistinguishable from the correction having failed.
↑ reads from - Scheduled extracts and one-off exports
holds Copies taken at a point in time, living outside the platform.
could corrupt Nothing you can reach. They hold the old numbers permanently and are the reason someone will show you a wrong figure next quarter.
↑ reads from - Models and features trained on the range
holds Learned parameters derived from data that has now changed.
could corrupt Silently — retraining is a decision nobody is prompted to make, and the training data no longer matches the serving data (Feature Pipelines).
Walk this list *before* the backfill and it is a rebuild plan. Walk it afterwards and it is an incident timeline.
Where the cost of a backfill actually goes
Asked to estimate a backfill, most people price the recompute. The recompute is rarely the largest term, and it is almost never the one that causes the argument afterwards.
The weights below are relative and are there to establish an ordering rather than a magnitude: the contention a backfill imposes on everything else, and the investigation cost when it goes wrong, both dominate the compute it consumes. They are also the two terms that never appear in a plan, because neither is charged to the backfill.
The ordering suggests where to spend effort. Isolating the compute addresses the largest term. Making the write idempotent addresses the second. Optimising the recompute itself — the term everyone starts with — addresses the middle of the list.
Months of work submitted into concurrency sized for a day. Paid by every team whose pipeline queues behind it, which is why it is never in the estimate.
Paid in people, not machines: diagnosing a doubled period, deciding which reports to correct, and rebuilding trust with the consumers who used the wrong numbers.
Proportional to range length times per-partition cost. The only term most estimates contain, and the easiest one to bound.
Columnar stores rewrite whole files, so correcting one column costs the full width of the affected partitions (File Compaction).
Every derived model and mart recomputed once. Grows with the depth of the dependency graph rather than with the size of the range.
The cost of being able to roll back. Small, bounded, and the one people try to avoid paying.
Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.
Relative weights for a warehouse-centric platform with shared compute, shown to establish an ordering. Not measurements, and not transferable to any particular bill.
How to build it
Most important first.
- Make the write idempotent before you make it historical. A merge on a genuinely unique business key, or a partition replacement, turns "ran twice" from an incident into a no-op — this is the
merge-on-keymitigation in the pipeline model, and it is the highest-leverage single change in this module (Upserts and Merges). - Bound the range on both ends and assert the upper bound in code.
WHERE day BETWEEN :start AND :endwith:enddefaulted to yesterday is a one-line change that removes the entire current-partition class of failure. - Isolate the compute: a separate warehouse, a separate queue, a separate cluster, or a concurrency cap that leaves headroom for the scheduled work. If the platform cannot express that, run it outside the window that matters (Separating Storage from Compute).
- Recompute from the retained raw layer rather than re-extracting from the source. This is the reason to keep raw history and the moment you find out whether you did (Keeping Raw History: The Recovery Position and the Liability).
- Enumerate the downstream rebuild set before starting, from lineage rather than from memory, and treat propagation as part of the operation rather than as follow-up (Impact Analysis).
- Make the run visible while it happens: a marker on the dashboards for the affected dataset, so anyone reading a number during the correction knows they are looking at a moving target.
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- An append-mode write guarantees the new rows are durable. It guarantees nothing about the rows that were already there, which is the property being relied upon and the property that does not exist.
- A partition replacement guarantees readers see either the old partition or the new one, per partition, if the table format provides snapshot isolation. Across a multi-partition range it guarantees nothing: mid-run the range is a mixture (Open Table Formats).
- A merge on the business key guarantees the target converges to one row per key regardless of how many times the range is re-run — provided the key is genuinely unique, which is an assumption about the data and not a property of the merge (Surrogate Keys).
- No mode of writing guarantees the downstream datasets agree with the one you just corrected. That is always a separate operation and always the one that gets forgotten.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- A uniqueness assertion on the business key over the backfilled range, evaluated on the staged output before publish and again on the target after. It catches the duplicated-period failure directly, which is the most common one and the one no other check sees.
- It misses duplication where the key is not actually unique in the source — two genuine orders sharing an id — and it misses the current-partition overwrite entirely, because losing rows preserves uniqueness perfectly.
- Pair it with a per-partition row count against the same partition's pre-backfill count. A partition that changed count by exactly a factor of two, and a partition that shrank when it should not have moved at all, are both immediately legible (Volume Anomalies).
- A backfill degrades the freshness of datasets it never touches, by consuming the capacity their pipelines need. This is the failure that surprises people, because the cause and the symptom share no lineage edge (Freshness Monitoring).
- On the corrected dataset, freshness monitoring based on the newest write timestamp goes green during a backfill even when the pipeline that normally feeds it has been broken for a day. The backfill wrote rows; the check cannot tell why.
- During the run the affected range is not stale and not fresh — it is inconsistent, and no single freshness number expresses that. A per-dataset "correction in progress" flag communicates it far better than any latency metric.
- A schema change inside the range means the recompute reads two shapes of input. A parser written for the current shape usually produces nulls rather than errors for the older one, so the backfill "succeeds" and empties a column for the earlier half of the range (Nullability & Defaults).
- If the business key itself changed during the range — a migration from a natural key to a surrogate one, say — then a merge cannot deduplicate across the boundary, and the idempotency you were relying on silently stops applying.
- The mitigation has its own evolution risk: a merge on a key that later gains duplicates in the source degrades from idempotent to non-deterministic, and nothing announces the change (Data Contracts).
- Recovering from a duplicated period is easy if the target has snapshots — roll back to the pre-backfill snapshot and start over — and hard otherwise, because deduplicating in place requires distinguishing the original row from its copy, and by construction they are identical (Open Table Formats).
- Recovering an overwritten current partition means re-running the normal daily pipeline for that day from raw, which works exactly to the extent that raw was retained and the day's inputs have not moved.
- Recovering from the compute incident is the only one that is free: it ends when the backfill ends. Recording that it happened is what stops the next person diagnosing it from scratch (Data Incidents).
What can go wrong
- The re-run appends and doubles a period, with every task green (Duplicate Rows).
- The range includes today and overwrites a complete partition with a partial one.
- The backfill saturates shared compute and breaks the freshness of unrelated pipelines.
- The recompute reads from the operational source at full width and degrades production.
- The merge that was supposed to make it idempotent runs against a key with duplicates in it and produces a non-deterministic result — the mitigation failing rather than the operation.
- The correction lands on the fact table and never propagates, leaving derived datasets disagreeing indefinitely.
- "It succeeded, so it is fine." The most reliable way to double a period is a successful run. Success and correctness are measured by different systems, and only one of them is switched on by default (The Pipeline Succeeded. The Data Is Wrong.).
- "Running it again cannot hurt." That is a statement about idempotency, and it is true only if someone made it true. In an append-mode pipeline it is precisely false, and the second run is exactly as harmful as the first was helpful.
- "The risk is that the backfill fails." The risk is that it succeeds against a target it was not designed to overwrite. A failed backfill costs an afternoon; a successful one against live data can cost a quarter.
- "We deduplicate downstream, so duplicates are handled." Downstream deduplication is usually windowed and keyed on an event id. A whole period re-appended has the same event ids and falls outside the window — the two mechanisms do not compose (Deduplication).
- A backfill that re-extracts from the source needs the same access review as any bulk read of production data, and it usually gets less because it is framed as maintenance rather than as access (Data Access Control).
- Rows suppressed for a deletion request can reappear in a duplicated period, because the suppression was applied to the original write and the copy came from raw. Duplicate-period incidents are therefore also privacy incidents (Deletion Requests).
Operating it
- Rows per partition before and after, as a chart across the range. Doubling and truncation are both a glance; neither appears anywhere in the orchestrator (Pipeline Metrics).
- A uniqueness test on the business key, scheduled rather than run once, so a duplication introduced by any operation is caught rather than only the one you were watching (The Data Quality Dashboard).
- Warehouse concurrency, queue depth and per-pipeline runtime during the window, annotated with the backfill's start and end, so the collateral damage is attributable (The Backlog Arithmetic: Four Levers and a Drain Time).
- Source-side load if the recompute reads the operational system — replication lag is the signal that this is about to become an application incident (Replication Lag: Reads That Are Correct and Stale).
- At 10x range the parallelism needed to finish makes the compute-saturation risk the dominant one, and the answer stops being "run it carefully" and becomes "run it somewhere else" (Separating Storage from Compute).
- At 100x, re-extraction from the operational source is simply not available — the source cannot serve it — so the recovery position is entirely decided by what the raw layer retained, months before anyone knew there was a bug.
- More downstream consumers do not change the mechanics and change the propagation cost linearly, and the ones that break are always the undocumented extracts rather than the modelled dependencies (Data Lineage).
- The direct cost is the range times the per-partition compute, spent in a burst. The indirect cost — everything else queueing behind it — is usually larger and is charged to other teams' pipelines.
- Re-extracting from the source instead of from raw converts a storage cost you already paid into a compute-and-risk cost on your most important database. That is the worst trade in this lesson.
- A duplicated period costs the storage of the copy, the scan cost of every query that reads it afterwards, and the investigation — the last of which dominates and is paid in people rather than machines (Scan Cost).
- Idempotency is not free: a merge reads the target as well as the source and writes more than an append does. It is bought deliberately, and the thing it buys is that re-running is boring.
- Merging on a key makes re-runs safe and hides the fact that you ran twice. That is usually the right trade and it removes a signal: the run log becomes the only evidence the operation happened at all.
- Isolated compute removes the collateral risk and costs a second environment that is idle most of the time — the classic availability-versus-utilisation trade, made about a workload that is rare and urgent.
- A hard upper bound on the range prevents the current-partition failure and makes "correct everything up to and including now" a two-step operation. That friction is the point.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALDuplication, current-partition overwrite, compute saturation and source load are the four risks in any stack. Which of them your platform makes hard to hit depends on the table format and on whether backfill compute is isolated — and most platforms address neither.
- SIMULATEDThe duplication figures come from the pipeline model in
src/de/sim/pipeline.ts, which appends a second copy of the period under theunsafe-backfillfault. It is a deterministic teaching model over generated events, not a measurement of any real platform. - FORMAT-SPECIFICOn Iceberg or Delta the failed backfill is undone by rolling back to the previous snapshot; on plain Parquet under directory partitioning the overwrite is destructive as it proceeds and there is nothing to roll back to. The risk is identical and the recovery is not.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the change-management side: a backfill on a shared platform is a change with a blast radius, and it belongs in whatever record the organisation keeps of those.