MERGE compares a source table with a target table on a key and, in one statement, updates the rows that match, inserts the ones that do not, and can delete some. It is the standard way to apply an incremental load to a target table.
Shape of a merge
MERGE INTO dim_customer t USING staging_customer s ON t.customer_id = s.customer_id WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET t.email = s.email, t.city = s.city, t.updated_at = s.updated_at WHEN NOT MATCHED THEN INSERT (customer_id, email, city, updated_at) VALUES (s.customer_id, s.email, s.city, s.updated_at);
Add WHEN MATCHED AND s.is_deleted THEN DELETE if the source marks deletions. This is what a Type 1 slowly changing dimension looks like: overwrite the old values.
Why it is safe to rerun
If the load runs twice with the same staging data, the second run matches every row and sets the same values. Nothing is duplicated. That is idempotency, and it is the main reason to prefer MERGE over a plain INSERT for incremental loads.
The problem: duplicate keys in the source
If staging_customer has two rows for customer_id = 42, the target row is matched twice and the engine has to decide which update wins. Many engines refuse. BigQuery raises an error that the target row matched multiple source rows. Snowflake by default raises an error too, and when configured to allow it, the result is non-deterministic. So deduplicate the source first, usually with ROW_NUMBER() ... QUALIFY on the latest version.
Engine notes
Snowflake, BigQuery, SQL Server, Oracle and Databricks (Delta) support MERGE. PostgreSQL added MERGE in version 15. Before that, and still very common, it uses INSERT ... ON CONFLICT (key) DO UPDATE, which needs a unique constraint on the key. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE.