Backfilling Historical Data While the Live Write Path Is Running

Backfilling historical data while the live write path is running involves both bulk loading and concurrent writes. Two processes must share a destination without duplicating rows, starving production, or exposing half-finished results. Safe backfills depend on deliberate choices about idempotency, throttling, write isolation, and reconciliation.

Why backfilling alongside a live write path is harder than it looks

A backfill is the normal pipeline pointed at the past. It still needs source read capacity, destination tables, and merge or compaction capacity that the live pipeline is already using. A new column may need historical values, a transformation bug may require retroactive correction, or an outage may leave gaps. None of those needs pauses incoming data. The old work and the new work must coexist.

That overlap creates three distinct ways to fail. Uncoordinated writes can produce phantom duplicates that inflate metrics into the classic “4× revenue on Monday” outcome. An unthrottled scan can exhaust source capacity or saturate destination merge threads, slowing the live path even when every row is correct. Meanwhile, queries can read a mixture of completed and unfinished history without any indication that the result is incomplete. Each failure needs its own safeguard, because keeping duplicate rows out does not set aside resources or hide results that are only partly filled in.

How the two write paths actually interact at the destination

The backfill process scans the source table in batches and writes those batches to the destination. At the same time, a change data capture, or CDC, process reads the transaction log and queues live changes for that same table. Both paths can write to the same logical row.

Would running the backfill first avoid that overlap? It changes where the work waits. In a sequential approach, live changes accumulate in the write-ahead log, or WAL, or in a message buffer while the historical scan runs. When the scan finishes, the consumer must drain that backlog, which causes replication lag and leaves the destination behind. Parallel processing keeps consuming the WAL or Kafka buffer as changes arrive, so there is no large backlog to drain, but both writers write to the destination at once.

The destination may have historical partitions that are filled next to ones that have yet to be filled. A query can execute successfully and still return a semantically incomplete answer, because valid table structure says nothing about historical coverage. The reader has no native completion signal.

MergeTree-family engines create sorted, immutable data parts on insert and consolidate small parts through background merges. Historical writes arriving out of order increase that merge work and can exhaust the per-table part count limit. The pressure is at the destination, so a source that handles the scan comfortably does not establish that the overall pipeline can sustain it.

Why idempotency is the prerequisite that makes everything else safe

Idempotency means repeating a write operation produces the same result. Without it, a crash, retry, or accidental re-run can append rows that already exist, regardless of careful scheduling. Retries must be safe by design. Otherwise, every interruption turns recovery into a question of which writes succeeded before the failure.

The practical foundation is a deterministic write strategy. Partition overwrite ties a run to a fixed date or partition key and replaces that partition entirely. Running the same date again then produces the same result. Where partition replacement is unavailable, a MERGE or upsert on a primary key updates existing rows and inserts new ones. These strategies make repeated writes converge, although coordinating them with live updates remains necessary.

The distinction between backfill, reprocess, and catchup clarifies why raw append is risky. A backfill works on dates that have no rows, while reprocessing works on dates that already have them. Catchup runs missed intervals automatically, but its name does not tell whether those intervals have any rows. Existing data decides the write strategy. An idempotent design covers all three cases without relying on an operator to remember which dates were previously loaded.

Databricks pipeline documentation treats duplicate handling as a table-design prerequisite and recommends appending to bronze tables while downstream silver layers deduplicate. That makes idempotency a layered responsibility. Schema validation is part of that work too, because missing columns or changed types can leave values wrong even when the row counts stay the same. Once repeat execution is genuinely safe, backfilling can become operationally boring. The danger lies in a pipeline that was never designed to revisit its own output.

Scoping and throttling the backfill so the live path keeps running

Bulk source scans use the same I/O, CPU, and memory as production queries, while historical inserts and live writes draw on the same resources at the destination. Idempotency does not prevent either contest. Insert batch size and concurrency on the backfill path should be limited to accommodate historical work while keeping the live path running.

Source isolation begins with where the scan runs. Reading from a replica rather than the primary separates backfill read load from production query load. That does not remove the need to consume live changes, however. In the sequential approach, an open CDC slot whose consumer is not draining can allow WAL to grow until it fills disk. Deferring streaming therefore exchanges immediate concurrency for a backlog that has its own resource cost.

The orchestrator also needs an explicit limit. Airflow illustrates why a historical date range should not be treated as permission to run every interval simultaneously. Its documented default is catchup=False, but backfill concurrency still needs deliberate control. A parameterized date range, processed one partition at a time or in small controlled batches, bounds the work presented to both ends of the pipeline.

