Idempotency

Overview

An operation is idempotent if running it once has the same effect as running it many times. SET x = 5 is idempotent—no matter how many times you run it, x ends up as 5. x = x + 1 is not—each execution changes the result. Idempotency matters enormously in data engineering because pipelines fail and get retried constantly: a network blip, a timeout, or an orchestrator restart can cause a task to run twice, and if that task isn't idempotent, the second run silently corrupts the data (duplicate rows, double-counted revenue, doubled inventory decrements).

Why it matters in pipelines

Distributed systems can rarely guarantee exactly-once delivery of messages or exactly-once execution of tasks; "at-least-once" is the realistic guarantee in most retry and message-queue systems. Idempotency is how engineers get exactly-once effective behavior on top of at-least-once delivery: if a task might run twice, make sure running it twice is harmless.

Common idempotent patterns in pipelines:

• Upserts instead of inserts — write logic that overwrites-by-key rather than always appending, so re-running a load doesn't create duplicates.
• Deterministic partition overwrites — a batch job that rebuilds "all data for 2026-07-06" by fully replacing that partition, rather than appending to it, is idempotent regardless of how many times it runs.
• Deduplication keys — attaching a unique event ID or idempotency key to each write so a downstream system can detect and discard duplicates.

Example: idempotent writes in DuckDB

Plain INSERT is not idempotent—running the same insert twice produces duplicate rows. DuckDB supports INSERT OR REPLACE INTO, which relies on a primary key or unique constraint to overwrite existing rows instead of duplicating them:

Copy code
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT,
    amount DECIMAL(10,2),
    updated_at TIMESTAMP
);

INSERT OR REPLACE INTO orders
VALUES (1001, 42, 59.99, '2026-07-06 10:00:00');

-- running this exact statement again leaves the table unchanged
INSERT OR REPLACE INTO orders
VALUES (1001, 42, 59.99, '2026-07-06 10:00:00');

The same idea works with ON CONFLICT, which gives more control over what gets updated on a duplicate key:

Copy code
INSERT INTO orders VALUES (1001, 42, 64.99, '2026-07-06 11:00:00')
ON CONFLICT (order_id) DO UPDATE SET
    amount = excluded.amount,
    updated_at = excluded.updated_at;

For batch pipelines, a common idempotent pattern is a full partition overwrite rather than a row-by-row upsert:

Copy code
DELETE FROM orders WHERE order_date = '2026-07-06';
INSERT INTO orders
SELECT * FROM read_parquet('s3://bucket/orders/dt=2026-07-06/*.parquet');

Re-running this for the same date always leaves the table in the same state, so a retry after a mid-run failure is safe.