Z-ordering (Z-order clustering) co-locates related values across multiple columns inside data files so that filters on those columns skip more file chunks. It is a multi-dimensional clustering technique popularized in Delta Lake (OPTIMIZE ... ZORDER BY).
Problem first. Partitioning helps when you filter on partition keys (often dt). Many queries also filter on user_id or product_id *inside* a day partition. Without clustering, matching rows are scattered across every file, so min/max stats rarely skip anything.
Before: user_id values mixed randomly across files file1 min/max user_id: 1 .. 9_000_000 ← almost never skippable file2 min/max user_id: 1 .. 9_000_000 After Z-ORDER BY user_id (simplified idea): file1 min/max: 1 .. 100_000 file2 min/max: 100_001 .. 200_000 → WHERE user_id = 150_000 can skip file1
When to use it
- High-cardinality filter columns that are not good partition keys
- Multiple filter columns (Z-order interleaves several dimensions)
- After large merges / before heavy read workloads
Caveats
- Rewrites data (compute + write cost)
- Helps skipping via stats; it is not a B-tree index
- Pick a few important columns, not twenty
Interview tip: "Z-order improves data skipping for non-partition filters by clustering values so file-level min/max become selective."