Author(s): chakshu_salgotra

Originally published on Towards AI.

Part X — Z-Ordering and Data Clustering Explained: Why Your Partitioned Table Still Scans Everything

Data skipping, min/max statistics, space-filling curves, and liquid clustering — the file-layout mechanics that decide whether your query reads 40 files or 40,000

Part I | Part II | Part III | Part IV | Part V | Part VI | Part VII | Part VIII | Part IX | Part X

The $18,000 Query That Read 96% Dead Data

An 11TB Delta Lake table of payment events, partitioned by event_date, 800+ days of history, roughly 46,000 Parquet files. The fraud team ships a new investigation dashboard with one core query: all events for a given merchant_id over the last 90 days.

The query is filtered, indexed-looking, innocent:

SELECT * FROM payment_events
WHERE event_date >= current_date - INTERVAL 90 DAYS
 AND merchant_id = 'M-88412';

Partition pruning works perfectly — the engine narrows 800 partitions down to 90. Then it reads every single file inside those 90 partitions: 5,100 files, 2.3TB scanned, to return 41MB of matching rows. P95 dashboard latency: 96 seconds. The fraud team runs this shape of query ~1,200 times a day. On pay-per-scan pricing, that’s roughly $18,000/month to repeatedly read data that is 96%+ irrelevant to every query.

The reflexive fix — partition by merchant_id too — is worse. With 2.1 million merchants, sub-partitioning explodes the table into tens of millions of KB-sized files, and now every query dies of small-file overhead and metadata bloat instead.

So: what are Z-ordering and data clustering, what do they actually do to bytes on disk, and why do they cut a 5,100-file scan down to double digits when partitioning can’t?

The Mechanic Underneath: Data Skipping Lives or Dies on File-Level Min/Max Stats

Modern table formats — Delta Lake, Apache Iceberg, Apache Hudi — keep min/max statistics per column, per file, in the table metadata (the Delta transaction log, Iceberg manifest files). Before reading anything, the engine compares your filter predicate against those ranges:

• File’s merchant_id range is [M-00001, M-04500], predicate wants M-88412 → file skipped, zero I/O.
• File’s range is [M-00001, M-99999] → range might contain the value → file must be read.

This is data skipping (file pruning), and it is the single highest-leverage read optimization in a lakehouse. But notice what it depends on: the stats are only useful if each file’s min/max range is narrow.

Here’s the failure mode. Ingestion writes events in arrival order. Every merchant transacts every day, so every file written on any given day contains a near-uniform sample of all merchant IDs:

Unclustered files (arrival order)
 file_001 merchant_id: [M-00003 .. M-99871]
 file_002 merchant_id: [M-00011 .. M-99904]
 file_003 merchant_id: [M-00007 .. M-99988]
 ...
 predicate M-88412 overlaps ALL ranges
 → 0 files skipped, full scan

Every file’s range spans essentially the whole key space. The stats exist, the engine checks them, and they prune nothing. This is why the 90-partition query still read 5,100 files: partition pruning and data skipping are two different mechanisms, and only the first one was doing any work.

Data clustering is the fix at the physical layer: co-locate rows with similar values into the same files, so per-file min/max ranges become narrow and disjoint, and data skipping starts eliminating files instead of shrugging at them.

Linear Sort: Perfect for One Column, Useless for the Second

The obvious clustering move is a global sort on merchant_id before writing. And for single-column predicates it's optimal — after sorting, file ranges tile the key space without overlap:

Sorted by merchant_id
 file_001 [M-00001 .. M-04211]
 file_002 [M-04212 .. M-08933]
 ...
 file_940 [M-88012 .. M-88997] <-- only hit
 → 5,099 of 5,100 files skipped

The problem appears the moment a second filter column shows up — say the risk team queries by card_country, not merchant_id. A linear (lexicographic) sort on (merchant_id, card_country) orders perfectly by the first column, but the second column's values are scattered: within each narrow merchant_id range, card_country still spans its full domain. File-level min/max on card_country goes back to [AD .. ZW] everywhere. A linear sort gives you data skipping on the leading column and almost nothing on the rest.

That’s the actual problem Z-ordering solves: clustering on multiple dimensions at once, trading a little pruning power on each column for usable pruning power on all of them.

What Z-Ordering Actually Is: Bit Interleaving on a Space-Filling Curve

Z-ordering maps multi-dimensional values onto a one-dimensional sort key using a Z-order curve (Morton curve) — a space-filling curve that preserves locality: points close together in N-dimensional space tend to land close together on the curve.

The mechanic is bit interleaving. Take two columns’ values as binary and weave their bits:

merchant_bits : 1 0 1 1
 country_bits : 0 1 0 0
 ↓ interleave
 z_value : 10 01 10 10

Sort the table by that interleaved z_value and write files in that order. Walking the curve traces a recursive "Z" pattern through the 2-D key space:

country →
 m 0 1 4 5
 e 2 3 6 7
 r
 c 8 9 12 13
 h 10 11 14 15
 ↓
 (each Z quadrant → one file)

