Production table maintenance

[A_Motia]

Hi Community members,

I'm interested in how people monitor the effectiveness of Liquid Clustering as a Delta table grows over time.

Consider below scenario:

A table initially performs well, but MERGE operations gradually become slower even though the clustering keys haven't changed.

•  Which metrics do you monitor to determine whether the issue is stale file layout, poor data skipping, or source-side processing?
• How do you decide when to run OPTIMIZE versus reconsider the clustering keys?
• Do you use Delta operation metrics from DESCRIBE HISTORY to track when performance starts degrading?

I'd appreciate examples of how you've diagnosed this in production system in an automated fashion

[SumeshKashyap · Accepted solution]

When a MERGE slows down but the clustering keys haven't changed, it's usually one of two things. Either the MERGE condition doesn't let Delta prune target files, or new data has piled up unclustered since the last OPTIMIZE. You can tell which from the MERGE metrics in DESCRIBE HISTORY: compare numTargetFilesAfterSkipping with numTargetFilesBeforeSkipping on each run. If the ratio is high, fix by pruning first. If it grows steadily between OPTIMIZE runs and drops after one, its layout drift.

What you should track per MERGE (from operationMetrics)

- Pruning: numTargetFilesAfterSkipping / numTargetFilesBeforeSkipping. This should stay small and flat.

- Write amplification: numTargetRowsCopied vs numTargetRowsUpdated + numTargetRowsDeleted. Lots of copied rows means each touched file holds mostly untouched rows, so files are too wide or the matches are scattered.

- Where the time goes: scanTimeMs vs rewriteTimeMs vs executionTimeMs, plotted against numSourceRows. If execution time rises in step with source rows while pruning stays flat, the slowdown is on the source side, not the table layout.

- Table shape from DESCRIBE DETAIL: numFiles, sizeInBytes (to get average file size) and clusteringColumns.

Automating it

Use a small scheduled job that appends new MERGE history rows to a metrics table and alerts on the ratios:

from delta.tables import DeltaTable

from pyspark.sql import functions as F

m = F.col("operationMetrics")

h = (DeltaTable.forName(spark, "cat.sch.tbl").history()

     .where("operation = 'MERGE'")

     .select("version", "timestamp",

             *[m[k].cast("long").alias(k) for k in [

               "executionTimeMs", "scanTimeMs", "rewriteTimeMs", "numSourceRows",

               "numTargetFilesBeforeSkipping", "numTargetFilesAfterSkipping",

               "numTargetRowsUpdated", "numTargetRowsCopied"]]))

# filter version > max(version) already stored, then append to ops.merge_metrics

Then use a Databricks SQL alert (or a check in the job itself) on the pruning ratio and the ms-per-source-row trend.

OPTIMIZE vs changing the keys

1. Pruning is poor even right after OPTIMIZE: check the MERGE ON clause first. It needs a predicate on the clustering columns, e.g. t.event_date >= <min date in source> or the key itself. Without one, no layout will help. If the clause is fine, the keys don't match how the MERGE looks up rows, so reconsider them. ALTER TABLE ... CLUSTER BY (...) only applies to newly clustered data, so follow it with OPTIMIZE ... FULL.

2. Pruning is good after OPTIMIZE but degrades between runs: run OPTIMIZE more often (it's incremental for liquid clustering), or enable predictive optimization so Databricks schedules it. You can check what it ran in system.storage.predictive_optimization_operations_history.

3. If you're not sure the keys are right, CLUSTER BY AUTO (with predictive optimization) lets Databricks pick keys from your actual query patterns.

Docs: https://docs.databricks.com/aws/en/delta/clustering

[Aravind_Reddy]

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.