Data Layout Optimization: Liquid Clustering and Predictive Optimization

Introduction

This lesson completes the Troubleshooting, Monitoring, and Optimization section of the official exam outline (about 10% of the scored questions). The previous lesson showed how to diagnose slow jobs. This one deals with slow queries caused by how a Delta table is laid out in storage. A Delta table is a set of Parquet files plus a transaction log. When a query filters on a column, Delta can skip files whose min/max statistics prove they contain no matching rows, but only if rows with similar values are stored together. Data layout is the art of organizing files so that queries read as little as possible.

Historically, engineers used partitioning and Z-ordering for this. Today Databricks recommends liquid clustering for new tables, and predictive optimization to run maintenance automatically on Unity Catalog managed tables. This lesson explains each feature, how they differ, and when layout improvements actually help.

Learning objectives

• Explain data skipping and why file layout affects query speed.
• Create and alter tables with liquid clustering (CLUSTER BY, CLUSTER BY AUTO).
• Compare liquid clustering with partitioning and Z-ordering.
• Use OPTIMIZE and VACUUM correctly, including the retention rules.
• Explain what predictive optimization does and which tables it covers.
• Decide when a layout feature will, or will not, improve performance.

1. Data skipping and the small files problem

Each Parquet file added to a Delta table has statistics in the transaction log: for the first columns of the table (32 by default) the minimum and maximum values, and the null count. At query time, a filter such as WHERE order_date = '2026-03-14' lets Delta skip every file whose min/max range cannot contain that date. This is data skipping.

Skipping works well only if each file contains a narrow range of the filter column. If every file contains orders from every day of the year, every file's range spans the whole year and none can be skipped. A good layout clusters similar values into the same files.

A second problem is small files. Frequent small appends (streaming micro-batches, many small INSERTs) create thousands of tiny files. Reading many small files is slow because of per-file overhead (listing, opening, scheduling a task per file). Compaction rewrites many small files into fewer, larger ones (the default target is around 1 GB, adapted to the table).

2. OPTIMIZE and VACUUM

OPTIMIZE

OPTIMIZE compacts small files into larger ones. It is a maintenance command; it does not change the table's logical content, so readers keep working while it runs.

OPTIMIZE main.sales.orders;

On a table with liquid clustering, OPTIMIZE also clusters the data according to the clustering keys. It is incremental: it processes files that are not yet clustered, rather than rewriting the entire table every time. The command can be scheduled, or you can leave it to predictive optimization.

VACUUM

Delta keeps old data files after UPDATE, DELETE, MERGE and OPTIMIZE so that time travel and concurrent readers keep working. VACUUM physically deletes files that are no longer referenced by the current table version and are older than the retention period.

VACUUM main.sales.orders;                 -- default retention: 7 days
VACUUM main.sales.orders RETAIN 168 HOURS; -- explicit retention (168 hours = 7 days)

Facts to remember:

• The default retention threshold is 7 days. Setting a shorter retention is blocked by a safety check unless you disable it, because a long-running query or a stream may still need the older files.
• After VACUUM, you can no longer time travel to versions that depended on the deleted files. The transaction log entries remain, but reading those versions fails.
• VACUUM removes files, OPTIMIZE rewrites files. They are complementary: OPTIMIZE leaves the old small files behind (they are no longer referenced), and VACUUM removes them after the retention period.

Concrete example. A streaming job appends 20 small files per hour to bronze.clickstream. After three months the table has 40,000 files, and a simple count takes minutes. Running OPTIMIZE bronze.clickstream compacts them into a few hundred files. The old files stay on storage until VACUUM runs after 7 days, which is when storage costs actually drop.

3. Partitioning and Z-ordering (the older approach)

Partitioning

Partitioning (PARTITIONED BY (country)) splits the table into directories by the value of one or more columns. Queries that filter on the partition column read only the matching directories. But partitioning has drawbacks:

• The partition columns are fixed at table creation; changing them means rewriting the table.
• A column with high cardinality (such as customer_id) creates millions of tiny partitions: this is the small files problem again.
• A column with skewed values creates one huge partition and many tiny ones.
• Databricks guidance says that most tables under about 1 TB do not need partitioning.

Z-ordering

Z-ordering (OPTIMIZE table ZORDER BY (col1, col2)) co-locates related values of one or more columns in the same files using a space-filling curve, improving data skipping on several columns. But:

• ZORDER is not incremental: each run rewrites the files again, so it gets expensive on large tables.
• You must run it yourself (or schedule it), and you must repeat it as new data arrives.
• The columns are chosen at the time of the command.

