How would you backfill two years of history into a table that a nightly incremental load keeps current?

## Frame the decision first A two-year backfill is a capacity and risk exercise more than a coding one. Before any query runs, get four numbers: how many rows and bytes the range holds, what the source can spare in read throughput without hurting production, what the target write will cost, and what the deadline actually is. Those decide chunk size, concurrency and whether the backfill runs for six hours or six days — and the honest answer is often that a slower backfill is strictly better, because nothing downstream is waiting on the oldest month. ## Rule one: separate job, separate state The most common failure is to rewind the nightly job's watermark to two years ago and let it run. That does three bad things at once: today's freshness now depends on a job that will take many hours, the whole range becomes one query with no resume point, and any failure restarts from the beginning. Run the backfill as its own job with its own state, and leave the nightly load untouched and on schedule. Freshness for recent data is preserved throughout, the two jobs can proceed at different speeds, and you can pause or kill the backfill at any moment without touching the production path. ## Rule two: bounded, replayable chunks Split the range along a natural boundary — usually the same partition column the target uses — into half-open windows: `[2024-01-01, 2024-02-01)`, and so on. Every chunk is a run of the ordinary extract job with explicit `lo` and `hi` parameters. That gives you three properties for free: any chunk can be re-run in isolation, progress is measurable in chunks completed, and the failure domain is one chunk rather than the whole range. Size chunks so a single one finishes in a comfortable unit of time — long enough that per-chunk overhead is negligible, short enough that losing one to a failure costs minutes rather than hours. Uneven data density matters here: a chunking scheme calibrated on a quiet month will produce one monstrous chunk over a peak period, so size by row count where you can rather than by calendar span alone. ## Rule three: a manifest, not a log line Keep a small table with one row per chunk: bounds, status, attempt count, rows read, rows written, start and end time. The job's driver picks the next chunk in `pending` state, marks it `running`, and marks it `done` only after the target write commits. This is what makes the backfill resumable and, just as importantly, auditable. When someone asks in three weeks whether March 2025 was actually loaded, the manifest answers. It also makes a partial backfill an honest object: you can tell consumers exactly which range is complete instead of "most of it, probably". ## Rule four: the write must tolerate overlap The backfill and the nightly job will touch the same keys — a row updated last week may also fall in a chunk you are replaying, and lookback windows widen the overlap further. Two properties make that a non-issue: - **Keyed writes.** Merge on identity, or replace whole partitions where the chunk maps onto partitions exactly. Never append. - **Order guards.** Update only when the incoming change value is at least the stored one, so an old chunk landing after the nightly job cannot overwrite fresher data. With both, the backfill needs no coordination with the nightly job beyond not saturating shared resources. Without them, you need locks, freeze windows and a change-management conversation — which is why the guards are worth building before the backfill, not during it. ## Rule five: protect the source The backfill's reads are the part that can cause an incident in someone else's system. Read from a replica rather than the primary where one exists. Cap concurrency to a fixed small number of chunks in flight, and make that number a knob you can turn down without redeploying. Prefer many bounded queries over one long-running scan, which on MVCC engines holds an old snapshot open and causes version bloat. Schedule the heavy phase outside the source's peak. And monitor the source, not just your job: replica lag, source CPU, lock waits and the source team's own alerts are the signals that tell you to throttle. A backfill that finishes in four hours and pages another team is a failure. ## Rule six: sequence for the consumers Order is a product decision. Newest-first gets the recent, most-queried history usable while the long tail is still loading, and it is the right default for most analytical consumers. Oldest-first suits a rebuild that must be strictly chronological, or where downstream models assume contiguous history from the beginning. Decide deliberately, publish the plan, and expose which range is complete so nobody builds a report on a half-loaded table and quietly reports wrong numbers. Where consumers cannot tolerate a partially loaded table at all, backfill into a shadow table and swap once complete — at the cost of double storage and a period where the shadow needs its own catch-up from the nightly stream. ## Rule seven: budget the target side and clean up after Many small window writes against a columnar or file-based target create many small files, fragmented partitions and merge debt. Plan the compaction or optimise step as part of the backfill, not as a surprise afterwards, and account for its cost in the estimate. On credit-billed warehouses, decide up front what the backfill is allowed to spend and put a ceiling on concurrency to enforce it. ## Close the loop When the last chunk is done, verify rather than declare: reconcile row counts per chunk against the source for a sample of ranges, check that no key exists twice, and confirm the nightly job's watermark was never touched. Then delete or archive the backfill job's state so the next person cannot accidentally resume it.