RecoveryGENERALSOURCE-SPECIFICFORMAT-SPECIFIC

Validating a Backfill Before You Publish

Reconcile the range against the source, explain every old-versus-new difference, and prove a period the bug never touched is unchanged — the check people skip.

What actually happens How to build it Can I trust 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 question

The recomputed range is sitting in staging. What has to be true before it is allowed to replace what consumers are reading?

Who needs this

The person who has to defend the new numbers. A finance analyst does not want to hear that the pipeline ran; they want to hear that revenue for March fell by exactly the value of March's refunds, that the total still reconciles to the payment provider, and that February — which was never affected — is bit-for-bit what it was yesterday.

What one row is

Validation is evaluated per partition and then summarised over the range. One row of a validation report is one (partition, check) pair with an old value, a new value and a stated expectation — because a range-level total can be right while individual days are wrong in offsetting directions.

The obvious build

Look at the corrected chart. If the line moved in the direction the fix predicted and the shape still looks like the business, publish. This is genuinely informative — a chart catches gross errors instantly — and it is a check on plausibility rather than on correctness, which is why it passes on a doubled period at the wrong scale as readily as on a correct one.

Why it breaks

The new total is compared only against the old total. Both are internally consistent and neither is compared against the source, so a recompute that lost a whole category of orders looks like the fix working (Reconciliation).

How it breaks with real data

The new total is compared only against the old total. Both are internally consistent and neither is compared against the source, so a recompute that lost a whole category of orders looks like the fix working (Reconciliation).
The difference is observed and not explained. "Revenue went down, which is what we expected" is compatible with the refund fix working, with an over-broad filter, and with a join that dropped rows — three explanations, one observation.
Only the backfilled range is checked. The backfill also altered a partition outside it, because a predicate was wrong, and nothing looked there (What Backfills Break).
The check runs after publication, against the table the backfill just wrote, so the thing it would have compared against no longer exists.
Row counts match perfectly because the range was replaced correctly and every amount in it was cast to null by a parser written for the current schema. Completeness passes; the numbers are all zero (Nullability & Defaults).
The validation is calibrated after seeing the output — thresholds chosen so the run passes — which converts the gate into a formality.

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

There are only three questions a backfill validation can answer, and they are independent. Is the range complete and correct against the source? Is the difference from the old version explainable? And is everything outside the range unchanged? Each catches a class the other two cannot.
Reconciliation against the source is the only check that observes the ends of the journey at once. It compares a closed period's row count and summed measure in the authoritative system against the staged output, and it catches loss, duplication and fan-out in a single query (Reconciliation).
The old-versus-new diff is a check on intent. You predicted the fix would change the numbers a particular way; the diff either matches the prediction or it does not, and a difference you cannot name in one sentence is an unexplained change to published history.
The control period — a period the bug provably did not affect — is the check on blast radius, and it is the one people skip. Every other check looks where the change was supposed to happen, so none of them can see a change that happened somewhere else. This is the check that catches a backfill which quietly altered data that was already correct.
All three must run against the staged output, before publication, because after publication two of the three have lost their comparison basis. Validation is therefore a property of the strategy, not a step you can add later (Planning a Backfill).
A validation that only runs at range level hides offsetting errors. Two days wrong in opposite directions sum to a correct range, which is the arithmetic reason the report is per partition (Grain: What Does One Row Represent?).

Three checks, three different blind spots

A backfill validation is not one check with a threshold. It is three checks that answer different questions, and each is blind to what the others see. Running two of them and calling it validated is the common case, and the one that is usually dropped is the third.

The misses column is the reason all three are needed. Reconciliation cannot see a change outside the range because it only looks inside it. The old-versus-new diff cannot see an error shared by both versions. The control period cannot see anything about the range itself. Together they cover the mechanics; individually none of them does.