4. Liquid clustering

Liquid clustering is the modern replacement for partitioning and Z-ordering on Delta tables. You declare clustering keys on the table, and Delta organizes the data by those keys when OPTIMIZE runs. Keys can be changed later without rewriting all existing data.

-- Create a table with clustering keys
CREATE TABLE main.sales.orders (
  order_id BIGINT,
  customer_id BIGINT,
  order_date DATE,
  amount DECIMAL(12,2)
)
CLUSTER BY (order_date, customer_id);

-- Change the clustering keys on an existing table
ALTER TABLE main.sales.orders CLUSTER BY (order_date);

-- Let Databricks choose and evolve the keys
ALTER TABLE main.sales.orders CLUSTER BY AUTO;

-- Remove clustering
ALTER TABLE main.sales.orders CLUSTER BY NONE;

In PySpark, the DataFrame writer offers clusterBy:

(df.write
   .clusterBy("order_date", "customer_id")
   .saveAsTable("main.sales.orders"))

Key properties:

Writes do not fully cluster the data on their own at write time in every case. The data is clustered by OPTIMIZE, and for Unity Catalog managed tables with predictive optimization this happens automatically. Without an OPTIMIZE (manual or automatic), data written after table creation may not be clustered yet, which is a typical exam trap.

Automatic liquid clustering: CLUSTER BY AUTO

With CLUSTER BY AUTO, Databricks chooses the clustering keys for you by analyzing the table's historical query workload, and changes them when the expected savings from better data skipping outweigh the cost of reclustering. Requirements and behavior:

• The table must be a Unity Catalog managed Delta table, on a supported runtime.
• Predictive optimization must be enabled for the table, because the key selection and clustering run as a background maintenance operation.
• Some tables get no keys: too small, already well laid out, or rarely queried.
• You can still switch to explicit keys (CLUSTER BY (col)) when you know the access pattern.

Concrete example. A product team creates main.web.events with CLUSTER BY AUTO. The first months, analysts filter by event_date and user_id; later, a new dashboard filters by campaign_id. Because the keys follow the observed workload, the team does not have to rewrite the table or rerun a Z-order: Databricks adapts the clustering in the background.

5. Choosing between partitioning, Z-ordering and liquid clustering

A simple rule: new table -> liquid clustering (or CLUSTER BY AUTO on a managed table). Keep partitioning only for legacy tables or special cases such as partitions needed for data retention by directory. Use Z-ordering only on older tables that are not yet migrated.

6. Predictive optimization

Predictive optimization is a Databricks service that automatically runs maintenance on Unity Catalog managed tables, so that engineers do not need to schedule it themselves. It uses the table's usage to decide when to run:

• OPTIMIZE to compact files and cluster data (for liquid-clustered tables);
• VACUUM to delete unreferenced files after the retention period;
• ANALYZE to maintain statistics for the query optimizer;
• automatic key selection for tables using CLUSTER BY AUTO.

Facts to remember:

• It applies to Unity Catalog managed tables, not to external tables, and not to tables in the legacy Hive metastore.
• It is configured at the account, catalog or schema level, and inherited downward; you can enable or disable it at a lower level.
• It runs on serverless compute managed by Databricks and is billed as a predictive optimization usage item; the operations are listed in a system table (the predictive optimization operations history) so that you can audit and cost them.
• It decides when maintenance is worth the cost: for example, it does not run OPTIMIZE on a table nobody queries.
• Databricks recommends enabling it, together with liquid clustering, for managed tables.

Concrete example. A data platform team has 600 managed tables in the catalog prod. Before, an engineer maintained a schedule of OPTIMIZE and VACUUM notebooks and regularly forgot new tables. After enabling predictive optimization at the catalog level, every managed table in the catalog is maintained automatically. The notebooks were deleted, and the team monitors the operations history table instead.

7. When do layout features actually help?

Data layout is not a magic switch. A layout feature helps when the query reads fewer files or the files are healthier.

Always measure: run the query before, apply the change, wait for OPTIMIZE or automatic clustering, run it again, and compare the data scanned (files and bytes read) and duration in the query profile.

Worked example: modernizing a large events table

Scenario. prod.web.events is a managed table of 8 TB, partitioned by event_date. Analysts mostly filter by user_id and event_type, which are not partition columns, so queries scan most files. The table is also compacted nightly by a notebook running OPTIMIZE ... ZORDER BY (user_id) which takes four hours and rewrites a lot of data.

Step 1: Understand the access pattern. Filters use event_date (range), user_id and event_type. user_id has high cardinality, which is a bad partition column and a good clustering column.

