Databricks Community

arthurfr23 · Thursday

I put together a practical guide to Delta Lake table layouts, with SQL examples in Databricks:

When to consider partitioning or Liquid Clustering.
How OPTIMIZE works with partitions, ZORDER, and clustering.
How to compare bytes scanned, query latency, and maintenance costs using the same data and queries.

Read the guide:
https://medium.com/@arthurfr23/choosing-a-delta-lake-layout-when-to-partition-use-liquid-clustering-...

If you’ve moved from partitioning or ZORDER to Liquid Clustering, what changed in your query performance and maintenance costs?

Arthur Ferreira Reis

Satyasai · Thursday

@arthurfr23

The guide provides an in-depth overview of how to test different table layouts in Delta Lake. Transitioning from Hive-style partitioning and Z-Ordering to Liquid Clustering (CLUSTER BY) has significant benefits on both query performance and maintenance overhead. Here’s a breakdown of the advantages:

1. Query Performance Realignment
Eliminating Multi-Column Penalty
With Z-Order the performance penalty kicks in exponentially with each additional column resulting in significantly worse query times for more than 2-3 columns.
Using Liquid Clustering you can cluster by up to 4 keys without significant multi-column penalty. Applying filters on both high and low cardinality columns yields excellent performance gains due to file skipping.
Fixing Skew and High Cardinality Issues
Hive-style partitioning by high cardinality columns (ex: customer_id or date string) has poor performance characteristics due to excessive directory nesting and metadata scanning overhead for skewed values. There is also the additional problem of the small file issue.
Partitioning also leads to extreme skew if you partition by an unevenly distributed column. Liquid Clustering resolves all these issues by keeping the directory structure flat while being able to skip files by leveraging the internal Z-Cube file metadata statistics (stored in transaction log) without incurring additional metadata scanning overhead. This results in roughly the same byte scanned as optimal Z-Order but without the expensive directory tree traversal.
Avoiding Performance Degradation over Time
Queries on recently appended data on Z-Ordered tables quickly degrade in performance until an expensive OPTIMIZE ZORDER is run.
Queries on clustered tables have consistently good performance characteristics since Liquid Clustering is incremental (only rewriting the dirty cubes) and supports eager clustering during writes for tables smaller than 512 GB.

2.Key Operational Metrics to Benchmark
When running the lab please use the following guidelines while comparing the 3 configurations (gld_orders_plain, gld_orders_partitioned, gld_orders_liquid):
1. Compare Byte Scanning Performance using Spark UI
Look for the number of files read vs. pruned files for queries which leverage multiple predicates (ex: WHERE customer_id = 842 AND order_date = ‘2026-03-15’). Liquid Clustering should either match or beat Z-Order while scanning significantly lower number of metadata files.
2. Compare Maintenance Runtime
Run OPTIMIZE on recently appended data and measure the maintenance runtime. OPTIMIZE on liquid clustered tables should be significantly faster and consume fewer DBUs compared to OPTIMIZE ZORDER BY.

Liquid Clustering vs Partitioning in Delta Lake
This video shows how partitioning compares to Liquid Clustering in practice. It’s a great complement to the benchmarking guidance described in this post.