For years, Delta Lake table layout meant two choices made on day one: which column to partition by, and which columns to Z-ORDER. Get either wrong and you were stuck with thousands of tiny partitions, or a nightly OPTIMIZE ZORDER BY job that rewrote the whole table and still left queries scanning too many files. Liquid clustering replaces both with one flexible layout that you can change later without rewriting history.
This guide is for data engineers running Delta tables on Databricks. It covers what liquid clustering does, how it compares with partitioning and Z-ORDER, the SQL you need to create, migrate and maintain clustered tables, and the pitfalls that catch teams out in production.
Why partitioning and Z-ORDER struggle
Hive-style partitioning works well when you have one low-cardinality column that almost every query filters on, such as event_date. It breaks down when:
- The partition column has high cardinality (customer ID, device ID), creating many small files and slow metadata listing.
- Data is skewed, so a few partitions hold most of the rows.
- Query patterns change and a different column becomes the main filter. Repartitioning means rewriting the whole table.
Z-ORDER helps by co-locating related values inside files so data skipping can prune more. But OPTIMIZE ... ZORDER BY is not incremental in the way you would like. Each run rewrites data within the selected partitions, which is expensive on large tables, and the Z-ORDER columns are not stored as a table property. Every job has to remember them.
What liquid clustering is
Liquid clustering stores clustering keys as part of the table definition. Databricks then organises data files so that rows with similar key values sit together, which gives strong file-level data skipping when you filter on those keys. The main differences from the older approach:
- Incremental:
OPTIMIZEonly rewrites files that need clustering, not the whole table. - Changeable keys:
ALTER TABLE ... CLUSTER BYchanges the keys without an immediate rewrite of existing data. - Up to four keys, which can include high-cardinality columns that would be terrible partition columns.
- Clustering on write for operations such as
INSERT INTO, CTAS andCOPY INTOfrom Parquet, once the write is above a size threshold.
Liquid clustering for Delta tables is generally available on Databricks Runtime 15.4 LTS and above. Note that it cannot be combined with partitioning or Z-ORDER on the same table. It replaces them.
Partitioning vs Z-ORDER vs liquid clustering
| Aspect | Partitioning | Z-ORDER | Liquid clustering |
|---|---|---|---|
| Defined where | Table definition | Each OPTIMIZE command | Table definition |
| High-cardinality columns | Poor (small files) | Good | Good |
| Change layout later | Full rewrite | Rewrite on next run | ALTER TABLE, recluster when ready |
| Maintenance cost | Low, but small-file risk | High, rewrites a lot of data | Incremental OPTIMIZE |
| Automatic key choice | No | No | Yes, with CLUSTER BY AUTO |
Hands-on: creating and maintaining clustered tables
Create a new clustered table
Add CLUSTER BY after the table name. For CTAS, it goes before AS, not inside the SELECT.
-- Empty table with two clustering keys
CREATE TABLE sales.events (
event_id BIGINT,
customer_id BIGINT,
event_type STRING,
event_ts TIMESTAMP,
amount DECIMAL(12,2)
)
CLUSTER BY (customer_id, event_ts);
-- Create from an existing table (CTAS)
CREATE TABLE sales.events_2026
CLUSTER BY (customer_id, event_ts)
AS SELECT * FROM sales.events_raw
WHERE event_ts >= '2026-01-01';
-- Check which keys a table uses
DESCRIBE DETAIL sales.events; -- see the clusteringColumns fieldIn PySpark, the DataFrame and DeltaTable APIs support clustering on Runtime 14.3 LTS and above. Keys can be set when creating the table or in overwrite mode, not in append mode.
(df.write
.format("delta")
.clusterBy("customer_id", "event_ts")
.mode("overwrite")
.saveAsTable("sales.events"))Enable clustering on an existing table
For an unpartitioned Delta table, you can switch it on directly. Then run OPTIMIZE FULL (Runtime 16.0+) once to recluster the existing data. On a large table this can take hours, so schedule it off-peak.
-- Unpartitioned table: add clustering keys
ALTER TABLE sales.orders CLUSTER BY (customer_id, order_date);
-- Recluster existing data with the new keys
OPTIMIZE sales.orders FULL;
-- Regular incremental maintenance afterwards
OPTIMIZE sales.orders;
-- Change your mind later: new keys, no immediate rewrite
ALTER TABLE sales.orders CLUSTER BY (order_date, region);
-- Stop clustering future OPTIMIZE runs (existing files untouched)
ALTER TABLE sales.orders CLUSTER BY NONE;For partitioned tables, Databricks Runtime 18.1 and above adds an in-place conversion. On older runtimes, the usual route is a CTAS or deep clone into a new clustered table.
-- Runtime 18.1+: convert a partitioned table to liquid clustering
ALTER TABLE sales.clicks
REPLACE PARTITIONED BY WITH CLUSTER BY (click_date, user_id);
-- Runtime 18.1+: recluster only part of the table
OPTIMIZE sales.clicks FULL WHERE click_date >= '2026-01-01';Let Databricks choose the keys: CLUSTER BY AUTO
For Unity Catalog managed Delta tables on Runtime 15.4 LTS and above, CLUSTER BY AUTO hands key selection to predictive optimization. It looks at your real query history and changes keys only when the predicted data-skipping savings outweigh the cost of reclustering. You can give it a starting point first:
ALTER TABLE sales.events CLUSTER BY (customer_id); -- initial hint
ALTER TABLE sales.events CLUSTER BY AUTO; -- let it adaptWith predictive optimization on, Databricks also runs OPTIMIZE for you and recommends turning off your own scheduled OPTIMIZE jobs. Without it, schedule OPTIMIZE yourself. Databricks suggests every one or two hours for tables that are updated often.
Choosing good clustering keys
- Start from your filters. Pick the columns that appear most often in
WHEREclauses and join conditions on large scans. - Migrating? Databricks’ own guidance is simple. Old partition columns and old Z-ORDER columns become the clustering keys.
- Use the original column, such as
event_ts, rather than a derived low-cardinality column likeevent_datethat only existed to make partitioning workable. - Fewer keys on smaller tables. The limit is four. On tables under about 10 TB, extra keys can slow single-column filters, so two or three is often the sweet spot.
- Keys need statistics. Delta collects stats on the first 32 columns by default, so a key outside that range needs the stats settings adjusted.
Clustering on write and streaming
Clustering on write only happens when the write is big enough. For Unity Catalog managed tables, the thresholds start at 64 MB for one key and rise to 1 GB for four keys. Other Delta tables have higher thresholds (256 MB to 4 GB). Small, frequent batch writes therefore land unclustered and rely on OPTIMIZE to tidy up later.
For Structured Streaming, creating clustered tables from a stream needs Runtime 16.4 LTS or above. Eager clustering of streaming writes is controlled by spark.databricks.delta.liquid.eagerClustering.streaming.enabled. Even then, it only triggers when at least one of the last five micro-batches is above the size threshold.
Common pitfalls
- Forgetting OPTIMIZE FULL after changing keys.
ALTER TABLE ... CLUSTER BYonly affects future clustering. Old files keep the old layout until you recluster. - Leaving legacy ZORDER jobs running. Z-ORDER and liquid clustering can’t be mixed. Remove
ZORDER BYfrom maintenance notebooks when you migrate. - Clustering on complex types.
STRUCT,MAPandARRAYcolumns can’t be keys. You can use a nested struct field with dot notation instead. - Old readers. Clustered tables use Delta writer version 7 and reader version 3, and the protocol can’t be downgraded. Check external readers (older Spark, Trino or other engines) before enabling clustering on shared tables.
- Expecting small writes to be clustered. Below the size thresholds, nothing happens on write. Watch file counts and keep maintenance scheduled.
- Too many keys “just in case”. Each extra key dilutes clustering for the others. Measure with real queries before adding a fourth key.
How to measure the benefit
Run your heaviest queries before and after the migration and compare files read and bytes read in the query profile, not only run time, because warm caches can hide the difference. A well-chosen key on a selective filter should cut the files scanned dramatically. If it doesn’t, check that the filter column really is a clustering key, that it has statistics, and that OPTIMIZE has run since the data landed.
Key takeaways
- Liquid clustering replaces partitioning and Z-ORDER for most new Delta tables on Databricks. It is GA on Runtime 15.4 LTS and above.
- Keys live in the table definition, can be changed with
ALTER TABLE, and are maintained incrementally byOPTIMIZE. - Use
OPTIMIZE FULLonce after enabling or changing keys, then regularOPTIMIZEor predictive optimization. CLUSTER BY AUTOlets Databricks pick keys from real query patterns on Unity Catalog managed tables.- Keep to two or three keys on mid-sized tables, check reader compatibility, and measure files scanned to prove the gain.
