CLUSTER BY AUTO in Databricks: stop picking clustering keys by hand

For years, optimizing the physical layout of a table in the lakehouse was a handcrafted task: you looked at the most frequent queries, decided which columns the data was most filtered by, applied PARTITIONED BY or ZORDER, and came back months later to readjust when the access pattern changed. It worked — but it was a decision that aged and turned into silent technical debt.

Automatic Liquid Clustering, triggered by the CLUSTER BY AUTO keyword, changes the game: Databricks itself now chooses and maintains the clustering keys of Unity Catalog managed tables, with no manual intervention.

A step back: partitioning, ZORDER and Liquid Clustering

To understand AUTO, it's worth recalling the evolution:

• Partitioning (PARTITIONED BY) splits data into folders by column value. Great in theory, but easy to get wrong: high cardinality causes the "small files" problem; low cardinality doesn't help pruning. And the choice is rigid — changing it requires rewriting the table.
• ZORDER reorganizes data within files across multiple columns, improving data skipping. But it runs inside OPTIMIZE and has to be maintained manually.
• Liquid Clustering replaces both. It's flexible (you swap the keys without rewriting the table), handles skew and high-cardinality columns well, and avoids the small-files problem.

CLUSTER BY AUTO is the next step: instead of you naming the Liquid Clustering columns, the engine decides for you.

What CLUSTER BY AUTO does

By marking a Unity Catalog managed table with CLUSTER BY AUTO, you delegate key selection to Predictive Optimization. It:

1. Analyzes the real query history over the table (which columns show up in filters and joins).
2. Selects the most effective clustering keys for that workload.
3. Maintains the table on its own, running OPTIMIZE, VACUUM and ANALYZE automatically.
4. Re-adapts the keys over time — if the access pattern changes, the clustering follows.

In other words: optimization stops being a one-off event and becomes a continuous, managed process.

How to use it — the 3 steps

1. New table

CREATE TABLE sales (
  id BIGINT,
  ts TIMESTAMP
)
CLUSTER BY AUTO;

2. An existing table (unpartitioned or already Liquid)

ALTER TABLE sales CLUSTER BY AUTO;

3. Convert a partitioned table

To migrate a partitioned table to automatic clustering from its current partition columns:

ALTER TABLE sales REPLACE PARTITIONED BY WITH CLUSTER BY AUTO;

After that, you no longer need to call OPTIMIZE by hand — Predictive Optimization takes care of maintenance.

Requirements

• Databricks Runtime 15.4 LTS or higher.
• A Unity Catalog managed table — managed Delta Lake or Iceberg.
• Predictive Optimization enabled (on by default for new accounts; the rollout to existing accounts completed over the course of 2026).

To confirm AUTO is active:

DESCRIBE DETAIL sales;      -- look at clusteringColumns
SHOW TBLPROPERTIES sales;   -- clusterByAuto = true

The clusteringColumns can change over time — that's expected, and it's precisely the re-adaptation mechanism at work.

When to use it — and when NOT to

Good cases for AUTO:

• Medium and large managed tables with evolving query patterns.
• Teams that don't want to (or don't have time to) maintain ZORDER/partition strategies by hand.
• Tables where yesterday's "obvious" key no longer matches today's filters.

Cases where it might not be worth it:

• Very small tables, where data skipping has little impact — the maintenance cost doesn't pay off.
• Tables with already well-defined, stable partitioning, where the marginal gain is low.
• Scenarios with regulatory requirements for a specific physical layout (e.g. purging by date partition), where you need explicit control.

In those cases you can still use Liquid Clustering with fixed keys (CLUSTER BY (column)) instead of AUTO.

How to monitor

• Track the evolution of clusteringColumns via DESCRIBE DETAIL.
• Check the maintenance operation history (the OPTIMIZE/VACUUM runs triggered by Predictive Optimization) to understand frequency and cost.
• Compare query times and the volume of data read (data skipping) before and after enabling AUTO — that's the metric that really matters.

Conclusion

CLUSTER BY AUTO takes off the data engineer's plate a decision that was always an educated guess and turns it into a governed, adaptive process. The practical result is twofold: less manual maintenance and less technical debt in physical layout — because the table is no longer "stuck" with the best decision you managed to make six months ago. For most Unity Catalog managed tables, enabling AUTO is a one-line SQL change with a high return: faster reads and lower TCO, with no recurring effort.