Idempotent data pipelines: partition overwrite, safe reruns and backfills without double counting

Goal

Make every scheduled task safe to run twice, so that retries, manual reruns and historical backfills never duplicate or lose rows, and so that "reprocess last month" is a command rather than a project.

Prerequisites

A scheduler that attaches a data interval to each run. The Airflow documentation (cited) defines the interval as the time range a run operates on, schedules a run after its interval has ended so that all data of the period can be collected, and calls running a Dag for a specified historical period a backfill. Its best-practice page states that, because tasks may be retried, tasks should produce the same outcome on every re-run; it advises against plain INSERT on rerun (duplicate rows), recommends UPSERT, and says to read and write in a specific partition instead of "the latest available data".

Steps

1. Parameterise every task by the interval (data_interval_start, data_interval_end); never by "now". Input selection filters on the interval, so a rerun sees the same input unless the source itself changed.
2. Write output as a replacement of the interval's slice: delete-then-insert within one transaction in a relational target, or a partition overwrite in a file-based target. Spark's INSERT OVERWRITE with partitionOverwriteMode=dynamic overwrites only the partitions that receive data in the run (cited); in static mode it first deletes every partition matching the partition specification.
3. Where replacement is impossible (event tables shared with other writers), upsert on a deterministic key derived from the record, for example the source identifier plus interval, so that a rerun updates rather than appends.
4. Make downstream aggregates recompute from the replaced slice rather than adding deltas; an aggregate that sums increments will count a backfilled partition twice.
5. For a backfill, run the intervals in order with a bounded concurrency, and after each interval compare the row count and a checksum of key columns with the previous version of that partition; log the difference.
6. Re-run dependent tasks for the same intervals; a backfill of one table without its consumers leaves the warehouse internally inconsistent.

Expected result

Rerunning any interval leaves the output identical to a single clean run. Backfilling a range produces the same tables as if the corrected code had run on schedule.

Limits and test basis

Sources that overwrite history (mutable operational tables) break interval determinism; take a snapshot per interval or use change data capture. A backfill that spans a schema change needs the new schema for all intervals. The method is a protocol derived from the cited documentation; no measurement of its effect is claimed.