Why Your Data Pipeline Needs to Survive Being Run Twice

The most common source isn't a bug in your pipeline logic, it's the delivery guarantee of whatever's feeding it. Most message queues and event streams offer at-least-once delivery, not exactly-once, which means your pipeline should assume every message might arrive more than once. A network timeout during a write can also cause a retry that reprocesses data the first attempt actually succeeded at writing, just without the caller ever finding out. Both cases look identical from the pipeline's perspective: the same data, arriving again.

Idempotency depends on being able to answer one question cheaply: have I already processed this exact unit of work? That requires a stable key, either a natural one already present in the data, an order ID, an event ID, or one you assign at the point of ingestion if the source doesn't provide one. With that key, an upsert, insert if the key doesn't exist, update or skip if it does, replaces a blind insert, and reprocessing the same record becomes a no-op instead of a duplicate row.

Upserts solve duplication for row-level ingestion, but aggregation steps, summing revenue into a daily total, incrementing a counter, need a different approach, because blindly adding a value twice is exactly the bug idempotency is supposed to prevent. The fix is to make the aggregation itself derivable from the underlying idempotent records rather than an incrementing counter: recompute the daily total from the deduplicated rows instead of adding a delta to a running number. It costs more compute per run, but it means a reprocessed batch can never silently inflate a total, since the total is recalculated from source data that's already protected against duplication.

Say your payment provider's webhook fires for a successful charge, your pipeline starts processing it, and the connection drops before your service can send back an acknowledgment. The provider, having not received confirmation, resends the same webhook a minute later. If your handler just inserts a new revenue record on receipt, you now have the same charge recorded twice. If it instead upserts on the payment provider's event ID, checking whether that ID has already been recorded before writing anything, the second delivery becomes a harmless no-op, and your revenue numbers stay correct without anyone having to notice or intervene.

The only reliable way to know a pipeline is actually idempotent is to run the same batch through it twice, on purpose, in a test or staging environment, and diff the output. Assuming idempotency because the code looks like it should behave that way is how these bugs end up in production. Make replaying a batch a standard part of your testing for any new ingestion job, not a special case you only think of after the first duplicate-row incident.

A quick idempotency checklist for any ingestion job:

• Assume every message can arrive more than once, since most queues and event streams offer at-least-once delivery rather than exactly-once.
• Give each unit of work a stable key, natural or assigned at ingestion, and upsert or skip based on it.
• Derive aggregates from the idempotent underlying records instead of blindly incrementing a counter.
• Replay the same batch twice in staging and diff the output before trusting the job in production.
• For a pipeline you did not build this way, add a deduplication table checked before any write happens.

Retrofitting idempotency into an existing pipeline is harder than building it in from the start, but it's rarely as bad as rewriting the whole thing. Start by adding a deduplication table that records the key of every unit of work already processed, checked before any write happens, even if the rest of the pipeline logic stays exactly as it is. This is less elegant than redesigning the pipeline around upserts throughout, but it closes the actual gap, duplicate processing, without requiring you to touch every downstream step at once. Once that safety net is in place, you can migrate individual steps to a cleaner upsert-based pattern over time instead of under pressure during an incident.