Merge Upsert
Applying a batch of changes to a target by matching on a key and inserting, updating or deleting per row, which is expensive and frequently avoidable.
Given a set of incoming records and a target table, a merge matches on a business key and applies the appropriate operation per row. It is the natural expression of "keep the target in sync with the source" and it is one of the most costly operations in an analytical store, because it must locate existing rows in a system optimised for scanning rather than for lookup.
Cost is driven by how much of the target has to be examined. A merge scoped by a partition predicate — only the last seven days — touches a fraction; the same merge without one rewrites the table.
The design question worth asking first is whether the merge is needed at all. Append-only ingestion with a view that selects the latest version per key is often cheaper, keeps history for free, and removes the write amplification entirely. It costs read-time work instead, which is the right trade when writes are frequent and reads are not.
The correctness trap is a source that does not communicate deletes. A merge on a key can only insert and update what it is given; rows deleted at source persist indefinitely unless the source emits tombstones or a periodic full comparison detects them.