Idempotent Loads: Safe to Run Twice

Scenario

Example company: the nightly job appends each day's revenue to a daily_revenue table. One night it times out after the insert but before it reports success. The orchestrator retries it automatically, and in the morning an engineer also reruns it by hand "to be sure".

What you see in production

• Tuesday's revenue in the dashboard is exactly three times the real number.
• The finance team's weekly report disagrees with the payment provider's settlement totals.
• Nobody can tell which rows are duplicates, because the duplicate rows are identical.

The naive code

INSERT INTO daily_revenue (day, revenue)
SELECT o.order_date, SUM(i.quantity * i.unit_price)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date = :day
GROUP BY o.order_date;

Reproduce it

This runs against the course's sample shop database (orders and order items). The append load runs twice; then the same load is written as an upsert keyed on the day and also runs twice.

CREATE TABLE daily_revenue_append (day TEXT, revenue REAL);
CREATE TABLE daily_revenue (day TEXT PRIMARY KEY, revenue REAL);

-- the same append load, run twice (a retry)
INSERT INTO daily_revenue_append
SELECT o.order_date, ROUND(SUM(i.quantity * i.unit_price), 2)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date = '2026-01-05' GROUP BY o.order_date;
INSERT INTO daily_revenue_append
SELECT o.order_date, ROUND(SUM(i.quantity * i.unit_price), 2)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date = '2026-01-05' GROUP BY o.order_date;

-- the idempotent version: upsert by the table's key, run twice
INSERT INTO daily_revenue
SELECT o.order_date, ROUND(SUM(i.quantity * i.unit_price), 2)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date = '2026-01-05' GROUP BY o.order_date
ON CONFLICT (day) DO UPDATE SET revenue = excluded.revenue;
INSERT INTO daily_revenue
SELECT o.order_date, ROUND(SUM(i.quantity * i.unit_price), 2)
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.order_date = '2026-01-05' GROUP BY o.order_date
ON CONFLICT (day) DO UPDATE SET revenue = excluded.revenue;

SELECT 'append' AS load, COUNT(*) AS rows_for_day, SUM(revenue) AS reported_revenue FROM daily_revenue_append
UNION ALL
SELECT 'upsert', COUNT(*), SUM(revenue) FROM daily_revenue;

load | rows_for_day | reported_revenue
append | 2 | 107.98
upsert | 1 | 53.99

Root cause

The load was not idempotent: running it twice produced a different result from running it once. Retries are normal (timeouts, crashes after the write but before success is recorded, manual reruns), so any non-idempotent step will eventually duplicate data.

The fix

Design every load so repeating it gives the same final state:

• Upsert / MERGE by key: INSERT ... ON CONFLICT DO UPDATE in PostgreSQL and SQLite, MERGE in BigQuery, Snowflake, SQL Server and Redshift.
• Partition overwrite: in one transaction, delete the target partition (WHERE day = :day) and insert its rows again, or use the warehouse's "replace partition" write mode.
• Staging and swap: load into a staging table, validate it, then swap or merge it into the target atomically.

Every target table needs a clear key (the grain: one row per day, per order, per order line) for this to work.

Trade-offs

• Upserts and merges cost more than blind appends, especially on large tables; partition overwrites are often cheaper.
• Append-only is fine for immutable event logs if each event has a unique ID and readers deduplicate (see the deduplication lesson).

Detect it in production

• Uniqueness tests on each table's key (COUNT(*) vs COUNT(DISTINCT key)), run after every load. dbt's unique data test does this.
• Reconcile totals with an independent source (payment settlements, the source database).

Key points

• Retries and reruns are guaranteed, so every load must be idempotent.
• Use upsert/MERGE by key, partition overwrite, or staging + swap.
• Every table needs a declared key (its grain) and a uniqueness test.