Backfills
Recomputing history after the logic or the inputs changed — and why the hard part is publishing the result, not computing it.
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.
The revenue model has been miscounting refunds for six months. The fix is merged and today is correct. What does it take to make those six months right?
Everyone who has already used the wrong numbers: a finance team that closed six months on them, an analyst whose year-on-year comparison spans the boundary, a forecasting model trained on the bad series, and an executive who will ask why the chart changed shape overnight. They need the corrected history, the date the correction applies from, and a one-sentence explanation of the difference.
The unit of a backfill is the partition — one (dataset, period) cell that will be recomputed and republished as a whole. Not the row, because rows are not individually addressable in most analytical stores; not the DAG run, because one run may touch several partitions and one partition may need several runs.
Set the scheduler's start date back six months, let it catch up, and watch the runs go green. The pipeline already knows how to build a day — a backfill is that same code with older dates, and the orchestrator has a button for exactly this. For a pipeline that appends immutable events into an empty range, this genuinely is the whole job.
The range is chosen from when the bug was noticed rather than from when it was introduced, so the earliest weeks keep their wrong numbers. The corrected series now has a step in it at the range boundary that nobody can explain, and the explanation is your backfill.
- The range is chosen from when the bug was noticed rather than from when it was introduced, so the earliest weeks keep their wrong numbers. The corrected series now has a step in it at the range boundary that nobody can explain, and the explanation is your backfill.
- The pipeline writes with
INSERT. Every day in the range that had already been computed now exists twice, so the corrected period is roughly doubled rather than corrected — and the run that did it succeeded (What Backfills Break). - The transformation joins a dimension table that holds only current state, so an order placed in March is recomputed against the customer's tier as it is today. The backfill produces a history that never happened (Slowly Changing Dimensions).
- The backfill and the scheduled daily run collide on the newest partition. The daily run publishes a complete day; the backfill overwrites it with a version computed from a snapshot taken before the day closed; the rows lost are ones nobody knows to look for.
- A filter somewhere in the model references
current_daterather than the run's logical date, so all 180 runs compute the same thing — today — and the backfill reports 180 successes without touching a single historical partition (Idempotent Data Pipelines). - The source has moved on. A SaaS API that only serves the last ninety days, a CDC topic whose retention has expired, an operational table that has since been hard-deleted for a privacy request — the input the backfill needs no longer exists (Retention and Replay).
What is actually happening
- A backfill has three separable parts, and conflating them is where nearly every backfill incident comes from: selecting the range that is wrong, recomputing it, and publishing the result over data that consumers are already reading. Only the middle part is the pipeline you already have (Atomic Publish).
- Recomputation is meaningful only if the transformation is a deterministic function of inputs you still hold. The moment it reads mutable state — a dimension that has since changed, an API that answers as of now, the wall clock — re-running does not reproduce history, it overwrites history with a version computed against today (Keeping Raw History: The Recovery Position and the Liability).
- Publishing over live data is a write-visibility problem rather than a data problem. Replacing a partition, swapping a table and merging on a business key each give a different atomicity story; appending gives none, which is why the same button that fixes an empty range corrupts a populated one (Upserts and Merges).
- The correction does not stop at the table you rebuilt. Every model, mart, extract and dashboard cache derived from it still holds the old numbers until it too is rebuilt, so mid-correction the platform holds two answers to the same question and both are being read (Impact Analysis).
- A backfill is also a cost and capacity event. It compresses into a few hours the compute the platform normally spreads across months, against a warehouse sized for the daily shape (Compute Waste).
- Nothing in this is exotic. It is the same problem as a schema migration on a live database — change the shape of something people are reading, without them observing a half-changed state (Expand and Contract Migrations in Backend covers the transactional analogue).
Four different operations, one word
People say "backfill" for at least four things, and the risk profile of each is completely different. Being precise about which one you are doing decides whether the publish is dangerous, whether the range matters, and whether the current partition is in play at all.
The safest of the four — populating an empty range for a brand new dataset — is the one that shapes everyone's intuition, because it is the one you do first and the one where appending is genuinely correct. The intuition then transfers to the case where the target is populated and being read, and that is where the incidents come from.
Note the last column. In three of the four rows there is data that must come out of the run unchanged, and nothing in a pipeline enforces that by default. It is the thing a backfill validation exists to prove (Validating a Backfill Before You Publish).
| What people call a backfill | What actually changed | What must be recomputed | What must not change |
|---|---|---|---|
| New dataset | Nothing — the table did not exist before. | Every period you want history for, bounded by what raw data you still hold. | Nothing yet. This is the only genuinely low-risk case, and it is the one everyone generalises from. |
| Logic correction | The transformation. Today is already right; history is not. | Exactly the range built by the broken code, from pinned inputs. | Every partition outside that range, to the row. A silent change there means the fix altered data that was correct. |
| New column | The schema. Existing columns keep their values. | The new column, for whatever history the source can still support. | Every existing column in every existing row. The publish must be a merge or a rewrite that reproduces them exactly. |
| Missing data recovery | Nothing in your code — an upstream gap, an outage, a connector that fell behind retention. | Only the periods that are incomplete, after establishing which ones those are from the source rather than from the pipeline's logs. | The periods that were complete. Re-ingesting a period that already landed is the classic route to a duplicated month (Deduplication). |
The word does not distinguish these, which is why the first question in any backfill conversation is "which of these are we doing", and the second is "what is currently in the target".
Adding a column is a backfill too
A product team asks for orders to be split by acquisition channel. The column is added to the model, today's run populates it, and the dashboard shows a beautiful chart that starts on Tuesday. Everything before Tuesday is null, and the request was for a trend.
This is the cheapest backfill to reason about — no existing value changes, so a mistake nulls a new column rather than restating revenue — and it is still a backfill, because the value has to be derived from data as it was then. If channel is only recoverable from a session log with ninety days of retention, the answer to "can we have this for last year" is no, and it is better to say so before the column ships.
The schema diff below is the part that gets reviewed. The impact rows are the part that does not, and two of them are silent: a SELECT * consumer gains a column it did not ask for, and an aggregate over the new column silently reports only the post-Tuesday portion of the business as though it were all of it.
- order_id
- customer_id
- order_ts
- country
- amount_minor
- is_refunded
- order_id
- customer_id
- order_ts
- country
- amount_minor
- is_refunded
- acquisition_channel
change A nullable string column is added. Forward runs populate it from the session log; history is null until a backfill fills it, and the session log only retains ninety days.
| Consumer | Effect | How it shows up |
|---|---|---|
| The requesting dashboard | Charts the new dimension from Tuesday onward and renders the earlier period as a single "unknown" bucket, which reads as a real category rather than as absent data. | Silently — no error, wrong result |
| Revenue by channel, summed | Excludes or lumps every pre-Tuesday order depending on how nulls are handled, so channel revenue does not add up to total revenue and nobody notices until it does not. | Silently — no error, wrong result |
| A downstream `SELECT *` extract | Gains a column mid-week; a strict loader with a fixed column list fails outright, which is the loud and preferable outcome. | Loudly — it raises |
| Contract-checked consumers | An added nullable field is a backward-compatible change and passes, correctly. The compatibility check is about shape and has nothing to say about the column being empty for most of history (Backward Compatibility). | Loudly — it raises |
The shape of a backfill run
Written out as stages, a backfill stops looking like a scheduler operation and starts looking like a small release: scope it, build it somewhere safe, prove it, ship it atomically, tell people, and keep the ability to undo. Each stage promises something specific, and the ones people skip are the first and the last.
Read the guarantees column downward. Nothing before the publish stage affects a consumer at all, which is the entire argument for staging: everything expensive, slow and error-prone happens where being wrong is free. The publish itself is a single, reviewable, reversible action.
The failure column is worth reading as a checklist during a real backfill, because each entry is a specific thing to go and look at rather than a category. "The range was wrong" is not actionable; "the range was taken from when we noticed rather than when it deployed" is.
- 1Scope
Establishes the affected dataset and the exact partition range, from the deploy history and from a discontinuity in the data itself.
guarantees That the range is defensible and written down. Nothing about it being complete.
fails by Anchoring on the date the bug was reported, which is always after the date it shipped, leaving a corrected series with a step in it.
- 2Pin inputs
Fixes the version of the raw data, the dimensions and the code that the recompute will read.
guarantees Determinism: running the same stage twice produces identical output.
fails by A dimension join that silently reads current state, so history is recomputed against a present that did not exist then.
- 3Recompute to a staging location
Runs the fixed transformation over the range, writing where no consumer reads.
guarantees Zero consumer impact regardless of outcome, and a result you can inspect before committing to it.
fails by Saturating shared compute — the staging location protects the data, not the cluster (Workload Isolation).
- 4Validate
Reconciles against the source, diffs old against new, and checks an unaffected control period is untouched.
guarantees That the differences you can name are the only differences there are.
fails by Validating only the range that changed, which cannot detect a backfill that also altered data outside it.
- 5Publish
Replaces the range in one operation — a partition swap, a table swap or a merge on the business key.
guarantees Atomicity per partition, if the format supports it. Never atomicity across the whole range.
fails by Appending. The period is now present twice, every run reported success, and additive measures are inflated (What Backfills Break).
- 6Propagate and announce
Rebuilds dependent models and marts, and tells the humans which numbers changed and by how much.
guarantees Nothing automatic. Every downstream rebuild is its own decision (Impact Analysis).
fails by Stopping at the table that was fixed, leaving derived datasets holding the old answer and two dashboards disagreeing.
The pipeline you already have implements one of these six stages. That ratio is the lesson.
How to build it
Most important first.
- Establish the range from evidence rather than memory. Find the commit that introduced the behaviour and the first partition built after it deployed, then cross-check against the data: an affected metric almost always has a visible discontinuity at the true boundary, and it is rarely where anyone guessed.
- Parameterise the run explicitly by logical period and forbid the current time in the entire code path. A backfill is a function from a period to a partition; anything that makes it a function of when you ran it is a bug waiting for its 180th repetition (Idempotent Data Pipelines).
- Pin the inputs. Recompute from the retained raw layer or an as-of snapshot of the dimensions, not from whatever the source says today, so the output is what the fixed logic *would have produced then* (Snapshot Tables).
- Write to a location no consumer reads, validate there, and publish by swap or merge. This is the single change that turns a backfill from a risk into a routine operation (Planning a Backfill, Validating a Backfill Before You Publish).
- Run one representative partition end to end — including the publish and the validation — and look at it before authorising the other 179. The first partition costs an hour; discovering the problem on partition 180 costs the range.
- Announce it. A corrected series is a different series, and a finance team that reconciled against the old numbers needs to know which of their reports are now stale rather than wrong (Data Incidents).
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.
- A backfill guarantees exactly what its publish mechanism guarantees, and no more. Partition replacement gives atomicity per partition and nothing across partitions: while it is running the range is genuinely half-old and half-new, and a query spanning it returns a number that was never correct under either version of the logic.
- It guarantees nothing about datasets derived from the one you replaced. Those keep the old values until they are rebuilt, which is a separate decision and usually a separate run (Data Lineage).
- Determinism is an assumption you are making, not a property you are given. Unless the inputs are immutable and the code has no clock, "recompute" and "reproduce" are different operations.
- Nothing guarantees the new logic is right. A backfill makes the current definition retroactive — including when the current definition is also wrong, in which case you have now made the same mistake across all of history instead of only since Tuesday.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The publishable check is a three-part comparison, run against the staged output before anything is swapped: reconcile the range against the source, diff old against new for the range and be able to explain the difference, and confirm a period the bug did not affect is byte-for-byte unchanged (Validating a Backfill Before You Publish).
- It misses anything wrong identically in the source and the recompute — a bug in shared parsing logic reconciles perfectly — and it misses any column the reconciliation does not sum.
- It also cannot tell you that the fix was the right fix. Every check here compares numbers to numbers; whether the new definition of revenue is the one the business means is a conversation, not a query (Two Dashboards, Two Numbers).
- A backfill has no freshness requirement of its own — its output is old by construction — but it competes for the same compute as the pipelines that do have one. The freshness incident caused by a backfill is usually on a completely unrelated dataset (Workload Isolation).
- While a range is being republished, the freshness of the affected dataset is ambiguous in a way most monitors handle badly: the rows are newly written and the period they describe is months old, so a "max updated_at" freshness check goes green for the wrong reason (Freshness Checks).
- Downstream consumers see the correction on their own refresh schedules, so a corrected fact table and a not-yet-rebuilt mart disagree for exactly one refresh interval. That interval is when somebody screenshots the dashboard.
- If the schema changed during the range, one backfill run must read several shapes of input. Recomputing March with today's parser is the most common way a backfill produces nulls where the old data had values (Schema Evolution).
- A backfill after a semantic change is the harder case: the column name and type are identical on both sides of the boundary and the meaning of the number is not. Backfilling then silently restates history under a definition it was never measured under (Semantic Changes).
- Adding a column and populating it for history is a backfill with an easier publish story — nothing existing changes value — and the same range-selection problem. It is still the operation that fills a new column with a value derived from data that has since moved.
- Keep the pre-backfill version of the affected range long enough to put it back. A table format with snapshots makes that a metadata operation; an
INSERT OVERWRITEacross plain files makes it impossible, and you discover which one you have during the incident (Open Table Formats). - Recovering *from* a bad backfill is the case people forget to design. Plan the undo before the do: name the snapshot, the retention on it, and who is allowed to trigger the restore (Rolling Back Data).
- If the rollback plan is "run the old code again", note that the old code is no longer on the main branch and the person who remembers why it was written that way is on holiday. Pin the artefact, not the intention.
What can go wrong
- Duplicated periods, because the publish appended instead of replacing. The most common backfill failure and the quietest (Duplicate Rows).
- The current partition overwritten by a backfill that was supposed to stop at yesterday, so today loses rows and the loss looks like a slow day.
- A range that starts too late, leaving a discontinuity that will be re-investigated in six months as a business trend.
- History recomputed against current dimensions, producing a plausible, internally consistent past that did not happen.
- The backfill saturating the warehouse and causing a freshness incident on unrelated pipelines — the mitigation failing rather than the operation.
- A successful, validated, correctly published backfill that nobody told the consumers about, so three teams reconcile against numbers that changed under them.
- "A backfill is the same DAG pointed at older dates." The computation is the same; the write is not. Building an empty partition and replacing a populated one that people are reading are different operations with different failure modes, and the orchestrator offers one button for both.
- "The runs all went green, so the backfill worked." Green means the code ran 180 times. It is compatible with computing today 180 times, with doubling every period, and with writing perfectly correct data into a table nobody reads (The Pipeline Succeeded. The Data Is Wrong.).
- "We fixed the code, so the numbers are fixed." The fix applies from the moment it deployed. History keeps the old logic until something rewrites it, which is exactly the operation being avoided when a team says the bug is fixed.
- "We can always backfill later." Only while the inputs still exist. Retention windows, hard deletes and mutable dimensions all convert "later" into "never", quietly and on a schedule (Data Retention).
- A backfill from the raw layer can resurrect rows that were deleted for a privacy request, because the deletion was applied to the serving table and the raw event was left in place. Any backfill of personal data must re-apply the suppression list as part of the run (Deletion Requests).
- The staged location holds a full copy of the range with the same classification as the table it will replace, and it is created outside the normal access model unless someone decides otherwise. Give it the same grants and the same lifecycle as its target (Data Access Control).
Operating it
- A written record per backfill: dataset, range, code version, who ran it, when it published, and the old-versus-new delta per period. This is the artefact that answers "why did last March change" a year later (Dataset Documentation).
- Row count and summed measure per partition, plotted across the range boundary, before and after. A doubled period and a truncated period are both obvious on that chart and invisible in the orchestrator (Volume Anomalies).
- Warehouse concurrency and queue depth during the run, so the collateral freshness damage is attributed to the backfill rather than investigated as a mystery (Pipeline Metrics).
- At 10x range, partition-at-a-time serial execution stops finishing inside a maintenance window and the run needs bounded parallelism — which reintroduces the collision risk between concurrent partitions writing shared outputs.
- At 100x, the question changes from "how do we run this" to "can we avoid it": platforms at that size design for corrections by keeping the raw layer replayable and the transformations pure, because a manual six-month backfill is no longer an event a team can staff (Full Refresh vs Incremental).
- Consumer count scales the communication problem rather than the compute one. Twenty dependent datasets means twenty rebuild decisions, and the ones that get missed are the extracts living in someone's spreadsheet.
- A backfill's compute is proportional to the range times the per-partition cost, and it is spent in one burst against capacity provisioned for the daily shape. The cost that hurts is usually the queueing it inflicts on everything else, not its own consumption (What Actually Drives Data Platform Cost).
- Republishing rewrites bytes, which in a columnar store means writing whole files rather than rows — the write amplification of a one-column correction is the full width of the table (File Compaction).
- Storage doubles for the affected range while both versions exist, which is the cost of being able to roll back and is worth paying for a bounded window.
- The cheapest backfill is the one whose range is correct. Over-wide ranges are chosen defensively and paid for in full.
- Doing a backfill properly — staged location, validation, atomic publish, retained rollback — costs a day of engineering and a second copy of the data. Doing it directly costs nothing until the first time it costs a quarter of restated revenue.
- Recomputing from pinned inputs gives a faithful history and requires you to have kept them. Recomputing from current inputs is always possible and is not history.
- Correcting history makes the series right and makes every report anyone has already published wrong. Sometimes the honest choice is to fix forward and annotate the boundary rather than restate (Reprocessing vs Retrying).
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.
- GENERALRange selection, deterministic recomputation and atomic publication are the three parts of a backfill in every stack. What varies is which of the three your tooling makes easy — most make the middle one easy and the other two manual.
- FORMAT-SPECIFICIceberg, Delta and Hudi give snapshot isolation and time travel, so replacing a partition is transactional and rolling back is a metadata operation; plain Parquet under a Hive-style directory layout gives neither, and an overwrite there is destructive the instant it starts.
- TOOL-SPECIFICAirflow expresses a backfill as re-running logical dates and will happily re-run tasks that append; dbt expresses it as
--full-refreshon an incremental model, which rebuilds the whole table rather than a range. Neither validates the result, and the two failure shapes are different.
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 release side of this: pinning an artefact version, deploying a change behind a boundary, and rolling it back. A backfill is a data deployment and deserves the same review, the same change record and the same undo plan.
- — Distributed Systems owns what "atomic across partitions" would even mean here, and why almost no analytical store offers it.