https://community.databricks.com/t5/technical-blog/mastering-delta-lake-merge-performance-why-it-slows-down-and-how/ba-p/156305 <P><SPAN>If you've worked with Delta Lake at scale, you've encountered this: your MERGE operation that once completed in seconds now takes minutes or hours, as your table grows.</SPAN></P> <P><SPAN>The culprit? Without optimization, MERGE operations scan far more data than necessary. Even when your table is properly laid out and your MERGE condition includes the key column.</SPAN></P> <P><SPAN>Here's the part that's often overlooked: the key is to pre-scan your source data and inject literal values into the MERGE condition. This enables pruning that would otherwise be impossible—for both streaming and batch workloads.</SPAN></P> <P><SPAN>This article explains how MERGE executes at a high level, why the default behavior is problematic, and the strategies to fix it.</SPAN></P> <H2><SPAN>HOW MERGE WORKS AT A HIGH LEVEL</SPAN></H2> <P><SPAN>A typical MERGE looks innocent enough:</SPAN></P> <LI-CODE lang="python">target_delta.alias("target").merge( source=source_df.alias("source"), condition="target.order_id = source.order_id" ).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()</LI-CODE> <P><SPAN>Delta Lake executes this through multiple phases:</SPAN></P> <P><STRONG>Phase 1: Transaction Log Analysis</STRONG><SPAN> Spark reads the </SPAN><SPAN>_delta_log</SPAN><SPAN> to build the current table state—loading checkpoint files, reading commit JSONs, and extracting file paths, and column statistics.</SPAN></P> <P><STRONG>Phase 2: File Selection (THE PROBLEM)</STRONG><SPAN> Delta determines which files to scan. By default, this is ALL files. We'll dive deep into why shortly.</SPAN></P> <P><STRONG>Phase 3: Join Execution</STRONG><SPAN> Delta joins source and target to find matches. The join strategy is chosen by the optimizer based on data sizes and the MERGE clauses — you'll see </SPAN><SPAN>BroadcastHashJoin</SPAN><SPAN>, </SPAN><SPAN>SortMergeJoin</SPAN><SPAN>, or </SPAN><SPAN>ShuffledHashJoin</SPAN><SPAN> in the query plan, with join types like </SPAN><SPAN>LeftSemi</SPAN><SPAN>, </SPAN><SPAN>Inner</SPAN><SPAN>, or </SPAN><SPAN>LeftAnti</SPAN><SPAN> depending on whether the MERGE has matched updates, inserts, or both.</SPAN></P> <P><STRONG>Phase 4: File Rewrite (Copy-on-Write)</STRONG><SPAN> By default, every affected file is rewritten entirely. Even updating 1 row in a 256MB file means reading 256MB and writing 256MB. This is write amplification. (With Deletion Vectors enabled, Delta can mark rows as deleted without full file rewrites—more on this later.)</SPAN></P> <P><STRONG>Phase 5: Transaction Commit</STRONG><SPAN> The transaction log is updated atomically with remove actions for old files and add actions for new files.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dhruvkumar1_6-1778088130228.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26715iF749AB70B3ABF2B1/image-size/medium?v=v2&amp;px=400" role="button" title="dhruvkumar1_6-1778088130228.png" alt="dhruvkumar1_6-1778088130228.png" /></span></P> <H2><SPAN>THE SCENARIO</SPAN></H2> <P><SPAN>Consider an e-commerce orders table:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>500 million orders across 50 regions</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Organized by: </SPAN><SPAN>region</SPAN><SPAN> — e.g. </SPAN><SPAN>CLUSTER BY (region, event_time)</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Primary key: </SPAN><SPAN>order_id</SPAN><SPAN>, </SPAN><SPAN>region,event_time</SPAN><SPAN> (together they uniquely identify each event for a given order)</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Streaming updates from order management system (status changes, shipping updates, returns)</SPAN></LI> </UL> <P><SPAN>Your MERGE condition looks like this:</SPAN></P> <LI-CODE lang="python">target.region = source.region AND target.order_id = source.order_id AND target.event_time = source.event_time</LI-CODE> <P><SPAN>This is exactly how you should write it—matching on the key column and full primary key. Yet every micro-batch scans the entire table, even when processing just 5,000 order updates from 2 regions.</SPAN></P> <P><SPAN>Why? This brings us back to Phase 2.</SPAN></P> <H2><SPAN>THE ROOT CAUSE: PHASE 2 SCANS EVERYTHING BY DEFAULT</SPAN></H2> <P><SPAN>The MERGE condition above includes </SPAN><SPAN>region</SPAN><SPAN> — which is a clustering key. Intuitively, you'd expect Spark to:</SPAN></P> <OL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Look at the source batch's region values</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Only scan files for those regions</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Skip everything else</SPAN></LI> </OL> <P><SPAN>But this doesn't happen.</SPAN></P> <P><SPAN>At query planning time (Phase 2), the source DataFrame hasn't been evaluated—Spark doesn't know what values it contains. The condition </SPAN><SPAN>target.region = source.region</SPAN><SPAN> references a column from an unevaluated DataFrame. Spark cannot push this down as a predicate because it has no concrete values to filter on.</SPAN></P> <P><SPAN>The result: every file in your table gets scanned, regardless of how you structure your join condition — even if using </SPAN><SPAN>CLUSTER BY(region, ...)</SPAN><SPAN>.</SPAN></P> <P><SPAN>This is a common surprise for engineers: "My table is clustered/partitioned by region and I'm joining on region—why is it still scanning everything?"</SPAN></P> <H2><SPAN>THE SOLUTION: LITERAL VALUE INJECTION</SPAN></H2> <P><SPAN>Pruning only works when the merge condition contains literal values that Spark can evaluate at plan time.&nbsp;</SPAN></P> <P><SPAN>Same table, same primary key. But now we extract values first:</SPAN></P> <LI-CODE lang="python"># Pre-scan the source batch regions = source_df.select("region").distinct().collect() region_list = ", ".join([f"'{r.region}'" for r in regions]) # Inject literals into the condition condition = f""" target.region IN ({region_list}) AND target.region = source.region AND target.order_id = source.order_id AND target.event_time = source.event_time """</LI-CODE> <P><SPAN>The </SPAN><SPAN>IN ({region_list})</SPAN><SPAN> clause contains literal strings like </SPAN><SPAN>IN ('us-east', 'eu-central')</SPAN><SPAN>. These are known at plan time. Spark can now skip data by looking at files where the min/max doesn’t overlap.</SPAN></P> <P><SPAN>The rest of the condition handles the actual row matching. The literal clause handles the pruning.</SPAN></P> <P><STRONG>Key insight:</STRONG><SPAN> You're not replacing the join condition—you're augmenting it with pruning hints that Spark can evaluate at plan time.&nbsp;</SPAN></P> <H2><SPAN>HOW LITERAL INJECTION DRIVES PRUNING</SPAN></H2> <P><SPAN>Your injected literals drive file skipping. The mechanism depends on your table layout, but the result is the same — Spark reads only the files that could contain matching rows:</SPAN></P> <TABLE> <THEAD> <TR> <TH> <P><STRONG>Table layout</STRONG></P> </TH> <TH> <P><STRONG>What </STRONG><STRONG>target.region IN ('us-east-1', 'eu-central-1')</STRONG><STRONG> does</STRONG></P> </TH> </TR> </THEAD> <TBODY> <TR> <TD> <P><STRONG>CLUSTER BY (region, ...)</STRONG></P> </TD> <TD> <P><SPAN>Skips files whose min/max on </SPAN><SPAN>region</SPAN><SPAN> don't overlap.</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P><STRONG>Go further with additional columns.</STRONG><SPAN> Delta Lake stores min/max statistics on columns of </SPAN><STRONG>numeric, string, date, and timestamp</STRONG><SPAN> types. Inject ranges on </SPAN><I><SPAN>any</SPAN></I><SPAN> column with statistics — not just the clustering/partition key:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>target.event_time &gt;= '2024-03-25'</SPAN><SPAN> — skips files with only older data</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>target.order_id &gt;= 'ORD-0050000' AND target.order_id &lt;= 'ORD-0055000'</SPAN><SPAN> — skips files outside this ID range</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Combine multiple filters — the more selective, the more files skipped</SPAN></LI> </UL> <P><SPAN>With Liquid Clustering, all clustering keys (</SPAN><SPAN>region</SPAN><SPAN>, </SPAN><SPAN>event_time</SPAN><SPAN>, </SPAN><SPAN>order_id</SPAN><SPAN>) benefit because of non-overlapping ranges on all keys. With traditional partitioning, only the partition column gets file pruning — other columns rely on Z-ordering for tight file ranges.</SPAN></P> <P><SPAN>Here's the full pattern — extract values from the source, inject them as literals on key columns:</SPAN></P> <LI-CODE lang="python"># Extract pruning values regions = source_df.select("region").distinct().collect() region_list = ", ".join([f"'{r.region}'" for r in regions]) time_bounds = source_df.agg(F.min("event_time"), F.max("event_time")).collect()[0] # Inject literals — works for both liquid clustered and partitioned tables condition = f""" target.region IN ({region_list}) AND target.event_time &gt;= '{time_bounds.min_time}' AND target.event_time &lt;= '{time_bounds.max_time}' AND target.region = source.region AND target.order_id = source.order_id AND target.event_time = source.event_time """</LI-CODE> <P><STRONG>Important:</STRONG><SPAN> File-level statistics are only collected for the first 32 columns by default (controlled by </SPAN><SPAN>delta.dataSkippingNumIndexedCols</SPAN><SPAN>). For string types it only keeps the first 32 chars. For wide tables, either reorder your columns, increase this setting, or use </SPAN><SPAN>delta.dataSkippingStatsColumns</SPAN><SPAN> to specify columns by name.</SPAN></P> <P><STRONG>Safety rule:</STRONG><SPAN> Only inject range filters on columns that are part of your MERGE key. Filtering on a column not in the MERGE condition risks skipping files that contain rows needing updates.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dhruvkumar1_7-1778088130231.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26717iCC1B208181B81D3C/image-size/medium?v=v2&amp;px=400" role="button" title="dhruvkumar1_7-1778088130231.png" alt="dhruvkumar1_7-1778088130231.png" /></span></P> <H2><SPAN>STREAMING VS BATCH: THE PRE-SCAN PATTERN</SPAN></H2> <P><SPAN>The literal injection technique requires two passes over your source data: one to extract pruning values, one for the MERGE itself. This has important implications for how you structure your pipeline.</SPAN></P> <P><STRONG>Key requirement for streaming:</STRONG><SPAN> You </SPAN><STRONG>must</STRONG><SPAN> use </SPAN><SPAN>foreachBatch</SPAN><SPAN> mode. In a continuous streaming query (without </SPAN><SPAN>foreachBatch</SPAN><SPAN>), Spark builds a single execution plan for the entire stream — there's no hook to pre-scan a micro-batch and inject literals before the MERGE runs. The </SPAN><SPAN>foreachBatch</SPAN><SPAN> callback gives you a materialized DataFrame that you can query separately before passing it to the MERGE. Without </SPAN><SPAN>foreachBatch</SPAN><SPAN>, literal injection is not possible in streaming.</SPAN></P> <P><STRONG>For Streaming (foreachBatch):</STRONG></P> <LI-CODE lang="python">def merge_micro_batch(batch_df, batch_id): # batch_df is already materialized — safe to query twice regions = batch_df.select("region").distinct().collect() region_list = ", ".join([f"'{r.region}'" for r in regions]) condition = f""" target.region IN ({region_list}) AND target.region = source.region AND target.order_id = source.order_id AND target.event_time = source.event_time """ target_delta.alias("target").merge( source=batch_df.alias("source"), condition=condition ).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute() stream.writeStream.foreachBatch(merge_micro_batch).start()</LI-CODE> <P><SPAN>The micro-batch is small and already bounded — recomputation is negligible. Caching is optional.</SPAN></P> <P><STRONG>For Batch:</STRONG><SPAN> Cache your source DataFrame first. Without caching, the extract step triggers one computation and the MERGE triggers a second. This double-read is wasteful and dangerous:</SPAN></P> <P><STRONG>Warning — Data Consistency Risk:</STRONG><SPAN> If your source is non-deterministic (e.g., uses </SPAN><SPAN>current_timestamp()</SPAN><SPAN>, reads from a file source that changed between evaluations, or samples data), the MERGE might see different data than what you used for pruning. This can lead to </SPAN><STRONG>silent data loss or corruption</STRONG><SPAN> — rows that existed during pruning may vanish during the MERGE, or new rows may appear that don't match the pruning predicates. Always cache non-deterministic sources before extracting pruning literals.</SPAN></P> <P><STRONG>Pattern:</STRONG> <SPAN>source.cache()</SPAN><SPAN> → </SPAN><SPAN>collect()</SPAN><SPAN> → </SPAN><SPAN>merge()</SPAN><SPAN> → </SPAN><SPAN>source.unpersist()</SPAN></P> <P><STRONG>A note on cost:</STRONG><SPAN> The </SPAN><SPAN>collect()</SPAN><SPAN> call introduces its own overhead — it materializes results and returns them to the driver, adding a round-trip. For very small source batches, this overhead may not be worth it if the target table is already well-clustered and the MERGE is fast. Weigh the cost of the </SPAN><SPAN>collect()</SPAN><SPAN> against the savings on the MERGE: if file pruning lets you skip scanning a large percentage of the target table, the driver round-trip is a bargain.</SPAN></P> <P><STRONG>Bonus — Broadcast Hint:</STRONG><SPAN> For small source batches (common in streaming), adding a broadcast hint eliminates the shuffle join:</SPAN></P> <LI-CODE lang="python">from pyspark.sql.functions import broadcast target_delta.alias("target").merge( source=broadcast(batch_df).alias("source"), condition=condition ).whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()</LI-CODE> <P><SPAN>This pairs well with literal injection — pruning reduces the target side, broadcast eliminates the shuffle on the source side.</SPAN></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dhruvkumar1_8-1778088130229.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26718i040603F39A9E0F34/image-size/medium?v=v2&amp;px=400" role="button" title="dhruvkumar1_8-1778088130229.png" alt="dhruvkumar1_8-1778088130229.png" /></span></P> <H2><SPAN>PHYSICAL LAYOUT: MAKING LITERAL INJECTION EFFECTIVE</SPAN></H2> <P><SPAN>Literal injection tells Spark </SPAN><I><SPAN>what</SPAN></I><SPAN> to filter. But how many files it actually skips depends on how your data is physically organized. If files contain randomly scattered values, even perfect literals won't skip much — every file's min/max range overlaps with everything.</SPAN></P> <P><SPAN>This is where physical layout matters: organize your data so that files contain tight, non-overlapping value ranges. Then your injected literals become surgically precise.</SPAN></P> <P><SPAN>You have two options — choose based on whether you're building new tables or optimizing existing ones.</SPAN></P> <H3><SPAN>LIQUID CLUSTERING (RECOMMENDED)</SPAN></H3> <P><STRONG>For new tables, use Liquid Clustering.</STRONG><SPAN> It replaces both partitioning and Z-ordering with a single, unified mechanism.</SPAN></P> <LI-CODE lang="python">-- Everything you need: one line CREATE TABLE orders (...) CLUSTER BY (region, event_time, order_id);</LI-CODE> <P><SPAN>This is better than the traditional two-step approach:</SPAN></P> <LI-CODE lang="python">-- Legacy: two separate mechanisms CREATE TABLE orders (...) PARTITIONED BY (region); OPTIMIZE orders ZORDER BY (event_time, order_id);</LI-CODE> <P><STRONG>Why it's better for MERGE:</STRONG></P> <P><SPAN>With </SPAN><SPAN>CLUSTER BY (region, event_time, order_id)</SPAN><SPAN>, all three columns participate in an optimized layout. Files are organized across the entire table — not within partition boundaries. When you inject literals on </SPAN><I><SPAN>any</SPAN></I><SPAN> of these columns, Spark uses all filters together to skip files:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>target.region IN ('us-east-1', 'eu-central-1')</SPAN><SPAN> — skips files for other regions</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>target.event_time &gt;= '2024-03-25'</SPAN><SPAN> — skips files with older data</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Both filters combined — skips even more files than either alone</SPAN></LI> </UL> <P><STRONG>Literal injection on a clustering key is functionally equivalent to partition pruning</STRONG><SPAN> — Spark skips all files whose min/max stats on that key don't match the literal. The difference is that it's file-level, there are no partition boundaries, and </SPAN><I><SPAN>every</SPAN></I><SPAN> clustering key benefits equally from the same technique.</SPAN></P> <P><STRONG>Key advantages:</STRONG></P> <TABLE> <THEAD> <TR> <TH> <P><STRONG>Feature</STRONG></P> </TH> <TH> <P><STRONG>Liquid Clustering</STRONG></P> </TH> <TH> <P><STRONG>Partitioning + Z-Order</STRONG></P> </TH> </TR> </THEAD> <TBODY> <TR> <TD> <P><SPAN>Pruning mechanism</SPAN></P> </TD> <TD> <P><SPAN>File-level stats on all clustering keys</SPAN></P> </TD> <TD> <P><SPAN>Partition pruning on partition column + file-level stats on Z-ordered columns within partitions</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Maintenance</SPAN></P> </TD> <TD> <P><SPAN>OPTIMIZE</SPAN><SPAN> is incremental — only rewrites files with new/unclustered data</SPAN></P> </TD> <TD> <P><SPAN>OPTIMIZE ZORDER BY</SPAN><SPAN> is also incremental — targets files with new/changed data, but re-sorts and rewrites all qualifying files to maintain Z-order</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Column flexibility</SPAN></P> </TD> <TD> <P><SPAN>ALTER TABLE ... CLUSTER BY (new_cols)</SPAN><SPAN> — metadata-only change, no rewrite. New data uses new keys. Run </SPAN><SPAN>OPTIMIZE FULL</SPAN><SPAN> (DBR 16.0+) to recluster old data</SPAN></P> </TD> <TD> <P><SPAN>Changing partition columns requires full table rewrite. Changing Z-order columns takes effect on next </SPAN><SPAN>OPTIMIZE</SPAN><SPAN> for qualifying files</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Cardinality</SPAN></P> </TD> <TD> <P><SPAN>Handles automatically any cardinality. It also offers Hierarchical Clustering while lets you prioritize certain low-cardinality columns (like date or region) in Liquid Clustering so data is fully sorted by those columns first, then ZORDERed on the rest</SPAN></P> </TD> <TD> <P><SPAN>High-cardinality partitioning creates millions of tiny directories. Z-ordering handles high cardinality well but only within partitions</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Cross-key optimization</SPAN></P> </TD> <TD> <P><SPAN>It optimizes all keys simultaneously across the entire table</SPAN></P> </TD> <TD> <P><SPAN>Z-ordering limited to within partition boundaries — files can't be reorganized across partitions</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>Automatic tuning</SPAN></P> </TD> <TD> <P><SPAN>CLUSTER BY AUTO</SPAN><SPAN> (DBR 15.4+) — Predictive Optimization selects and evolves keys</SPAN></P> </TD> <TD> <P><SPAN>Manual column selection for both partition key and Z-order columns</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P><STRONG>Best practice:</STRONG><SPAN> Choose 1-4 columns that appear most frequently in your MERGE conditions and query filters. In our scenario: </SPAN><SPAN>CLUSTER BY (region, event_time)</SPAN><SPAN>.</SPAN></P> <H3><SPAN>REDUCING WRITE AMPLIFICATION: FILE SIZE TUNING AND DELETION VECTORS</SPAN></H3> <P><SPAN>Without Deletion Vectors, Delta Lake uses Copy-on-Write: to update a row, the entire file containing that row must be rewritten. This creates write amplification.</SPAN></P> <P><STRONG>Example scenario:</STRONG></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Your orders table has 256MB files, each containing ~500K rows</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>A MERGE updates 1,000 orders scattered across 100 files</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Result: 100 files x 256MB = 25.6GB read + 25.6GB written</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>For just 1,000 row changes, you've moved 51GB of data</SPAN></LI> </UL> <P><STRONG>With smaller files (64MB):</STRONG></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>Same 1,000 orders across 100 files</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>Result: 100 files x 64MB = 6.4GB read + 6.4GB written</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>4x reduction in I/O for the same MERGE</SPAN></LI> </UL> <P><STRONG>Trade-off:</STRONG></P> <UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Smaller files (32-128 MB):</STRONG><SPAN> Lower write amplification, but more files to manage and more time spent id cloud APIs</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Larger files (256 MB - 1 GB):</STRONG><SPAN> Better read performance, but higher write amplification</SPAN></LI> </UL> <P><SPAN>For MERGE-heavy tables: Consider smaller file size or enable Deletion Vectors (Delta Lake 2.3+) if the join keys have high cardinality.</SPAN></P> <P><STRONG>What are Deletion Vectors?</STRONG></P> <P><SPAN>Normally, updating or deleting even a single row requires rewriting the entire file that contains it (Copy-on-Write). Deletion Vectors take a different approach: instead of rewriting files, they store a compact bitmap alongside each data file that marks which row positions have been invalidated.</SPAN></P> <P><SPAN>During a MERGE with Deletion Vectors enabled:</SPAN></P> <UL> <LI style="font-weight: 400;" aria-level="1"><SPAN>For </SPAN><STRONG>deletes</STRONG><SPAN>, the row is simply marked as removed in the bitmap.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><SPAN>For </SPAN><STRONG>updates</STRONG><SPAN>, the old row is marked as removed and the new row is written to a separate small file.</SPAN></LI> </UL> <P><SPAN>The original data files remain untouched. This bypasses Copy-on-Write entirely — writes become much faster at the cost of slightly slower reads (readers must check the deletion vector to filter out invalidated rows). Periodic OPTIMIZE does two things: (1) merge small files (e.g., from new row inserts) into larger, optimally-sized files, and (2) rewrite files to physically remove rows marked as deleted via Deletion Vectors. For (2) specifically, the REORG command is also available as a dedicated alternative.</SPAN></P> <H2><SPAN>HOW TO DIAGNOSE YOUR MERGE</SPAN></H2> <P><SPAN>Don't guess—measure. Run </SPAN><SPAN>DESCRIBE HISTORY your_table</SPAN><SPAN> after any MERGE. The </SPAN><SPAN>operationMetrics</SPAN><SPAN> map tells you exactly what happened.</SPAN></P> <P><STRONG>Three metrics that matter most:</STRONG></P> <TABLE> <THEAD> <TR> <TH> <P><STRONG>Metric</STRONG></P> </TH> <TH> <P><STRONG>What it tells you</STRONG></P> </TH> <TH> <P><STRONG>Red flag</STRONG></P> </TH> </TR> </THEAD> <TBODY> <TR> <TD> <P><SPAN>scanTimeMs</SPAN></P> </TD> <TD> <P><SPAN>Time spent reading target files</SPAN></P> </TD> <TD> <P><SPAN>Dominates total time → improve pruning</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>rewriteTimeMs</SPAN></P> </TD> <TD> <P><SPAN>Time spent writing new files</SPAN></P> </TD> <TD> <P><SPAN>Dominates total time → enable DVs or shrink files</SPAN></P> </TD> </TR> <TR> <TD> <P><SPAN>numTargetRowsCopied</SPAN></P> </TD> <TD> <P><SPAN>Unchanged rows rewritten alongside updated ones</SPAN></P> </TD> <TD> <P><SPAN>Much larger than </SPAN><SPAN>numTargetRowsUpdated</SPAN><SPAN> → write amplification</SPAN></P> </TD> </TR> </TBODY> </TABLE> <P><STRONG>How to act on them:</STRONG></P> <P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="dhruvkumar1_9-1778088130230.png" style="width: 400px;"><img src="https://community.databricks.com/t5/image/serverpage/image-id/26719iB6E2F67E20404383/image-size/medium?v=v2&amp;px=400" role="button" title="dhruvkumar1_9-1778088130230.png" alt="dhruvkumar1_9-1778088130230.png" /></span></P> <H2><SPAN>SUMMARY</SPAN></H2> <OL> <LI style="font-weight: 400;" aria-level="1"><STRONG>MERGE scans everything by default</STRONG><SPAN> because Spark cannot evaluate </SPAN><SPAN>source.column</SPAN><SPAN> values during planning.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Pre-scan your source</STRONG><SPAN> to extract literal values — partition keys, value ranges on key columns (timestamps, IDs, dates — any column with min/max stats).</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Inject these as literals</STRONG><SPAN> into your MERGE condition. This works identically for partitioned tables and Liquid Clustered tables.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>Organize your data for effective pruning:</STRONG></LI> <UL> <LI style="font-weight: 400;" aria-level="2"><STRONG>New tables:</STRONG><SPAN> Use Liquid Clustering — </SPAN><SPAN>CLUSTER BY (region, event_time, order_id)</SPAN><SPAN> unifies partition pruning + column stats pruning + Z-ordering into one mechanism. Literal injection on any clustering key drives file skipping.</SPAN></LI> <LI style="font-weight: 400;" aria-level="2"><STRONG>Existing partitioned tables:</STRONG><SPAN> Add Z-ordering on MERGE key columns to tighten file-level statistics within partitions.</SPAN></LI> </UL> <LI style="font-weight: 400;" aria-level="1"><STRONG>Reduce write amplification</STRONG><SPAN> with Deletion Vectors (avoid full file rewrites) and appropriate file sizing.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG>For streaming, use </STRONG><STRONG>foreachBatch</STRONG><SPAN> — it's mandatory for literal injection. For batch, cache your source to prevent data inconsistency.</SPAN></LI> <LI style="font-weight: 400;" aria-level="1"><STRONG style="color: #1b3139; font-family: inherit;">Measure and monitor</STRONG><SPAN> — </SPAN><SPAN>DESCRIBE HISTORY</SPAN><SPAN> for spot checks, trended dashboards for regression</SPAN></LI> </OL> Thu, 07 May 2026 13:35:56 GMT dhruvkumar1 2026-05-07T13:35:56Z https://community.databricks.com/t5/technical-blog/mastering-delta-lake-merge-performance-why-it-slows-down-and-how/ba-p/156305 <P data-local-id="3c490d4a254a" data-renderer-start-pos="1">Delta Lake MERGE scans all files by default — even with partitioning or Liquid Clustering. Learn the literal injection technique to dramatically reduce scan times.</P> <UL class="ak-ul" data-indent-level="1" data-local-id="de017c10062f"> <LI> <P data-local-id="39aefec4b11c" data-renderer-start-pos="168"><STRONG data-renderer-mark="true">MERGE scans everything by default</STRONG> because column references in the MERGE condition are opaque at plan time — Spark can't push them down as filters. Pre-scanning your source and injecting literal values enables file pruning on both partitioned and Liquid Clustered tables.</P> </LI> <LI> <P data-local-id="e4e333a1fd46" data-renderer-start-pos="443"><STRONG data-renderer-mark="true">Liquid Clustering unifies partition pruning and Z-ordering</STRONG> into a single mechanism where literal injection on any clustering key drives file skipping — no partition boundaries, incremental maintenance, and automatic key selection on newer Databricks runtimes.</P> </LI> <LI> <P data-local-id="75a8d59962cc" data-renderer-start-pos="706"><STRONG data-renderer-mark="true">Layer read and write optimizations together:</STRONG> literal injection combined with physical layout optimization reduces files scanned, while Deletion Vectors eliminate full file rewrites — use DESCRIBE HISTORY metrics to diagnose and monitor.</P> </LI> </UL> Thu, 07 May 2026 13:35:56 GMT https://community.databricks.com/t5/technical-blog/mastering-delta-lake-merge-performance-why-it-slows-down-and-how/ba-p/156305 dhruvkumar1 2026-05-07T13:35:56Z https://community.databricks.com/t5/technical-blog/mastering-delta-lake-merge-performance-why-it-slows-down-and-how/bc-p/156563#M1056 <P><a href="https://community.databricks.com/t5/user/viewprofilepage/user-id/227759">@dhruvkumar1</a>&nbsp;Hey, thanks for the article.&nbsp; I was under the impression that if you have photon enabled then it uses dynamic file pruning to trim the target down for the merge.&nbsp; Is this incorrect?</P><P><A href="https://learn.microsoft.com/en-gb/azure/databricks/optimizations/dynamic-file-pruning" target="_blank">https://learn.microsoft.com/en-gb/azure/databricks/optimizations/dynamic-file-pruning</A></P> Mon, 11 May 2026 10:49:54 GMT https://community.databricks.com/t5/technical-blog/mastering-delta-lake-merge-performance-why-it-slows-down-and-how/bc-p/156563#M1056 mdungey 2026-05-11T10:49:54Z