A pipeline you cannot safely re-run is a pipeline you cannot safely fix. This page explains idempotency for data jobs, the write patterns that achieve it (partition overwrite and MERGE), and how to reprocess history with backfills without creating duplicates or gaps.
An operation is idempotent if doing it twice has the same effect as doing it once. For a data pipeline, it means a job run for a given logical input (say, "orders for 30 September") produces the same target state whether it runs once, is retried after a crash, or is re-run a month later.
The diagram contrasts the two most common write styles. An append job adds its output every time; a retry after a partial failure leaves duplicates. A partition-overwrite job replaces the slice of the table it owns, so re-running converges on the correct answer.
A backfill is a deliberate re-run over historical inputs: to fill a new column, fix a bug, load a new source, or recover from an outage. Backfills are only routine when the jobs being re-run are idempotent.
Failures are normal: spot instances are reclaimed, warehouses time out, orchestrators retry tasks automatically, and streaming engines replay from checkpoints. Without idempotency, every retry is a data-quality incident waiting to happen, and every bug fix requires hand-written cleanup SQL. With it, the recovery procedure for nearly every problem is the same: fix the code, re-run the affected partitions.
Idempotency also unlocks parallelism (run thirty days at once), reproducibility (the table can be rebuilt from raw data), and simpler orchestration (Airflow, Dagster and similar tools can retry without special cases).
It helps to separate two ideas that are often blurred. At-least-once delivery is what most infrastructure actually guarantees: a message, file or task may be processed more than once, but will not be lost. Exactly-once results are what consumers want. The bridge between them is an idempotent write: if processing the same input twice cannot change the outcome, then at-least-once delivery is enough to give exactly-once results. This is why streaming systems pair checkpoint replay with transactional or upsert sinks, and why batch teams should design every table load as if it will run more than once, because eventually it will. A short test is worth adding to CI: run the job twice against a fixture and assert the target is unchanged.
Three building blocks cover most cases:
dt = '2026-09-30'), not by "now". Code that uses CURRENT_DATE or "rows since the last run" reads different data on a retry. In Airflow terms, use the data interval, not wall-clock time.INSERT OVERWRITE, Spark dynamic partition overwrite, Delta replaceWhere), or delete-then-insert within one transaction.MERGE on a stable business key so replaying a batch updates existing rows rather than inserting new ones. Deduplicate the source first: a MERGE fails or behaves unpredictably when several source rows match one target row.Two supporting habits matter. Make writes atomic, so a half-finished run is never visible (table formats such as Delta Lake and Iceberg give this through transactional commits). And keep the raw layer immutable and append-only, so you always have the original inputs to rebuild from.
Two SQL patterns: partition overwrite for a daily fact aggregate, and MERGE for a slowly changing customer table.
-- 1. Partition overwrite (Spark SQL / Databricks): rebuild exactly one day.
INSERT OVERWRITE TABLE analytics.daily_orders PARTITION (dt = '2026-09-30')
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS revenue
FROM raw.orders
WHERE dt = '2026-09-30'
GROUP BY customer_id;
-- 2. MERGE on a business key (Delta Lake, Snowflake, BigQuery syntax is similar).
MERGE INTO core.customers AS t
USING (
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY updated_at DESC) AS rn
FROM staging.customers_batch
) WHERE rn = 1 -- dedupe the source first
) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
email = s.email, tier = s.tier, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, email, tier, updated_at)
VALUES (s.customer_id, s.email, s.tier, s.updated_at);The overwrite statement reads only the source partition for its date and replaces only the matching target partition; running it again yields identical results. The MERGE first keeps only the latest source row per key, then updates only when the incoming record is newer, so replaying an old batch cannot overwrite fresher data. That updated_at guard is what makes it safe to replay out of order.
The backfill driver then simply loops over dates:
# Backfill driver: re-run one idempotent task per partition.
from datetime import date, timedelta
from concurrent.futures import ThreadPoolExecutor
def run_partition(dt: date) -> str:
# In real life: submit the SQL above with dt as a parameter.
sql = f"INSERT OVERWRITE TABLE analytics.daily_orders PARTITION (dt='{dt}') ..."
# spark.sql(sql)
return f"{dt} ok"
start, end = date(2026, 9, 1), date(2026, 9, 30)
days = [start + timedelta(d) for d in range((end - start).days + 1)]
with ThreadPoolExecutor(max_workers=4) as pool: # safe because each run is idempotent
for result in pool.map(run_partition, days):
print(result)Treat each partition as the unit of work. Steps that keep backfills uneventful:
Orchestrators help: Airflow's backfill command and catchup, and Dagster's partitioned assets, both schedule one run per logical interval.
| Strategy | Idempotent? | Best for | Watch out for |
|---|---|---|---|
| Plain append | No | Immutable event logs with dedup downstream | Duplicates on retry |
| Append + dedup key | Yes, if dedup is enforced | Event ingestion | Dedup window and cost |
| Partition overwrite | Yes | Date-partitioned facts and aggregates | Late data for older partitions |
| Delete + insert in a transaction | Yes | Warehouses without partition overwrite | Must be one transaction |
| MERGE / upsert | Yes, with deduped source | Dimensions, CDC targets | Multiple source matches; cost on big tables |
| Full table rebuild | Yes | Small tables | Cost grows with history |
now(), random sampling without a seed, or ROW_NUMBER() with ties produce different output each run.spark.sql.sources.partitionOverwriteMode=dynamic or use replaceWhere.