What is Idempotency?

How it works

Every step in a long-lived pipeline runs more than once eventually: a scheduler retries after a timeout, an engineer reruns a day after fixing a bug, a backfill replays a month, or a streaming consumer reprocesses messages after a crash. Idempotency is what makes the second run harmless.

An idempotent job combines a few ingredients:

• A deterministic slice. The run processes a range fixed by its parameters (for example the run’s data interval), not by the current clock. A retry of Monday’s run on Tuesday still reads Monday.
• Replace or merge, never blind append. The job either deletes and rewrites its slice in one transaction (partition overwrite) or upserts rows by a stable key with MERGE or INSERT ... ON CONFLICT. Appending the same rows twice is the classic source of duplicates.
• Atomic publish. Readers see either the old result or the new one, never a half-written table.
• Guarded side effects. Emails, webhooks and API calls are recorded with a key so a retry can check whether they already happened.

Idempotency is also what turns at-least-once delivery into an effectively-once result: if the sink ignores or overwrites a repeated record, duplicates from retries do no damage.

Example

A daily load that deletes and rebuilds its own date in one transaction. Running the same block twice leaves the same three rows, even though the staging data contains an exact duplicate.

CREATE TABLE staging_orders (order_id int, amount numeric(10,2), order_date date);
INSERT INTO staging_orders VALUES
  (1, 50.00, '2026-10-05'), (2, 20.00, '2026-10-05'),
  (2, 20.00, '2026-10-05'), (3, 75.00, '2026-10-05');

CREATE TABLE fact_orders (order_id int, amount numeric(10,2), order_date date);

-- The run for 2026-10-05, executed twice (the second time is a retry).
BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-10-05';
INSERT INTO fact_orders
SELECT DISTINCT order_id, amount, order_date FROM staging_orders
WHERE order_date = DATE '2026-10-05';
COMMIT;

BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-10-05';
INSERT INTO fact_orders
SELECT DISTINCT order_id, amount, order_date FROM staging_orders
WHERE order_date = DATE '2026-10-05';
COMMIT;

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

 row_count | total
-----------+--------
         3 | 145.00

Common mistakes

• Filtering on now() - interval '1 day': a retry an hour later reads a different window, and a backfill reads today’s data instead of the requested day.
• Deleting with a different predicate than the insert uses, so reruns slowly drift.
• Running MERGE with duplicate keys in the source. A target row may match only one source row, so deduplicate first or the statement fails.
• A Spark static overwrite during a one-day rerun, which replaces the whole table instead of one partition.