What is the Snowflake MERGE statement and how does it work?

Snowflake’s MERGE statement is a single DML operation that performs an upsert: it matches rows from a source (table, query, or stage file) to a target table based on a key, then updates matched rows and inserts unmatched rows in one atomic pass. The syntax is:

MERGE INTO target_table AS tgt

USING source_table AS src

ON tgt.key = src.key

WHEN MATCHED THEN UPDATE SET tgt.col1 = src.col1, ...

WHEN NOT MATCHED THEN INSERT (col1, col2, ...) VALUES (src.col1, src.col2, ...);

You can also include WHEN NOT MATCHED BY SOURCE THEN DELETE to remove rows that no longer exist in the source. MERGE is efficient because it avoids separate SELECT, UPDATE, and INSERT statements, and it guarantees consistency even under concurrent loads. It’s especially useful for incremental data loads, CDC pipelines, or synchronizing staging tables with production.