Upsert generator

Upsert into Google BigQuery: MERGE with partition-pruned target

Name your table, key and columns and get the statement Google BigQuery actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Google BigQuery incrementally.

Generate the statement

Statement · MERGE with partition-pruned target

MERGE `deals` AS t
USING (
  SELECT id, name, stage, amount, owner_id, updated_at
  FROM `deals_staging`
  QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1
) AS s
ON t.id = s.id
  -- AND t._PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE SET
  name = s.name,
  stage = s.stage,
  amount = s.amount,
  owner_id = s.owner_id,
  updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (id, name, stage, amount, owner_id, updated_at)
  VALUES (s.id, s.name, s.stage, s.amount, s.owner_id, s.updated_at)

How the upsert works in Google BigQuery

BigQuery has no upsert keyword; MERGE is the mechanism, and the pricing makes one detail non-negotiable. A MERGE bills the bytes it scans in the target, and without a constant partition filter in the ON clause it scans everything. The generated statement carries that filter as a comment for you to set, dedupes the staging rows with QUALIFY, and skips stale rows via the watermark predicate on the MATCHED branch.

Partition the target by ingestion or event date and cluster it on the key columns, so the matched rows sit in a handful of blocks. Load the staging table with a batch load job rather than streaming, because rows still in the streaming buffer cannot be modified by DML and the MERGE will skip or fail on them.

Before you run it

A MERGE scans the whole target unless the ON clause carries a constant partition filter (a literal or query parameter, not a subquery). Uncomment the partition line and set the window your late data actually needs.
Rows still in the streaming buffer (recently streamed via the legacy insertAll API) cannot be modified by DML; batch load the staging table or wait for the buffer to flush.
Cluster the target on the key columns so the matched rows sit in few blocks; BigQuery bills the bytes it reads to find them.

Questions people ask

Does a MERGE scan the whole table?

Yes, unless the ON clause has a partition filter with a literal or query parameter. A subquery does not prune. Set the window to how late your source data can arrive.

Why did rows in the staging table not update?

They were streamed and still sit in the streaming buffer, which DML cannot touch for up to 90 minutes. Use load jobs for staging data.

Is there a DML quota to worry about?

The old 1,000 DML statements per table per day limit was removed in 2020. Concurrent mutating statements on one table are queued rather than rejected, so schedule merges per entity, not per row.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Google BigQuery with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog