Understanding how Liquid Clustering organizes Delta Lake data stored in Amazon S3, reduces unnecessary data scanning, and improves query performance.
Introduction
Imagine you are working on a data engineering project where your company receives millions of customer transactions every day.
All the incoming data is stored in Amazon S3, and Databricks is used to process it using Apache Spark.
Initially, everything works fine.
But as the data grows from a few gigabytes to several terabytes, something interesting starts happening.
A simple query that used to take a few seconds now takes several minutes.
For example:
SELECT *
FROM customer_transactions
WHERE customer_id = 10501;
You are only looking for transactions belonging to one customer, but Spark might still need to examine a large number of data files to find those records.
Now you start thinking:
- Should I partition the table?
- Should I use Z-ORDER?
- How can I organize the data so that queries scan fewer files?
- What happens when the query patterns change?
This is a common performance challenge when working with large Delta Lake tables.
Databricks introduced Liquid Clustering to make data layout optimization more flexible and easier to manage.
In this article, let's understand how it works, why it matters, and how we can use it with Delta Lake tables stored in Amazon S3.
1. First, understand the problem with traditional partitioning
Before understanding Liquid Clustering, let's look at traditional partitioning.
Partitioning is a technique used to divide a large table into smaller logical sections based on one or more columns.
For example, imagine we have transaction data containing:
| transaction_id | customer_id | transaction_date | amount |
|---|---|---|---|
| 101 | 1001 | 2026-01-10 | 500 |
| 102 | 1002 | 2026-01-10 | 700 |
| 103 | 1003 | 2026-01-11 | 900 |
| 104 | 1001 | 2026-01-11 | 300 |
| 105 | 1004 | 2026-01-12 | 400 |
We can partition the table using the transaction date.
CREATE TABLE transactions
USING DELTA
PARTITIONED BY (transaction_date)
AS
SELECT * FROM source_transactions;
The data is physically organized into date-based partitions.
Conceptually, the directory structure could look like this:
transactions/
│
├── transaction_date=2026-01-10/
│ └── part-001.parquet
│
├── transaction_date=2026-01-11/
│ └── part-002.parquet
│
└── transaction_date=2026-01-12/
└── part-003.parquet
Now consider this query:
SELECT *
FROM transactions
WHERE transaction_date = '2026-01-11';
Spark can eliminate partitions that do not match the filter. This is called partition pruning.
Instead of scanning every partition, it can read only the relevant one.
Sounds good, right?
But there is a problem.
What happens when the partition column is not useful?
Imagine your business frequently searches for transactions using customer_id.
SELECT *
FROM transactions
WHERE customer_id = 1001;
Our table is partitioned by transaction date, not customer ID.
Spark may need to scan files across multiple date partitions to find the customer's records.
Another problem occurs when we partition using a high-cardinality column such as customer_id.
If we have millions of customers, we could end up creating an enormous number of small partitions.
This creates metadata overhead and small-file problems.
So, partitioning is useful in the right situations, but choosing the wrong partition column can hurt performance.
2. What is Z-ORDER, and why do we need something better?
Databricks introduced Z-ORDER as a way to improve data skipping.
Instead of dividing a table into separate directory partitions, Z-ORDER organizes related records closer together within data files.
For example:
OPTIMIZE transactions
ZORDER BY (customer_id);
Suppose a table contains millions of customer transactions.
When records are organized using customer_id, queries filtering on that column can skip data files that cannot contain matching records, based on available statistics.
This can reduce unnecessary data scanning.
However, Z-ORDER also requires maintenance.
If your query patterns change, you may need to revisit the columns used for Z-ORDER and run optimization again.
It is also not compatible with Liquid Clustering on the same table.
This brings us to the newer approach.
3. What exactly is Liquid Clustering?
Liquid Clustering is a data layout optimization technique in Databricks that replaces traditional table partitioning and Z-ORDER for supported Delta Lake tables.
Instead of requiring a fixed partition structure, it organizes data around selected clustering columns.
The interesting part is that clustering keys can be changed as your query requirements evolve, without immediately rewriting all existing data.
Let's understand this with our transaction example.
Suppose our table contains:
- 500 million transaction records.
- Millions of different customer IDs.
- Transaction dates.
- Transaction amounts.
- Transaction types.
Our queries frequently filter using:
WHERE customer_id = 1001
And sometimes:
WHERE transaction_date = '2026-01-11'
We can choose these two columns as clustering keys.
CLUSTER BY (customer_id, transaction_date)
Databricks uses these keys to organize data so that related records are more likely to be stored together.
As a result, data skipping can become more effective.
One important point: Liquid Clustering does not create one directory per customer or date. It manages the physical layout of the underlying data files.
Think of it as organizing the contents of a large library so that finding related books becomes easier, without creating a separate building for every book category.
4. How does Liquid Clustering work with AWS S3?
Now let's connect this concept to AWS.
In a typical Databricks-on-AWS architecture, Amazon S3 provides the object storage layer, while Databricks provides the processing and table-management capabilities.
A simplified architecture looks like this:
Data Sources
|
v
Databricks / Spark
|
v
Delta Lake Table
|
Liquid Clustering
|
v
Amazon S3
|
-----------------
| |
Parquet _delta_log
Files Transaction
Log
Let's understand what happens.
Step 1: Data is stored in S3
Our transaction data is stored in Parquet files managed as a Delta Lake table.
Step 2: We define clustering keys
We tell Databricks which columns are important for organizing the data.
For example:
CLUSTER BY (customer_id, transaction_date)
Step 3: Databricks optimizes the data layout
When we run the OPTIMIZE command, Databricks incrementally reorganizes the relevant data files based on the clustering keys.
Step 4: Delta Lake tracks the changes
The new data files and the removal of old active files are recorded through the Delta transaction log.
Step 5: Queries benefit from data skipping
When a query filters on clustering columns, Databricks can use file statistics to avoid reading files that cannot contain matching records.
Remember:
Amazon S3 stores the data. Databricks manages the clustering operation. Delta Lake maintains the table's transactional state.
Liquid Clustering is not an AWS S3 feature. It is a Databricks and Delta Lake capability that works with data stored in S3.
5. Let's implement Liquid Clustering using Databricks SQL
Theory is useful, but let's see how we can implement it.
For this example, assume:
- We have a Databricks workspace running on AWS.
- Our S3 bucket is accessible through a configured Unity Catalog external location.
- We are using a compatible Databricks Runtime.
- We want to create a Delta table containing customer transactions.
Our S3 storage path:
s3://my-company-data-lake/transactions/
Step 1: Create a Delta table with Liquid Clustering
CREATE TABLE main.sales.customer_transactions (
transaction_id BIGINT,
customer_id BIGINT,
transaction_date DATE,
amount DOUBLE,
transaction_type STRING
)
USING DELTA
CLUSTER BY (customer_id, transaction_date)
LOCATION 's3://my-company-data-lake/transactions/';
Here is what each part does:
-
USING DELTAcreates a Delta Lake table. -
CLUSTER BYspecifies the columns used for clustering. -
LOCATIONspecifies the S3 storage location.
For this example, the external location must already be configured in Unity Catalog and you need the appropriate permissions.
Step 2: Insert sample records
Let's insert a few transaction records.
INSERT INTO main.sales.customer_transactions
VALUES
(101, 1001, '2026-01-10', 500, 'ONLINE'),
(102, 1002, '2026-01-10', 700, 'STORE'),
(103, 1001, '2026-01-11', 900, 'ONLINE'),
(104, 1003, '2026-01-11', 300, 'STORE'),
(105, 1002, '2026-01-12', 400, 'ONLINE');
In a real project, this data could come from an API, Kafka, AWS Glue, or another ingestion pipeline.
Step 3: Run OPTIMIZE
Now let's execute:
OPTIMIZE main.sales.customer_transactions;
This is an important step.
Creating a table with clustering keys does not automatically mean all existing data has already been reorganized.
OPTIMIZE triggers clustering work and incrementally reorganizes files where necessary.
Step 4: Query the table
Now execute:
SELECT *
FROM main.sales.customer_transactions
WHERE customer_id = 1001;
Databricks can use the clustering layout and file statistics to skip irrelevant files.
The main objective is to reduce unnecessary data reads.
For large datasets, this can make a meaningful difference in query performance.
6. Liquid Clustering vs Partitioning vs Z-ORDER
This is probably the most important comparison for a Data Engineer.
| Feature | Partitioning | Z-ORDER | Liquid Clustering |
|---|---|---|---|
| Data organization | Directory partitions | Data file organization | Data file organization |
| High-cardinality columns | Often problematic | Supported | Supported |
| Changing columns | Requires restructuring/rewrite | Reconfigure and optimize | Change clustering keys |
| Maintenance | Partition management | OPTIMIZE with ZORDER | OPTIMIZE |
| Small-file risk | Can increase with excessive partitions | Depends on layout and writes | Helps avoid rigid partition layouts |
| Best use | Suitable low-cardinality partitioning scenarios | Existing Z-ORDER workloads | New Delta Lake table optimization |
An important distinction:
Liquid Clustering is not simply a faster version of Z-ORDER. It is a different approach to managing table layout.
Databricks recommends Liquid Clustering over Z-ORDER for new tables.
Also, you cannot combine Liquid Clustering with traditional partitioning or Z-ORDER on the same Delta table.
7. Can we change clustering columns later?
Yes. This is one of the most useful features.
Imagine that initially your business queries mostly use customer_id.
After a few months, the analytics team starts querying heavily using transaction_type.
You may want to change your clustering keys.
You can execute:
ALTER TABLE main.sales.customer_transactions
CLUSTER BY (transaction_type, transaction_date);
Does this immediately rewrite all existing Parquet files?
No.
Changing clustering keys changes the table's clustering configuration. Existing data is not automatically rewritten just because you changed the keys.
To recluster existing data, you need to run the appropriate optimization operation.
For supported Databricks Runtime versions, you can use:
OPTIMIZE main.sales.customer_transactions FULL;
This forces reclustering of existing data according to the current keys.
For large tables, this operation can be expensive, so plan it carefully.
8. What is Automatic Liquid Clustering?
So far, we have manually selected the clustering columns.
But what if we don't know which columns are best?
Databricks also supports Automatic Liquid Clustering.
Instead of manually specifying clustering keys, we can use:
CREATE TABLE main.sales.customer_transactions_auto (
transaction_id BIGINT,
customer_id BIGINT,
transaction_date DATE,
amount DOUBLE
)
USING DELTA
CLUSTER BY AUTO;
Databricks can analyze historical query patterns and select suitable clustering keys.
It can also adapt the selection when query patterns change.
There are some important requirements:
- Automatic Liquid Clustering is available for Unity Catalog managed Delta tables.
- It relies on Predictive Optimization for automatic key selection and clustering operations.
- It is available with Databricks Runtime 15.4 LTS and above for Delta Lake tables.
For an S3-backed external table, do not assume CLUSTER BY AUTO is available just because the data is stored in S3. The managed-table and Predictive Optimization requirements matter.
9. When should you use Liquid Clustering?
Liquid Clustering can be especially useful in these situations:
1. Large Delta tables
When your tables grow to hundreds of millions or billions of records, organizing data effectively becomes important.
2. High-cardinality columns
For example, customer IDs, transaction IDs, or product IDs.
3. Changing query patterns
When business teams frequently change the columns they filter on, changing clustering keys is easier than redesigning a rigid partition structure.
4. Frequently growing tables
When new records are continuously inserted into a table, incremental clustering can help maintain an efficient data layout.
5. Multiple query patterns
When users filter data using different combinations of columns, Liquid Clustering can provide more flexibility than a fixed partitioning strategy.
However, Liquid Clustering is not magic.
For very small tables, the optimization overhead might not be worthwhile. Also, choosing the wrong clustering keys can reduce the expected performance benefit.
Always measure your actual workload.
10. Important things to remember before using it
A few practical points are worth keeping in mind:
- Liquid Clustering is generally available for Delta Lake tables with Databricks Runtime 15.4 LTS and above.
- You can specify up to four clustering keys.
- Choose columns based on actual query filter patterns.
- Clustering keys must have Delta statistics collected.
- Run
OPTIMIZEto trigger clustering. - Changing keys does not automatically rewrite existing data.
- Liquid Clustering cannot be combined with
PARTITIONED BYorZORDERon the same table. - Verify Delta protocol compatibility before using clustered tables with older external clients.
Conclusion
When I first learned about partitioning and Z-ORDER, I thought choosing the right column once would solve most query performance problems.
But real-world data engineering is not always that simple.
Data grows, business requirements change, and query patterns evolve.
A table that works efficiently today might not work as efficiently six months later.
This is where Liquid Clustering becomes interesting.
It provides a more flexible way to organize Delta Lake data without depending on rigid partition structures or manually maintaining Z-ORDER configurations.
When working with Databricks on AWS, the combination of Amazon S3, Delta Lake, and Liquid Clustering gives Data Engineers another useful way to manage large-scale analytical workloads.
The key takeaway: Don't just think about where you store your data. Think about how your data is organized and how your queries access it.
That is where data layout optimization makes a difference.
If you are learning Databricks and Apache Spark, try creating a small Delta table, testing different clustering keys, and comparing query performance. Understanding the actual execution behavior is much more valuable than simply memorizing the syntax.
Top comments (0)