A transform has been writing wrong revenue figures for three days and six downstream tables have consumed it. How do you backfill the corrected data without double-counting anything?

Short answer

Double-counting is not caused by rerunning the job — it is caused by the job appending. Fix the write semantics first so that processing a day produces the same end state no matter how many times it runs, then rerun the affected partitions. After that, the harder half: work out which downstream tables merely read the bad data, which will self-correct, and which accumulated it, which will not.

Make the write idempotent before touching the data

An append-only insert is not rerunnable by construction. Every execution adds rows, so the second run doubles the day. Every safe backfill strategy is a way of making the write express "this partition should contain exactly this" rather than "add these rows".

Partition overwrite is the cleanest when the data is partitioned by the processing date:

-- The write replaces the partition atomically; running it five times
-- leaves exactly the same rows as running it once.
INSERT OVERWRITE TABLE revenue_daily PARTITION (event_date = '2026-08-13')
SELECT ... FROM source WHERE event_date = '2026-08-13';

Merge on a natural key is the option when partitions do not align with the correction:

MERGE INTO revenue_daily t
USING corrected s ON t.txn_id = s.txn_id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Delete-then-insert inside one transaction works where neither is available, and it is only safe if the delete and insert commit atomically — otherwise a failure between them leaves a hole that looks like data loss.

If the target genuinely cannot be changed from append-only, the fallback is to write to a new table or a new run partition and swap a view or pointer once it is validated. That also gives you a trivial rollback, which is worth having when the correction itself may be wrong.

Trace the contamination before you rerun

Repairing the source does not repair everything downstream, and this is where backfills usually go wrong.

A downstream table that fully recomputes from the source for its window will correct itself on the next run. Nothing more is needed beyond triggering it.

A downstream table that accumulates — a running total, a customer lifetime value, a materialised counter updated with += today — has permanently absorbed the wrong numbers. Fixing the source changes nothing, because the wrong value is already baked into a total that will never be recalculated. These have to be identified explicitly and either recomputed from scratch or corrected with a compensating adjustment.

A table that has snapshotted the bad data — a daily extract, a report table, an export sent to a partner — has a copy that no rerun will touch.

So the first real task is a dependency walk from the broken transform, classifying each consumer into recompute, accumulate, or snapshot. The accumulate and snapshot ones are the actual work; the recompute ones are free.

Choosing the window

The obvious window is the three days the transform was wrong, and it is usually too narrow. Two things stretch it.

Late-arriving data. If events for 13 August continued landing on 14 and 15 August, the partition for the 13th was rewritten by later runs, so the contamination extends past the days you think. Backfill by event date across the full late-arrival window, not by the date the job ran.

Boundary effects. Anything computing a rolling window — a seven-day average, a month-to-date figure — read the bad days from outside the bad range. Those need recomputing for their whole window, not just the intersection.

The honest approach is to be generous with the window. Reprocessing extra correct partitions with an idempotent write costs compute and changes nothing, whereas missing contaminated ones means doing this twice.

Validate before you publish

Do not overwrite production and then check. Write the corrected output to a staging location and compare it against something independent before promoting it: a control total from the source system, the same aggregate computed a different way, or row counts and sums per partition against the pre-incident baseline.

The specific check worth running is the delta — for each affected day, the difference between old and new figures. If that delta is not roughly the size and shape you expect from the described bug, your correction is wrong or the bug was not what you think. A correction that changes numbers you did not predict it would change should stop the backfill.

The consumers who already acted

The part candidates most often miss. If finance filed a report, a partner received a settlement file, or a model was retrained on the bad data, fixing the warehouse does not fix any of those, and it may make things worse by making the numbers irreproducible — the report no longer matches the source it was drawn from.

The sequencing that works is to notify before you correct, so consumers know their figures are about to change, and to preserve the old values rather than destroying them. Keeping both the erroneous and corrected versions, with the correction timestamped, means an auditor can reconstruct what was known when. In regulated contexts that is not optional; a silent restatement is itself a finding.

Preventing the three days

The reason this became a backfill rather than a rollback is that nothing noticed for three days. The controls that shorten that are cheap: assertions on the transform's output — revenue non-negative, row counts within an expected band, a day-over-day change threshold that fails the run rather than publishing — plus a reconciliation against the source system on a schedule. Proposing those alongside the backfill is what turns an incident response into a fix, and it is usually the part an interviewer is waiting to hear.

---

© 2026 Preptima. Originally published at preptima.com.