Liquid
Clustering.

The problem it solves

When a table is small, the engine just reads all of it. When a table grows to billions of rows, a query like WHERE country = 'IN' shouldn't have to scan everything — it should read only the files that could contain matching rows and skip the rest. That skipping is the whole game, and it's called data skipping.

Data skipping only works if related rows are physically stored near each other on disk. The old tools for arranging that were partitioning and Z-ordering. Both work, but both are rigid and high-maintenance. Liquid clustering is the replacement.

Analogy
Liquid clustering is like re-shelving a library by the labels people actually search for — and you can re-shelve later without emptying the building. Partitioning nails the shelves to the floor on day one.

Use case
A fast-growing events table you filter by event_type and event_date: CLUSTER BY (event_type, event_date) colocates those rows so a query reads a handful of files — and you can switch keys later with one ALTER TABLE, no full rewrite.

Why it beats the old ways

• No upfront guessing. With partitioning you chose the column at table creation, and changing it later meant rewriting the whole table. With liquid clustering you can change the clustering columns anytime, no rewrite of existing data required.
• Handles high-cardinality & skew. Partitioning on something like customer_id creates millions of tiny partitions. Liquid clustering is built for exactly those columns.
• Incremental maintenance. OPTIMIZE only reorganizes new or changed data, so routine upkeep stays cheap and fast.
• It's the default recommendation. Databricks recommends liquid clustering for all new tables — including streaming tables and materialized views.

See it work — data skipping

Below is a table stored as 64 files. Pick a layout, then run a filter and watch which files get scanned vs. skipped. The fewer files scanned, the faster and cheaper the query.

This is a simplified illustration of the concept. Real data skipping uses per-file min/max statistics on clustering keys; results depend on data distribution and file sizes.

See it work — change keys with no rewrite

The headline benefit: you can redefine the clustering keys at any time, and existing data is not rewritten until you choose to recluster. Step through the lifecycle to see what each command touches.

Simplified illustration. In reality ALTER TABLE … CLUSTER BY changes only metadata; new writes and incremental OPTIMIZE use the new keys, and OPTIMIZE FULL reclusters historical data.

Two flavors

Choosing good keys

• Columns you filter or join on most often.
• High-cardinality columns are fine — that's a strength.
• Up to 4 keys; for tables under ~10 TB, fewer keys often skips better on single-column filters.
• Keys must have statistics collected (by default the first 32 columns of the table).
• Supported types: date, timestamp, string, the integer family, float/decimal, and struct fields via dot notation. Not complex types (Map/Array/Variant).

The commands you'll actually use

Create a table with clustering (SQL)

SQL-- CLUSTER BY goes right after the column list (and before AS for CTAS)
CREATE OR REPLACE TABLE catalog.schema.customers (
  customer_id BIGINT,
  country     STRING,
  signup_date DATE
)
CLUSTER BY (country, signup_date);   -- up to 4 keys

Create a table with clustering (PySpark / DeltaTable API, DBR 14.3 LTS+)

PYSPARKfrom delta.tables import DeltaTable

# DeltaTable builder with clustering keys
(DeltaTable.create(spark)
    .tableName("catalog.schema.customers")
    .addColumn("customer_id", "BIGINT")
    .addColumn("country", "STRING")
    .addColumn("signup_date", "DATE")
    .clusterBy("country", "signup_date")   # the clustering keys
    .execute())

# Or cluster on write straight from a DataFrame
(df.write.clusterBy("country", "signup_date")
   .saveAsTable("catalog.schema.customers"))

Enable on an existing (unpartitioned) table

SQLALTER TABLE catalog.schema.customers CLUSTER BY (country, signup_date);
-- recluster existing data to the new keys (DBR 16.0+)
OPTIMIZE catalog.schema.customers FULL;

Trigger clustering (incremental)

SQL-- only rewrites what's needed; cheap to run often (every 1-2h for high-churn tables)
OPTIMIZE catalog.schema.customers;

PYSPARKfrom delta.tables import DeltaTable
DeltaTable.forName(spark, "catalog.schema.customers").optimize().executeCompaction()

Change the keys later — no full rewrite

SQL-- metadata-only change; existing files are NOT rewritten
ALTER TABLE catalog.schema.customers CLUSTER BY (customer_id);
-- reorganize ALL existing data to the new keys (can be slow on huge tables; DBR 16.0+)
OPTIMIZE catalog.schema.customers FULL;

Let Databricks pick the keys (AUTO)

SQL-- UC managed table + predictive optimization required
ALTER TABLE catalog.schema.customers CLUSTER BY AUTO;
SHOW TBLPROPERTIES catalog.schema.customers;  -- look for clusterByAuto = true

Convert a partitioned table (DBR 18.1+)

SQLALTER TABLE catalog.schema.customers REPLACE PARTITIONED BY WITH CLUSTER BY (country, signup_date);
OPTIMIZE catalog.schema.customers FULL;

Inspect / turn off

SQLDESCRIBE DETAIL catalog.schema.customers;        -- see clusteringColumns
ALTER TABLE catalog.schema.customers CLUSTER BY NONE;  -- stop clustering

Liquid clustering vs partitioning vs Z-order

Uses, edge cases & limitations

Uses — when to reach for it (and when not)

• All new Delta tables — Databricks' default layout recommendation; also streaming tables & materialized views.
• Fast-growing tables filtered/joined on a few columns (events, orders, telemetry).
• High-cardinality filter columns (customer_id, device_id) that would explode partitions.
• When NOT to: truly tiny tables (the engine just reads them); external tables that must stay readable by old Delta clients (the protocol upgrade blocks them). Partitioning still has a niche for low-cardinality filters on tables > 1 TB, but clustering is preferred.

Edge cases interviewers probe

• Changing keys: ALTER TABLE … CLUSTER BY is metadata-only — old data keeps the old layout until OPTIMIZE FULL. No instant skipping on history.
• Too many keys: for tables < ~10 TB, 3–4 keys can dilute single-column-filter skipping; start with your 1–2 most selective keys.
• Tiny / sub-threshold writes: clustering-on-write is skipped below the size threshold (1 key 64 MB … 4 keys 1 GB on UC managed), so frequent small writes need regular OPTIMIZE.
• Concurrent writes: OPTIMIZE runs under snapshot isolation — readers/streams aren't blocked and no data changes.
• Stats on long strings: stats truncate on long strings, so a long-string key skips poorly — prefer shorter, selective columns.

Limitations (honest constraints)

• ≤ 4 clustering keys.
• Incompatible with partitioning AND ZORDER — use instead of, never alongside.
• Delta writer v7 / reader v3; cannot downgrade — very old Delta clients can't read the table.
• CLUSTER BY AUTO requires DBR 15.4 LTS+, a UC managed table, and predictive optimization.
• OPTIMIZE FULL needs DBR 16.0+; OPTIMIZE FULL WHERE and REPLACE PARTITIONED BY WITH CLUSTER BY need DBR 18.1+.
• Keys need stats (first 32 columns by default); not complex types.

Quick gotchas

• Don't mix with partitioning or ZORDER — pick one approach.
• OPTIMIZE after changing keys only affects new data; use OPTIMIZE FULL to rewrite existing data to the new keys.
• Clustering on write only kicks in past a size threshold, so run OPTIMIZE regularly (or rely on predictive optimization on managed tables).
• Tables with liquid clustering use newer Delta reader/writer protocols — very old Delta clients may not read them, and you can't downgrade the protocol.
• GA is DBR 15.4 LTS+ (not 15.2). State the runtime; OPTIMIZE FULL is DBR 16.0+.