Gerador de upsert

Upsert no Databricks SQL Warehouse: Delta Lake MERGE INTO

Nomeie tabela, chave e colunas e receba a instrução que o Databricks SQL Warehouse realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Databricks SQL Warehouse de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · Delta Lake MERGE INTO

MERGE INTO `deals` AS t
USING `deals_staging` AS s
ON t.`id` = s.`id`
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`)

Como o upsert funciona no Databricks SQL Warehouse

Delta Lake tables on Databricks support a full MERGE INTO, including WHEN NOT MATCHED BY SOURCE for deletes and UPDATE SET * / INSERT * shorthands when the schemas line up. Every MERGE commits a new table version, so DESCRIBE HISTORY tells you exactly which batch changed what, and a bad load is a RESTORE away. The generated statement uses explicit column lists so it stays valid when staging carries extra columns.

Cost is file rewriting. Enable deletion vectors on the target so a merge marks rows as removed instead of rewriting every Parquet file that contained a changed row, and cluster the table (liquid clustering, or Z-ORDER on the key) so matched rows are co-located. Unity Catalog governs the table, so grants and lineage come along with the merge for free.

Antes de executar

UPDATE SET * and INSERT * are valid shorthands when staging and target share a schema; MERGE WITH SCHEMA EVOLUTION (or delta.schema.autoMerge) lets new source columns land without a manual ALTER.
Enable deletion vectors on the target so a MERGE marks changed rows instead of rewriting whole Parquet files; pair it with liquid clustering or Z-ORDER on the key.
Every MERGE is a new table version: DESCRIBE HISTORY shows what changed, and RESTORE TABLE … TO VERSION AS OF undoes a bad batch.

Perguntas frequentes

How do I handle a new column from the source?

MERGE WITH SCHEMA EVOLUTION INTO … (Databricks Runtime 15.2+) adds it to the target automatically; on older runtimes set spark.databricks.delta.schema.autoMerge.enabled = true for the session.

Why is my MERGE rewriting the whole table?

Without deletion vectors, any file holding a matched row is rewritten. Turn them on with ALTER TABLE … SET TBLPROPERTIES ('delta.enableDeletionVectors' = true) and cluster on the key.

Can I undo a bad merge?

Yes. RESTORE TABLE deals TO VERSION AS OF <n> rolls the table back to the version before the batch; the history is kept until VACUUM removes it.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Databricks SQL Warehouse com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações