What you will be able to do
- Explain what liquid clustering is and which older layout techniques it replaces
- Describe how a clustered layout lets filtered queries skip files
- Pick out the table and query patterns that benefit most from liquid clustering
- Choose between liquid clustering and partitioning, based on table size and how the table is accessed
Key concept
Liquid clustering — A Delta Lake table layout where Databricks groups data files by the clustering keys you choose. Queries that filter on those keys can then skip files that cannot match. Unlike partitioning, you can change the keys later without rewriting the existing data.
1.What liquid clustering is
Say a table holds billions of sales rows and most dashboards filter it by customer_id or order_date. If those rows are scattered across every data file, each query has to read almost the whole table. The fix is to change how the data is laid out on storage, not to rewrite the query.
Liquid clustering is Databricks' layout optimization for this. You name one or more clustering keys, and Databricks organizes the table's data around them. Databricks says liquid clustering "simplifies table management and optimizes query performance by automatically organizing data based on clustering keys."
It replaces two older techniques: partitioning a table into fixed directories, and running ZORDER to co-locate values. The big practical difference is flexibility. With partitioning, you are stuck with the layout you picked at creation. With liquid clustering, you can redefine the keys later without rewriting the data already in the table, so the layout can change as your queries change.
Databricks now treats liquid clustering as the default choice, not a special-case tuning step. It recommends liquid clustering for all new tables, including streaming tables and materialized views. For Delta Lake tables, it is generally available in Databricks Runtime 15.4 LTS and above.
Checkpoint 1 of 5· Check yourself
Which two older layout techniques does liquid clustering replace?
The docs define liquid clustering as replacing table partitioning and ZORDER. Bin-packing, caching and VACUUM are separate mechanisms.
“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”Source: docs.databricks.com
Sources1
2.Why a clustered layout makes filters faster
The speed-up depends on a mechanism that runs whether or not you cluster: data skipping. When data is written to a Delta Lake table, Databricks automatically collects statistics for each file: the minimum and maximum values, null counts and total records. At query time, Databricks reads those statistics to skip irrelevant files and speed up queries.
Skipping only works well when a file's minimum and maximum for the filtered column are close together. If every file covers almost the full range of customer_id, no file can be ruled out. Clustering groups rows with similar key values into the same files, so a filter on a clustering key matches far fewer files. Databricks' BI data-preparation guidance describes liquid clustering as improving "query performance with file and data skipping." Its action item for analysts is to apply it to large tables with filter patterns.
Statistics only help when they can rule a file out. If rows for every customer are spread across all files, each file's min/max range for customer_id will include 42, so no file can be skipped. Clustering on customer_id gives each file a narrower range, which is what makes the statistics useful.
Checkpoint 2 of 5· Check yourself
What does Databricks use at query time to decide which files a filtered query can ignore?
Delta Lake collects per-file statistics (minimum and maximum values, null counts, total records) on write. Databricks uses them at query time to skip irrelevant files.
“at query time to skip irrelevant files and speed up queries”Source: docs.databricks.com
3.Tables that benefit most
Databricks recommends liquid clustering for every new table, but it names some scenarios that gain the most. For an analyst, the first one matters most: queries that filter on high-cardinality columns, meaning columns with many distinct values, such as IDs.
| Scenario | What it looks like in practice |
|---|---|
| Queries that filter on high cardinality columns | Dashboards filtering on IDs or other columns with many distinct values |
| Tables with heavy data skew | A few key values account for most of the rows |
| Fast growing tables that require maintenance and tuning effort | Tables whose layout would otherwise need constant manual tuning |
| Tables with concurrent write requirements | Several writers updating the same table |
| Tables with varied or changing access patterns | Filter columns that differ between teams or change over time |
| Tables where a typical partition key might return results from too many or too few partitions | Partitioning would produce either a few huge partitions or many tiny ones |
There is one more case where liquid clustering is the only option. If you filter on a field inside a struct column, such as struct_col.field, you cannot partition by it. Liquid clustering accepts a struct field as a clustering key, which makes it the only way to data-skip on that field without first extracting it into a top-level column.
Checkpoint 3 of 5· Check yourself
An analyst's slowest dashboard query filters a multi-terabyte table on transaction_id, which has millions of distinct values. Which description best explains why this table is a strong candidate for liquid clustering?
Filtering on high-cardinality columns heads the docs' list of scenarios that benefit from clustering. Clustering also works for low-cardinality columns and for batch tables.
“Queries that filter on high cardinality columns.”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A data analyst manages a 40 TB Delta table that is queried mainly through filters on `customer_id`, a column with millions of distinct values. Query Profile shows most runtime is spent scanning files that do not match the filter. Which change to the table's physical layout is most likely to speed up these queries?
Correct answer: A — Enable liquid clustering on the table using `customer_id` as the clustering key so files are organized for skipping on that column
- A. Liquid clustering on the high-cardinality filter column groups related rows together so the engine can skip non-matching files during a scan, which directly targets the wasted scanning shown in Query Profile. It also avoids the small-file and skew problems that partitioning on a high-cardinality column would create.
- B. Partitioning on a column with millions of distinct values creates an excessive number of tiny partitions, which hurts performance instead of helping it and is exactly the scenario liquid clustering is meant to replace.
- C. A one-time Z-order pass degrades as new data is written because Z-ordering does not incrementally maintain itself the way liquid clustering does, so filter performance would drift back toward a full scan over time.
- D. Adding compute does not reduce the amount of data that must be read from storage; the underlying problem is a lack of data skipping, so more warehouse capacity only scans the same unnecessary files faster rather than avoiding them.
4.Liquid clustering versus partitioning
Many people assume that a big table needs partitioning. Databricks' guidance points the other way. Partitioning has a hidden cost: an ineffective strategy can hurt query performance, and fixing it means a full rewrite of the data, which is expensive and slow on a large table. Liquid clustering works for both low- and high-cardinality columns and avoids the fixed partition boundaries and small-file problems of static partitioning.
| 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 performance improvements |
You also cannot mix the approaches on one table. Clustering is not compatible with partitioning or ZORDER, and the ZORDER BY clause of OPTIMIZE cannot be used on a table with liquid clustering. Choosing liquid clustering means letting it handle the whole layout.
Checkpoint 5 of 5· Check yourself
A table already uses liquid clustering. A colleague suggests also running OPTIMIZE ... ZORDER BY (eventType) to speed up filters on a second column. What happens?
Clustering is not compatible with ZORDER. To speed up filters on a second column, add that column as a clustering key.
“You can't use this clause on tables that use liquid clustering.”Source: docs.databricks.com
Sources4
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A very large table should be partitioned on its main filter column to speed up queries.Why is that wrong?
For tables between 1 TB and 100 TB, Databricks says to use liquid clustering instead of partitioning. Above 100 TB, it still recommends trying clustering first.
Covered in Liquid clustering versus partitioning
2.You can combine liquid clustering with partitioning or ZORDER for extra speed.Why is that wrong?
Liquid clustering cannot be combined with partitioning or ZORDER on the same table. Put the extra filter columns in the clustering keys instead.
Covered in Liquid clustering versus partitioning
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/clusteringOfficial docs
“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ What liquid clustering is“Databricks recommends liquid clustering for all new tables, including streaming tables and materialized views.”
↩︎ What liquid clustering is“Queries that filter on high cardinality columns.”
↩︎ Tables that benefit most“Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER.”
↩︎ Key concept“Clustering is not compatible with partitioning or ZORDER.”
↩︎ Exam trap 2“you can redefine clustering keys without rewriting existing data”
↩︎ Prediction - 2.
“at query time to skip irrelevant files and speed up queries”
↩︎ Why a clustered layout makes filters faster - 3.
“Improves query performance with file and data skipping.”
↩︎ Why a clustered layout makes filters faster - 4.https://docs.databricks.com/aws/en/tables/partitionsOfficial docs
“Liquid clustering is the only way to data-skip on a struct field without first extracting it into a top-level column.”
↩︎ Tables that benefit most“An ineffective partitioning strategy might negatively affect query performance and require a full rewrite of data to fix.”
↩︎ Liquid clustering versus partitioning“avoids the fixed partition boundaries and small-file issues common with static partitioning”
↩︎ Liquid clustering versus partitioning“With more than 1 TB to 100 TB of data, use liquid clustering instead of partitioning.”
↩︎ Exam trap 1“With more than 1 TB to 100 TB of data, use liquid clustering instead of partitioning.”
↩︎ Prediction
Also cited
“You can't use this clause on tables that use liquid clustering.”
↩︎ Checkpoint