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 on the key, keeps large merges affordable. A merge is idempotent when its batch is deduplicated: applying the same batch twice leaves the same table. See also SCD Type 1 and 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 and natural key.
A merge is only well defined when each key appears once in the incoming batch, so pipelines usually deduplicate first. The merge idiom differs by platform, which is why it is a field in every Emolysis platform profile. The CDC merge and SCD Type 2 shapes both rest on it.