Aravind_Reddy
Databricks Partner

Hello @A_Motia 

In my experience, when MERGE operations gradually become slower on a growing Delta table, I would first separate the time spent scanning the target table from the time spent processing the source data and rewriting files. I wouldn't immediately change the Liquid Clustering keys without identifying the bottleneck.

A few practices I would recommend:

Monitor MERGE operation metrics: Use DESCRIBE HISTORY to track execution time and available operation metrics, such as files added or removed, rows inserted or updated, and scan or rewrite time where reported. Compare these metrics across successive MERGE operations to identify when performance starts degrading.

Investigate data skipping and file layout: Review query profiles and scan metrics to understand how many files and bytes are being scanned compared with the amount of data actually processed. If the scan volume increases significantly as the table grows, investigate whether the clustering layout is still effective for the workload.

Use OPTIMIZE based on evidence: Check whether file layout and clustering maintenance are contributing to the slowdown before running OPTIMIZE. For Liquid Clustered tables, OPTIMIZE can incrementally improve the layout. Monitor the impact on subsequent MERGE operations rather than judging success only by the number of files rewritten.

Reevaluate clustering keys using workload patterns: If maintenance does not improve performance, review the columns used in MERGE conditions and frequent filters. Clustering keys should reflect the actual query workload and data distribution, not just the table schema. Also consider whether the MERGE condition is selective enough to avoid scanning unnecessary target data.

Automate performance tracking: Capture operation metrics from Delta history into a monitoring table and compare execution time, scan volume, file changes, and row counts over time. Establish baselines and alert on sustained deviations rather than a single slow run.

One important point is to inspect the Spark query profile and execution plan as well. If target-side scanning is not the bottleneck, investigate source preparation, joins, shuffles, data skew, and compute capacity before attributing the slowdown to clustering.

I would start by correlating MERGE execution time with scan and rewrite metrics over several runs, then test whether targeted maintenance improves the results. This helps distinguish a file-layout issue from a workload or compute issue before making changes to production clustering keys.

Another approach worth considering is incremental change tracking. We can identify source records as INSERT, UPDATE, or DELETE and process only the relevant changes through the MERGE workflow. Where downstream processing is required, Delta Change Data Feed can help capture and consume the resulting changes incrementally. Soft deletes can also be useful when auditability or recovery is important, although they should be evaluated carefully because retaining deleted rows can increase storage and scanning overhead. The key is to reduce unnecessary processing while preserving the required data-retention and consistency guarantees.