The fourth and fifth rows are cheap additions that close the two most common silent failures: a doubled period, which every count-based check tolerates if the counts are compared against the wrong baseline, and a nulled column, which every count-based check tolerates unconditionally.

What to run against staging, before publishing anything

Check	Expresses	Catches	Still misses
Reconcile each backfilled partition against the source: row count and summed measure.	The recomputed range agrees with the authoritative system about what happened.	Rows lost by an over-broad filter, rows duplicated by a fan-out join, a range that recomputed the wrong days, an input that was incomplete when the recompute read it.	Anything wrong identically in the source and the recompute; any column not summed; any period the source cannot answer for; anything outside the range, which it never looks at.
Diff old against new, per partition, on counts and every measure — with a written expectation.	The only differences are the ones the fix was supposed to cause.	Differences of the wrong sign, the wrong magnitude or in the wrong columns; partitions that changed when they should not have; a fix that changed more than it was meant to.	Errors present in both versions, since both were produced by your pipeline. It also misses nothing-changed-at-all if the expectation was never stated numerically.
Exact equality on a control period the bug provably did not affect.	The operation stayed inside its blast radius.	A wrong range predicate, a merge whose staged data contained keys outside the range, a full-table rebuild disguised as a ranged one — a backfill that quietly altered data that was already correct.	Corruption of a different unaffected period than the one sampled. It is a spot check, and its power depends entirely on picking a period with enough volume to be sensitive.
Uniqueness on the business key across the staged range.	Each real-world entity appears once.	The doubled period directly, and any fan-out introduced by the recompute — this is the merge's precondition as well as a check (Upserts and Merges).	Duplicates that are genuinely present in the source, and every failure that loses rows, since losing rows preserves uniqueness perfectly.
Null rate and value distribution per column, staged versus the source period.	The values are the same values, not merely the same number of rows.	A parser written for the current schema silently nulling an older payload shape; a cast that produced null instead of raising; a category that vanished from a dimension join.	A uniform shift in values — every amount multiplied by a wrong currency factor keeps the shape and moves the mean, so pair it with the summed reconciliation above (Distribution Tests).

Run all five against staging. Two of them lose their comparison basis the moment you publish, which makes validation a property of the strategy rather than a step that can be added afterwards.

The check people skip

GENERALThe argument holds for any operation with a declared blast radius, not just backfills — the same reasoning applies to a targeted delete or a partial restore. What varies is how cheaply you can compare against the previous state, which on a snapshot-capable format is one query and otherwise requires a copy taken in advance.

Ask a team how they validated a backfill and you will hear about the range: reconciliation, before-and-after totals, sometimes a distribution check. Ask what they checked outside the range and the answer is usually that there was no reason to check outside it, because the backfill only touched the range.

That is exactly the assumption under test. Every failure where a backfill damaged good data is a failure where the operation reached further than intended — an off-by-one on a partition boundary, a MERGE whose staged input contained keys from outside the range, a --full-refresh that rebuilt the entire table because someone reached for the blunt option under time pressure. In every one of those, all the range-scoped checks pass.

The control period is cheap, sharp and unpopular, because it is the check most likely to produce a finding you did not want. That is what makes it worth running: it is the only one whose failure tells you something you had no other way to learn.

Two validations of the same backfill

Validating the range

Reconcile the backfilled range against the source per partition. Compare the range total before and after and confirm it moved down by roughly the refund value. Eyeball the corrected chart. Publish.

Validating the range and its boundary

All of that, plus: pick a period from before the bug shipped, with normal volume, and assert the staged output for it is exactly equal to what is currently published — same row count, same key set, same measures to the last unit. Assert that the staged relation contains no keys outside the declared range. Then publish.

Every range-scoped check is conditioned on the assumption that the operation stayed inside the range, so none of them can test that assumption. The most expensive backfill failures are not wrong corrections; they are correct corrections that also silently altered data that was already right, and the only check that sees those is one that looks where nothing was supposed to happen.

