How to use MERGE (upsert) in BigQuery and Snowflake

What MERGE is for

MERGE is a write operation, unlike a JOIN, which only reads. You use it to load a daily extract into a warehouse table: new customers get inserted, changed ones get updated, and optionally rows that vanished from the source get deleted. For reading two tables together, see combining two tables in SQL instead.

BigQuery syntax

MERGE INTO analytics.customers AS t
USING staging.customers_today AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
  UPDATE SET email = s.email,
             country = s.country,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, country, updated_at)
  VALUES (s.customer_id, s.email, s.country, s.updated_at)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

• The INTO keyword is optional in BigQuery.
• WHEN NOT MATCHED BY SOURCE acts on target rows with no match in the source, which is how you delete or flag removed records. Leave it out for a plain upsert, otherwise a partial daily extract deletes everything not in it.
• INSERT ROW inserts all columns in order when source and target have the same schema.
• If more than one source row matches the same target row, BigQuery raises an error for the UPDATE or DELETE.

Snowflake syntax

MERGE INTO analytics.customers AS t
USING staging.customers_today AS s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN
  UPDATE SET t.email = s.email,
             t.country = s.country,
             t.updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, country, updated_at)
  VALUES (s.customer_id, s.email, s.country, s.updated_at);

• Snowflake supports multiple WHEN MATCHED clauses with extra AND conditions; they are evaluated in order.
• Snowflake has no NOT MATCHED BY SOURCE clause. To delete target rows missing from a full extract, run a separate DELETE, or use a soft-delete flag in the source as above.
• When several source rows match one target row, Snowflake's behaviour depends on the ERROR_ON_NONDETERMINISTIC_MERGE parameter. By default it raises an error; if that's turned off, it picks one row in a way you can't predict.

Deduplicate the source first

Both errors above come from the same cause: the staging table has two rows for one key, typically because an incremental extract caught a customer twice. Keep only the latest version per key with a window function and use that as the USING source.

MERGE INTO analytics.customers AS t
USING (
  SELECT * EXCEPT (rn) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY updated_at DESC
    ) AS rn
    FROM staging.customers_today
  ) WHERE rn = 1
) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET email = s.email, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT ROW;

That is BigQuery syntax. In Snowflake, use QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1 in the subquery and SELECT * EXCLUDE (rn) if you need to drop a helper column. It is the same idea as aggregating before a join in why a SQL join returns too many rows.

Worked example

After the merge the target holds 5 rows: 4 existing plus 1 insert, with 2 updates. Without the dedup step, C3 would cause the error described above. A quick check is to compare COUNT(*) and COUNT(DISTINCT customer_id) in staging before every run.

Practical tips

• Add a condition such as s.updated_at > t.updated_at to WHEN MATCHED so replays of old data don't overwrite newer rows.
• NULL keys never match in the ON clause, so source rows with a NULL key are inserted every run. Filter them out.
• Test on a copy of the target first; MERGE changes data and there's no preview.
• Both warehouses report how many rows were inserted, updated and deleted; log those numbers.

Analistable is read-only by design: it runs SELECT queries against spreadsheets loaded in your browser and never writes back. If you want to sanity-check a staging export against the current table before a MERGE, export both to CSV and compare them there. More SQL patterns are in the pandas, R and SQL merge guide.

Costs and performance

MERGE scans the target to find matches, so on large tables limit the work. In BigQuery, if the target is partitioned by date and you only load recent data, add a filter on the partition column inside the ON clause (for example AND t.order_date >= '2026-10-01') so fewer partitions are read. In Snowflake, clustering the target on the join key can help pruning. In both, keep the source small: load only new or changed rows into staging rather than the full history each day.

Frequently asked questions

Can MERGE update and insert in the same run?

Yes. That's its main purpose: WHEN MATCHED updates existing rows and WHEN NOT MATCHED inserts new ones in a single statement.

Is MERGE atomic?

Yes. In both BigQuery and Snowflake a MERGE runs as a single statement: all its inserts, updates and deletes apply together or not at all.

Can I use a CTE as the MERGE source?

Put the query in the USING clause as a subquery, as in the example. That works in both warehouses and avoids depending on dialect-specific WITH placement.

What's the difference between MERGE and INSERT OVERWRITE?

INSERT OVERWRITE replaces the whole table (or partition) with new data. MERGE changes only the rows that match or don't match, keeping everything else.

Does MERGE work in Postgres?

PostgreSQL added MERGE in version 15. Older versions use INSERT … ON CONFLICT DO UPDATE for upserts.

Related guides

• How to combine two tables in SQL
• Why a SQL join returns too many rows, and how to fix it
• How to merge datasets in Stata
• How to merge CSV files in Python
• How to merge data frames in R