Liquid clustering

What it is

Liquid Clustering is how Delta Lake (see Delta Lake, the lakehouse table format) physically organizes a table’s files around one or more columns, so that queries filtering on those columns read fewer files. It replaces two older techniques: folder-based partitioning and the Z-ORDER performed by OPTIMIZE. Keys are declared with CLUSTER BY and can be changed at any time.

Predictive optimization is the service that, on Unity Catalog managed tables, decides on its own when to run OPTIMIZE, VACUUM, and statistics collection, and does so on serverless compute with no job to schedule.

Why it exists

Partitioning has to be chosen at creation time, only works well with low-cardinality columns, and, if chosen poorly, produces thousands of tiny folders or a handful of huge ones. Z-ORDER improves the layout inside partitions but rewrites all the data on every run and has to be triggered by hand. Liquid Clustering removes both constraints; predictive optimization removes the scheduled maintenance that every team eventually forgot to run.

How it works

Declaring the keys

CREATE TABLE sales (data DATE, negozio_id INT, amount DECIMAL(10,2))
CLUSTER BY (data, negozio_id);

ALTER TABLE sales CLUSTER BY (negozio_id);   -- changes the keys, rewrites nothing
ALTER TABLE sales CLUSTER BY NONE;           -- disables clustering

Up to four clustering columns. New writes respect the current keys; existing files stay as they are until OPTIMIZE reorganizes them. You can see the keys with DESCRIBE DETAIL (the clusteringColumns field).

With CLUSTER BY AUTO, Databricks picks and updates the keys itself based on the predicates it sees in your queries. It requires a Unity Catalog managed table with predictive optimization enabled.

From PySpark: df.write.clusterBy("data", "negozio_id").saveAsTable("sales"). Streaming tables and materialized views in pipelines support CLUSTER BY the same way.

Incremental OPTIMIZE

OPTIMIZE sales;          -- reorganizes only the files not yet clustered
OPTIMIZE sales FULL;     -- rewrites everything: after a key change or the first activation

With clustering, OPTIMIZE is incremental: it only touches files that arrived since the last run, so it’s cheap and can run often. FULL is needed only when you change the keys on a table that already has data.

Comparison

Databricks recommends Liquid Clustering for all new tables. It particularly pays off with filters on high-cardinality columns, skewed data, fast-growing tables, changing access patterns, and concurrent writes.

Automatic maintenance

Clustering is only useful if something keeps applying it. On Unity Catalog managed tables that something is Predictive optimization, which runs OPTIMIZE, VACUUM and ANALYZE when it judges the benefit worth the cost, including the incremental clustering described above. It never applies ZORDER.

The practical consequence for this page: on a managed table you declare the keys and stop. On an external table you are still responsible for scheduling OPTIMIZE yourself.

Example

Migrating a table partitioned by day that suffers from small files and slow queries filtered on cliente_id:

-- 1. new table with clustering, loaded from the old one
CREATE TABLE vendite_prod.silver.ordini_v2
CLUSTER BY (cliente_id, data_ordine)
AS SELECT * FROM vendite_prod.silver.orders;

-- 2. first full layout pass
OPTIMIZE vendite_prod.silver.ordini_v2 FULL;

-- 3. from here on predictive optimization takes over (schema enabled);
--    alternatively, a periodic SQL task:
OPTIMIZE vendite_prod.silver.ordini_v2;

(spark.table("vendite_prod.silver.orders")
   .write.clusterBy("cliente_id", "data_ordine")
   .saveAsTable("vendite_prod.silver.ordini_v2"))
spark.sql("OPTIMIZE vendite_prod.silver.ordini_v2 FULL")

Common mistakes

• Declaring CLUSTER BY and PARTITIONED BY on the same table: an error, they’re mutually exclusive.
• Changing the keys with ALTER TABLE and expecting faster queries right away: without OPTIMIZE FULL, old data stays in its previous layout.
• Picking too many keys: you can’t go beyond four, and even three or four on small tables can make filters on a single column worse.
• Turning on predictive optimization and expecting it to touch external tables: it doesn’t; those need scheduled OPTIMIZE and VACUUM.
• Still running OPTIMIZE ZORDER BY on clustered tables: it’s not allowed, and it would do a worse job anyway.