The three queries

Written out, the validation is short. Three statements, all against staging, all producing rows only when something is wrong — which is the right shape for a gate, because an empty result set is unambiguous in a way a chart is not.

The third query is the one worth reading twice. It is a full outer join between the staged output and the currently published table over a period nobody expected to change, and it returns a row for every key that appears on one side only or whose measure differs. On a correct backfill it returns nothing, and on a backfill that reached too far it returns exactly the damage.

The pass criteria are that queries one and three return no rows, and that every row query two returns has a written explanation. That last part is not automatable and is not optional: an unexplained difference in published history is the thing this entire lesson exists to prevent.

Reconcile, diff, and prove the boundary held

1-- 1. Reconciliation: the staged range against the source, per partition.
2--    Returns a row for every day that disagrees. Empty result = pass.
3WITH src AS (
4    SELECT order_date, COUNT(*) AS n, SUM(amount_minor) AS gross
5    FROM   source.orders
6    WHERE  order_date BETWEEN DATE '2026-01-14' AND DATE '2026-06-30'
7    GROUP  BY order_date
8), staged AS (
9    SELECT order_date, COUNT(*) AS n, SUM(amount_minor) AS gross
10    FROM   backfill_staging.fct_orders__bf20260826_a
11    GROUP  BY order_date
12)
13SELECT COALESCE(src.order_date, staged.order_date) AS order_date,
14       src.n, staged.n, src.gross, staged.gross
15FROM src FULL OUTER JOIN staged USING (order_date)
16WHERE src.n IS DISTINCT FROM staged.n
17   OR src.gross IS DISTINCT FROM staged.gross;
18 
19-- 2. Old vs new, per partition. Every row this returns needs a sentence.
20--    Expectation, written before the run: net_amount falls by exactly the
21--    value of refunded orders; gross and row count do not move at all.
22SELECT p.order_date,
23       p.n            AS old_rows,      s.n            AS new_rows,
24       p.net          AS old_net,       s.net          AS new_net,
25       s.net - p.net  AS delta_net
26FROM (SELECT order_date, COUNT(*) n, SUM(net_amount_minor) net
27      FROM analytics.fct_orders
28      WHERE order_date BETWEEN DATE '2026-01-14' AND DATE '2026-06-30'
29      GROUP BY order_date) p
30FULL OUTER JOIN
31     (SELECT order_date, COUNT(*) n, SUM(net_amount_minor) net
32      FROM backfill_staging.fct_orders__bf20260826_a
33      GROUP BY order_date) s
34USING (order_date)
35WHERE p.n IS DISTINCT FROM s.n OR p.net IS DISTINCT FROM s.net;
36 
37-- 3. The control period. 2025-11 is before the bug shipped, so the staged
38--    logic must reproduce what is published exactly. Any row is a finding.
39SELECT COALESCE(pub.order_id, new.order_id) AS order_id,
40       pub.net_amount_minor AS published,
41       new.net_amount_minor AS recomputed
42FROM (SELECT order_id, net_amount_minor FROM analytics.fct_orders
43      WHERE order_date BETWEEN DATE '2025-11-01' AND DATE '2025-11-30') pub
44FULL OUTER JOIN
45     (SELECT order_id, net_amount_minor FROM staging.fct_orders_control_202511) new
46USING (order_id)
47WHERE pub.order_id IS NULL
48   OR new.order_id IS NULL
49   OR pub.net_amount_minor IS DISTINCT FROM new.net_amount_minor;

Query 3 requires recomputing a period you are not backfilling, which feels wasteful and is the cheapest insurance in the module — it is the only query here that can detect a correction which also altered data that was already right. IS DISTINCT FROM rather than <> throughout, because a null on one side is a difference and <> returns null for it, which a WHERE clause treats as "no problem".

Product detail — verify current documentation

