# Merge (upsert)

> A merge, or upsert, writes a batch of rows into a table in one statement: existing keys are updated and new keys are inserted.

A merge, or upsert, writes a batch of rows into a table in one statement: rows whose key already exists are updated, new keys are inserted, and, if asked, matching rows are deleted. Most platforms express it as `MERGE INTO`.

For example, applying a day's customer changes takes one statement, `MERGE INTO customers USING changes ON customers.customer_id = changes.customer_id`, with three clauses: `WHEN MATCHED AND changes.op = 'delete' THEN DELETE` removes closed accounts, `WHEN MATCHED THEN UPDATE` sets the new email and city for existing customers, and `WHEN NOT MATCHED THEN INSERT` adds new ones. The whole batch is applied in one transaction, so readers never see some customers updated and others not.

If the batch holds two changes for the same customer, two source rows match one target row, and most engines either fail the statement or apply one of the two without saying which. The fix is to keep only the latest change per key before the merge, ranked by a commit timestamp or sequence number. The merge condition should also use the full key, because matching on part of a composite key updates the wrong rows.

Merges rewrite the files that hold matched rows, so their cost grows with the target table. Limiting the target to the partitions or date range the batch can touch, and keeping the table [clustered](https://emolysis.com/glossary/clustering) on the key, keeps large merges affordable. A merge is [idempotent](https://emolysis.com/glossary/idempotency) when its batch is deduplicated: applying the same batch twice leaves the same table. See also [SCD Type 1](https://emolysis.com/glossary/scd-type-1) and [Delta Lake](https://emolysis.com/glossary/delta-lake).

On Databricks and in Microsoft Fabric lakehouses, `MERGE INTO` works on Delta tables; on Snowflake, `MERGE` often reads from a stream so that only changed rows are merged. See also [change data capture](https://emolysis.com/glossary/change-data-capture) and [natural key](https://emolysis.com/glossary/natural-key).

A merge is only well defined when each key appears once in the incoming batch, so pipelines usually [deduplicate](https://emolysis.com/glossary/deduplication) first. The merge idiom differs by platform, which is why it is a field in every Emolysis [platform profile](https://emolysis.com/docs/platform-profiles). The [CDC merge](https://emolysis.com/shapes/cdc-merge) and [SCD Type 2](https://emolysis.com/shapes/scd2) shapes both rest on it.

---

Page: https://emolysis.com/glossary/merge-upsert. Emolysis is a product of Bandpey GmbH, Munich, Germany. It is in private development, and nothing is released yet.