Generador de upsert

Upsert en Google BigQuery: MERGE with partition-pruned target

Nombra tu tabla, clave y columnas y obtén la sentencia que Google BigQuery acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga Google BigQuery de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · 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)

Cómo funciona el upsert en 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.

Antes de ejecutarla

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.

Preguntas frecuentes

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Google BigQuery con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones