Backfills Are the Real Test of a Pipeline

A pipeline that has never been backfilled has never really been tested. The scheduled path is the easy case: one partition of data, arriving roughly when expected, processed once, against a table that does not yet contain the answer. A backfill inverts every one of those conditions. You are asking the same code to process many partitions, possibly out of order, possibly twice, against a table that already holds some version of the result. Everything the daily schedule papered over becomes visible at once.

This matters to whoever owns the platform rather than whoever chose it. Backfill behaviour is not a feature you evaluate; it is a property that emerges from how the writes were built, and it is expensive to retrofit.

What actually breaks when you run a backfill?

Four things, roughly in order of how often they bite.

The first is scope derived from the clock. A task that computes its own window from the current date cannot be pointed at last March. You can fake it with environment variables and a stopwatch, but you have lost the ability to reason about which partitions have been processed.

The second is append-only writes. Appending is idempotent only in the sense that it never fails; it happily produces duplicates. If the loader appends and the downstream model deduplicates, the deduplication logic is now load-bearing infrastructure that nobody tests.

The third is side effects that live outside the target table. Surrogate keys drawn from a sequence, slowly-changing dimension processing that compares against the current state, notifications, file names stamped with the execution time, calls to an external API that charges per request. Each of these turns a retry into a semantic change.

The fourth is concurrency. Backfills are the one workload where you deliberately run many instances of the same task in parallel. If two instances can touch the same target partition, you have a race that the daily schedule will never surface.

Why is idempotency a property of the write, not the task?

Because the task is transient and the table is not. “Idempotent task” is a comfortable phrase that hides the actual requirement: running the write twice with the same inputs must leave the target in the same state as running it once. That is a statement about the target table, and it can only be satisfied by the write pattern.

There are three honest patterns. Delete-and-reinsert scoped to a partition boundary: the cheapest to reason about, and the one that fails loudly if your partition boundaries do not line up with the source’s change window. Merge on a business key: correct when the source genuinely has a stable key at the target grain, and quietly wrong when it does not, because the merge will happily update the wrong row rather than duplicate it. Full rebuild of the affected partitions from a staged snapshot: expensive, but the only pattern that survives a change in the transformation logic itself.

What is not an honest pattern is appending and hoping. Nor is truncate-and-reload dressed up as incremental refresh — incremental refresh processes only changed partitions rather than a full dataset, and if your “incremental” model rewrites the whole table on every run, you have a full load with extra vocabulary.

-- the partition key arrives as a parameter, never from now()
delete from analytics.fact_orders
where order_date >= :window_start
and order_date <  :window_end;

insert into analytics.fact_orders
select ...
from staging.orders
where order_date >= :window_start
and order_date <  :window_end;

-- the assertion that makes it a test rather than a hope:
-- running this block twice must not change row counts,
-- and the grain must stay unique afterwards.

The snippet is unremarkable, which is the point. The interesting decision is not the SQL, it is whether the delete predicate and the insert predicate are guaranteed to describe the same set of rows. If the source can move a record across the partition boundary — an order whose date is corrected — then the delete misses the old copy and you have created a duplicate that no uniqueness test on the new partition will catch.

What does partition scoping have to do with restatement?

Everything, because there are two clocks. Event time is when the thing happened. Load time is when you found out. Choosing which one partitions the table decides which class of problem is cheap and which is expensive.

Most warehouses need both, which usually means partitioning the fact by event time and carrying load time as a column so you can answer “what did we know on Tuesday”. The failure mode is picking load time for convenience during build and discovering at the first restatement request that the table cannot express the question.

Where should orchestration state live?

As little of it as you can manage. There is a strong temptation to make the orchestrator the system of record for what has been processed — a watermark table, a run-history table, a set of markers. Every one of those is a second copy of the truth that can disagree with the warehouse.

If the write is genuinely partition-scoped and idempotent, then “has this partition been processed” is answerable from the target table itself, and the orchestrator’s job shrinks to deciding which partitions to attempt. Retries become free. Reprocessing a range becomes a parameter rather than a manual intervention. A failed run leaves no half-state to unwind, because a half-written partition is simply overwritten by the next attempt.

When orchestration state is unavoidable — a source that only exposes a change feed you cannot rewind, for instance — treat it as a durable asset with its own tests, not as scheduler bookkeeping.

How do you choose a write strategy?

Work down from what the source can guarantee, not up from what the warehouse makes easy.

What does getting it wrong actually cost?

Rarely an outage. The costs are slower and harder to attribute.

Duplicates that survive into a metric, so a number moves when someone re-runs a job and nobody can say which value was right. Backfills that take days because scope is not parameterised and the only way to reprocess a quarter is to run the daily job ninety times by hand. A team that becomes frightened of re-running anything, which means defects get patched with manual SQL rather than fixed in the transformation, which means the transformation and the table diverge permanently.

On lakehouse tables there is a second-order cost: a backfill that writes many small files per partition degrades read performance until someone compacts, and table maintenance is exactly the kind of work that has no owner until it becomes urgent. Budget for it in the same conversation as the backfill, not afterwards.

None of this is glamorous, and none of it appears in a platform evaluation. It is decided in the first fortnight of building, by whoever writes the first loader, and inherited by everyone after. The reasonable response is to make partition-scoped, re-runnable writes the default shape for every table, prove it with a double-run test, and treat any pipeline that cannot be pointed at an arbitrary window as an open defect rather than a quirk.