Idempotent data pipelines: how to make every re-run safe

At 02:10 a nightly job fails halfway through loading payments into the warehouse. The on-call engineer has two options: re-run it and hope it does not double the rows already written, or spend an hour working out which rows made it in. If your team reaches for the second option, the pipeline is not idempotent, and every incident will cost far more than it should.

An idempotent job produces the same final state whether it runs once, twice or ten times for the same input window. This guide walks through the patterns we use to get there: deterministic windows, partition overwrite, MERGE upserts, deduplication keys and watermarks, plus a sober look at what "exactly-once" really promises. By the end you should be able to look at any job and say whether clicking "retry" is safe.

Start with a deterministic input window

Idempotency starts before the write. If the job decides what to read from the clock, two runs of "the same" job read different data:

# Not repeatable: the window moves with the clock.
start = datetime.now(timezone.utc) - timedelta(hours=1)

Pass the window in instead, from the orchestrator or the command line, and make it half-open so adjacent windows never overlap or leave gaps:

SELECT *
FROM raw.payments
WHERE event_time >= :window_start
  AND event_time <  :window_end;

Orchestrators already model this. Airflow exposes data_interval_start and data_interval_end, Dagster has partitions, and dbt incremental models can take variables. A run is identified by its window, and a re-run of that window is the same unit of work.

Pattern 1: overwrite the partition

The simplest idempotent write replaces everything the window owns. If the target table is partitioned by day and each run owns exactly one day, the write becomes "delete that day, insert the new rows" in one transaction:

BEGIN;
DELETE FROM analytics.payments_daily WHERE payment_date = :day;
INSERT INTO analytics.payments_daily
SELECT payment_date, merchant_id, SUM(amount_cents) AS amount_cents, COUNT(*) AS payments
FROM staging.payments
WHERE payment_date = :day
GROUP BY payment_date, merchant_id;
COMMIT;

In Spark, the key setting is dynamic partition overwrite. Without it, mode("overwrite") on a partitioned table replaces the whole table, not just the partitions in your DataFrame:

spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(daily_df
    .write
    .mode("overwrite")
    .insertInto("analytics.payments_daily"))

With dynamic, Spark overwrites only the partitions present in daily_df. Table formats such as Delta Lake also support replaceWhere to overwrite a predicate explicitly, and Apache Iceberg offers dynamic overwrite through overwritePartitions(). Each one turns a re-run into a clean replacement.

Partition overwrite works best when the window and the partition line up. If a run for 5 May can produce rows for 4 May (late events), it must own and rewrite both partitions, or you need a different pattern.

Pattern 2: MERGE on a business key

When rows can be updated after they first arrive, such as an order that changes status, overwrite by partition is awkward. A MERGE (upsert) keyed on a stable identifier is idempotent as long as the source is deduplicated first:

MERGE INTO analytics.orders AS t
USING (
  SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, ingest_id DESC) AS rn
    FROM staging.orders o
    WHERE updated_at >= :window_start AND updated_at < :window_end
  ) WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at >= t.updated_at THEN
  UPDATE SET status = s.status, total_cents = s.total_cents, updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (order_id, status, total_cents, updated_at)
  VALUES (s.order_id, s.status, s.total_cents, s.updated_at);

Three details make this safe:

• Deduplicate the source. Most engines raise an error, or behave unpredictably, when more than one source row matches the same target row. The ROW_NUMBER() filter guarantees one row per key.
• Guard updates with a version. The s.updated_at >= t.updated_at condition stops an older re-run from overwriting newer data that a later run already wrote.
• Use a deterministic tie-breaker. Two events with the same timestamp need a second ordering column, here an ingestion sequence, or two runs can pick different winners.

MERGE costs more than an append because it must find matches. Partition pruning on the target (for example t.order_date >= :min_date in the join condition) keeps it affordable on large tables.

Pattern 3: deduplication keys for append-only data

