Databricks MERGE Was 5x Slower Than an Overwrite — The Hidden Mistake That Was Killing Our SLA

Avinash Narala

6 min read

·

Jun 3, 2026

--

1

Listen

Share

Databricks · Delta Lake · PySpark · Performance

We had a MERGE job that was supposed to run in 20 minutes. It was running in 90.

The cluster looked fine. No OOM errors. No stragglers on the Spark UI — at least not obviously. The job just… crawled. And the frustrating part was that a full overwrite of the same table completed in 18 minutes.

That’s the thing about MERGE performance issues. They don’t announce themselves with a loud error. They just silently eat your SLA.

Here’s what was actually happening.

But Wait — Why Not Just Always Overwrite?

Fair question. If overwrite is faster, why use MERGE at all?

The short answer: overwrite only works when you have the complete truth for a bounded slice of data. The moment your source delivers changes instead of a full snapshot, overwrite becomes dangerous.

Here’s when overwrite is safe:

• You’re reprocessing a known date range and the source gives you all records for that range
• Running the job twice produces the same result (idempotent)
• No record outside that date range needs updating

# Safe overwrite — replace only the partitions in source
df.write.format("delta") \
    .mode("overwrite") \
    .option("replaceWhere", "event_date >= '2026-05-01' AND event_date <= '2026-05-07'") \
    .saveAsTable("catalog.schema.my_table")

But overwrite breaks down when:

• Your source is a queue or CDC stream (Kafka, Event Hub) — you can’t rewind and replay the full picture
• Late-arriving data crosses partition boundaries — a March record arriving in a June load gets silently dropped if you’re only overwriting June
• You need to handle deletes — overwrite replaces a partition, it can’t express “this specific row was removed upstream”
• Your join key doesn’t align with your partition column — a customer_id-keyed table partitioned by event_date means one customer's history spans hundreds of partitions; to overwrite safely, you'd touch all of them and lose the speed benefit entirely

The mental model: overwrite says “replace this slice entirely,” MERGE says “apply these specific changes.” Most production pipelines receive changes, not complete snapshots. That’s why MERGE exists — and why making it fast matters.

Press enter or click to view image in full size

The Setup

We had a Delta table with about 600 million rows, partitioned by event_date. Daily MERGE bringing in ~2 million new or updated records, matched on a composite key.

(
    target_df.alias("target")
    .merge(
        source_df.alias("source"),
        "target.customer_id = source.customer_id AND target.event_date = source.event_date"
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

Looks reasonable. And it was — until the table grew past a certain size.

What MERGE Actually Does Under the Hood

This is the part most people skip, and it’s where the problem hides.

When Databricks executes a MERGE, it doesn’t just touch the rows you’re updating. It rewrites every file that contains at least one matching row.

So if your source has 2 million records spread across 30 partitions, and each of those partitions has files with mixed data, Databricks rewrites all of them — even the parts that didn’t change.

In our case, the real problem was that our customer_id values were not correlated with our partition column (event_date). A single high-volume customer had records scattered across hundreds of partitions. So the MERGE scan touched almost the entire table.

Press enter or click to view image in full size

Diagnosing It

The Spark UI told the story once I knew where to look.

Under the MERGE job’s SQL tab, I found two key scans:

1. Source scan — small, fast, expected.
2. Target scan — reading 580 out of 600 partition directories.

That second number is what matters. MERGE is supposed to benefit from partition pruning. If it’s reading most of your table, pruning isn’t happening.

You can also check this directly:

from delta.tables import DeltaTable

dt = DeltaTable.forName(spark, "catalog.schema.my_table")
history = dt.history(1)
history.select("operationMetrics").show(truncate=False)

Look at numTargetFilesScanned vs numTargetFilesAdded. If the scan count is way higher than the write count, you're reading a lot to change a little.

The Fix: Partition Pruning in the MERGE Condition

The single most effective change we made was adding an explicit date range filter to the MERGE condition — one that matches the partition column.

from pyspark.sql.functions import min, max

# Get the date range of the incoming source data
date_bounds = source_df.agg(
    min("event_date").alias("min_date"),
    max("event_date").alias("max_date")
).collect()[0]

min_date = date_bounds["min_date"]
max_date = date_bounds["max_date"]

# Pre-filter the target to only the relevant partitions
target_filtered = spark.read.table("catalog.schema.my_table").filter(
    (col("event_date") >= min_date) & (col("event_date") <= max_date)
)

(
    DeltaTable.forName(spark, "catalog.schema.my_table").alias("target")
    .merge(
        source_df.alias("source"),
        """
        target.customer_id = source.customer_id 
        AND target.event_date = source.event_date
        AND target.event_date >= '{min_date}' 
        AND target.event_date <= '{max_date}'
        """.format(min_date=min_date, max_date=max_date)
    )
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute()
)

This is the key insight: Delta’s partition pruning only kicks in if the partition column appears directly in the MERGE condition with a literal or deterministic expression. Joining on target.event_date = source.event_date doesn't give the optimizer enough to prune with. You need explicit bounds.

After this change, target scan dropped from 580 partitions to 31. MERGE time went from 90 minutes to 22.

The Second Fix: File Sizes in the Target

While investigating, I also noticed the target table had a lot of small files — some partitions had 200+ files averaging 8MB each. That compounds the MERGE problem because each file needs to be opened, checked, and potentially rewritten.

A quick OPTIMIZE on the affected partitions helped:

spark.sql(f"""
    OPTIMIZE catalog.schema.my_table
    WHERE event_date >= '{min_date}' AND event_date <= '{max_date}'
    ZORDER BY (customer_id)
""")

ZORDER by customer_id means rows for the same customer end up co-located in fewer files. Less file-opening, better pruning, faster MERGE.

Don’t run OPTIMIZE on the whole table before every MERGE — that’s overkill. Run it periodically, or scope it to the partitions that are actually getting hammered.

What I’d Check If You’re Hitting This

1. What’s your numTargetFilesScanned? Pull it from operationMetrics after a MERGE run. If it's close to your total file count, pruning isn't working.

2. Does your MERGE condition have explicit partition column bounds? target.date = source.date is not enough. Add literal bounds derived from the source data.

3. How big are your files in the hot partitions? Run DESCRIBE DETAIL and check numFiles vs table size. Anything under 32MB per file is likely creating overhead.

4. Is your key correlated with your partition column? If your join key (like customer_id) is spread randomly across partitions, every MERGE touches everything. Consider whether your partition strategy still makes sense for your access patterns.

The Takeaway

A full overwrite being faster than a MERGE is a signal, not a fluke. It usually means the MERGE is doing too much — scanning partitions it doesn’t need to, opening files it won’t change.

The fix isn’t to give up on MERGE. It’s to give the optimizer the information it needs to be selective.

Explicit partition bounds in the MERGE condition. Reasonably sized files in the target. ZORDER on your join key if cardinality is high.

Those three things turned a 90-minute job into a 22-minute one. No cluster resizing, no schema changes, no infrastructure cost.

Press enter or click to view image in full size

If you’ve run into similar MERGE performance issues — especially around partition pruning or small file problems — I’d be curious what other patterns you’ve seen. Drop a comment.

Also related: if you’re dealing with schema evolution after a MERGE, that’s a separate trap worth understanding — wrote about it here.