Z-ordering (data clustering)

What is Z-ordering?

Z-ordering rearranges the rows of a Delta Lake table so that rows you usually query together land in the same files. It maps the values of several columns onto a single space-filling curve, the Z-order curve that gives the technique its name, then stores rows that sit near each other on that curve in the same Parquet files. The Delta Lake documentation describes it as "a technique to colocate related information in the same set of files".

On its own, ordering rows does nothing for speed. It pays off because the query engine can then skip whole files it knows cannot match, like a library that shelves each author's books together so you check one shelf, not every aisle. Z-ordering is the fine-grained counterpart to partitioning, a routine maintenance step on lakehouse tables.

Data skipping and file statistics

Z-ordering helps only because Delta tables carry per-file statistics: for each column, Delta stores the minimum and maximum value, the null count, and the number of rows. When a query filters on a column, the engine pushes that filter down to the file level, a step called predicate pushdown, and skips any file whose min and max range cannot hold a matching value.

This works only when each file covers a narrow range. If every file spans customer IDs from 1 to 999,999, the min and max say nothing and the engine reads them all. You build the ordering with the OPTIMIZE command, for example OPTIMIZE sales ZORDER BY (customer_id, region), which also runs table compaction by merging many small files into fewer larger ones. Now one file might hold only customers 4000 to 4200 for a single region, so a query for one customer touches a few files instead of the whole table.

Z-ordering versus partitioning

Both cut how much data a query reads, but they act at different levels. Partitioning splits a table into separate directories on one low-cardinality column, such as a folder per year or per country, and only helps when you filter on that column. Point it at a high-cardinality column like a timestamp or a customer ID and you get thousands of tiny folders, the small files problem, which makes scans slower rather than faster.

Z-ordering works inside the files rather than the directory tree, and it targets the high-cardinality columns where partitioning breaks down. Use ZORDER BY on a column that is commonly used in query predicates and has high cardinality. Order by no more than the two or three columns you filter on together, because the locality falls off with each extra column.

Liquid clustering as the current recommendation

For new Delta tables, Databricks now recommends liquid clustering instead of Z-ordering. You define clustering keys with CLUSTER BY and the engine keeps the layout up to date on its own. That fixes a rough edge of Z-ordering, which is not idempotent: after each load you rerun OPTIMIZE to re-sort the new files. You can also redefine clustering keys later without rewriting existing data, which neither partitioning nor Z-order allows.

The two do not combine: you cannot use ZORDER with liquid clustering on one table. Tables already running OPTIMIZE ZORDER BY are fine to keep, but for a fresh table liquid clustering is usually the simpler choice.