Event streams are usually append-only, and the same event can arrive twice: a producer retried after a timeout, a consumer crashed before committing its offset, or someone replayed a topic. The fix is an identity that survives retries, created as close to the source as possible:

event = {
    "event_id": str(uuid.uuid4()),  # generated once by the producer, reused on every retry
    "type": "payment.captured",
    "occurred_at": "2026-05-04T09:12:44Z",
    "payment_id": "pay_123",
}

Downstream, deduplicate on that key within a bounded window, or let the storage layer do it with a unique constraint:

INSERT INTO raw.payment_events (event_id, payload, occurred_at)
VALUES (:event_id, :payload, :occurred_at)
ON CONFLICT (event_id) DO NOTHING;

If the source cannot provide an ID, a hash of the fields that define "the same event" is a fallback, but be strict about which fields go in. Including a receive timestamp makes every retry look unique.

Pattern 4: watermarks that only move after the write

Incremental jobs often keep a watermark: "I have processed everything up to updated_at = X". The classic bug is advancing the watermark before the write is durable. If the job dies in between, the next run starts after data that never landed.

The safe order is read, write, then advance the watermark, ideally in the same transaction as the write:

BEGIN;
-- 1. write the batch (MERGE or partition overwrite)
-- 2. advance the watermark only when the write succeeded
UPDATE etl.watermarks
SET high_water = :batch_max_updated_at
WHERE job = 'orders_incremental' AND high_water < :batch_max_updated_at;
COMMIT;

Also leave a small overlap. Source systems commit out of order, so a row with an older updated_at can become visible after you already read past it. Reading from high_water - 15 minutes and relying on an idempotent MERGE to absorb the overlap is far cheaper than chasing missing rows.

The exactly-once myth

"Exactly-once" is usually shorthand for "the effects are as if each record were processed once". Systems achieve this inside their own boundary. Kafka's idempotent producer and transactions give exactly-once semantics for read-process-write cycles that stay inside Kafka. Apache Flink's checkpointing gives exactly-once state, and with two-phase commit sinks it can extend that to some external systems.

The guarantee stops where the boundary stops. The moment your job calls an HTTP API, writes to a database without a transactional sink, or sends an email, delivery is at-least-once again, and the receiving side must deduplicate. In practice "exactly-once" is built from at-least-once delivery plus idempotent writes, which is the pattern this whole guide is about.

A re-run checklist for any job

Before a job ships, we ask these questions in review:

The last row deserves practice, not just theory. Pick a past window, re-run it in staging and compare:

SELECT payment_date, COUNT(*) AS rows, SUM(amount_cents) AS total
FROM analytics.payments_daily
WHERE payment_date = DATE '2026-05-04'
GROUP BY payment_date;

If the numbers differ between the two runs and the source did not change, you have found a non-idempotent step before your on-call engineer does.

Common mistakes

Key takeaways

• A run is defined by its input window; never derive the window from the clock.
• Prefer writes that replace (partition overwrite) or reconcile (MERGE) over writes that append.
• Deduplicate on keys created at the source and survive retries.
• Advance watermarks only after the data is durable, and read with a small overlap.
• Exactly-once holds inside a system's boundary; across boundaries, idempotent receivers do the work.
• Test re-runs deliberately and compare counts and checksums before trusting them.

References

• Apache Spark documentation, configuration (spark.sql.sources.partitionOverwriteMode) — https://spark.apache.org/docs/latest/configuration.html
• Delta Lake documentation, selective overwrite with replaceWhere — https://docs.delta.io/latest/delta-batch.html
• Apache Iceberg documentation, Spark writes — https://iceberg.apache.org/docs/latest/spark-writes/
• PostgreSQL documentation, MERGE — https://www.postgresql.org/docs/current/sql-merge.html
• Apache Kafka documentation, message delivery semantics — https://kafka.apache.org/documentation/#semantics
• Apache Airflow documentation, DAG runs and data intervals — https://airflow.apache.org/docs/apache-airflow/stable/core-concepts/dag-run.html