Upsert generator

Upsert into Databricks SQL Warehouse: Delta Lake MERGE INTO

Name your table, key and columns and get the statement Databricks SQL Warehouse 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 Databricks SQL Warehouse incrementally.

Generate the statement

Statement · 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`)

How the upsert works in 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.

Before you run it

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.

Questions people ask

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.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Databricks SQL Warehouse 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