Each file now covers a compact rectangle in the multi-dimensional space rather than a full-width stripe. Concretely, per-file stats after Z-ordering on both columns look like:

file_017 merchant: [M-24000..M-31999]
 country : [DE..FR]

Both ranges are narrow. A predicate on either column — or both — prunes effectively. The trade you’re making, and it’s worth stating plainly:

• Linear sort: ~100% pruning power on column 1, ~0% on columns 2..N.
• Z-order: strong (not perfect) pruning on each of the 2–4 clustered columns simultaneously.
• The curse of dimensionality still applies: every added Z-order column dilutes the locality of all the others. Past 3–4 columns, per-file ranges widen until skipping decays toward useless. Cluster on the columns that actually appear in WHERE clauses, ranked by frequency — not on everything "just in case."

Iceberg also supports the Hilbert curve as an alternative sort strategy — it takes no diagonal jumps between quadrants, so it preserves locality measurably better than the Z curve at higher dimension counts. Same concept, better constant factor.

Clustering vs. Partitioning: Not Competitors, Different Layers

A distinction that trips up even experienced teams:

• Partitioning is coarse physical separation: distinct directories/file groups per partition value. It’s metadata-cheap to prune but blows up on high-cardinality columns (the 2.1M-merchant small-file disaster).
• Clustering (Z-order) is ordering within the files: no directory explosion, no cardinality limit, works precisely where partitioning can’t. A merchant_id with millions of values is a terrible partition key and an excellent Z-order key.
• The production pattern is both: partition by the low-cardinality time dimension (event_date), Z-order by the high-cardinality predicate columns (merchant_id, card_country) inside each partition.

The Fix, and What It Cost

In Delta Lake, Z-ordering runs through OPTIMIZE — it rewrites files clustered by the interleaved key:

-- Naive: partition by merchant_id → 2.1M partitions, small-file death
-- Optimized: keep date partitioning, Z-order the high-cardinality predicates
OPTIMIZE payment_events
WHERE event_date >= current_date - INTERVAL 90 DAYS
ZORDER BY (merchant_id, card_country);

The Iceberg equivalent via a compaction procedure, using the Hilbert-capable sort rewrite:

-- Rewrites data files sorted along the space-filling curve
CALL catalog.system.rewrite_data_files(
 table => 'db.payment_events',
 strategy => 'sort',
 sort_order => 'zorder(merchant_id, card_country)'
);

Result on the fraud dashboard query: files read dropped from 5,100 → 38, bytes scanned from 2.3TB → 9.4GB, P95 latency from 96s → 3.1s. Scan-based cost for that workload fell by roughly 99% — against a one-time OPTIMIZE rewrite of the hot 90 days that cost about 40 minutes of cluster time.

The Catch: Z-Order Decays, and OPTIMIZE Doesn’t Come Free

Two operational realities the docs undersell:

• New writes are unclustered. Every append lands in arrival order again, so the table drifts back toward overlapping ranges. Classic ZORDER BY is a full-file-group rewrite each time — expensive to run nightly, wasteful to re-sort data that hasn't changed.
• Write amplification is the real bill. Re-Z-ordering a partition rewrites all of it, even if 2% of rows are new.

This is exactly the problem liquid clustering (Delta Lake / Databricks) was built for — it replaces both hive-style partitioning and static ZORDER with incremental, redistributable clustering:

-- Naive: recurring full OPTIMIZE ZORDER BY rewrites (write amplification)
-- Optimized: declarative clustering, maintained incrementally on write
CREATE TABLE payment_events (
 event_date DATE,
 merchant_id STRING,
 card_country STRING,
 amount DECIMAL(18,2)
)
CLUSTER BY (merchant_id, card_country);

OPTIMIZE on a liquid-clustered table only touches unclustered or poorly clustered files, and the clustering keys can be changed later without rewriting the table. Snowflake's clustering keys with the background reclustering service and BigQuery's CLUSTER BY are the same idea as managed services: the warehouse continuously maintains narrow per-block ranges (Snowflake's equivalent stat unit is the micro-partition) so pruning stays effective as data lands.

The end-to-end read path, with each pruning layer doing its job:

Takeaways

• Data skipping is a stats game. Min/max pruning only works when per-file value ranges are narrow; arrival-order ingestion makes every file span the whole key space and silently disables it.
• Partitioning and clustering solve different cardinalities. Partition on low-cardinality dimensions (date); Z-order/cluster on high-cardinality predicate columns (IDs). Never partition by a million-value key.
• Z-ordering = bit-interleaved space-filling curve. It trades perfect single-column pruning for strong multi-column pruning — effective up to ~3–4 columns, then locality dilution wins.
• Choose clustering columns from query logs, ranked by WHERE-clause frequency, not by intuition.
• Static Z-order decays under appends. Budget for OPTIMIZE cadence and its write amplification — or use liquid clustering / managed reclustering to maintain layout incrementally.

If this saved you a 3 AM pager incident, follow me on Medium for Twice a week for the next 3 Months of Advanced Data Engineering & AI. Let’s connect on LinkedIn and X.

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI