MERGE combines matched updates with unmatched inserts in one statement. The target key defines which row counts as the same entity. That key must be clear before you decide which source values should replace the target.
An update feed can contain several versions of one customer. BigQuery raises an error when multiple source rows match one target row for an update or delete. Reduce the source to one row per key first. ROW_NUMBER ordered by a revision or timestamp is a common pattern; add a stable tie-breaker if the ordering values can tie.
The supplied update fixture has an older and newer revision for each changed customer. Choose the newest revision, then upsert it into a small snapshot. Run the same upsert twice when testing: the second run should leave the same rows and values. Idempotence means a retry doesn't create duplicate customers or repeat an additive change.
MERGE practice.customer_snapshot AS t
USING (SELECT * FROM practice.customer_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY revision DESC) = 1) AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET plan = s.plan, revision = s.revision
WHEN NOT MATCHED THEN INSERT (customer_id, full_name, plan, revision)
VALUES (s.customer_id, s.full_name, s.plan, s.revision);
Choose the current source version before matching it to the snapshot.