What does it mean for a data pipeline to be idempotent, and how do you achieve it?

Summary

Running it more than once produces the same final state as running it once. Achieve it with upserts or MERGE on a declared key, overwriting whole partitions, or loading into staging and swapping atomically, never with blind appends of derived data.

Longer Answer

Retries are guaranteed: jobs fail after writing but before recording success, orchestrators retry, people rerun by hand, and backfills reprocess history. Each target table needs a declared grain (key) and a uniqueness test so duplicates are detected immediately.

Code Example

INSERT INTO daily_revenue (day, revenue)
SELECT order_date, SUM(amount) FROM fct_order_lines WHERE order_date = :day GROUP BY order_date
ON CONFLICT (day) DO UPDATE SET revenue = excluded.revenue;   -- safe to rerun

Key Points

• Same result however many times it runs.
• Upsert/MERGE by key or partition overwrite.
• Staging + atomic swap.
• Uniqueness tests on the grain.

Deep dive: ETL and Data Pipelines: Idempotent Loads

What interviewers look for

Concrete techniques and why retries make idempotency mandatory.

Follow-up questions

• How do you make an append-only event table safe to reload?