Planning a Backfill
Five questions to answer before the first partition runs: which range, is the re-run safe, where does the compute go, how do we validate, and how do we publish.
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.
Before a single historical partition is recomputed, what has to be decided — and which of those decisions is irreversible once the run starts?
The engineer who will run it and the team that will be reading the affected dataset while it runs. The first needs a plan specific enough to execute at 2 p.m. on a Tuesday; the second needs to know when their numbers will move and by roughly how much.
The plan is written per (dataset, range) pair. One backfill plan covers one target dataset over one contiguous range; a correction that touches three datasets is three plans, executed in dependency order, because the second reads the output of the first.
Discuss it in a thread, agree the bug started "around March", and start the runs. The engineer doing it knows the pipeline well and will notice if something looks wrong. This works often enough that the discipline below looks like ceremony — right up until the run that does not go well, which is the one where nobody can say what the range was supposed to be.
Nobody wrote down the range, so when the numbers move the team cannot answer "was March supposed to change" and has to reconstruct the intent from the run logs.
- Nobody wrote down the range, so when the numbers move the team cannot answer "was March supposed to change" and has to reconstruct the intent from the run logs.
- The pipeline was assumed to be idempotent because it had been re-run before — on an empty range. Against a populated one it appends, and the assumption was never tested where it mattered (What Backfills Break).
- Compute was not isolated, so the plan's "run it over the weekend" turned into a Monday morning freshness incident on six other datasets (The Freshness SLO).
- Validation was defined as "check the numbers look right", which is a check that passes on any output that is plausibly shaped, including a doubled one.
- Publication was left implicit, so it happened partition by partition as each run finished, and consumers spent nine hours reading a range that was part old logic and part new (Atomic Publish).
- The plan covered
fct_ordersand notrevenue_daily, so the correction was declared complete while the mart everyone actually queries still held the old values (Data Marts).
What is actually happening
- The five questions are not a checklist for its own sake; each one closes off a specific failure class from What Backfills Break, and skipping one is a decision to accept that class. Written that way, the plan is short.
- Range selection is an evidence problem. The deploy history gives you a candidate boundary; the data gives you the real one, because a metric affected by a logic bug almost always has a discontinuity you can find with a single query over the daily series.
- Re-run safety is a property of the write, not of the transformation. A perfectly pure transformation followed by an
INSERTis not safely re-runnable, and testing it requires running it twice against a populated target in a non-production copy (Idempotent Data Pipelines). - Compute isolation and publication are both about the boundary between the operation and its consumers: isolation keeps it from stealing their capacity, atomic publication keeps them from observing it half-done. They are the same concern applied to two different resources (Workload Isolation).
- Staging is what makes validation possible at all. You cannot validate an output you have already published over the thing you would compare it to, so the staged copy is not a nicety — it is the precondition for the check (Validating a Backfill Before You Publish).
- The plan is also the communication artefact. "We are correcting
fct_ordersfor 2026-01-14 to 2026-06-30; revenue for that range will fall by about the value of refunds; nothing outside the range moves" is what a finance team needs, and it falls out of the plan for free.
The five questions
Every backfill plan answers the same five questions, and each answer closes a specific failure class. The value is not in the answers being sophisticated — most of them are one line — it is in none of them being implicit.
The decision below is deliberately not a recommendation. Each option is correct in some context and expensive in others, and the criteria are what transfer. What does not vary is that an answer exists for all five before the first partition runs.
Notice that the cheapest option on every row is the one that works fine until the target is populated and being read. That is the same observation as the whole module: the operation people practise is the safe case, and the operation that matters is the other one.
For this dataset and this range, what are the five answers?
when A logic bug with a known introducing commit and a metric whose daily series shows a step at the boundary.
cost One query and half an hour. Gives a defensible boundary and a number you can quote to consumers before the run.
when The introducing change cannot be dated, the table is small enough to rebuild, or the logic changed in a way that touched everything.
cost Maximum compute and maximum blast radius, and no unaffected control period left to validate against — which removes the single most useful check in Validating a Backfill Before You Publish.
when The target has a genuinely unique key and the storage layer supports a merge.
cost More expensive than an append, requires the key assumption to hold, and makes a duplicate run invisible rather than harmless-and-visible (Upserts and Merges).
when The target is partitioned on the same grain as the backfill range and the format supports atomic replacement.
cost Rewrites entire partitions even for a one-column correction, and gives no protection if the range boundaries are wrong — it replaces exactly what you tell it to.
when The platform shares compute with pipelines that have freshness commitments.
cost A second environment, and the operational work of keeping it equivalent to production. Removes the largest cost driver in What Backfills Break.
when No isolated option exists and the range is small relative to daily volume.
cost Slower, and it still competes. Requires someone to define off-peak from actual pipeline schedules rather than from working hours.
when The whole staged range fits and consumers care about internal consistency across it.
cost Peak storage of two copies of the range, and the correction lands later. Gives consumers a single instant at which numbers change.
when The range is very large, or consumers query single days and never span the boundary.
cost The range is inconsistent for the duration of the run, and an interrupted backfill leaves it permanently mixed unless someone tracks which partitions landed.
Staging: compute where being wrong is free
The single structural change that makes backfills routine is that the recompute writes somewhere no consumer reads. Everything expensive and error-prone happens there; the only thing that touches the live table is one reviewable operation at the end.
The layout below is deliberately boring. A staging schema mirroring the target, one relation per backfill run, named with the range and the run so two concurrent corrections cannot collide, and a retained pre-publish copy of the affected partitions that is the rollback. Nothing here is clever, and the absence of any one part is a failure class.
The retained copy is the part that gets dropped from the plan first, because it looks redundant next to a table format that offers time travel. Keep it anyway when the format's snapshot expiry is shorter than the time it takes someone to notice a wrong number — which, for a monthly reporting cycle, it usually is (Data Retention).
warehouse/ ├─ analytics/ # what consumers read │ ├─ fct_orders # the target; partitioned by order_date │ └─ revenue_daily # a mart derived from it — rebuild after publish │ ├─ backfill_staging/ # no consumer grants, lifecycle-expired │ ├─ fct_orders__bf20260826_a/ # this run's recomputed range │ │ order_date=2026-01-14/ ... # one partition per day, written once │ │ order_date=2026-06-30/ │ └─ fct_orders__bf20260826_a__validation/ │ reconciliation.csv # source vs staged, per partition │ old_vs_new.csv # per-partition delta, with an explanation │ control_period.csv # a period the bug did NOT affect │ └─ backfill_rollback/ # the undo, retained past the reporting cycle └─ fct_orders__pre_bf20260826_a/ # the affected partitions, exactly as published
The run, written as SQL
The plan becomes three statements: build the range into staging from pinned inputs, validate there, and publish in one operation. Writing them out makes two things obvious that prose hides — that the current partition is excluded by a literal bound, and that the publish is a single statement whose failure leaves the target untouched.
The MERGE below is the idempotent form: re-running it converges rather than accumulates, which is the merge-on-key property the pipeline model in src/de/sim/pipeline.ts pins. Its correctness rests entirely on order_id being unique in the staged range, which is why the uniqueness assertion is not optional decoration but the merge's precondition (Upserts and Merges).
The rollback statement is not shown because it depends on the storage layer, and that is the point: write it down for yours, in the plan, next to the publish. A publish whose inverse cannot be stated in one line is a publish that will be reversed by hand under pressure.
1-- 1. Recompute the range into staging, from the raw layer and an as-of2-- dimension snapshot. Note the upper bound: the current partition is3-- owned by the scheduled pipeline and is never in a backfill range.4CREATE OR REPLACE TABLE backfill_staging.fct_orders__bf20260826_a AS5SELECT6 r.order_id,7 r.customer_id,8 r.order_ts,9 d.country, -- as of the order, not as of today10 r.amount_minor,11 r.is_refunded,12 CASE WHEN r.is_refunded THEN 0 ELSE r.amount_minor END AS net_amount_minor13FROM raw.orders_events AS r14JOIN dim.customer_snapshot AS d15 ON d.customer_id = r.customer_id16 AND r.order_ts >= d.valid_from17 AND r.order_ts < d.valid_to -- the join that makes it history, not now18WHERE r.order_date BETWEEN DATE '2026-01-14' AND DATE '2026-06-30';19 20-- 2. The merge's precondition. If this returns a row, stop: the MERGE below21-- is not idempotent against a key that is not unique.22SELECT order_id, COUNT(*) AS n23FROM backfill_staging.fct_orders__bf20260826_a24GROUP BY order_id25HAVING COUNT(*) > 1;26 27-- 3. Publish. One statement; a failure leaves the target exactly as it was.28-- Re-running this statement is a no-op, which is the entire point.29MERGE INTO analytics.fct_orders AS t30USING backfill_staging.fct_orders__bf20260826_a AS s31 ON t.order_id = s.order_id32WHEN MATCHED THEN UPDATE SET33 t.country = s.country,34 t.amount_minor = s.amount_minor,35 t.is_refunded = s.is_refunded,36 t.net_amount_minor = s.net_amount_minor37WHEN NOT MATCHED THEN INSERT38 (order_id, customer_id, order_ts, country, amount_minor,39 is_refunded, net_amount_minor)40VALUES41 (s.order_id, s.customer_id, s.order_ts, s.country, s.amount_minor,42 s.is_refunded, s.net_amount_minor);The dimension join in step 1 is the difference between reproducing history and rewriting it: valid_from/valid_to selects the customer row that was current when the order happened, which is what a type-2 dimension exists for (SCD Type 2 in Practice). Swap it for a plain join on customer_id and every step here still runs, still validates on row counts, and produces a past that never occurred.
MERGE syntax and the exact atomicity it provides differ between warehouses and table formats, and a MERGE that does not match a target row will insert it — which silently widens the range if the staged data contains keys outside it. Verify current documentation for your engine, and add the range predicate to the ON clause if it supports one.
How to build it
Most important first.
- Find the range from the deploy that introduced the behaviour, then confirm it against a query over the daily metric. Write both the candidate boundary and the confirmed one into the plan, because they differ often enough to be worth recording.
- Prove the re-run is safe by doing it: run one already-populated partition twice against a copy of the target and assert the result is identical. If it is not, fix the write before the range, not after (Upserts and Merges).
- Decide where the compute runs and cap its concurrency explicitly. "Off-peak" is a decision; "whenever the runs happen to be submitted" is not (Separating Storage from Compute).
- Write the validation queries before the run, against the staged location, and state their pass criteria numerically. A check invented after seeing the output is a check calibrated to the output (Validating a Backfill Before You Publish).
- Choose the publish mechanism from what the storage layer actually supports — snapshot swap, partition replacement, merge on key — and state the rollback for that mechanism in the same sentence (Open Table Formats).
- Enumerate the downstream rebuilds from lineage and put them in the plan with the same specificity as the primary run. The correction is not done when the target is right (Impact Analysis).
- Run one partition end to end, including publish and validation, and stop. Look at it. Then authorise the rest.
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 plan guarantees nothing about the outcome. What it guarantees is that after the run, every difference between the old and new data can be attributed to a decision someone made deliberately — which is the property you need during the conversation afterwards.
- Staging guarantees zero consumer impact up to the moment of publish, and nothing about the compute the staged run consumes.
- A tested idempotent write guarantees that re-running a partition converges rather than accumulates — under the key uniqueness assumption the test was run with, which is a statement about the data as it was that day (Surrogate Keys).
- Nothing in the plan guarantees the range is right. It guarantees the range is written down, which is what makes the next backfill cheaper.
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The plan's own quality gate is that every one of the five questions has a written answer, and that the validation queries exist before the run does. A plan with four answers is a plan that has accepted one failure class silently.
- It misses the thing no plan can catch: that the new logic is also wrong. Nothing in a backfill validates the definition, only the mechanics of applying it (Two Dashboards, Two Numbers).
- It also misses scope errors in the plan itself. If the plan names the wrong target dataset, every check will pass against the wrong table.
- A staged backfill has no freshness obligation until publish, which is exactly why it can be scheduled into whatever window is cheapest for everyone else.
- Publishing the whole range in one operation gives consumers a single instant at which their numbers change. Publishing partition by partition gives them a window during which the range is internally inconsistent, and the length of that window is the length of the run.
- Downstream rebuild order decides how long the platform holds two answers. Rebuilding depth-first — target, then its dependents, then theirs — closes the inconsistency faster than rebuilding everything on its own schedule (The Transformation DAG).
- If the range spans a schema change, the plan must say which parser applies where. This is usually discovered mid-run and belongs in the plan (Schema Evolution).
- If the range spans a semantic change — the definition of an active customer moved — then a single backfill cannot make the series consistent, and the honest plan says the boundary stays and is annotated (Semantic Changes).
- Plans age. A plan written for a pipeline that has since become incremental does not describe the same operation, and reusing last year's runbook is how a team discovers that (Incremental Processing).
- Every plan names its rollback with the same specificity as its publish: which snapshot, retained until when, restorable by whom, and how long the restore takes (Rolling Back Data).
- The rollback is tested on the single trial partition, not assumed. A restore path that has never been exercised is a hypothesis (Restore Testing in Cloud makes the same argument about backups).
- If the storage layer offers no rollback, the plan must include taking an explicit copy of the affected range before publishing, and deleting it on a stated date rather than whenever someone notices.
What can go wrong
- A plan with a range that was guessed, so the corrected series has a discontinuity nobody can explain.
- A plan that assumes idempotency instead of testing it, which is the assumption that fails most often and most expensively.
- A plan that stops at the target dataset, leaving the derived marts inconsistent with it.
- A trial partition that is run but not inspected, which is the same as not running one.
- Validation queries written after the output exists and calibrated to it — the mitigation failing rather than the operation.
- A rollback that exists in the plan and not in the storage layer, discovered at the moment it is needed.
- "We know the pipeline, so we do not need a plan." The plan is not for the person who knows the pipeline. It is for the conversation with finance, the person who takes over halfway, and the reader in six months asking why March moved.
- "Staging is over-engineering for a small backfill." Staging is what makes validation possible. Without it the choice is publish-then-check, and check-after-publish is not a gate (Validating a Backfill Before You Publish).
- "We will validate it afterwards." Afterwards, the thing you would have compared against has been overwritten by the thing you are trying to validate.
- "The plan is done when the target table is correct." The plan is done when everything derived from the target is correct and the people reading it have been told (Impact Analysis).
- A backfill plan for a dataset containing personal data states how the suppression list is applied during the recompute, and confirms the staged copy inherits the target's classification and access grants rather than the default ones (PII in Pipelines).
- The plan is also the audit artefact: for regulated reporting, being able to show who authorised a restatement, over what range, and what the before-and-after numbers were is usually a requirement rather than good practice (Audit Logs for Privileged Actions in Security covers the general form).
Operating it
- The plan itself, stored with the run: dataset, range, code version, publish mechanism, validation results, rollback location, and the observed old-versus-new delta. This is the record that answers questions a year later (Dataset Documentation).
- The trial partition's validation output, kept as the baseline the remaining partitions are compared against.
- Progress as partitions published versus partitions planned, so an interrupted run is legible rather than a question (Pipeline Metrics).
- At 10x range the plan gains a batching decision — how many partitions per submission and in what order — and the trial partition becomes proportionally cheaper insurance.
- At 100x, backfills stop being planned individually and become a platform capability: a parameterised, isolated, staged-and-validated path that any pipeline can use, because the alternative is a bespoke plan every week.
- More consumers make the communication section of the plan the longest one, and it is the section that decides whether the correction is experienced as competence or as chaos.
- Planning costs a few hours of one engineer, and the trial partition costs one partition of compute. Both are small relative to the range and tiny relative to a restatement.
- Staging costs a duplicate copy of the range for the duration. Isolated compute costs an environment that is idle when there is no backfill.
- The largest avoidable cost the plan removes is a second full run of the range, which is what an unplanned backfill needs roughly whenever it goes wrong (Compute Waste).
- The full discipline costs a day on an operation that often takes an hour. It buys the ability to say exactly what changed, which is worth nothing until the first time it is worth everything.
- Staging and validating means the correction lands later than it could have. Consumers reading wrong numbers for one more day is usually a better trade than consumers reading differently wrong numbers today.
- A single atomic publish across the whole range is the best consumer experience and needs the whole range staged simultaneously — which is the most storage the operation will ever hold, at once.
Backfill lab — re-running a range
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| Property | Append | Merge on key | Replace partition |
|---|---|---|---|
| Idempotent | no | yes | yes |
| Needs a unique key | No | Yes — and a key that is nearly unique is worse than none, because it merges rows that were different. | No, but it needs the partition boundary to match the backfill range exactly. |
| Readers during the run | See a growing, double-counted table. | See consistent rows if the merge is atomic; a table format decides whether it is. | See an empty partition between the delete and the write, unless the format supports an atomic swap. |
| Cost | Cheapest write, most expensive mistake. | Rewrites matched files; more work than an append. | Rewrites the whole partition even for one changed row. |
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.
- GENERALThe five questions apply to any correction of published historical data, in any stack. What changes is how much of the answer the platform gives you for free — a lakehouse table format answers the publish and rollback questions; a directory of Parquet files answers neither.
- TOOL-SPECIFICdbt frames this as an incremental model with a custom range filter and
--full-refreshas the blunt alternative; Airflow frames it as clearing and re-running logical dates. dbt makes staging natural via a temporary relation, Airflow makes the range natural via logical dates, and neither provides the validation gate. - ORG-SPECIFICWhether a restatement needs sign-off before publication depends entirely on who consumes the data. A product analytics table can be corrected by the engineer who found the bug; a table feeding financial reporting usually cannot, and the plan is where that approval attaches.
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 analogue of this plan for code: a change record, a staged rollout, a verification step and a documented rollback. A backfill plan is that document for data, and the two should look similar enough that an engineer recognises it.