Your Data Pipeline Cannot Be Rerun and That Is a Design Flaw
Table of Contents
- The Pipeline You Cannot Rerun
- Append Versus Replace
- Partitioning Defines the Unit of Work
- Late-Arriving Data Breaks Assumptions
- Backfills Are the Real Test
- Data Quality as a Gate
- Schema Change Without Breaking Consumers
- Making Failure Cheap
- Common Pitfalls
- Conclusion
- Frequently Asked Questions
Key takeaway: A pipeline run should replace the output for its partition rather than adding to it. That single property makes retries safe, backfills routine, and failures recoverable without manual cleanup.
The Pipeline You Cannot Rerun
A daily job fails at three in the morning, having written half its output. Someone must determine what was written, delete it, and rerun.
Or worse: the job succeeds twice due to a scheduler retry, and every row is now duplicated. The duplication propagates into aggregates, which propagate into reports, and someone notices a fortnight later that revenue numbers are wrong.
Both problems have the same cause. The pipeline appends, which means running it twice produces different results than running it once.
The consequences accumulate across everything a data team does. Retries require manual cleanup, so failures need human attention rather than automatic recovery. Backfills are dangerous, so historical corrections are avoided. Nobody can verify whether a run completed correctly, because there is no defined correct output for a given input. And duplicates appear in downstream data with no obvious source.
The fix is a design property rather than a tool: a run should produce the complete, correct output for a defined slice of data, replacing whatever was there before. Run it once or five times and the result is identical.
Append Versus Replace
The distinction that determines whether everything else works.
Append semantics. Each run adds rows. Running twice doubles the data. Recovery from partial failure requires knowing exactly what was written.
Replace semantics. Each run produces the output for its partition and overwrites it. Running twice is identical to running once. Partial failure leaves the previous state intact until the new output is complete.
-- Append: rerunning duplicates
INSERT INTO daily_summary
SELECT date, region, sum(amount)
FROM orders WHERE date = '2026-08-03'
GROUP BY date, region;
-- Replace: rerunning is a no-op in effect
DELETE FROM daily_summary WHERE date = '2026-08-03';
INSERT INTO daily_summary
SELECT date, region, sum(amount)
FROM orders WHERE date = '2026-08-03'
GROUP BY date, region;The second form is idempotent, and the improvement is larger than the code difference suggests. It makes retries free, makes backfilling a matter of running historical partitions, and makes partial failure harmless.
Better still is an atomic swap: write to a temporary location, verify, then replace the partition in one operation. That removes the window during which the delete has happened and the insert has not — a window in which consumers see missing data.
Modern table formats support atomic partition replacement directly, which makes this the default rather than something to engineer. Where they are unavailable, writing to a staging table and swapping is the equivalent.
Partitioning Defines the Unit of Work
Partition choice determines what “one run” means, and it is the most consequential design decision in a pipeline.
The partition column should be the dimension the pipeline processes incrementally — usually a date. That makes the unit of work explicit: this run owns this date and replaces it entirely.
Granularity involves a real trade-off. Daily partitions are the common default: manageable partition count, reasonable file sizes, natural alignment with reporting. Hourly partitions permit fresher data and lower-latency reprocessing, at the cost of many more partitions and smaller files. Monthly partitions are efficient to query and mean a reprocess covers a large volume.
Two failures to avoid.
Too many small partitions. Thousands of tiny files impose per-file overhead on every query, and the metadata itself becomes a bottleneck. This is the more common error, and it arrives gradually as hourly partitions accumulate over years.
Partitioning by high-cardinality columns. Partitioning by customer identifier produces one partition per customer, which is unmanageable. High-cardinality columns belong in clustering or sort order rather than partitioning.
A useful heuristic: partitions should be large enough that per-partition overhead is negligible relative to their content, and small enough that reprocessing one is quick. Compaction jobs that merge small files within a partition address the first concern without changing the partition scheme.
Late-Arriving Data Breaks Assumptions
A pipeline processing yesterday’s data assumes yesterday’s data has arrived. Frequently it has not.
Events are delayed by client buffering, mobile devices that were offline, upstream retries, and timezone confusion. An event that occurred yesterday may arrive today, or next week.
This forces a distinction that must be made explicitly:
Event time. When the thing happened.
Processing time. When your pipeline saw it.
Partitioning by processing time is simple and produces incorrect aggregates, because yesterday’s partition lacks the events that arrived today. Partitioning by event time produces correct aggregates and means old partitions require updating when late data arrives.
Correctness requires event time, which makes idempotent replacement necessary rather than merely convenient — late data means reprocessing historical partitions is a routine operation rather than an exception.
The strategies for handling it:
A lookback window. Each run reprocesses the last N days rather than only yesterday, catching late arrivals within the window. Simple, effective, and it costs N times the compute.
Watermarks. Track how complete each partition is believed to be, and reprocess when new data arrives for a partition considered settled.
Explicit reprocessing triggers. Detect late data on ingestion and queue the affected partitions for recomputation.
Accepting a cutoff. Declare that data arriving more than N days late is discarded. Simple and it must be a documented decision rather than an accident.
The lookback window is the pragmatic default for most pipelines, and the window should be chosen from measured arrival delay rather than guessed.
Backfills Are the Real Test
A backfill — reprocessing historical data after fixing logic or adding a field — is where pipeline design is genuinely tested.
If your pipeline is idempotent and partitioned, a backfill is running the same job across a date range. If it is not, a backfill is a bespoke project with manual cleanup and meaningful risk of corrupting good data.
What makes backfills routine:
Idempotent partition replacement. Each historical partition is reprocessed independently and safely.
Parameterised date ranges. The job accepts the partition to process rather than assuming yesterday. A job hard-coded to yesterday cannot backfill at all.
No dependence on current state. Logic that reads “the current customer tier” produces wrong results when reprocessing last year, because tiers changed. Historical accuracy requires the state as of the event time, which means the source data must record it.
Bounded parallelism. Backfilling two years of daily partitions is 730 jobs. Running them all at once overwhelms the source systems and the warehouse.
Progress tracking and resumability. A backfill interrupted at partition 400 should resume rather than restart.
That third point is the one that most often makes historical reprocessing produce wrong answers. A pipeline that joins to current dimension tables cannot reproduce historical output, because the dimensions have changed. Slowly-changing dimension patterns that retain history exist specifically for this, and they must be designed in before the backfill is needed.
Data Quality as a Gate
A pipeline that propagates bad data is worse than one that fails, because the bad data spreads into everything downstream and is expensive to trace.
Checks worth running as gates rather than as reports:
Row count within expected bounds. A partition with ten rows where a thousand is normal indicates upstream failure. So does ten thousand.
Null rates on required fields. A sudden rise indicates an upstream schema or logic change.
Referential integrity. Foreign keys that do not resolve indicate ordering problems or missing upstream data.
Value distribution. Numeric ranges, categorical value sets, and detection of unexpected new categories.
Uniqueness. Duplicate primary keys indicate exactly the non-idempotency this article is about.
Freshness. The source data covers the period the run expects.
The critical design choice is what happens on failure. A check that logs a warning is a report nobody reads. A check that fails the run and leaves the previous partition in place prevents bad data from reaching consumers — and is only possible because the pipeline uses replacement rather than append.
The pattern that works: write to a staging location, run the checks, and swap into place only if they pass. Failure leaves the previous good data visible and alerts someone.
Schema Change Without Breaking Consumers
Pipeline outputs are contracts. Consumers build reports and models on them, and breaking them breaks downstream work.
Changes that are safe: adding a nullable column, adding a new partition, widening a numeric type, adding values to a categorical field where consumers handle unknowns.
Changes that break consumers: removing or renaming a column, changing a type incompatibly, changing the meaning of an existing column, and changing granularity — one row per order becoming one row per line item.
That last category is the most damaging because it is silent. A column whose meaning changed still parses, still has the right type, and now means something different. Every downstream calculation using it is now wrong, and nothing errors.
Practices that make evolution survivable: additive change wherever possible, with old columns retained until consumers migrate. A new column rather than a redefinition when meaning changes. Documented schema versions so consumers know what they are reading. Known consumers recorded, so you can identify who to notify. And a deprecation period with actual notification rather than a silent change.
The parallel with API versioning is exact, and data pipelines are usually treated with less rigour despite the consumers being equally dependent.
Making Failure Cheap
The goal is a pipeline where failure is unremarkable rather than an incident.
Idempotent partition replacement. The foundation — retries are free.
Small, independent units of work. A per-day job that fails affects one day. A monolithic job affects everything.
Automatic retry with backoff. For transient failures, which are most failures.
Alert on failure with context. Which partition, which step, what error. An alert saying only that a job failed requires investigation before it can be acted on.
A dead letter path for unprocessable records. A single malformed record should not fail an entire partition, and it should not be silently dropped either.
Explicit dependency declaration. A downstream job should not run on incomplete upstream data. This requires the orchestrator to know the dependency rather than relying on schedule timing.
Observable lineage. When a number is wrong, tracing which pipeline produced it and from what inputs should take minutes.
That dependency point deserves emphasis. Pipelines coordinated by schedule — the downstream job runs an hour after the upstream one, hoping it finished — fail silently when the upstream job is slow. The downstream job runs on incomplete data and produces plausible wrong numbers. Explicit dependencies make it wait instead.
Common Pitfalls
Append-only writes. Retries duplicate, backfills are dangerous.
Partitioning by processing time. Produces incorrect aggregates when data arrives late.
Hard-coded to yesterday. Cannot backfill at all.
Joining to current dimension state. Historical reprocessing produces wrong answers.
Quality checks that only warn. Bad data reaches consumers regardless.
Schedule-based coordination. Downstream jobs run on incomplete data.
Too many small partitions. Per-file overhead dominates query cost.
Conclusion
The property that makes data pipelines operable is that a run replaces the output for its partition rather than adding to it. That single change makes retries free, backfills routine, and partial failure harmless — and it is the prerequisite for quality checks that can actually block bad data, because blocking requires the previous good partition to remain in place.
Partition by event time rather than processing time, because correctness requires it and because late-arriving data is normal rather than exceptional. Accept that this makes historical reprocessing routine, which the idempotent design already handles.
Parameterise the date range so backfills are possible at all, and preserve historical dimension state so reprocessing produces the answer that was correct at the time rather than the answer that is correct now.
Then gate on quality — write to staging, verify, swap — and declare dependencies explicitly rather than coordinating by schedule. A downstream job that runs on incomplete upstream data produces wrong numbers that look right, which is the most expensive failure mode available.
Frequently Asked Questions
How is an existing append-only pipeline made idempotent? Add a partition column reflecting the processing unit, then change the write to delete-and-insert or an atomic partition swap. Historical data may need deduplication once as part of the transition.
What partition granularity is appropriate? Daily suits most cases. Hourly where freshness matters, accepting more partitions and smaller files. Add compaction to merge small files within partitions regardless.
How should late-arriving data be handled? A lookback window reprocessing the last several days is the pragmatic default. Choose the window from measured arrival delays rather than assumption.
Should quality checks block or warn? Block, for checks indicating genuinely bad data. Warning-only checks are reports nobody reads, and bad data propagates. Blocking requires idempotent writes so the previous partition survives.
How is a large backfill run safely? Bounded parallelism, progress tracking, resumability from the last completed partition, and validation of a sample before committing to the full range. Backfilling into a separate location first is safer for high-stakes reprocessing.
Why does reprocessing produce different results than the original run? Almost always because the pipeline joins to current state that has since changed. Historical accuracy requires the dimension state as of the event time, which the source data must record.
Do streaming pipelines have the same requirements? Yes, with different mechanics. Exactly-once processing through transactional sinks or deduplication, watermarks for late data, and the same need for replayability from a known offset.
