Home / Databricks on Azure

Databricks on AzureHow-toIntermediate

Optimization: faster Delta MERGE on Azure Databricks - pruning, deletion vectors and source deduplication

MERGE is often the most expensive step of an incremental pipeline. Make it touch fewer files with pruning predicates and clustering on the join key, enable deletion vectors to avoid rewriting whole files, deduplicate the source first, and read the operation metrics to see what each run really rewrote.

Deepak Bhardwaj2 min read

Applies toDelta Lake MERGE INTO on Azure Databricks (Databricks Runtime with deletion vectors and liquid clustering support)

Why MERGE gets slow

Cause Effect
No predicate limiting the target Every file is scanned for matches
Target not clustered on the merge key Matching rows spread across many files, all rewritten
Copy-on-write rewrites A single updated row rewrites a whole file
Duplicate keys in the source Error, or extra work to resolve
Many small files Overhead per file

Read what a MERGE did

DESCRIBE HISTORY silver.orders LIMIT 5;
-- operationMetrics: numTargetFilesAdded, numTargetFilesRemoved,
-- numTargetRowsUpdated, numTargetRowsCopied, executionTimeMs

High numTargetRowsCopied compared with rows updated means you're rewriting lots of untouched rows.

Fixes in order

1. Add pruning predicates

MERGE INTO silver.orders t
USING updates s
  ON t.order_id = s.order_id
 AND t.order_date >= current_date() - INTERVAL 7 DAYS   -- limits target files scanned
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

Only add a predicate if updates really can't touch older rows; otherwise you'll insert duplicates.

2. Cluster the target on the key

ALTER TABLE silver.orders CLUSTER BY (order_date, order_id);
OPTIMIZE silver.orders;

Liquid clustering keeps related keys together so fewer files match (see the liquid clustering post).

3. Enable deletion vectors

ALTER TABLE silver.orders SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);

Updates and deletes mark rows as removed instead of rewriting files; later OPTIMIZE or auto compaction purges them. Check that every reader of the table supports deletion vectors (external engines may not).

4. Deduplicate the source

from pyspark.sql import functions as F, Window
w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
updates = (raw.withColumn("rn", F.row_number().over(w))
              .filter("rn = 1").drop("rn"))

5. Right-size the batch

Very small, very frequent MERGEs create many commits and small files; very large ones need big clusters. Find a batch interval that fits the freshness SLA (see the batch vs streaming guide).

Measure

Compare executionTimeMs, files removed/added and DBUs per run before and after each change.

Tip

Photon accelerates MERGE significantly on many workloads. If the pipeline is MERGE-heavy, test with Photon enabled and compare cost per run.

Checklist

  • Operation metrics reviewed
  • Safe pruning predicate added
  • Target clustered on merge keys
  • Deletion vectors enabled (reader compatibility checked)
  • Source deduplicated
  • Batch interval tuned; before/after measured

Related fixes