Not every engine supports FULL OUTER JOIN ... USING or IS DISTINCT FROM; some spell the latter <=> or require an explicit null-safe expression. The logic is what matters — treat a null on either side as a difference — and the syntax is worth checking against current documentation for your engine.

How to build it

Most important first.

Write the three checks and their pass criteria before the run, and state the expected old-versus-new delta as a prediction. A prediction made in advance is a test; the same sentence written afterwards is a description.
Reconcile per partition against the source system, not against another derived table. Comparing two things that share an upstream tells you they share an upstream (Missing Rows).
Diff old against new per partition on both row count and every measure that matters, and require every non-zero difference to have an explanation attached to it in the report.
Pick the control period deliberately: a period before the bug shipped, with enough volume to be sensitive, and compare it with an exact-equality check rather than a tolerance. It should be identical, and "approximately identical" is a finding.
Assert uniqueness on the business key over the staged range, because it is the merge's precondition and the direct detector for a doubled period (Deduplication).
Check the distribution, not only the totals — nulls per column, a category breakdown, an amount histogram. A parser failure that nulls one column preserves every count-based check and nothing else (Distribution Tests).
Keep the validation output next to the run, permanently. It is the artefact that answers "what exactly changed in March" long after everyone has forgotten (Dataset Documentation).

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.

Reconciliation guarantees that the staged range agrees with the source on the aggregates you compared, for periods that are closed. It says nothing about open periods and nothing about columns you did not sum.
The old-versus-new diff guarantees you can enumerate the differences. Whether the differences are *right* is a judgement about the fix, and no query settles it (Two Dashboards, Two Numbers).
The control period guarantees only that the specific period you checked is unchanged. It is a sample, and a backfill that corrupted a different unaffected period passes it.
Passing all three guarantees the mechanics are sound. It does not guarantee the new definition is the one the business means, which is the failure no amount of validation reaches.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The validation is itself the quality gate, so its own quality question is whether the checks were written before the output existed and whether their pass criteria are numeric. Both are answerable from the plan (Planning a Backfill).
It misses everything wrong identically in source and recompute, everything in columns nobody compared, and every unaffected period other than the control one you happened to pick.
It also misses the case where the source itself was corrected between the original run and the backfill. The range then legitimately differs from what was published, the reconciliation passes, and the old-versus-new difference has two causes rather than one (Source of Truth).

Freshness

Validation runs entirely on staged data with no freshness obligation, which is why it can be as thorough as it needs to be — it is the one place in a correction where taking longer costs nothing except the delay in publishing.
Reconciliation against a source is only meaningful for periods the source considers closed. A range whose upper bound touches recent activity will show differences that are lateness rather than error, which is one more reason to bound the range at yesterday (Late-Arriving Data).
The validation report is what lets you publish and announce in the same breath, so consumers experience one change with an explanation rather than a change followed by an investigation.

When the schema or meaning changes

If the range spans a schema change, the reconciliation query has to handle both shapes, and a validation that silently coerces one of them is validating its own coercion (Schema Evolution).
If a measure was redefined as part of the fix, old-versus-new is comparing two different quantities and the expected delta must be stated in terms of the definition change rather than as a tolerance (Semantic Changes).
Validation queries decay. One written against a column that later gets renamed silently checks nothing if the query tolerates missing columns, which is why they belong in the tested model rather than in a notebook (Data Tests).

How to re-run this safely

A validation failure on staged data costs a re-run and no consumer impact, which is the entire return on staging. Fix the cause, rebuild the range, validate again.
A validation failure discovered after publication is an incident: restore from the retained pre-backfill copy, then diagnose (Rolling Back Data).
Keep the validation output even for successful runs. When a number is questioned six months later, the report is the difference between an answer and an archaeology project.

What can go wrong

Failure modes

