OPTIMIZE, Z-ORDER & liquid clustering
Databricks · Delta Lake
Theory
Many small writes leave a table with thousands of small files, which slows reads. OPTIMIZE compacts small files into larger ones. Data skipping then uses per-file min/max statistics to skip files that cannot match a filter, and it works best when similar values sit in the same files. The older way to get that layout was PARTITIONED BY plus OPTIMIZE ... ZORDER BY (col), which rewrites data every time.
Liquid clustering replaces both. You declare CLUSTER BY (col1, col2), up to four keys, on CREATE or ALTER TABLE; OPTIMIZE then clusters data incrementally, only rewriting what is needed, and you can change keys later without rewriting the table. CLUSTER BY AUTO lets Databricks choose keys from your query patterns (it needs predictive optimization). Databricks recommends liquid clustering for all new tables. For example, orders clustered by (order_date, region) lets a query for region = 'West' in January skip most files.
Why we use it
Reading fewer, larger, well-organized files is the biggest lever for Delta query speed. These commands turn a messy table into one where filters skip most of the data.
When to use it
Use liquid clustering (or CLUSTER BY AUTO) for new tables, choosing columns you filter or join on. Keep ZORDER and partitioning only on existing tables you have not migrated. Liquid clustering cannot be combined with partitioning or ZORDER on the same table.
How it works
- Queries on a table are slow
- DESCRIBE DETAIL: many small files?
- Is it a new table or can it be migrated? Yes: CLUSTER BY (keys) or CLUSTER BY AUTO. No: Keep partitions; OPTIMIZE ... ZORDER BY.
- Run OPTIMIZE (or let predictive optimization run it)
- Filters skip most files using statistics
Syntax
CREATE TABLE t (...) CLUSTER BY (col1, col2)
ALTER TABLE t CLUSTER BY (col1) | CLUSTER BY AUTO | CLUSTER BY NONE
OPTIMIZE t -- incremental clustering / compaction
OPTIMIZE t FULL -- recluster all data
OPTIMIZE t ZORDER BY (col) -- legacy, non-clustered tablesExample
-- New table with liquid clustering
CREATE TABLE main.sales.orders_lc (
order_id INT, customer_id INT, region STRING,
amount DECIMAL(10,2), order_date DATE)
CLUSTER BY (order_date, region);
INSERT INTO main.sales.orders_lc SELECT * FROM main.sales.orders;
OPTIMIZE main.sales.orders_lc;
-- Let Databricks pick the keys
ALTER TABLE main.sales.orders_lc CLUSTER BY AUTO;
-- Legacy layout on an old unclustered table
OPTIMIZE main.sales.orders_legacy ZORDER BY (customer_id);
DESCRIBE DETAIL main.sales.orders_lc; -- clusteringColumns, numFilesKey points
- OPTIMIZE compacts small files; on clustered tables it also clusters incrementally
- Liquid clustering: up to four keys, changeable with ALTER TABLE, no full rewrite
- CLUSTER BY AUTO picks keys for you and requires predictive optimization
- Liquid clustering is not compatible with partitioning or ZORDER on the same table
- Pick clustering keys from columns used in filters and joins