The Myth of “Exactly-Once”: A Conversation on Why Your Pipeline Is Doubling Data

Format: A transcript of a Code Review session between Sam (Junior Data Engineer) and Elena (Staff Data Engineer).

Sam: “Hey Elena, can you approve this PR? I enabled processing.guarantee = exactly_once in the Spark config. Now we won’t have any duplicate payments in the warehouse!"

Elena: Sighs and puts down her coffee. “Sam, come sit down. We need to talk about the most dangerous lie in distributed systems.”

Sam: “What lie?”

Elena: “That ‘Exactly-Once’ actually exists.”

The Trap: Delivery vs. Processing

Elena: “You turned on a setting in Spark Structured Streaming. What do you think that actually does?”

Sam: “It guarantees that every record is processed one time. No duplicates.”

Elena: “No. It guarantees that Spark will not commit the offset to Kafka until the batch is processed. It handles the Delivery mechanism. It does not handle the Side Effects.”

Sam: “I’m lost. What is the difference?”

Elena: “Imagine you are a pizza delivery guy. ‘Exactly-Once Delivery’ means you promise to ring my doorbell exactly one time. But what if I don’t answer? You ring again. (At-Least-Once). What if you ring, I open the door, take the pizza, but then the wind slams the door shut before you can get me to sign the receipt?”

Sam: “I guess… you assume I didn’t get the pizza, so you go back to the store, get another one, and come back?”

Elena: “Exactly. And now I have two pizzas. That is what happens to our database. If Spark writes the data to Postgres, but the network cuts out before Spark gets the ‘Success’ ACK from Postgres, Spark crashes. When it restarts, it replays the batch. It delivers the pizza again. Now we have double payments.”

The Solution: Idempotency Keys

Sam: “Okay, that sounds bad. So how do we stop it? Do we use distributed transactions? Two-Phase Commit (2PC)?”

Elena: “Please don’t. 2PC is the easiest way to make our system slow and brittle. If one node locks, everything locks. We want to solve this at the Application Layer, not the Network Layer. We need Idempotency.”

Sam: “I’ve heard that word. Ideally, $f(f(x)) = f(x)$.”

Elena: “Mathematically, yes. In Data Engineering, it means: We need a unique ID for every event.”

Elena: “Look at your source data. When the payment event is generated, does it have a UUID?”

Sam: “Yes, transaction_id."

Elena: “Perfect. That is your Idempotency Key. Instead of blindly inserting data, we use that key to check if we have seen this pizza before.”

The Implementation: The “Upsert” Pattern

Sam: “So I should do a SELECT to check if the ID exists, and if not, INSERT?"

Elena: “No! That creates a Race Condition. If two threads pick up the same event at the same time, they both SELECT, see nothing, and both INSERT. You still get duplicates."

Elena: “You need the database to enforce it atomically. In SQL, we call this the Upsert (Update/Insert) or MERGE. Here, let me rewrite your code snippet."

Elena takes the keyboard.

The Anti-Pattern (What Sam wrote):

# ❌ This relies on Spark configuration magic
df.write \
  .format("jdbc") \
  .mode("append") \ # DANGER: This will create duplicates on retry
  .save()

The Senior Pattern (What Elena wrote):

# ✅ This relies on Database Physics (Idempotency)
query = """
INSERT INTO payments_warehouse (transaction_id, user_id, amount, timestamp)
VALUES (?, ?, ?, ?)
ON CONFLICT (transaction_id) 
DO UPDATE SET 
  amount = EXCLUDED.amount,
  timestamp = EXCLUDED.timestamp;
"""
# Even if Spark retries this batch 50 times, the database state remains identical.

The “Zombie” Scenario

Sam: “Okay, I get the database part. But what about when we write to S3? We can’t do an ‘Upsert’ on a file in S3. It’s immutable.”

Elena: “Sharp observation. This is the hardest part of the interview. If you are writing to a Data Lake (S3/Parquet), you have two options:”

1. The Staging/Compaction Pattern: You write duplicates to a ‘Raw’ folder (At-Least-Once). Then, a separate scheduled job (Delta Lake or Hudi) reads the raw files, creates a distinct set based on IDs, and writes to the ‘Curated’ layer.
2. Write-Ahead-Log (WAL): You use a format like Delta Lake or Iceberg. They bring ACID transaction logs to S3. They handle the optimistic concurrency control for you.

Sam: “So, using Delta Lake is basically buying ‘Idempotency as a Service’?”

Elena: “Pretty much. It shifts the complexity from your code to the storage format.”

Summary: The Senior Engineer’s Checklist

Elena: “So, Sam, before you merge this PR, I want you to answer three questions:”

1. Identity: What column is the unique Idempotency Key?
2. Destination: Does the destination support atomic Upserts (Postgres/Snowflake) or do we need a Delta/Hudi layer (S3)?
3. Failure Mode: If the worker pulls the plug right after writing but before committing, what happens when the job restarts?

Sam: “If the job restarts, it tries to write the same transaction_id again. The database catches it, updates the existing row to the same value, and moves on. No duplicates."

Elena: “Approved. Ship it.”

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — -

If you enjoyed this breakdown and want deeper, real-world case studies, tradeoffs, and architecture patterns, my System Design Guide for Data Engineers goes far beyond the basics.

Check it out here — FAANG System Design

Enjoyed this Article?

If you found this article helpful, make sure to follow me to stay up-to-date with my latest articles on Data Engineering, MLOps, and AWS/Azure Cloud.