Upsert

What is an upsert?

An upsert is a single write that inserts a row if it does not exist yet and updates it if it does. The name blends insert and update, and the database picks which one by matching the incoming row against a key you name.

Upserts are the standard way to make a repeated load safe: run the same batch twice and you get the same result, not doubled rows. That property is idempotence.

Choosing a match key

An upsert needs a key that tells the database when two rows are the same thing, usually a primary key or unique constraint. Get it wrong and the upsert quietly corrupts data instead of failing loudly.

Say you sync customers and match on email address. Two people share a mailbox, or one changes theirs: the upsert overwrites the wrong record or splits one customer into two. Nothing errors and the row count looks fine, so you only notice weeks later when the numbers stop adding up.

A key that is too broad collapses unrelated records into one; one too narrow lets duplicates through. Delta Lake refuses a merge when several source rows match the same target row, because it cannot tell which should win, so deduplicate the source first.

Upsert syntax by database

There is no single standard; each engine spells it differently.

PostgreSQL uses INSERT ... ON CONFLICT. You name the conflicting column or constraint, and EXCLUDED refers to the row you tried to insert.

INSERT INTO customers (customer_id, name, email)
VALUES (42, 'Acme NV', 'finance@acme.example')
ON CONFLICT (customer_id)
DO UPDATE SET
  name  = EXCLUDED.name,
  email = EXCLUDED.email;

MySQL writes INSERT ... ON DUPLICATE KEY UPDATE, which fires when the row would duplicate a primary key or unique index. SQL Server and Delta Lake both use MERGE INTO ... ON <condition> with explicit WHEN MATCHED and WHEN NOT MATCHED branches. MERGE is wider than a plain upsert: it can also delete target rows that no longer exist in the source.

Why upserts make loads idempotent

Most pipelines process only what changed, an incremental load driven by Change Data Capture or a modified-since timestamp. The risk is replay: a job retries after a timeout, or the same event arrives twice.

Upserts absorb that. A nightly customer sync keyed on customer_id can run once or five times, and the target still holds one Acme NV with its latest name and email. That is why loading staging data into a data warehouse, in an ETL or ELT pipeline, almost always upserts, not inserts.

What to watch out for with upserts

• Deletes need a separate rule. Rows that vanished from the source stay put unless you add a delete step, or use a MERGE with a WHEN NOT MATCHED BY SOURCE clause.

• Guard against stale updates. An out-of-order event can overwrite newer data with older values. Add a timestamp or version check to the update condition so an older row never wins.

• MERGE is not free. Microsoft cautions that MERGE in SQL Server can hit concurrency problems at scale, where separate insert and update statements sometimes perform better.