DE Idempotency in Pipelines
Data pipelines fail. Network timeouts, source system outages, transformation bugs, and infrastructure issues all cause pipelines to stop before completing. When a failed pipeline reruns, what happens to the data it already wrote? Without idempotency, rerunning a pipeline duplicates records, corrupts aggregates, and produces wrong answers. Idempotency is the design principle that makes reruns safe.
What Idempotency Means
An idempotent pipeline produces the same result whether it runs once or ten times on the same input. Running it a second time after a failure does not create duplicate records or change the outcome — it simply confirms the already-correct state. The word comes from mathematics: an idempotent operation applied repeatedly leaves the result unchanged after the first application.
The Light Switch Analogy
Turning a light switch to the ON position once turns the light on. Turning it to ON a second time does not turn the light on twice — the room stays lit, unchanged. That is idempotency. Now imagine a light switch that adds a light bulb every time you press ON — two presses, two bulbs; three presses, three bulbs. That is a non-idempotent pipeline: every retry adds more records to the destination table.
Why Non-Idempotent Pipelines Cause Problems
Non-Idempotent Pipeline (INSERT without deduplication):
Day 1 Run 1: Loads 10,000 orders --> destination has 10,000 rows
Day 1 Run 2 (after failure): Loads same 10,000 orders again
--> destination now has 20,000 rows (DUPLICATED!)
Day 1 Run 3 (retry again): Loads same orders again
--> destination now has 30,000 rows (TRIPLED!)
Revenue SUM() from destination: 3x actual revenue -- WRONG REPORT
Strategies for Achieving Idempotency
Strategy 1: DELETE-then-INSERT (Truncate and Reload)
Before loading data for a time period, delete all existing records for that period, then insert the fresh batch. Running the pipeline twice produces the same result because the second run first deletes the records the first run inserted, then re-inserts the same clean batch.
Idempotent Pattern: DELETE-then-INSERT
Step 1: Delete existing records for the target date
DELETE FROM orders_daily
WHERE sale_date = '2024-05-15';
Step 2: Insert the new batch
INSERT INTO orders_daily
SELECT * FROM staging_orders
WHERE sale_date = '2024-05-15';
Result: Run once or ten times -- orders_daily always contains
exactly one copy of each order for 2024-05-15.
Strategy 2: UPSERT (MERGE)
An UPSERT updates existing records if they match a key, and inserts new records if they do not. Running an UPSERT twice on the same data updates existing rows to the same values and skips insertion of records that already exist — producing the same result both times.
Idempotent Pattern: MERGE/UPSERT
MERGE INTO orders_daily AS target
USING staging_orders AS source
ON target.order_id = source.order_id -- Match on unique key
WHEN MATCHED THEN
UPDATE SET amount = source.amount,
status = source.status
WHEN NOT MATCHED THEN
INSERT (order_id, amount, status, sale_date)
VALUES (source.order_id, source.amount,
source.status, source.sale_date);
Result: Existing orders update to correct values.
New orders insert once. No duplicates.
Running twice: same result.
Strategy 3: Partition Overwrite
Write the entire partition (e.g., one day's data) as a complete replacement. In Spark, writing a DataFrame in "overwrite" mode to a partitioned Parquet table replaces only the target partition, leaving other partitions intact. Each run produces the same files in the same partition.
Idempotent Pattern: Partition Overwrite (Spark)
df.write \
.mode("overwrite") \
.option("partitionOverwriteMode", "dynamic") \
.partitionBy("sale_date") \
.parquet("s3://bucket/fact_sales/")
Result:
Run 1: Writes 2024-05-15 partition
Run 2: Overwrites 2024-05-15 partition with same data
Other partitions (May 14, May 13...): untouched in both runs
Strategy 4: Deduplication at Read Time
If the destination stores every insert (like an append-only log), deduplicate at query time. Views or downstream transformations use ROW_NUMBER() or MAX() to return only the latest version of each record.
Idempotent Pattern: Dedup at query time
CREATE VIEW orders_latest AS
SELECT * FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY loaded_at DESC
) AS rn
FROM orders_raw
) WHERE rn = 1;
Result: The underlying table may have duplicates from retries,
but the view always shows exactly one row per order_id.
Idempotency in Batch vs Streaming
Batch Pipelines
Batch pipelines are easier to make idempotent. Each run processes a clearly defined time window. DELETE-then-INSERT or partition overwrite handles the entire batch atomically.
Streaming Pipelines
Streaming idempotency is harder. Events arrive continuously and reprocessing from a checkpoint might reprocess events already written to the destination. Kafka and Flink support exactly-once processing semantics — each event is processed and written exactly once even after failures and restarts, using transactional writes coordinated between the processing engine and the destination.
Designing for Idempotency from the Start
Idempotency Checklist for New Pipelines: [ ] Does the destination table have a natural unique key per batch? [ ] Does the load strategy (DELETE-INSERT, UPSERT, overwrite) handle retries? [ ] Can the pipeline rerun for a past date without changing the result? [ ] Does the pipeline write atomically (all-or-nothing, not partial rows)? [ ] Are surrogate keys stable across reruns (not auto-increment per run)?
Summary
Idempotency ensures that a pipeline produces the same result regardless of how many times it runs on the same input. The four main strategies — DELETE-then-INSERT, UPSERT/MERGE, partition overwrite, and deduplication at query time — each handle reruns safely in different situations. Building idempotency into every pipeline from the start eliminates an entire class of data quality bugs that appear only under failure and retry conditions — exactly the conditions that occur most often in production systems.