Idempotency in Data Pipeline

Idempotency means running the same operation multiple times produces the exact same result as running it once.

In data engineering, an idempotent pipeline can be safely re-run after a failure, a retry, or a manual restart and all without creating duplicate data or corrupting what’s already there.

Why It Matters

Pipelines failure
Networks drop mid-transfer
Jobs time out
Someone accidentally clicks “run” twice

All these aren’t a hypothetical edge case rather it’s a certainty over a long enough timeline, and it happens constantly at scale.

The question isn’t whether a pipeline will need to be re-run rather it’s what happens when it is. If a pipeline isn’t idempotent, re-running it after a partial failure can silently double-count revenue, duplicate customer orders in a report, or corrupt a machine learning model’s training data which often without throwing any error at all. This is one of the most common causes of “the numbers don’t match” incidents that take days to trace back to their root cause.

If you’re technical lead or an architect, this is worth making a non-negotiable standard rather than a nice-to-have: every production pipeline should be safe to re-run. It’s far cheaper to design for this upfront than to debug silent data corruption in production months later.

A Simple Example

Imagine a pipeline that processes new orders and increments a total_revenue counter for each one.

Not idempotent:

UPDATE daily_summary
SET total_revenue = total_revenue + 500
WHERE order_date = '2026-09-13';

If this job fails halfway through and gets re-run, or accidentally runs twice, total_revenue gets incremented twice and the number is now wrong, and nothing in the system flags it.

Idempotent version:

INSERT INTO daily_summary (order_date, total_revenue)
VALUES ('2026-09-13', 500)
ON CONFLICT (order_date)
DO UPDATE SET total_revenue = EXCLUDED.total_revenue;

Or, even more robustly, recompute the total from source records rather than incrementing:

INSERT INTO daily_summary (order_date, total_revenue)
SELECT order_date, SUM(amount)
FROM orders
WHERE order_date = '2026-09-13'
GROUP BY order_date
ON CONFLICT (order_date)
DO UPDATE SET total_revenue = EXCLUDED.total_revenue;

Here, order_date must be a primary key or have a unique constraint in the daily_summary table.

The first time the statement runs, the row does not exist, so it is inserted, for ex: 2026-09-13 | 500.

If the statement runs again, PostgreSQL detects that 2026-09-13 already exists. Instead of inserting another row, it executes the DO UPDATE clause. Therefore, repeated executions produce same result.

Run this once, run it five times, run it after a crash halfway through and the result is identical every time, because it always recomputes the true total rather than blindly adding to whatever was there before.

Common Techniques for Building Idempotent Pipelines

Upserts instead of inserts
- Use INSERT … ON CONFLICT (or MERGE) keyed on a unique identifier, so re-processing the same record overwrites it instead of duplicating it.
Recompute rather than increment
- Aggregate from source data instead of adding to a running total.
- A full recomputation naturally produces the same answer no matter how many times it runs.
Deduplication keys
- Attach a unique event ID or idempotency key to each record, and check for it before processing, so the same event is never applied twice even if it’s delivered twice.
Partition overwrite
- For batch jobs, overwrite an entire partition (e.g., “all of September 13th’s data”) rather than appending to it, so re-running the job for that day produces a clean, correct result rather than duplicated rows.
Idempotent APIs
- When calling external APIs (e.g., triggering a payment or sending a notification), pass an idempotency key the API provider supports, so a retried request doesn’t trigger the action twice.

Idempotency is more of a design principle than a specific tool, but these help enforce it in practice:

Databases: any system supporting UPSERT/MERGE syntax (PostgreSQL, Snowflake, BigQuery, Databricks)
Orchestration: Airflow, Dagster all support partition-based reprocessing patterns that pair naturally with idempotent design
Streaming: Kafka’s transactional producer APIs and consumer offset management help build idempotent stream processing

Real-World Scenarios

Company / Scenario	What could go wrong without idempotency	How idempotency prevents it
Swiggy/Zomato — order placement events reaching the billing system twice (a common network-retry scenario)	A customer gets billed twice for the same order if the “charge customer” event is processed more than once.	The billing service checks the order’s unique ID before processing and if that ID has already been charged, the duplicate event is safely ignored.
Amazon — a nightly inventory reconciliation job crashes halfway through and gets re-run	Stock counts get adjusted twice for the records processed before the crash, showing incorrect inventory levels.	The job overwrites the full inventory snapshot for that day rather than incrementally adjusting counts, so re-running it from scratch produces the same correct result.
A bank — a payment retry after a network timeout	The bank’s system might not know if the original payment actually went through before the timeout and blindly retrying could double-charge the customer.	Payment APIs support idempotency keys and the same key sent twice is recognized as the same request, and only processed once.
Uber/Ola — a driver-payout batch job is manually restarted after being accidentally triggered twice	Drivers could be paid twice for the same set of trips.	The payout job recomputes total earnings from trip records for the pay period rather than adding to a running balance, so restarting it produces the identical, correct payout amount.

Concepts