Step 2: Choose the target layout. Migrate to liquid clustering. Because the access patterns evolve and it is a managed table, use CLUSTER BY AUTO (or explicit keys such as (event_date, user_id) if the team wants control).

Step 3: Recreate the table. Liquid clustering cannot be combined with partitioning, so create a new clustered table from the old one (for example with CREATE TABLE ... CLUSTER BY ... AS SELECT), validate the data, then switch consumers to the new table.

Step 4: Remove the manual maintenance. Enable predictive optimization on the catalog; delete the nightly Z-order notebook.

Step 5: Measure. Compare the files read and the duration of the five most important queries before and after.

Why the alternatives are worse. Adding user_id as a second partition column would create millions of directories. Running Z-order more often would rewrite more data each time. Doing nothing leaves queries scanning almost the whole table.

Common pitfalls and exam tips

• CLUSTER BY replaces partitioning and Z-ordering for new tables; they cannot be combined on one table.
• Up to 4 clustering keys, usually the columns most used in filters.
• OPTIMIZE does the clustering (incrementally for liquid clustering); new data may be unclustered until then.
• CLUSTER BY AUTO needs Unity Catalog managed tables and predictive optimization.
• VACUUM default retention is 7 days; vacuuming removes time travel to older versions.
• Predictive optimization covers managed tables only, not external ones.
• Do not partition by high-cardinality columns. Under about 1 TB, partitioning is rarely needed.
• A layout feature does not fix a skewed join or a full scan.
• Measure before and after.

Exam tip: If the question says "a new table, queried by several columns, with changing access patterns, and the team does not want to manage maintenance", the answer is liquid clustering with CLUSTER BY AUTO and predictive optimization on a Unity Catalog managed table.

Mini case study

Situation. A telecom company has the Delta table usage_records (3 TB, external table in the legacy partitioning style: PARTITIONED BY (customer_id)). It contains 40 million partitions, listing the table takes minutes, and compaction jobs fail. Queries filter by customer_id and by record_date ranges. The team plans to move to Unity Catalog managed tables.

Task. Recommend a layout and maintenance strategy.

Solution.

1. Partitioning by customer_id is the root cause: far too many partitions. Remove it.
2. Create a new Unity Catalog managed table with liquid clustering. Use CLUSTER BY (customer_id, record_date) or CLUSTER BY AUTO if workloads are expected to change.
3. Load the data with INSERT INTO ... SELECT (or CREATE TABLE ... AS SELECT) from the old table, validate counts, and switch consumers.
4. Enable predictive optimization for the schema so that OPTIMIZE and VACUUM run automatically and clustering is maintained; stop the failing manual compaction jobs.
5. Keep the old table for one retention cycle and then drop it, and verify the improvement with before/after metrics (files scanned, duration).

Key terms

• Data skipping: skipping files whose min/max statistics exclude the filter.
• Small files problem: many tiny files that slow down reads.
• OPTIMIZE: command that compacts files (and clusters liquid-clustered tables).
• VACUUM: command that deletes unreferenced files older than the retention period (default 7 days).
• Partitioning: directory-level split by column values.
• Z-ordering: co-location of values with OPTIMIZE ... ZORDER BY; not incremental.
• Liquid clustering: flexible, incremental data organization declared with CLUSTER BY.
• CLUSTER BY AUTO: automatic selection of clustering keys on managed tables.
• Predictive optimization: automatic maintenance (OPTIMIZE, VACUUM, ANALYZE) of Unity Catalog managed tables.

Summary checklist

• I can explain data skipping and why small files hurt.
• I can write CREATE TABLE ... CLUSTER BY, ALTER TABLE ... CLUSTER BY and CLUSTER BY AUTO.
• I can compare liquid clustering, partitioning and Z-ordering.
• I know what OPTIMIZE and VACUUM do and the 7-day default retention.
• I know predictive optimization works on Unity Catalog managed tables.
• I can tell when layout changes help and when they do not.

Official sources

• Exam guide: https://www.databricks.com/sites/default/files/2026-05/databricks-certified-data-engineer-associate-exam-guide-may-2026.pdf (opens in a new tab)
• Use liquid clustering for tables: https://docs.databricks.com/aws/en/delta/clustering (opens in a new tab)
• CLUSTER BY clause: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-cluster-by (opens in a new tab)
• Predictive optimization for Unity Catalog managed tables: https://docs.databricks.com/aws/en/optimizations/predictive-optimization (opens in a new tab)
• Remove unused data files with VACUUM: https://docs.databricks.com/aws/en/delta/vacuum (opens in a new tab)