Delta Lake
Delta Lake MERGE Performance Patterns for Large Tables
Why Delta MERGE gets slow on large tables and the patterns that keep it fast: clean sources, pruning predicates, fewer touched files, sane file sizes, safe concurrency and knowing when not to MERGE.
On this page
MERGE is the default upsert tool for Delta tables, and it is also one of the most common reasons a pipeline that took four minutes last quarter now takes forty. The statement looks like a row-level operation. Physically it is a file-level operation: Delta finds the files that contain matching rows and writes new versions of them. Most MERGE performance work is about making that set of files smaller, and making sure the work that remains is spent on rows that actually changed.
The examples use PySpark and Spark SQL and run in Microsoft Fabric notebooks or any Spark environment with Delta Lake.
What MERGE actually does
A MERGE has a source (the new or changed rows), a target (the Delta table), a match condition, and a set of WHEN clauses. Conceptually, Delta executes it in two passes:
- Find the touched files. Delta joins the source with the target on the match condition to find which target files contain at least one matched row. This pass only needs the columns used in the condition, so it is relatively light — unless the condition forces it to read the whole table.
- Rewrite. For each touched file, Delta produces the new content: updated rows, deleted rows removed, unchanged rows carried over. Inserts are written as new files. The commit then marks the old files as removed and the new files as added in the transaction log. (If file-level rewrites are unfamiliar, Delta tables for SQL Server engineers explains the storage model first.)
The expensive part is “carried over”. With classic copy-on-write, a 512 MB file containing one updated row is rewritten in full; 2,000 updates scattered across 2,000 files mean 2,000 rewritten files. Two features change that arithmetic:
- Low shuffle merge, enabled by default in all Fabric Spark runtimes, excludes unmodified rows from the expensive shuffle in the second pass. They are still written, separately and in their original order, which can produce up to twice as many output files.
- Deletion vectors let an update or delete mark old rows as removed in a small sidecar file instead of rewriting the data file. Fabric enables them by default for new tables from Spark Runtime 2.0 (Delta 4.1). The cost moves to readers until compaction rewrites the files.
Neither feature removes the need for good pruning. Both make a touched file cheaper; neither makes a full-table scan disappear.
Measure before you tune
Every MERGE commit records operation metrics in the Delta history. Read them before changing anything:
from delta.tables import DeltaTable
target = DeltaTable.forName(spark, "silver_orders")
history = (
target.history(20)
.filter("operation = 'MERGE'")
.select("version", "timestamp", "operationParameters.predicate", "operationMetrics")
)
for row in history.collect():
m = row["operationMetrics"]
changed = int(m["numTargetRowsUpdated"]) + int(m["numTargetRowsInserted"]) + int(m["numTargetRowsDeleted"])
copied = int(m["numTargetRowsCopied"])
print({
"version": row["version"],
"source_rows": m["numSourceRows"],
"changed_rows": changed,
"copied_rows": copied,
"copy_ratio": round(copied / changed, 1) if changed else None,
"files_added": m["numTargetFilesAdded"],
"files_removed": m["numTargetFilesRemoved"],
})The useful signals:
numTargetRowsCopiedversus changed rows. This is write amplification. A ratio of 500 means MERGE wrote 500 unchanged rows for every row that changed. That is the number to drive down.numTargetFilesRemoved. How many files the MERGE touched. If it is close to the total number of files in the table, the match condition is not pruning anything.numSourceRowsversus changed rows. If the source has 10 million rows and 30,000 changed, most of the source was no-op work. Filtering the source is cheaper than letting MERGE discover that nothing changed.numTargetFilesAddedversus removed. A MERGE that removes 40 files and adds 4,000 is fragmenting the table and making the next run slower.
Log these with the duration of each run. A trend over weeks tells you more than any single run, and the Spark UI shows where one run spends its time: target scan, join or write.
Pattern 1: fix the source first
Many MERGE problems are source problems.
Duplicate source keys. If two source rows match the same target row, Delta cannot decide which update wins and fails with an error saying multiple source rows matched and attempted to modify the same target row. Even when the duplicates only hit the insert branch, they insert duplicates silently. Deduplicate deterministically before the MERGE, keeping the latest version per key:
from pyspark.sql import Window
from pyspark.sql import functions as F
latest_per_key = Window.partitionBy("order_id").orderBy(
F.col("source_updated_at").desc(),
F.col("ingestion_id").desc(),
)
source = (
raw_changes
.withColumn("rn", F.row_number().over(latest_per_key))
.filter("rn = 1")
.drop("rn", "ingestion_id")
)The second ordering column is a tie-breaker. Without one, two rows with the same timestamp produce a nondeterministic winner, and reruns can give different results. Dropping it afterwards keeps the source schema equal to the target schema, which matters for INSERT * and schema evolution later.
No-op rows. Full extracts often resend rows that have not changed. Updating them rewrites files for nothing. A row hash makes change detection cheap and avoids long OR chains:
tracked = ["status", "amount", "currency", "customer_id"]
source = source.withColumn(
"row_hash",
F.sha2(F.concat_ws("||", *[F.coalesce(F.col(c).cast("string"), F.lit("<null>")) for c in tracked]), 256),
)Store row_hash in the target too, then use WHEN MATCHED AND t.row_hash IS DISTINCT FROM s.row_hash THEN UPDATE. Use the null-safe comparison: with a plain <>, a target row whose hash is still NULL (for example, rows loaded before you added the column) compares as unknown and is never updated. Be clear about what that condition buys you: it keeps unchanged rows from being updated, but a matched row still pulls its file into the rewrite set. Re-merging an unchanged batch in a local Delta 3.2 test reported zero updated rows and still removed and re-added every touched file. To avoid touching those files at all, drop unchanged rows from the source before the MERGE, for example with an anti-join against a narrow (order_id, row_hash) projection of the target in the same incremental window, and keep the clause condition as a safety net.
Merge cardinality. One source row should match at most one target row, on the business key. If a key is unique by convention rather than by validation, check the target periodically: a silent many-to-one match is a correctness bug before it is a performance bug.
Pattern 2: narrow the target with the match condition
By default, MERGE searches the whole target for matches. The Delta documentation’s first tuning recommendation is to reduce that search space by adding known constraints to the match condition. Those constraints prune files only if Delta can evaluate them against file metadata — partition values or per-file min/max statistics — before reading data.
The most reliable way is to compute the bounds from the source and inject them as literals:
bounds = source.agg(
F.min("order_date").alias("min_d"),
F.max("order_date").alias("max_d"),
).first()
if bounds["min_d"] is None:
print("Empty source, nothing to merge")
else:
condition = (
"t.order_id = s.order_id "
f"AND t.order_date >= DATE'{bounds['min_d']}' "
f"AND t.order_date <= DATE'{bounds['max_d']}'"
)
(
target.alias("t")
.merge(source.alias("s"), condition)
.whenMatchedUpdate(
condition="t.row_hash IS DISTINCT FROM s.row_hash",
set={c: f"s.{c}" for c in tracked + ["row_hash", "order_date", "source_updated_at"]},
)
.whenNotMatchedInsertAll()
.execute()
)This only makes sense when order_date is immutable for a given order_id. If a row can move from one date to another, a date-bounded condition will miss the old version and insert a duplicate. Choose a pruning column that is stable for the life of the key.
The same pattern in Spark SQL, with the bounds supplied as parameters by the pipeline:
MERGE INTO silver_orders AS t
USING staged_orders AS s
ON t.order_id = s.order_id
AND t.order_date >= DATE'2026-10-01'
AND t.order_date <= DATE'2026-10-07'
WHEN MATCHED AND t.row_hash IS DISTINCT FROM s.row_hash THEN
UPDATE SET *
WHEN NOT MATCHED THEN
INSERT *UPDATE SET * and INSERT * require the source to contain every target column. That is convenient for a staging table with the same schema, and risky otherwise; see the schema section below.
When partitioning actually helps MERGE
“Partition the table and MERGE gets faster” is true only under specific conditions:
- The partition column appears in the match condition with values Delta can resolve before reading data — literals or expressions that reduce to a fixed set of partitions. A join equality such as
t.order_date = s.order_datealone may not prune the target, because the matching values are only known after the source is read. - The changes are concentrated in a few partitions. If every daily batch updates rows across three years of dates, the MERGE touches most partitions anyway and partitioning adds directory and file overhead without reducing work.
- Each partition is large enough. Fabric’s guidance is to target at least 1 GB per partition and keep cardinality low to moderate. Partitioning a modest table by date creates thousands of small partitions, which makes every scan, including the matching pass, slower.
Fabric now positions partitioning mainly as a way to isolate concurrent writers, and recommends liquid clustering for read layout from Runtime 2.0. For MERGE: partition on a stable, low-cardinality column that every MERGE targets explicitly and that separates concurrent jobs; otherwise rely on clustering and statistics.
When file statistics help
Without partitions, Delta can skip files using per-file min/max statistics on the condition columns, but only if a key range lives in few files. If order_id values are random UUIDs spread across every file, each file’s min/max covers almost the whole key space and nothing is skipped. Clustering on the merge columns (liquid clustering or OPTIMIZE ... ZORDER BY) and keys that follow arrival order (dates, increasing IDs) make statistics-based pruning work.
Pattern 3: design the predicate for pruning, not convenience
Small details in the ON clause decide whether pruning happens:
- Do not wrap target columns in functions.
CAST(t.order_id AS STRING) = s.order_idorto_date(t.created_at) = ...can prevent file skipping on that column. Cast the source side instead, and fix type mismatches in the schema. - Avoid
ORacross different columns in the match condition; it turns a selective lookup into a broad scan. Two targeted MERGE statements or a normalized key are usually better. - Keep the
ONclause about identity and pruning. Change detection and business rules belong in theWHENconditions. - Make bounds deterministic. Expressions such as
current_date()in the condition make reruns depend on the clock. Pass the window as a parameter from the pipeline so a rerun of yesterday’s load prunes yesterday’s data; Fabric notebook parameters covers that pattern. - Be careful with
WHEN NOT MATCHED BY SOURCE. It updates or deletes target rows that have no match in the source, which means it has to consider the whole target unless you bound it. Always add a condition, such as the same date window, to theWHEN NOT MATCHED BY SOURCEclause.
Pattern 4: reduce the files you touch
Once the match condition prunes properly, the remaining cost is proportional to the number of touched files and their size.
- Align layout with the merge key. If updates arrive for recent orders, a layout clustered by order date keeps those rows in a small number of recent files. A layout clustered by customer spreads one day’s changes across the whole table.
- Use deletion vectors where the read trade-off is acceptable. For frequent small updates they are much cheaper than rewriting large files; schedule compaction so readers do not pay for an ever-growing pile of vectors.
- Do not run MERGE in a loop. One MERGE per entity or per tiny batch multiplies snapshot reads, joins, commits and small output files. Merge each table once per run.
Pattern 5: control file sizes and small files
MERGE writes through a shuffle, so the number of output files depends on shuffle parallelism. The Delta documentation notes that spark.sql.shuffle.partitions controls both the parallelism and the number of output files, and that a higher value produces more, smaller files. For partitioned tables, a MERGE can write many more files than shuffle partitions because each task can write into several partitions; the documented mitigation is spark.databricks.delta.merge, which repartitions output by the partition columns before writing.
# Size shuffle parallelism to the data actually being rewritten, not to the cluster
spark.conf.set("spark.sql.shuffle.partitions", "64")
# For partitioned targets: repartition MERGE output by partition columns before writing
spark.conf.set("spark.databricks.delta.merge.repartitionBeforeWrite.enabled", "true")Treat these values as starting points to test. Adaptive query execution and Fabric’s write optimizations also affect file counts, so measure files added per MERGE before and after a change. Frequent MERGE operations still fragment a table over time, which makes the next matching pass slower, so compaction is part of MERGE performance (when to compact, and when not to):
-- Compact recent data and co-locate rows by the merge key
OPTIMIZE silver_orders
WHERE order_date >= DATE'2026-09-01'
ZORDER BY (order_id);The WHERE clause on OPTIMIZE must reference partition columns, so this form fits a table partitioned by order_date. On a liquid-clustered table, run a plain OPTIMIZE without ZORDER BY and let clustering decide the layout. Compaction can conflict with a concurrent MERGE on the same files, so schedule it outside the MERGE window.
Pattern 6: schema evolution without surprises
MERGE validates that the source fits the target schema. With UPDATE SET * and INSERT *, the source must contain every target column, and extra source columns are ignored unless schema evolution is enabled.
There are two ways to enable it. The session setting spark.databricks.delta.schema applies to every write in the session, which is broader than most pipelines intend. Delta Lake 3.2 and later also provides withSchemaEvolution() on the merge builder, which scopes evolution to one statement:
(
target.alias("t")
.merge(source.alias("s"), condition)
.withSchemaEvolution()
.whenMatchedUpdateAll(condition="t.row_hash IS DISTINCT FROM s.row_hash")
.whenNotMatchedInsertAll()
.execute()
)Check the Delta version your runtime ships before relying on the builder method. The risks show up in the same jobs: a typo in a source column becomes a permanent new column, an upstream type change fails the job or is cast silently, and readers see columns nobody reviewed. Evolve schema through reviewed changes, and keep automatic evolution for cases where new columns are expected and harmless.
Pattern 7: concurrency and optimistic commits
Delta uses optimistic concurrency control. A writer reads a snapshot, writes new files, then validates at commit time that nothing it read was changed by another commit. Two MERGE operations can conflict if they read or modify the same files; an INSERT cannot conflict with another INSERT, but a MERGE can conflict with almost anything that adds files to the data it read.
The most common surprise is ConcurrentAppendException when two jobs merge different regions or dates into the same table. They feel independent, but if the match condition does not explicitly restrict each job to its slice, each job reads the whole table and conflicts with the other’s new files. The Delta and Fabric documentation describe the same fix: partition by the column that separates the jobs, and put literal values for that column in each job’s match condition:
MERGE INTO sales_orders AS t
USING staged_orders_emea AS s
ON t.order_id = s.order_id
AND t.region = 'EMEA'
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *Without partitioning, or without the literal in the condition, the jobs can conflict even though they change different rows. When jobs cannot be separated cleanly, serialize them in the orchestrator. Before adding conflict retries, make the MERGE idempotent: deterministic deduplication and change detection mean a retry produces the same table state.
Measuring a change properly
Each run sees a different source, so compare variants on the same input: capture a representative batch, run the baseline and the change against a copy of the target at the same version, and compare duration plus files removed, files added and rows copied. Then run the next day’s batch after each variant, because a change that speeds up today’s MERGE by writing thousands of small files often makes tomorrow’s slower. A change is real when it reduces rows copied or files touched for the same changed rows; a faster run with the same copy ratio usually means a quieter cluster.
When MERGE is the wrong tool
MERGE is the right tool when a minority of target rows change per run and you need row-level upsert semantics. It is the wrong tool in several common situations:
- Append-only events. Logs and immutable facts need appends, not upserts. If the source resends duplicates, use an insert-only MERGE bounded to a recent window, or deduplicate downstream.
- Most of the slice changes anyway. If a run changes 60% of the rows in a date range, MERGE pays for matching and then rewrites nearly everything. Rebuild the slice.
- Full snapshots of small or medium dimensions are clearer as an overwrite than as a MERGE that compares every row.
- Recomputed aggregates. Gold tables fully derived from Silver are easier to rebuild for the affected window than to upsert.
For window rebuilds, Delta’s selective overwrite atomically replaces the rows that match a predicate and leaves the rest of the table untouched. In SQL (Delta Lake 2.4 and later) it is INSERT ... REPLACE WHERE; DataFrame writers expose the same feature as the replaceWhere option:
INSERT INTO gold_daily_sales
REPLACE WHERE order_date >= DATE'2026-10-01' AND order_date <= DATE'2026-10-07'
SELECT * FROM rebuilt_windowBy default Delta validates that every row written matches the predicate and fails the write otherwise, which protects you from accidentally writing outside the window. Readers see either the old window or the new one, never a half-written state.
Incremental windows and late data
Incremental jobs should bound both sides: filter the source to the change window and bound the target condition to the range those changes can affect. Bounding only the source still lets MERGE scan the whole target. For late data, add a lookback: if records arrive up to three days late, each run processes [watermark - 3 days, now] and the target bound matches it. With change detection, the overlap costs little. Keep the watermark in a control table, and pass the window in as a parameter so reruns and backfills use the same logic as scheduled runs. The correctness side of this, advancing the watermark only after success, deletes and safe replays, is covered in Incremental Loads in Microsoft Fabric.
Production checklist
- Source deduplicated by business key with a deterministic tie-breaker; no-op rows removed.
- Literal pruning bounds in the match condition, on columns that never change for a key; no functions on target columns.
WHEN NOT MATCHED BY SOURCEbounded, if used.- Layout aligned with the merge key or arrival pattern; partitioning only for low-cardinality columns that separate writers.
- Output file counts measured and compaction scheduled.
- Schema evolution scoped to the statement, not enabled session-wide.
- Concurrent jobs separated by explicit partition literals or serialized.
operationMetricsand duration logged and trended.- Append or window rebuild where most of the slice changes.
Recommendation
Start every MERGE investigation with the history metrics, not with Spark settings. If rows copied dwarf rows changed, work on pruning and layout. If files added grow every run, work on output sizing and compaction. If runs fail under concurrency, make the separation explicit in the match condition. And if most of the data in the window changes on each run, stop merging and rebuild the window.
For where MERGE fits in a broader design, Medallion architecture explained shows which layers typically need upserts, Fabric Lakehouse vs Warehouse covers when a T-SQL MERGE in a Warehouse is the better fit, and How to make Fabric notebooks faster covers the Spark side of slow jobs. The Delta Lake partitioning checklist is a quick reference when deciding whether a partition column is worth it.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetRead and Write Delta TablesRead and write Delta tables with PySpark using safe append, overwrite, merge, schema-evolution, partitioning, and maintenance patterns for production data pipelines.Open cheat sheet
- RoadmapFabric Performance at ScaleWhy Delta MERGE gets slow on large tables and the patterns that keep it fast: clean sources, pruning predicates, fewer touched files, sane file sizes, safe concurrency and knowing when not to MERGE.View roadmap
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.