Idempotent Data Pipelines: How to Achieve Exactly-Once Processing

Last Updated: July 2026 | 15 min read

Quick Answer: An idempotent data pipeline produces the same result no matter how many times it runs on the same input — so a retry, a re-run, or a backfill can never create duplicate rows or double-counted metrics. It matters because "exactly-once delivery" is provably impossible on a real network; every reliable system delivers at least once and relies on idempotency to make the effect happen exactly once. You achieve it with four core patterns: deterministic partition overwrite, MERGE/upsert on a natural key, idempotency keys with a dedup store, and the transactional outbox.

Every data engineer eventually ships the same bug: a nightly job fails halfway, someone retries it, and the next morning revenue is double what it should be. The pipeline "worked" — it just ran twice. That single failure mode is why idempotent data pipelines are the most important reliability skill in data engineering, and the one most tutorials skip. In this guide you'll learn why exactly-once delivery is a myth, how exactly-once processing is achievable, and the exact patterns — with PySpark and SQL — to make any batch or streaming pipeline safe to run twice. This builds on the Spark performance and Kafka fundamentals work in our data engineering series.

What is an idempotent data pipeline?

An idempotent data pipeline is one where running it N times with the same input leaves the target in exactly the same state as running it once. The word comes from mathematics: an operation is idempotent if applying it repeatedly changes nothing after the first application. x = 5 is idempotent; x += 5 is not. A pipeline that overwrites yesterday's partition is idempotent; a pipeline that appends yesterday's rows is not.

This property is the foundation of reliability because distributed systems fail partway through constantly — a node dies, a network blips, an orchestrator retries a task it isn't sure finished. If your pipeline is idempotent, all of those become non-events: you just run it again. If it isn't, every retry risks corrupting the very data you're paid to protect. Idempotency turns "did this job actually finish?" from a 2 a.m. incident into a shrug.

The key mental shift: idempotency is a property of the write, not the network. You don't prevent duplicates from arriving — you make duplicates harmless when they do.

Why exactly-once delivery is a myth

Exactly-once delivery — guaranteeing a message crosses the network once and only once — is provably impossible, and understanding why is the whole game. This is the single biggest source of confusion in data engineering, and getting it right is what separates a robust pipeline from a fragile one.

The proof is the classic Two Generals Problem. General A sends "attack at dawn" to General B and waits for an acknowledgement. If the ack never arrives, A cannot tell whether the message was lost or the ack was lost. If A resends, B might receive it twice. If A doesn't resend, B might never have received it. No finite protocol resolves this over an unreliable channel — you must choose to risk either loss (send once) or duplication (retry until acked). (Brave New Geek: You Cannot Have Exactly-Once Delivery.)

So every real transport picks one of these two safe options and lives with the consequence:

The resolution is a distinction that most blog posts blur: delivery is a transport property, processing is an application property. You cannot get exactly-once delivery. You can get exactly-once processing by accepting at-least-once delivery and making your consumer idempotent so duplicates have no effect. When Kafka, Flink, or Spark Structured Streaming advertise "exactly-once semantics" (EOS), this is precisely what they mean — at-least-once delivery plus transactional, idempotent writes. (Confluent: exactly-once semantics.)

The one-line takeaway: Stop trying to deliver each record once. Deliver at least once, and process each record's effect exactly once. Idempotency is how.

Why your pipeline runs twice (the failure modes)

Duplicate processing isn't an edge case — it's the normal behaviour of every reliable system. Before fixing it, know the four places it comes from:

• Orchestrator retries. Airflow, Dagster, and every scheduler retry a task when it fails or when they lose track of whether it succeeded. A task that timed out its heartbeat may have actually completed — the retry runs the write again. See Airflow fundamentals for how retries and depends_on_past interact.
• At-least-once sources. Kafka consumers commit offsets after processing. If a consumer crashes between processing a batch and committing the offset, it re-reads and re-processes that batch on restart. This is by design — see Kafka consumer lag.
• Manual re-runs and backfills. Someone re-runs last month to fix a bug, or a backfill overlaps data an incremental load already ingested. Without idempotency, overlap means duplicates.
• Producer retries (the dual-write problem). A service writes to its database, then tries to publish an event and fails, so it retries the publish — emitting the same event twice, or worse, committing the DB change but never emitting the event at all.

Every one of these is unavoidable in a correct system. The fix is never "make it not happen" — it's "make it not matter."

How to make a data pipeline idempotent (6 patterns)

There are six battle-tested patterns, and you rarely need more than one or two per pipeline. They're ordered from simplest to most involved — reach for the earliest one that fits.

Pattern 1 — Deterministic partition overwrite (the batch default)

The most reliable idempotency pattern for batch is to overwrite the whole partition instead of appending. Tie each run to a fixed input window (a date, an hour, a batch id) so the same input always produces the same output, then replace — never append — that partition.

