August 24, 2026 · StartupQuickstart
Backfills without double-counting
Loading history is easy; not corrupting what's already there is the skill. Idempotent loads, resumable batches, frozen snapshots, and the checks that prove a backfill landed exactly once.
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 always has the same shape, pouring a large slice of history into tables people already trust, and so does 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 Stripe's rate limit (live mode allows 100 requests per second across the account, and anything above that gets a 429) 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, and Q1 revenue is up and to the right in the next board deck until someone compares it against Stripe's own reporting. At that point the whole dashboard loses its credibility, not just one chart.
Append-only plus retry equals duplicates
The root cause is the load pattern, not the rate limit or the retry. Retries are good; you want them. The load was append-only: every execution inserts rows unconditionally, so the job is only correct if it runs exactly once, and no scheduler will ever promise you that. Networks drop, APIs throttle, containers get evicted. Airflow's own best-practices guide says to treat tasks like database transactions that produce the same outcome on every re-run, and warns that an INSERT during a re-run can lead to duplicate rows. dbt's documentation says the same of its append strategy: it doesn't check for duplicates. 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 or upsert on that key, inserting if new and updating if changed. Running the job five times produces the same table as running it once. Deduplicate staging first; PostgreSQL's MERGE documentation asks you to ensure the join produces at most one candidate change row per target row, and raises an error when it doesn't.
- 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. This is what dbt calls insert_overwrite or delete+insert. A window is either fully replaced or untouched, never half-loaded and 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. Inside each batch, back off on 429s with jitter, as Stripe recommends, so the backfill slows down instead of falling over.
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, so history reflects 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. Keeping yesterday's number fixed isn't stubbornness. That 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, announce it (“Q1 activation was restated from 34% to 31% because we fixed the bot filter”) instead of letting someone discover a silent overwrite 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. For Stripe, the Reports API exposes the same financial reports as the Dashboard; just pass the timezone you report in, because it defaults to UTC. 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. It's trivially idempotent and can't double-count, with 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 because the source keeps 90 days and your warehouse is now the system of record. In our experience, under a few million rows, reload beats clever.
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 and with restatements announced, is a standing part of our data-pipeline retainers.
Sources
- Best Practices, Apache Airflow documentation
- About incremental strategy, dbt Developer Hub
- MERGE, PostgreSQL documentation
- Rate limits, Stripe documentation
- How the Reports API works, Stripe documentation
Want systems like this built for you?
We build and run data pipelines, websites, and AI automation for startups.
