How would you design an idempotent batch pipeline?

Detailed explanation

Pipelines get rerun: a task fails and retries, someone backfills last month, a scheduler fires twice. If each run appends, every rerun duplicates data. Idempotency makes reruns harmless.

1. Deterministic input

Drive each run from its logical interval (for example 2026-10-03), never from now(). The run for a given day must always read the same slice of source data.

2. Replace, do not add

Pattern	How	Good for
Partition overwrite	INSERT OVERWRITE ... PARTITION (dt='2026-10-03') or delete-then-insert for that day	Daily facts, event tables
Upsert / MERGE	Match on a unique key, update or insert	Dimensions, corrections, CDC
Atomic swap	Build into a staging table, then rename or swap	Full rebuilds of small tables

3. Atomic writes

Wrap the write in a transaction or use a table format with atomic commits (such as Delta Lake), so a crash cannot leave half a partition.

4. Side effects

Emails, API calls and notifications are not naturally idempotent. Record what was sent (keyed by run and entity) and check before sending again.

Example

Two common write patterns, each safe to rerun. First a daily fact partition, replaced in one transaction:

CREATE TABLE staging_orders (order_id bigint, customer_id bigint, amount numeric(10,2), order_date date);
CREATE TABLE fact_orders    (order_id bigint, customer_id bigint, amount numeric(10,2), order_date date);
INSERT INTO staging_orders VALUES
  (1, 10, 50.00, '2026-10-03'), (2, 11, 20.00, '2026-10-03'), (3, 12, 75.00, '2026-10-04');

-- Idempotent daily load: rerunning for the same day gives the same result
BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-10-03';
INSERT INTO fact_orders
SELECT * FROM staging_orders WHERE order_date = DATE '2026-10-03';
COMMIT;

-- The retry
BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-10-03';
INSERT INTO fact_orders
SELECT * FROM staging_orders WHERE order_date = DATE '2026-10-03';
COMMIT;

SELECT count(*) AS rows_for_day, sum(amount) AS total FROM fact_orders;

rows_for_day	total
2	70.00

Then a dimension maintained by key, where the source may send the same row again or a correction:

CREATE TABLE dim_customer (customer_id bigint PRIMARY KEY, tier text NOT NULL, updated_at timestamptz NOT NULL);
CREATE TABLE staging_customer (customer_id bigint, tier text, updated_at timestamptz);
INSERT INTO staging_customer VALUES (10, 'free', '2026-10-03 09:00+00'), (10, 'pro', '2026-10-03 12:00+00'),
                                    (11, 'free', '2026-10-03 10:00+00');

MERGE INTO dim_customer AS t
USING (SELECT DISTINCT ON (customer_id) * FROM staging_customer ORDER BY customer_id, updated_at DESC) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET tier = s.tier, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT VALUES (s.customer_id, s.tier, s.updated_at);

SELECT * FROM dim_customer ORDER BY customer_id;

customer_id	tier	updated_at
10	pro	2026-10-03 12:00:00+00
11	free	2026-10-03 10:00:00+00

The source is reduced to one row per key before the MERGE (otherwise PostgreSQL raises “MERGE command cannot affect row a second time”), and the updated_at guard means replaying an older batch cannot overwrite newer values. Running the MERGE again changes nothing.

Trade-offs

Pattern	Strength	Cost
Partition overwrite	Simple, fast, natural for backfills	Rewrites the whole partition even if one row changed
MERGE on a key	Handles corrections and late updates	Needs a reliable unique key; merge cost grows with the target
Build and swap	Readers never see partial state	Rebuilds everything; only for small or medium tables
Insert-if-absent	Cheap for immutable events	Cannot apply corrections

Testing idempotency

Run the pipeline twice on the same input and assert the output (row count, key set, checksums of aggregates) is identical. Add this as an automated test, and run it once with a failure injected between the delete and the insert to prove the transaction rolls back. The idempotency lesson has runnable versions of each pattern.

Common mistakes

1. Using the current date inside the job, so a retry processes a different day.
2. Appending with no unique key or deduplication.
3. Deleting and inserting without a transaction.
4. Forgetting that late data needs the affected partitions to be reprocessed.
5. Merging a source that contains several rows per key.
6. In Spark, a static overwrite that replaces the whole table when only one partition was meant (use dynamic partition overwrite or Delta’s replaceWhere).