Checking only the range that changed, so a change outside it is structurally invisible.
Comparing the new output against another derived table rather than against the source, which confirms only that both share an upstream.
Range-level totals that pass while individual partitions are wrong in offsetting directions.
Thresholds chosen after seeing the output, so the gate certifies whatever was produced.
A control period chosen from a quiet window with too little volume to be sensitive to the error being looked for — the mitigation failing rather than the operation.
A green validation on data whose columns are all null, because every check was count-based (Volume Anomalies).

Misreads

"The numbers changed the way we expected, so it worked." Expectation is a prior, not evidence. The same direction of change is produced by the fix, by an over-broad filter and by a dropped join — the check that separates them is reconciliation against the source.
"We validated the range." The range is the part you meant to change. The check that matters most is the one on the period you did not (What Backfills Break).
"Row counts match, so the data is fine." Counts survive nulled columns, wrong values, wrong dimensions and correctly-replaced-but-wrongly-computed partitions. Counts are the first check, never the only one.
"We will spot it on the dashboard." A dashboard shows a shape. A doubled month at the wrong scale, a nulled column rendered as zero, and a correctly corrected series all produce plausible shapes (Stale Dashboards).

Operating it

How you see it in production

A per-partition validation table with old value, new value, expected delta, actual delta and the explanation, kept with the run record.
The control-period equality result, recorded explicitly as passed or failed rather than implied by silence — a check that is not reported is a check nobody knows was skipped.
Null rate per column on the staged range versus the same columns in the source period, which is the cheapest detector of a parser mismatch (The Dimensions of Data Quality).

What changes at 10x and 100x

At 10x range the per-partition report becomes too long to read and needs summarising by exception: partitions whose delta is unexplained, and partitions outside the range that moved at all.
At 100x, reconciling every partition against the source stops being affordable and validation moves to a sampled design — which trades a probability of detection for cost and should be stated as that trade rather than quietly adopted.
More columns scale the diff linearly and the explanation burden faster, because each changed column needs its own sentence. That pressure is a good argument for narrow, purposeful backfills.

What drives cost here

Validation is a handful of aggregate queries over the staged range plus one over the source. Its cost is proportional to the range, which means it is a fraction of the recompute it gates.
The control-period check adds one more scan of a period you are not otherwise touching, and it is the cheapest of the three by volume and the most valuable by outcome.
The cost avoided is the restatement — republishing a range twice, plus the human time spent establishing which of the two published versions any given report used (Compute Waste).

What this approach costs

Thorough validation delays the correction. Consumers read wrong numbers for one more day, which is almost always better than reading a second, differently wrong set.
Exact-equality on the control period will occasionally fail for legitimate reasons — a late-arriving row landed in that period since the original run — and each of those costs an investigation. That is the correct price for a check this sharp (Late-Arriving Data).
Per-partition checks catch offsetting errors and produce a report nobody wants to read. Summarise by exception, but compute at partition grain.

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.

GENERALReconcile against the source, explain the diff, prove the untouched period is untouched — the three checks are independent of engine and format. What varies is whether comparing against the pre-backfill version is a query or an archaeology exercise, which the table format decides.
SOURCE-SPECIFICReconciliation depends on the source being able to answer for a historical period at all. A Postgres source can be queried for March; a SaaS API with a ninety-day window cannot, and there the strongest available check is against the retained raw layer, which is weaker because raw and the recompute can be wrong together.
FORMAT-SPECIFICOn Iceberg or Delta the old-versus-new diff is a query against the previous snapshot; on plain files it requires having copied the affected partitions somewhere first. Same check, and in one case it is free and in the other it must be planned before the run.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasetransactions-and-acid aggregation-and-grouping joins

Backendduplicate-detection contract-tests

Observabilitycorrelation-vs-causation

Cloudrestore-testing

Domains that do not exist yet

— DevOps / Production Engineering owns the equivalent gate for code — a verification step that runs against a staged deployment and blocks promotion. The control-period check is the data analogue of a regression test: it asserts that the thing you did not mean to change did not change.