Databricks 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 Databricks monitoring and troubleshooting lesson diagnosed slow jobs. This lesson applies liquid clustering and predictive optimization to table layout for the Data Engineer Associate exam. 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, when the recorded ranges exclude the requested values; co-locating similar values improves that opportunity. 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
OPTIMIZEandVACUUMcorrectly, 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: on columns selected for statistics (by default the first 32 for Unity Catalog external Delta tables; predictive optimization can select additional useful columns on managed tables) 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, planning and scheduling work; a Spark task can read multiple small files). Compaction rewrites many small files into fewer, larger ones (the default target is around 1 GB, adapted to the table).
| Problem | Symptom | Remedy |
|---|---|---|
| Poor clustering of filter columns | Queries filtering on a column still scan almost all files | Cluster or sort the data by that column |
| Small files | Thousands of tiny files, slow listing, many short tasks | Compact with OPTIMIZE |
| Obsolete files | Storage grows although the table is stable | VACUUM old, unreferenced files |
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 liquid-clustered table, OPTIMIZE maintains the declared layout. After enabling or changing keys, existing files are not immediately rewritten; to force historical reclustering where necessary, Databricks Runtime 16.0+ supports OPTIMIZE main.sales.orders FULL. This can be expensive, so inspect the workload and measure it. 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
delta.deletedFileRetentionDurationis 7 days. Choose a retention longer than the longest concurrent operation and maximum stream lag, and compatible with required time travel. Do not disable the safety check to make an unsafe retention appear valid. Table properties can change the effective retention. - After
VACUUM, you can no longer time travel to versions that depended on the deleted files. Log retention is a separate setting and logs are cleaned up separately; retaining a log entry alone does not retain its data files. VACUUMremoves files,OPTIMIZErewrites files. They are complementary:OPTIMIZEleaves the old small files behind (they are no longer referenced), andVACUUMremoves them after the retention period.
Concrete example. A streaming job appends 20 small files per hour to bronze.clickstream. An illustrative retained subset has 40,000 files, and a query aggregating a payload field scans them slowly. A plain COUNT(*) may use metadata, so it is not a reliable scan benchmark. OPTIMIZE bronze.clickstream can reduce the file count; the actual result depends on data size and target file sizing. The old files stay on storage until VACUUM runs after 7 days, which can reduce stored bytes. New versions, object-store lifecycle/versioning and billing windows affect the observed bill.
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:
- Legacy partition changes typically require migration. On supported Delta tables, Databricks Runtime 18.1+ can replace partitioning with liquid clustering using
ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY; the resulting layout is clustered, not simultaneously partitioned. - 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:
- Z-ordering aims to be incremental but is not idempotent. Rerunning a just-Z-ordered partition with no new data has no effect; maintenance costs can still be substantial as data changes. Do not assume every execution rewrites every file.
- 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;These SQL statements illustrate separate choices; ending with CLUSTER BY NONE turns clustering off. Use a supported runtime (Delta liquid clustering is GA in DBR 15.4 LTS+), an existing catalog/schema and appropriate table privileges. Enabling clustering changes Delta protocol/features, so check compatibility of all readers and writers first.
The PySpark example below creates a different, not-yet-existing table from typed source data:
df = spark.read.table("main.sales.orders")
(df.write
.clusterBy("order_date", "customer_id")
.saveAsTable("main.sales.orders_copy"))Key properties:
| Property | Detail |
|---|---|
| Keys | Up to 4 clustering columns (they must have statistics collected) |
| Flexibility | Keys can be redefined with ALTER TABLE ... CLUSTER BY and existing data is not rewritten immediately; new writes and OPTIMIZE use the new keys |
| Incremental | OPTIMIZE clusters only data that is not yet clustered |
| Compatibility | Not combined with PARTITIONED BY or ZORDER BY on the same table |
| Fits | Tables with high-cardinality filter columns, skewed data, fast-growing data, concurrent writes, changing access patterns |
| Requirement | Examples here use Delta tables; current Databricks also supports managed Iceberg with format/runtime-specific requirements. Automatic keys require an eligible UC managed table and predictive optimization |
Writes do not fully cluster the data on their own at write time in every case. Supported writes can cluster on write when transaction-size thresholds are met; smaller appends may remain unclustered. Maintenance also clusters data through OPTIMIZE, and for Unity Catalog managed tables with predictive optimization this happens automatically. Without maintenance, data not clustered on write can remain poorly laid out, 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 avoids manually managing a Z-order schedule. Key selection is cost-based, and adapting the physical layout still involves background rewrites and compute; changing metadata is not free historical reclustering.
5. Choosing between partitioning, Z-ordering and liquid clustering
| Question | Partitioning | Z-ordering | Liquid clustering |
|---|---|---|---|
| Can the keys change later without a rewrite? | No | Yes (a new command column list), with rewrite work as needed | Yes |
| Good with high-cardinality columns? | No (too many partitions) | Yes | Yes |
| Handles skewed values? | Poorly | Reasonably | Well |
| Incremental maintenance? | n/a (directory layout) | Aims to be incremental; not idempotent | Incremental OPTIMIZE; FULL can recluster historical data |
| Recommended for new tables by Databricks? | Rarely | Replaced by clustering | Yes |
| Can be combined on the same table? | With Z-order only | With partitioning only | No with either |
A simple rule: new table -> liquid clustering (or CLUSTER BY AUTO on a managed table). Keep partitioning only for legacy tables or documented compatibility requirements. Z-order remains an option for eligible non-clustered tables. Never manually delete partition directories as a substitute for transactional deletes and retention management.
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:
OPTIMIZEto compact files and cluster data (for liquid-clustered tables);VACUUMto delete unreferenced files after the retention period;ANALYZEto 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, schema or table 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: an enabled table is eligible for cost-based scheduling, not guaranteed to receive every operation on every cycle.
- 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, eligible managed tables inherit the setting unless a lower-level override changes it. After confirming effective settings and maintenance history, the redundant schedules can be retired, 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.
| Situation | Does layout help? |
|---|---|
Selective filters (WHERE customer_id = 42) on a large table |
Yes, if the filter column is a clustering key |
| A query that truly scans every row without a selective filter | No filter-driven skipping benefit; compaction or other plan changes can still help |
| Filters on a non-key column | Statistics and natural correlation may still allow skipping; clustering does not guarantee improvement for this predicate |
| A table of a few MB | Little to gain; the table is too small |
| Slowness caused by a skewed join | Not a layout problem; see the skew section of the previous lesson |
| Thousands of tiny files | Yes: compaction by OPTIMIZE |
| Many concurrent writers conflicting on partitions | Liquid clustering reduces conflicts compared with partitions |
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: Migrate the layout. On supported DBR 18.1+ Delta tables, replace partitioning in place with ALTER TABLE prod.web.events REPLACE PARTITIONED BY WITH CLUSTER BY AUTO (managed table with predictive optimization). This changes the layout definition; run/monitor maintenance and validate results. Otherwise create a new clustered table with CTAS, reconcile data and permissions, then cut consumers over. Account for concurrent writes, downstream readers, history and retention before retiring the original.
Step 4: Remove the manual maintenance. Enable and verify predictive optimization at the chosen scope, preserve the required retention, then retire the redundant Z-order schedule after observing successful maintenance. Predictive optimization does not itself run ZORDER.
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 can increase maintenance cost without addressing the evolving layout contract. Doing nothing leaves queries scanning almost the whole table.
Common pitfalls and exam tips
CLUSTER BYreplaces 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.
- Clustering on write is conditional. OPTIMIZE maintains unclustered data; FULL can be needed to recluster history after key changes.
CLUSTER BY AUTOneeds Unity Catalog managed tables and predictive optimization.VACUUMdefault retention is 7 days; vacuuming makes versions unreadable when they need the deleted files; it is not a promise that every older version disappears at the same timestamp.- 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 AUTOand 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.
- Partitioning by
customer_idis the root cause: far too many partitions. Remove it. - Use an eligible external-to-managed conversion and layout conversion when their runtime/feature requirements are met, or create a new Unity Catalog managed table with liquid clustering. Use
CLUSTER BY (customer_id, record_date)orCLUSTER BY AUTOif workloads are expected to change. - Load the data with
INSERT INTO ... SELECT(orCREATE TABLE ... AS SELECT) from the old table, validate counts, and switch consumers. - Enable predictive optimization for the schema so that
OPTIMIZEandVACUUMrun automatically and clustering is maintained; stop the failing manual compaction jobs. - Retain the old table until data, access controls, readers/writers and recovery requirements have been validated; one arbitrary retention cycle is not a sufficient cutover policy, 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: file co-location with
OPTIMIZE ... ZORDER BY; aims to be incremental, but is not idempotent. - 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 BYandCLUSTER BY AUTO. - I can compare liquid clustering, partitioning and Z-ordering.
- I know what
OPTIMIZEandVACUUMdo 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.
For the managed/external distinction behind automatic maintenance, see Unity Catalog table lifecycle and privileges. For task-level shuffle problems, return to deduplication, aggregations and Spark tuning instead of expecting storage layout alone to fix a hot key.
Official sources
- Exam guide: https://www.databricks.com/sites/default/files/2026-10/Databricks-Certified-Data-Engineer-Associate-Exam-Guide-August-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/tables/operations/vacuum (opens in a new tab)
- Data skipping and Z-order semantics: https://docs.databricks.com/aws/en/tables/data-skipping (opens in a new tab)