# Idempotent by construction: re-running 2026-07-10 replaces the
# 2026-07-10 partition with an identical result. No duplicates, ever.
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(events_for_day
   .write
   .mode("overwrite")            # overwrite, never append
   .partitionBy("event_date")
   .parquet("s3://lake/events/"))

Dynamic partition overwrite touches only the partitions present in the DataFrame, so a re-run of one day never disturbs the others. This is the pattern I reach for first on AWS EMR: make the job a pure function of (input partition) -> (output partition) and let retries be free.

Pattern 2 — MERGE / upsert on a natural key (streaming + CDC)

When you can't overwrite a whole partition — for streaming upserts, slowly changing dimensions, or CDC — key every write on a natural primary key and MERGE. If the row exists, update it to the same values (a no-op); if not, insert it. Applying the same record twice converges to one row.

-- Iceberg / Delta / Snowflake all support MERGE. Re-applying the
-- same source rows is a no-op because the ON clause matches.
MERGE INTO orders t
USING staged_orders s
ON  t.order_id = s.order_id
WHEN MATCHED     THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

MERGE is the backbone of idempotent lakehouse pipelines — see Delta Lake vs Iceberg and the Iceberg table guide for engine-specific syntax and performance notes.

Pattern 3 — Idempotency keys + a dedup store (append-only sinks)

