What Is Idempotency in Data Engineering?

A bare INSERT INTO ... SELECT or a Spark write in append mode adds rows every time it runs: 2 runs double the data and 3 triple it. It's the most common violation. The fix is to replace or merge the slice the run owns.

Incrementing a stored total is the same bug in 1 row. UPDATE metrics SET page_views = page_views + 1500 adds another 1,500 on every rerun, so the counter drifts further from the truth each time. The fix is to write the absolute value computed from the source, as in SET page_views = 1500. The other fix is a ledger with 1 row per increment, keyed by its source event, with the total read as a SUM over it, so a replayed event updates its own row rather than adding a new one.

Inputs that change between runs break idempotency even when the write is correct. A step that reads "the latest data" or filters on current_date loads a different slice when it is retried an hour later or backfilled next month. Have the scheduler pass each run its interval or batch id, and make the step read and write only that slice.

Values generated at write time do the same in a quieter way. A surrogate key drawn from a sequence gives a rerun different values for the same source row, and so does a random UUID or a loaded_at = now() column. That breaks joins to earlier outputs and makes 2 runs impossible to compare. Derive keys from the source, such as a hash of the natural key, and take timestamps from the source or from the run's interval.

The simplest idempotent load deletes everything the run owns, then inserts it again from the source: DELETE FROM fact_orders WHERE order_date = :run_date followed by an INSERT ... SELECT with the same filter. A second run deletes what the first one wrote and writes the same rows back, so any number of runs ends in the same state. It needs no unique key, and it removes rows that disappeared from the source, which an upsert never does.

Both statements must commit together. If the delete commits and the insert fails, readers see an empty day until the retry lands; inside 1 transaction they see the old day or the new one, never neither. That guarantee is the atomicity in ACID, and it's why the pattern belongs on databases and warehouses with multi-statement transactions.

The delete filter and the insert filter must describe the same slice. A delete on order_date paired with an insert that filters on created_at in another time zone leaves rows at the edges of the day duplicated or missing, and the bug only shows on the days those edges hold data.

An upsert matches each source row to the target by a unique key, updates the row when it exists and inserts it when it does not. MERGE is the SQL standard form, and PostgreSQL's INSERT ... ON CONFLICT DO UPDATE is a single statement that, in the words of its documentation, guarantees an atomic insert or update outcome even under high concurrency. Rerun with the same source, it leaves every key with the same values. That makes it the pattern for dimension and entity tables, where each key should hold its latest version.

An upsert is idempotent only if the source holds at most 1 row per key. PostgreSQL raises a cardinality violation when 1 statement would affect the same row twice, and other engines either raise an error or let an arbitrary row win, which changes the result from run to run. Deduplicate the source first with ROW_NUMBER() ordered by a column that breaks every tie.

The values it writes also have to come from the source rather than the clock. updated_at = CURRENT_TIMESTAMP in the update branch changes every matched row on every rerun, so 2 runs never leave the same table. Carry the source's own change timestamp, and add a condition such as WHEN MATCHED AND t.email IS DISTINCT FROM s.email so unchanged rows are left alone and a rerun writes nothing.

An upsert only adds and changes rows, so a customer deleted in the source stays in the target forever. Handle deletes with a soft-delete flag from change data capture, or reload the whole table or partition when the source cannot report deletions.

Streaming systems deliver at least once by default: after a crash, a consumer resumes from its last committed offset and processes some records a second time. The sink therefore has to be idempotent, or every restart duplicates the records between the last commit and the crash.

Kafka's idempotent producer, enabled by default since Kafka 3.0, is often mistaken for the answer. It stops a producer's own retries from writing a message twice to a partition. It does not help when a job restarts and sends the same records again, and it does nothing for a consumer that writes to a database.

The durable fix is at the sink. Give each event a stable id from its source and upsert by that id, so a replayed event overwrites itself, or commit the output and the consumer's offsets in 1 transaction, which is what Kafka transactions and Flink's 2-phase commit sinks do. The first is simpler and works with any key-value or table sink; the second is what exactly-once means when a stream processor advertises it.

A pipeline that only writes tables can replace what it wrote. A pipeline that sends an email, charges a card, posts to a webhook or calls a partner API cannot take anything back, so a retry repeats the side effect: 2 invoices, 2 alerts, 2 charges.

Give every side effect an idempotency key derived from what it is about, such as the invoice id plus the run's date, never from the attempt. Record the key in a table in the same transaction that marks the work done, and skip any key already recorded. Many payment and messaging APIs accept the key as a request header and discard a second request that carries it, which covers the gap between sending and recording.

Keep side effects out of the steps that transform data. A load that also emails customers can't be retried freely; a load followed by a separate send step that records its keys can.

Run the step twice for the same interval and compare the target after each run. Row counts are the first check, but they miss changed values, so compare content as well: SELECT * FROM run_1 EXCEPT SELECT * FROM run_2 and the same in the other direction should both return 0 rows, excluding only columns that are allowed to differ, such as a run id.

Add a third run that fails on purpose halfway through, then a clean retry, and compare again. That is the case that breaks most loads in production, and it is the one a happy-path test never exercises.

In production, a uniqueness check on each table's natural key catches what tests miss. A load that doubled a partition fails it on the next run, long before the doubled revenue reaches a dashboard, and the fix is a rerun instead of a cleanup project.

Few interviewers ask for the definition. They hand over a load such as INSERT INTO fact_events SELECT * FROM staging_events WHERE event_date = :date and ask what happens when it is retried, or describe a job that ran twice a day for a week and ask how to repair the table and stop it happening again. A strong answer names the failure (duplicates in every rerun partition), picks the write pattern that fits the table (DELETE + INSERT for a date-partitioned fact table, MERGE on the event id when one exists), dedupes the damage with ROW_NUMBER() over the natural key, and adds a uniqueness check so it can't recur silently.

In a design round the same idea comes up as "what happens when this step fails halfway" for every box on the whiteboard, and the data engineering system design round guide covers how to answer it across a whole pipeline. The data pipeline interview questions are the ones real loops ask about retries and late-arriving data, and the data pipeline practice problems make you design the load and its failure path end to end. For a longer set of worked idempotency answers, the idempotent pipelines interview guide walks through MERGE and partition overwrite, then tombstones for deletes, and the ETL interview questions test the same loads in their batch form.