What you will be able to do
- Use table size, partition size and column cardinality to decide whether a table should be partitioned at all
- Pick liquid clustering keys, hierarchical keys or CLUSTER BY AUTO to match how a table is queried
- Convert a partitioned Delta table to liquid clustering, and know what happens to data that is already written
Key concept
Liquid clustering as the default layout — On Databricks, liquid clustering is the layout to start with. It groups data files by clustering keys that you can change later, so you don't fix a physical layout up front. Partitioning is now an exception that has to prove it does better.
1.Mapping partitioning to data size and grain
In Hive and plain Spark, partitioning was the main way to lay out data. On Databricks it is a narrow tool, and it only fits when the data's grain supports it. Each partition is a fixed physical boundary. For partitioning to help, the table needs to be very large, the partition column needs few values, and each partition needs to hold a lot of data. When one of these is missing, you end up with many small partitions full of small files. The guidance sets hard thresholds: below 1 TB, don't partition; from 1 TB to 100 TB, use liquid clustering; at 100 TB or more, partitioning *might* help, but try liquid clustering first and confirm that partitioning actually performs better. For any partition you do create, check that it holds at least 1 GB. Tables with fewer, larger partitions usually beat tables with many smaller ones.
| Table size | Recommendation |
|---|---|
| Less than 1 TB | Don't partition |
| More than 1 TB to 100 TB | Use liquid clustering instead of partitioning |
| 100 TB or more | Partitioning might help, but use liquid clustering first and verify any improvement |
| Any partitioned table | Each partition should contain at least 1 GB of data |
Cardinality is the other test. Partitioning only suits columns with few or known values, such as dates or physical locations. It does not suit high-cardinality columns like timestamps or customer IDs, because they produce huge numbers of tiny partitions. Partition columns must also be top-level columns of a supported type. You can't partition on complex types or on struct fields. If you need to data-skip on a struct field, liquid clustering is the only option.
You are not starting from nothing, either. Delta tables without partitions automatically use ingestion time clustering, which gives roughly the performance of partitioning on a datetime field with no tuning. A custom partitioning scheme has to beat that default. Getting it wrong is expensive, because fixing a bad partitioning strategy can take a full rewrite of the data. Managed Apache Iceberg tables go further: Unity Catalog supports only liquid clustering for them, and treats any PARTITION BY columns as clustering keys.
Checkpoint 1 of 5· Check yourself
You must partition a 300 TB table. Which column is the most defensible partition key?
Partitioning only works for low or known cardinality columns such as dates, and each partition should hold at least 1 GB. Timestamps and customer IDs are high cardinality, and struct fields can't be partition columns.
“Partitioning works well only for low or known cardinality fields (for example, date fields or physical locations)”Source: docs.databricks.com
Checkpoint 2 of 5· Exam question
A team is designing a `fact_events` table that will grow to several billion rows. Query patterns filter heavily on `event_date` and `customer_id`, but the exact date ranges and customer segments used in dashboards change frequently as new reports are built each month. The team wants a layout that avoids costly directory-listing overhead from thousands of narrow partitions while still letting the physical data layout adapt as query patterns shift. Which approach best meets these requirements?
Correct answer: A — Create the table with `CLUSTER BY (event_date, customer_id)` so Delta Lake incrementally reorganizes files around these columns without a fixed directory layout that must be rewritten as patterns change.
- A. Liquid clustering lets Delta Lake incrementally rewrite only the files that need reorganizing, and clustering keys can be changed later without a full rewrite, which matches both the scale requirement and the shifting query pattern requirement.
- B. Daily partitioning over several billion rows accumulated across years still produces a very large number of partition directories, so directory-listing overhead persists even though Z-order adds secondary skipping within each partition.
- C. Partitioning by a very high-cardinality column like customer id creates an enormous number of small partition directories, which is the exact small-partition overhead the team is trying to avoid.
- D. Auto Loader handles incremental file discovery and ingestion, not the physical layout or compaction of data already stored in the table, so it does not address directory or file-size overhead.
Sources1
2.Aligning clustering keys to how the table is queried
Partitioning follows the data's grain. Liquid clustering follows how the data is queried. You choose clustering keys based on the columns queries filter and group on, and OPTIMIZE then rewrites files so that rows with similar key values sit together. What makes it a better fit for changing workloads is that you can change the keys later without rewriting the data already in the table. Databricks recommends liquid clustering for all new tables, including streaming tables and materialized views. It singles out queries that filter on high-cardinality columns, tables with heavy skew, fast-growing tables, tables with concurrent writers, tables whose access patterns change, and tables where a partition key would return too many or too few partitions.
CREATE TABLE table1 (
col0 STRING,
col1 DATE,
col2 BIGINT
)
CLUSTER BY (col0, col1);CREATE TABLE table2 CLUSTER BY (col0)
AS SELECT * FROM table1;If queries keep filtering or grouping on one column, for example finding the latest date for each category, you can make that column a hierarchical clustering key. Be careful when the column has high cardinality: prioritizing it can make your other clustering keys less effective. If the access pattern is unclear or keeps changing, CLUSTER BY AUTO hands key selection to Databricks. It works on Unity Catalog managed Delta tables, and also on materialized views and streaming tables. It depends on predictive optimization to pick the keys and run clustering in the background. Clustering can't be combined with partitioning or ZORDER on the same table. With DataFrame APIs, you can only set clustering keys when you create or overwrite a table, not when you append.
Checkpoint 3 of 5· Check yourself
Analysts filter a 20 TB Unity Catalog managed orders table on different columns every quarter, and nobody wants to keep retuning keys. What is the best layout?
Automatic liquid clustering lets Databricks choose and change the keys based on the actual workload. It needs predictive optimization to do that.
“Automatic liquid clustering allows Databricks to intelligently choose clustering keys to optimize query performance, using the CLUSTER BY AUTO clause.”Source: docs.databricks.com
Sources2
3.Converting a partitioned table to liquid clustering
You can turn on liquid clustering for an existing unpartitioned Delta table with ALTER TABLE <table_name> CLUSTER BY (...). For partitioned Delta tables, Databricks Runtime 18.1 and above adds ALTER TABLE ... REPLACE PARTITIONED BY WITH CLUSTER BY. It works on both managed and external tables and keeps reader and writer downtime to a minimum. The CLUSTER BY clause gives you three options.
| Option | Resulting clustering keys |
|---|---|
| ( <clustering_columns> ) | New columns you choose. Very different columns trigger a large reclustering on the first OPTIMIZE |
| AUTO | Starts from the current partition columns, then predictive optimization adapts them. Unity Catalog managed tables only |
| No options specified | The current partition columns become the clustering columns |
ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id);
OPTIMIZE t1;The OPTIMIZE in that example is required. Changing the clustering columns has no effect until OPTIMIZE runs. More generally, enabling clustering does not recluster data that is already written. To force a recluster, use OPTIMIZE <table_name> FULL, optionally with a WHERE predicate. To confirm the conversion, DESCRIBE EXTENDED shows the new clustering columns, and DESCRIBE HISTORY shows REORG, UPGRADE PROTOCOL and REPLACE PARTITIONED BY WITH CLUSTER BY operations. Managed Iceberg tables need no conversion, since their partition definitions already act as clustering keys, and running the command on one raises an error. Clustered Delta tables use writer version 7 and reader version 3, and you can't downgrade those protocol versions.
Checkpoint 4 of 5· Check yourself
You run ALTER TABLE t1 REPLACE PARTITIONED BY WITH CLUSTER BY (day, id) and stop there. What happens to the existing files?
The ALTER only changes metadata. OPTIMIZE is the step that rewrites files around the new keys.
“To benefit from altering clustering columns, you must run OPTIMIZE.”Source: docs.databricks.com
Checkpoint 5 of 5· Exam question
A platform team manages hundreds of Unity Catalog managed Delta tables and does not have the capacity to manually choose and periodically revisit clustering keys as BI dashboards evolve. They want Databricks to observe query history and select clustering keys automatically, changing them only when the switch is expected to pay for itself in reduced maintenance cost. Which configuration satisfies this requirement?
Correct answer: A — Create or alter the tables with `CLUSTER BY AUTO` so Databricks Runtime 15.4 LTS or later analyzes each table's query history and updates clustering keys only when the savings outweigh the reclustering cost.
- A. This is exactly the automatic clustering-key selection feature: Databricks analyzes the workload against the table and only changes keys when doing so is expected to be cost-effective, requiring no manual key management.
- B. Auto compaction controls small-file bin-packing behavior on write, but it does not select or change clustering keys, so it does not meet the requirement to automate key selection.
- C. This describes a manually built and maintained job that inspects statistics and swaps keys by hand, which is the opposite of the hands-off, workload-aware automation the team wants.
- D. Predictive optimization schedules maintenance jobs like `OPTIMIZE` and `VACUUM`, and `ZORDER BY` is a separate, non-adaptive indexing technique used with partitioned tables, not an automatic clustering-key selector.
Sources2
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Changing liquid clustering keys forces a full rewrite of the table, just like changing partition columns.Why is that wrong?
You can redefine clustering keys without rewriting existing data. Only partitioning is rigid in this way.
Covered in Aligning clustering keys to how the table is queried
2.Enabling liquid clustering on an existing table immediately reclusters all the data in it.Why is that wrong?
By default only newly written data is clustered. You need OPTIMIZE, or OPTIMIZE FULL to force a recluster, to reorganize older files.
Covered in Converting a partitioned table to liquid clustering
3.Managed Iceberg tables need REPLACE PARTITIONED BY WITH CLUSTER BY to move to liquid clustering.Why is that wrong?
Managed Iceberg tables already use their partition definitions as clustering keys, and the conversion command fails on them.
Covered in Converting a partitioned table to liquid clustering
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/tables/partitionsOfficial docs
“For partitions, verify that each partition contains at least 1 GB of data.”
↩︎ Mapping partitioning to data size and grain“For managed tables using Apache Iceberg, Unity Catalog supports only liquid clustering and interprets PARTITION BY columns as clustering keys.”
↩︎ Mapping partitioning to data size and grain“Liquid clustering is the only way to data-skip on a struct field without first extracting it into a top-level column.”
↩︎ Mapping partitioning to data size and grain“With more than 1 TB to 100 TB of data, use liquid clustering instead of partitioning.”
↩︎ Prediction“Partitioning works well only for low or known cardinality fields (for example, date fields or physical locations)”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/tables/clusteringOfficial docs
“Prioritizing a high-cardinality column can reduce clustering effectiveness for your other clustering keys”
↩︎ Aligning clustering keys to how the table is queried“Automatic liquid clustering requires predictive optimization for automatic key selection and clustering operations, and runs asynchronously.”
↩︎ Aligning clustering keys to how the table is queried“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Aligning clustering keys to how the table is queried“Using very different columns triggers a large reclustering operation on the first OPTIMIZE run.”
↩︎ Converting a partitioned table to liquid clustering“To verify that a conversion was successful, run DESCRIBE EXTENDED to see the new clustering columns.”
↩︎ Converting a partitioned table to liquid clustering“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ Key concept“you can redefine clustering keys without rewriting existing data”
↩︎ Exam trap 1“The default behavior does not apply clustering to previously written data.”
↩︎ Exam trap 2“Running the conversion command raises an error.”
↩︎ Exam trap 3“Automatic liquid clustering allows Databricks to intelligently choose clustering keys to optimize query performance, using the CLUSTER BY AUTO clause.”
↩︎ Checkpoint“To benefit from altering clustering columns, you must run OPTIMIZE.”
↩︎ Checkpoint