Upsert in SQL: ON CONFLICT, MERGE and the rerun test

The write every pipeline needs is the one you can run twice. Jobs fail, get retried, get backfilled. If a rerun doubles your rows, the pipeline is not safe. This page covers why a plain INSERT fails that test, the upsert in Postgres, the MERGE everywhere else, and the key without which none of it works. The print-ready A4 PDF is at the bottom.

The problem

• INSERT run twice gives duplicates.
• DELETE plus INSERT leaves a hole in the table while it runs.

The answer

• INSERT ... ON CONFLICT in Postgres, with DO UPDATE SET.
• MERGE INTO in BigQuery, Snowflake and SQL Server.
• A unique key: no key, no upsert.

Two syntaxes, one idea

Match on the key. Matched rows update, the rest insert.

PostgreSQL:

INSERT INTO daily (day, total)
SELECT day, SUM(amount)
  FROM staging GROUP BY day
ON CONFLICT (day) DO UPDATE
  SET total = EXCLUDED.total,
      updated_at = now();

BigQuery and Snowflake:

MERGE INTO daily d
USING staging s ON d.day = s.day
WHEN MATCHED THEN
  UPDATE SET total = s.total
WHEN NOT MATCHED THEN
  INSERT (day, total)
  VALUES (s.day, s.total);

In ON CONFLICT, EXCLUDED is the row that was rejected: the new values you tried to insert.

Remember

• WHEN NOT MATCHED: insert.
• WHEN MATCHED: update, or delete.
• updated_at: set it in the update branch.

The trap: the upsert that duplicates anyway

Three ways an upsert still fails the rerun test.

The test is simple: run the job twice on the same input and count the rows. Same count, same values, you are done.

The quiz

Which one survives a rerun?

A) INSERT INTO daily (day, total)
   SELECT day, SUM(amount)
   FROM staging GROUP BY day;

B) INSERT INTO daily (day, total)
   SELECT day, SUM(amount)
   FROM staging GROUP BY day
   ON CONFLICT (day) DO UPDATE
   SET total = EXCLUDED.total;

B. Run A twice and every day is in the table twice. B matches on the day: the second run finds the rows and updates them, so the count does not move. It needs a unique index on day.