Why Delta Lake MERGE Gets Slower and How to Fix It

Same rows each night, ten times the runtime. Here is why.

Your nightly MERGE took four minutes in January. Now it takes forty. The number of new rows each night has barely changed. So what happened?

This is one of the most common questions data engineers ask about Delta Lake. The good news is that the cause is usually simple, and the fix is usually small.

What MERGE actually does

MERGE compares a small batch of new rows (the source) with a large table (the target). For each source row, Delta asks one question: does a matching row already exist?

To answer that, Delta must find the files in the target that might hold a match. Then it rewrites those files with the updated rows.

So MERGE cost depends on two things. How many files Delta has to read to find matches. And how many files it has to rewrite.

Your new batch did not grow. But your target table did. If Delta cannot skip files, it reads more of the table every night.

flowchart LR
  A[New rows: 10,000] --> B{Find matching files}
  B -->|Good layout| C[Read 12 files]
  B -->|Poor layout| D[Read 4,000 files]
  C --> E[Rewrite 12 files]
  D --> F[Rewrite hundreds of files]

Reason 1: The match condition is too loose

Look at your ON clause. If it only matches on order_id, Delta has no way to know which part of the table to look at. It scans everything.

If your new rows only cover recent dates, tell Delta that.

MERGE INTO orders AS target
USING new_orders AS source
ON target.order_id = source.order_id
  AND target.order_date >= current_date() - INTERVAL 7 DAYS
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *

That one extra line lets Delta skip every file that only holds older dates. Only add it when you are sure older rows never change. Otherwise you will insert duplicates.

Reason 2: The table layout does not help skipping

Delta keeps min and max values for columns in each file. If your keys are spread randomly across all files, every file's range covers almost everything. Nothing can be skipped.

Liquid Clustering fixes this by keeping similar key values together.

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

Pick the columns you use in the MERGE condition. You do not need to rewrite the pipeline.

Reason 3: Too many small files

Every nightly MERGE writes new files. Over months, a table can collect thousands of tiny ones. Reading thousands of small files is slow even when each one is quick.

Run OPTIMIZE regularly to combine them. We covered this in more detail in The Small File Problem.

Reason 4: Duplicate keys in the source

If the source has two rows with the same key, MERGE fails with an error about multiple source rows matching. Many teams work around this with extra logic that makes the job slower. The cleaner fix is to remove duplicates first.

from pyspark.sql import functions as F
from pyspark.sql.window import Window

latest_first = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())

deduped_df = (
    new_orders_df
    .withColumn("row_rank", F.row_number().over(latest_first))
    .filter(F.col("row_rank") == 1)
    .drop("row_rank")
)

Try it in Free Edition

You can see the effect yourself. Create a table, then check how much MERGE touched.

CREATE OR REPLACE TABLE workspace.default.merge_demo AS
SELECT
  id AS order_id,
  date_add('2026-01-01', CAST(id % 300 AS INT)) AS order_date,
  rand() * 100 AS amount
FROM range(0, 2000000);

CREATE OR REPLACE TEMP VIEW new_rows AS
SELECT order_id, order_date, amount + 1 AS amount
FROM workspace.default.merge_demo
WHERE order_date >= '2026-10-20'
LIMIT 5000;

MERGE INTO workspace.default.merge_demo AS target
USING new_rows AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET amount = source.amount;

DESCRIBE HISTORY workspace.default.merge_demo LIMIT 1;

In the history output, open operationMetrics. Note numTargetFilesRemoved and the execution time. Now add AND target.order_date >= '2026-10-20' to the ON clause, run again, and compare. Then try CLUSTER BY plus OPTIMIZE and run once more.

Your exact numbers will vary. The point is to watch the files touched go down.

A quick checklist

When a MERGE gets slower over time, check these in order:

Does the ON clause limit the search to the data that can change?
Is the table clustered on the match columns?
Has OPTIMIZE run recently?
Is the source free of duplicate keys?
Does DESCRIBE HISTORY show many files rewritten for few rows changed?

Why this matters

MERGE sits at the heart of most lakehouse pipelines. Customer tables, order updates, slowly changing dimensions. When it slows down, costs climb quietly and data arrives late.

Engineers who can read DESCRIBE HISTORY and explain why a MERGE got slow are the ones teams trust with production. It is a small skill with a large return.