Backfills without double-counting

Sooner or later every pipeline has to load the past. You connect a new source and want two years of history behind it. You fix a mapping bug and need to reprocess everything it touched. A schema change splits one field into three and the old rows have to catch up. The job is always the same shape — pour a large slice of history into tables people already trust — and so is the risk: get it slightly wrong and you don’t just fail to add data, you corrupt the data that was fine yesterday.

Here is the canonical version of getting it wrong. A backfill job walks Stripe charges from January to June, inserting as it goes. Somewhere in April it hits a rate limit and dies. The orchestrator does what orchestrators do: it retries, from the beginning. The second attempt dies too; the third finishes green. March now exists in the warehouse three times, Q1 revenue is up and to the right in the next board deck — until someone compares it against Stripe’s own reporting, and the whole dashboard, not just one chart, loses its credibility.

Append-only plus retry equals duplicates

The root cause is not the rate limit and not the retry. Retries are good; you want them. The root cause is that the load was append-only: every execution inserts rows unconditionally, so the job is only correct if it runs exactly once — and “exactly once” is a property no scheduler will ever give you. Networks drop, APIs throttle, containers get evicted. If running the job twice produces different data than running it once, the job is wrong; it just hasn’t failed yet.

The fix is idempotence, and two boring patterns cover nearly every case:

• Merge on a natural key. Every row from the source has an identity — a Stripe charge id, an order id, an event id. Load into staging, then merge/upsert on that key: insert if new, update if changed. Running the job five times produces the same table as running it once.
• Partition-replace by date range. When rows lack stable ids (log-like data, computed aggregates), make the unit of work a time window: delete the target rows for March and insert the fresh March inside one transaction. A window is either fully replaced or untouched — never half-loaded, never doubled.

Batch by time window so a failure resumes

The second structural mistake in the March story is that the job’s unit of work was “everything.” Six months in a single run means a failure at 80% costs you 80%, and the retry starts from zero against an API that just told you to slow down. Backfills should be batched by time window — one day or one week per batch — with progress recorded somewhere durable after each one. When batch 47 of 180 fails, the retry starts at batch 47, and because the writes are idempotent, even re-running a batch that half-completed is safe.

Batching also buys a progress bar — “60% done, ETA Tuesday” instead of “it’s running” — and a throttle, so history loads at night while daytime API quota goes to the incremental sync production depends on.

Frozen history or recomputed history — decide out loud

A harder question hides in every reprocessing backfill: if the fixed logic changes January’s numbers, should January change? Analytics wants recomputation — history should reflect your best current understanding. Finance usually wants the opposite: the January MRR that was reported to the board, the bank, and the tax filing should stay exactly what was reported. Yesterday’s number staying yesterday’s number is not stubbornness; it is what “reported” means.

Both positions are correct for different tables, and the failure mode is not choosing. Our default: keep a snapshot table of reported metric values, frozen at publication time, and let the live models recompute freely. When a backfill moves a historical number by more than a rounding error, that is an announcement — “Q1 activation was restated from 34% to 31% because we fixed the bot filter” — not a silent overwrite someone discovers in a quarter-over-quarter chart.

Proving it worked, and when none of this is warranted

A backfill isn’t done when the job exits zero. Validation is three cheap queries:

• Row counts per day, before and after. Snapshot counts by date before you start; diff after. Days inside the window should change the way you expected; days outside it should not change at all. This one check catches the triple-March failure instantly.
• Reconciliation totals against the source. Sum the measure that matters — revenue, orders, events — per month in the warehouse and in the source system’s own reporting. Agreement to within rounding, or you’re not done.
• Boundary spot-checks. The first and last day of the window, plus a timezone-edge day. An off-by-one window overlaps the incremental load and doubles a single day — small enough to miss, large enough to matter.

And the honest part: for small tables, skip the machinery. If a full reload takes four minutes, truncate-and-reload is the correct, boring answer — trivially idempotent, impossible to double-count, no merge keys or watermark bookkeeping to maintain. Merge and partition logic earn their complexity only when a full reload is too slow to run routinely, or when history is authoritative — the source keeps 90 days and your warehouse is now the system of record. Under a few million rows, reload beats clever every time.

Backfills are exactly the kind of work that decides whether a data stack stays trusted, and exactly the kind nobody budgets for. Running them safely — batched, idempotent, validated, restatements announced — is a standing part of our data-pipeline retainers.