When you must append (an event log, an audit table, an API you don't control), attach an idempotency key and check it before writing. The key is a deterministic id — an event id, or a hash of the payload — enforced by a unique constraint or a dedup table.

# Deterministic key = safe retries. A duplicate hits the unique
# constraint (or is filtered by dropDuplicates on a watermark).
from pyspark.sql import functions as F

deduped = (raw_events
    .withColumn("idem_key", F.sha2(F.concat_ws("|", "user_id", "event_ts", "event_type"), 256))
    .withWatermark("event_ts", "1 hour")
    .dropDuplicates(["idem_key"]))

A database unique index on idem_key (with INSERT ... ON CONFLICT DO NOTHING) turns duplicate inserts into silent no-ops — the strongest guarantee when the sink supports it.

Pattern 4 — Transactional outbox (fix the dual-write problem)

To guarantee a state change produces exactly one downstream event, write the event to an outbox table in the same transaction as the state change. Because the row and the event commit atomically, you can never update the database without also queuing the event. A separate relay (or Debezium CDC) reads the outbox and publishes to Kafka at-least-once — and the consumer dedups on the event id.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 42;
  INSERT INTO outbox (event_id, topic, payload)
  VALUES (gen_random_uuid(), 'account.debited', '{"id":42,"amount":100}');
COMMIT;   -- both succeed or both roll back

Pattern 5 — Recompute aggregates, don't increment

Never do count = count + 1 in a pipeline — it's the definition of non-idempotent. Incremental mutation double-counts on every retry. Instead recompute the aggregate from the full source set for the window, so the result depends only on the input, not on how many times you ran.

-- Idempotent: result is a pure function of the source rows.
INSERT OVERWRITE daily_revenue
SELECT event_date, SUM(amount) AS revenue
FROM   orders
WHERE  event_date = '2026-07-10'
GROUP BY event_date;

Pattern 6 — Atomic swap (write-audit-publish)

Make the final publish a single atomic operation so readers never see a half-written result. Write to a staging location, validate it, then atomically swap it in — a metadata pointer flip on Iceberg/Delta, or an INSERT OVERWRITE that replaces the table in one commit. A crash mid-write leaves the old data intact and the retry simply redoes the swap.

Which idempotency pattern should you use?

Match the pattern to your sink and load type — most pipelines need just one. Use this decision table:

The 90% path: for batch, overwrite partitions (Pattern 1). For streaming, MERGE on a key (Pattern 2). Those two cover the vast majority of real pipelines; reach for the rest only when the sink forces your hand.

Exactly-once in Kafka and Spark: what the frameworks actually give you

Kafka and Spark Structured Streaming both offer "exactly-once semantics," but the guarantee only holds inside their own boundaries — cross a boundary and you're back to idempotency.

• Kafka EOS combines an idempotent producer (dedups retries with a producer id + sequence number) and transactions (atomic writes across partitions plus the offset commit). It gives exactly-once processing for Kafka-to-Kafka flows. The moment you write to an external system that isn't in the transaction — S3, a warehouse, a REST API — the guarantee ends, and your sink must be idempotent.
• Spark Structured Streaming guarantees exactly-once via checkpointing and the write-ahead log only for idempotent, deterministic sinks. foreachBatch with a MERGE is exactly-once; a plain append to a non-transactional store is at-least-once. Read Spark streaming tuning for the checkpoint and trigger details. (Spark Structured Streaming semantics.)

The rule holds everywhere: the framework gets records to your sink at least once; your sink's idempotency is what makes the effect exactly once.

Common mistakes with idempotent pipelines

• Chasing exactly-once delivery. Teams burn weeks trying to guarantee each message crosses the wire once. It's impossible — design for at-least-once delivery and idempotent processing instead.
• Appending in a retryable task. df.write.mode("append") inside an Airflow task that can retry is a duplicate factory. Use overwrite or MERGE.
• Non-deterministic keys or inputs. Keying dedup on now(), a random UUID generated at write time, or a shuffled row order breaks idempotency — the "same" record gets a new key on retry. Keys must be derived from the data.
• Incrementing counters. Any x = x + delta write double-counts on re-run. Recompute from source.
• Idempotent write, non-idempotent side effects. The table write is safe, but the job also sends an email or calls a payment API on every run. Make side effects idempotent too, or move them behind their own dedup key.
• Forgetting late and duplicate data past the watermark. dropDuplicates on a watermark only dedups within the window; duplicates arriving later slip through. Size the watermark to your real duplicate delay.

If you're untangling a pipeline that double-counts on every retry, SolutionGigs can match you with a vetted data engineer who's built exactly-once pipelines in production — it's free to post a project.

Frequently Asked Questions

What is an idempotent data pipeline?

An idempotent data pipeline produces the same final result no matter how many times you run it with the same input. Running it once and running it five times leave the target dataset identical, so a retry after a failure or a re-run of yesterday's job can never create duplicate rows or double-counted metrics. Idempotency is what makes a pipeline safe to retry, and safe-to-retry is what makes a pipeline reliable.

Is exactly-once delivery possible?

No. Exactly-once delivery is provably impossible over an unreliable network, because a sender can never know whether a lost acknowledgement means the message failed or the ack failed — the Two Generals Problem. What is achievable is exactly-once processing: systems deliver a message at least once and the consumer makes the effect happen exactly once by being idempotent. When Kafka or Flink advertise exactly-once, they mean exactly-once processing, not delivery.

What is the difference between at-least-once and exactly-once processing?

At-least-once means a message is never lost but may be delivered more than once, so the consumer can see duplicates. Exactly-once processing means the observable effect of a message happens once even if it is delivered many times. You get from at-least-once to exactly-once by adding idempotency — dedup on a key, upsert instead of insert, or overwrite a partition. The delivery guarantee stays at-least-once; the processing becomes effectively once.

How do you make a batch pipeline idempotent?

The simplest and most reliable pattern is deterministic partition overwrite: tie each run to a fixed input window (a date or batch id) and overwrite the whole target partition instead of appending rows. Re-running the job replaces the partition with an identical result, so retries and backfills are safe. Spark's dynamic partition overwrite mode and Iceberg or Delta MERGE both implement this cleanly.

What is an idempotency key?

An idempotency key is a unique, deterministic identifier attached to each unit of work — an event id, a natural primary key, or a hash of the payload. Before applying an operation, the consumer checks whether that key was already processed, in a dedup table, a MERGE ON clause, or a unique constraint. If it was, the duplicate is skipped. Idempotency keys suppress duplicate deliveries without coordinating the whole distributed system.

What is the transactional outbox pattern?

The transactional outbox pattern writes an event into an outbox table in the same database transaction that changes your application state, so the state change and the event commit atomically. A separate relay reads the outbox and publishes to Kafka. This removes the dual-write problem, where a service updates its database but crashes before publishing the event, guaranteeing every committed change produces exactly one event downstream.

Does Kafka guarantee exactly-once?

Kafka guarantees exactly-once processing for Kafka-to-Kafka pipelines through its idempotent producer and transactions, not exactly-once delivery. The guarantee holds only inside Kafka's transactional boundary. As soon as you write to an external system — a database, S3, or an API that isn't part of the Kafka transaction — the sink itself must be idempotent for the end-to-end result to be exactly-once.

Conclusion

Idempotency is the quiet superpower of reliable data engineering: it turns every failure into a retry and every retry into a no-op. The mental model is worth more than any single technique — stop trying to deliver each record exactly once (you can't), and instead accept at-least-once delivery and make each record's effect happen exactly once. That reframing dissolves an entire category of 2 a.m. incidents.

In practice it comes down to a short ladder you climb only as far as your sink demands: overwrite partitions for batch, MERGE on a natural key for streaming and CDC, add an idempotency key and dedup store for append-only sinks, and reach for the transactional outbox when a service must emit events atomically with its state. Never increment when you can recompute, and remember that Kafka's and Spark's exactly-once guarantees stop at their own boundary — your sink carries it the rest of the way. Pick one pipeline you own today and ask the only question that matters: if this runs twice, does anything break? If the answer is yes, you now know exactly which pattern to reach for.

Building or fixing a pipeline that has to be safe to run twice? Get matched with a vetted data engineer on SolutionGigs — it's free to post your project.

Mohammed Yaseen

Founder, SolutionGigs

Mohammed builds Kafka and Spark pipelines on AWS EMR processing hundreds of millions of events a day, where idempotent, retry-safe writes are the difference between a quiet on-call week and a double-counted-revenue incident. LinkedIn →