Databricks Optimization Cheat Sheet

Updated 2026-04-27

Databricks is a unified lakehouse platform built on Apache Spark that combines data warehousing and data lake capabilities, enabling organizations to process and analyze massive datasets at scale. Optimizing Databricks performance directly impacts query speed, cluster efficiency, and cloud costs — making it essential for production workloads. The key to effective optimization lies in understanding that Databricks provides multiple optimization layers: from Delta Lake file management (OPTIMIZE, Z-ordering, liquid clustering) to Spark query execution (AQE, low shuffle merge, predicate pushdown) to cluster resource tuning (autoscaling, Photon), and each layer compounds the performance gains when applied correctly.

What This Cheat Sheet Covers

This topic spans 9 focused tables and 66 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Delta Lake File OptimizationTable 2: Partitioning and Clustering StrategiesTable 3: Query Execution OptimizationTable 4: Caching and Storage OptimizationTable 5: Cluster Configuration and ComputeTable 6: Code and UDF OptimizationTable 7: Streaming OptimizationTable 8: Monitoring and ProfilingTable 9: Advanced Techniques

Table 1: Delta Lake File Optimization

Most Databricks performance problems trace back to how data files are physically laid out, and this is the layer where you fix them. Compacting small files, clustering related rows together, and pruning dead files all reduce the bytes a query has to touch — and increasingly Databricks does this maintenance for you through predictive optimization and auto-compaction, so several of these commands fade into the background on managed tables.

Technique	Example	Description
OPTIMIZE command	`OPTIMIZE my_table` `OPTIMIZE my_table ZORDER BY (col1, col2)`	• Compacts small files into larger ones (targeting 1GB per file) to reduce metadata overhead and improve read performance • combines with Z-order for co-location.
Liquid clustering	`CREATE TABLE events` `CLUSTER BY (date, region)`	• Replaces partitioning and Z-order with adaptive clustering that automatically maintains data layout • supports incremental clustering during writes without full table rewrites.
Z-ordering	`OPTIMIZE events ZORDER BY (date, user_id)`	Co-locates related data within files using a space-filling curve to maximize data skipping during queries with filters on Z-ordered columns.
Predictive optimization	`ALTER TABLE my_table` `SET TBLPROPERTIES` `('delta.autoOptimize.autoCompact'='AUTO')`	• Automatically runs OPTIMIZE, VACUUM, and statistics collection based on usage patterns • enabled by default for Unity Catalog managed tables and being rolled out globally (completing April 2026).
Auto Optimize	`ALTER TABLE my_table` `SET TBLPROPERTIES` `('delta.autoOptimize.optimizeWrite'='true')`	• Automatically compacts small files during writes • combines optimizeWrite (reduces file count during write) and autoCompact (post-write compaction).
OPTIMIZE FULL	`OPTIMIZE my_table FULL`	• Forces full reclustering of all records in a liquid-clustered table (DBR 16.0+) • more expensive than incremental OPTIMIZE — use after changing cluster keys or when data layout has degraded.
VACUUM	`VACUUM my_table RETAIN 168 HOURS`	• Removes old data files no longer referenced by the Delta transaction log to reclaim storage • default retention is 7 days, critical for time travel support.

Table 1: Delta Lake File Optimization

Technique	Example	Description
OPTIMIZE command	`OPTIMIZE my_table` `OPTIMIZE my_table ZORDER BY (col1, col2)`	• Compacts small files into larger ones (targeting 1GB per file) to reduce metadata overhead and improve read performance • combines with Z-order for co-location.
Liquid clustering	`CREATE TABLE events` `CLUSTER BY (date, region)`	• Replaces partitioning and Z-order with adaptive clustering that automatically maintains data layout • supports incremental clustering during writes without full table rewrites.
Z-ordering	`OPTIMIZE events ZORDER BY (date, user_id)`	Co-locates related data within files using a space-filling curve to maximize data skipping during queries with filters on Z-ordered columns.
Predictive optimization	`ALTER TABLE my_table` `SET TBLPROPERTIES` `('delta.autoOptimize.autoCompact'='AUTO')`	• Automatically runs OPTIMIZE, VACUUM, and statistics collection based on usage patterns • enabled by default for Unity Catalog managed tables and being rolled out globally (completing April 2026).
Auto Optimize	`ALTER TABLE my_table` `SET TBLPROPERTIES` `('delta.autoOptimize.optimizeWrite'='true')`	• Automatically compacts small files during writes • combines optimizeWrite (reduces file count during write) and autoCompact (post-write compaction).
OPTIMIZE FULL	`OPTIMIZE my_table FULL`	• Forces full reclustering of all records in a liquid-clustered table (DBR 16.0+) • more expensive than incremental OPTIMIZE — use after changing cluster keys or when data layout has degraded.
VACUUM	`VACUUM my_table RETAIN 168 HOURS`	• Removes old data files no longer referenced by the Delta transaction log to reclaim storage • default retention is 7 days, critical for time travel support.