Missing Delta Optimization Features for High-Volume Tables

Without these, queries scanning large datasets may read far more data than necessary, increasing execution time and compute usage.

• Liquid clustering organizes data by clustering keys on commonly filtered columns to reduce scan scope, and keys can be changed without rewriting existing data.
• Partitioning and Z-Ordering are older layout techniques; Databricks advises against partitioning tables under 1 TB, and neither can be combined with liquid clustering.
• Delta Format enables additional optimizations like data skipping and compaction. Failing to use these features in high-volume tables often results in avoidable performance overhead and elevated spend, especially in environments with frequent exploratory queries or BI workloads.

Databricks charges are based on DBUs (Databricks Units) per hour, which correlate directly with compute resource use. Query performance heavily impacts DBU consumption. Inefficient data layout leads to longer scan times, increased cluster runtime, and higher costs.

• Large tables lacking liquid clustering on commonly filtered columns
• Legacy tables without liquid clustering that lack Z-Ordering on high-selectivity columns (e.g., timestamps, IDs), or that are partitioned despite holding under 1 TB
• Slow query performance tied to full-table scans
• High DBU usage by queries reading large volumes of data unnecessarily
• ETL pipelines writing to Delta tables without compaction or OPTIMIZE steps

• Apply liquid clustering (CLUSTER BY) to Delta tables, using columns commonly filtered in queries, and avoid partitioning tables under 1 TB
• On legacy tables that don't use liquid clustering, apply Z-Ordering on appropriate columns to improve data skipping efficiency
• Use OPTIMIZE (or predictive optimization) to compact small files and improve query performance, and use VACUUM to remove data files no longer referenced by the table, which reduces storage cost but does not speed up queries
• Standardize use of Delta Lake format in ETL pipelines
• Enable predictive optimization so OPTIMIZE runs automatically on Unity Catalog managed tables, and automate periodic optimizations for other long-lived tables

• Optimize data file layoutlearn.microsoft.com
• Use liquid clustering for tableslearn.microsoft.com
• When to partition tables on Azure Databrickslearn.microsoft.com
• OPTIMIZElearn.microsoft.com
• Remove unused data files with vacuumlearn.microsoft.com