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

  1. Queries on a table are slow
  2. DESCRIBE DETAIL: many small files?
  3. Is it a new table or can it be migrated? Yes: CLUSTER BY (keys) or CLUSTER BY AUTO. No: Keep partitions; OPTIMIZE ... ZORDER BY.
  4. Run OPTIMIZE (or let predictive optimization run it)
  5. 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 tables

Example

-- 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, numFiles

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic