Snowflake UPSERT operation (aka MERGE)

You want to insert data to a table, but if a corresponding row already exists (by some rule, e.g. unique key) you want to update that instead of adding a new row, keeping the dataset's unique requirements intact. That's an "UPDATE AND INSERT" operation, or UPSERT. Some SQL languages have native support for it. PostgreSQL has UPSERT as native. Also MySQL supports the operation with INSERT and ON DUPLICATE KEY UPDATE. How do you do UPSERT on Snowflake?

Here's how:

Snowflake UPSERT i.e. MERGE operation

Snowflake's UPSERT is called MERGE and it works just as conveniently. It just has a different name. Here's the simple usage:

MERGE INTO workspace.destination_table d
  USING workspace.source_table s ON d.id = s.id AND d.val1 = s.val1
  WHEN MATCHED THEN update SET d.val2 = s.val2, d.val3 = s.val3
  WHEN NOT MATCHED THEN INSERT (id, val1, val2, val3)
  VALUES (s.id, s.val1, s.val2, s.val3);

Here the destination_table and source_table are of similar form, but they don't have to be. The above query uses both id and val1 as the unique combination and updates values to val2 and val3, or if the combination of id and val1 doesn't exist, will insert that combination as a new row.