Databricks recommends a separate one-time flow per historical year for append-only backfills, using the ONCE option so those flows do not continue after the initial load. Its documentation also recommends raising max workers on non-serverless clusters so historical work can run while live streaming still has the resources it needs. Serverless compute with enhanced autoscaling scales automatically. Those options add compute capacity, while the layout at the destination still affects how much work it takes for writes to settle.

For storage engines that merge data in the background, the load comes from both accepted inserts and background merges. Backfill rows need to land in their historical partitions upfront, rather than polluting a catch-all current partition and its primary index. LSM-tree engines present a related constraint. Spreading controlled write concurrency across the historical key space keeps writes from piling up in one range and causing the hot-partition and memtable-pin pattern.

Write isolation: keeping the destination queryable while the backfill runs

A safely retried, carefully throttled backfill can still mislead readers. Neither safeguard prevents a query from combining finished history with an unfinished date range. What should a reader see before the fill is complete? The isolation boundary needs to answer that question explicitly, rather than leaving it to whichever batches have arrived.

The dual-table atomic swap pattern keeps the previous complete table available while the backfill writes into a shadow or staging table. After the new table is complete and verified, an atomic swap places it on the live query surface. Readers avoid the partly populated replacement. Artie’s online backfill documentation describes dual writes plus an atomic swap as the mechanism for keeping the destination queryable throughout the operation. The dual-write component is important because live changes continue while historical data is being assembled.

When replacing the whole table is impractical, partition-level isolation narrows the boundary. Backfill output goes to a staging partition or shadow partition key, and validated partitions are promoted individually. Date-filtered queries then reach complete partitions. This is especially useful when only a date range needs reprocessing, though partition completeness is not the same as a whole-table transition occurring at once.

Elastic’s backfill documentation describes another part of destination visibility for Elasticsearch TSDB. When a document’s timestamp falls outside existing backing indices, the system determines the time boundaries and creates the missing past backing indices atomically before writing. That preserves a complete, time-bounded index structure as history arrives out of order. Structural completeness is distinct from completing the historical load.

The same distinction applies to automated readers. An AI agent querying through a persistent session has no protocol-level signal that a table is mid-fill. The practical response is to expose only complete partitions or provide a backfill-in-progress flag through a readable metadata resource. A valid response alone cannot tell either an agent or a person whether the requested history is ready.

Reconciliation: verifying correctness after the backfill completes

A successful job status establishes that execution finished. Reconciliation compares the destination with the source before the replacement table or partition is promoted. Completion is a data claim. The checks need to test missing records, duplicate identities, and incorrect values separately.

Start with row counts for each partition, comparing source and destination over the same scope. A mismatch can reveal a missed batch, a dropped row, or an undetected duplicate. Next, check the primary or deduplication key distribution for keys appearing more than once. Matching counts alone are not enough. A missing record and an extra copy can leave the total unchanged while the underlying data is wrong.

Then compare a sampled aggregate, such as a sum, mean, or distinct count, for the same period. Drift can point to a transformation error, a schema mismatch, or a join fan-out that multiplied rows. Volume anomalies during the run offer an earlier warning of some of these failures, including a Cartesian product or a deduplication step that stopped working. Reconciliation still has to establish the final result. An early warning is not a substitute for comparing completed output.

The overlapping time window needs one final check before lifting isolation. Reconciliation tests the handoff between the writers by checking that rows both the backfill and live CDC paths touch end up with one canonical value and no conflicting versions left behind. Repeated backfills can also expose a wider observability gap, with bad data discovered only when downstream reports break. In that case, the recurring repair is a symptom of failures that went undetected upstream.

How these patterns apply to analytical databases running at scale

At high volume, architecture determines how much protection scheduling alone can provide. How data is laid out affects merge pressure, how compute is shared affects contention, and how writes are designed affects whether historical and live updates converge. These choices precede the backfill command. A large historical run exposes the boundaries already present in the system, including the ones that were never made explicit.

Separating object storage from query compute creates room for an isolated backfill allocation that reads the same storage without taking live query compute. Sandboxed or branched compute extends the separation to validation. A backfill against a branch or a copy derived from read-only data can undergo schema checks, aggregate comparisons, and row-count reconciliation before its output reaches production queries. This applies the staging principle at the compute layer.

Scale also makes write provenance harder to reconstruct. At hundreds of terabytes, a reconciliation discrepancy may require knowing which rows came from the historical path, which came from the live path, and in what order. Full query and write logging on backfill compute supplies that audit trail without touching the live path’s own record.

Schema-as-code tools address another risk before execution begins. Describing, validating, and deploying the target schema ahead of writes moves compatibility checks to deployment time, instead of leaving a query to reveal corrupted values later.