Understanding Z-Ordering

When you index data you don’t often think “I wonder where all this data physically resides”. Or maybe you do, but anyway, when working with massive datasets, even the smartest indexes and metadata can’t help if your data is scattered all over the storage layer.

Z-Ordering is a physical data layout technique that clusters related column values together on disk, so that query engines can skip more data and scan less. As the data volume grows it’s impact and utility increases. It’s especially useful in lakehouse platforms like Databricks Delta Lake, but the underlying concept applies to any system where data skipping is possible.

What Exactly Is Z-Ordering?

Z-Ordering is a multi-dimensional clustering technique based on a Z-order curve (also called a Morton curve).
It’s a space-filling curve that maps multi-column values into a single one-dimensional value while preserving spatial locality. That was a lot of words. Let’s try and break this down.

Key idea:
If two rows have similar values in multiple columns, Z-Ordering places them physically close together in storage.

Why Not Just Sort by One Column?

Sorting by a single column works well if most queries filter on that column.
But in analytics, queries often filter by two or more dimensions, e.g

SELECT * FROM events WHERE country = 'US' AND event_date BETWEEN '2025-07-01' AND '2025-07-15';

SELECT * FROM events WHERE country = 'US' AND event_date BETWEEN '2025-07-01' AND '2025-07-15';

If data is only sorted by event_date, all US rows will be scattered across many files.
Z-Ordering interleaves bits from both columns so that similar (country, event_date) pairs are grouped.

How Z-Order Mapping Works

Imagine you have two columns:

• Column A: country_id (binary form: c1c2c3...)
• Column B: event_day (binary form: d1d2d3...)

A Z-order curve interleaves their bits:Z-value = c1 d1 c2 d2 c3 d3 ...

Rows with close Z-values will be close in multi-dimensional space.

Practical Example

Let’s say you have event data: [country, event_date, revenue]

US 2025-07-01 100.00

UK 2025-07-01 120.00

US 2025-07-02 150.00

UK 2025-07-02 110.00

If sorted by Z-order on [country, event_date], the layout might be:

US 2025-07-01

US 2025-07-02

UK 2025-07-01

UK 2025-07-02

This keeps related country/date combinations together.

In Databricks Delta Lake

Delta Lake implements Z-Ordering as a data file reorganization step:

OPTIMIZE events ZORDER BY (country, event_date);

• The optimizer computes a Z-value for each row.
• Data files are rewritten so rows with similar Z-values are stored together.
• Data skipping based on min/max stats becomes more effective because relevant rows are packed into fewer files.

📖 Reference: Databricks Z-Ordering Docs

What Are The Benefits?

• Faster multi-column queries: especially with data skipping
• Improved metadata pruning: fewer files to read
• Compatible with columnar formats: (Parquet, ORC)
• Transparent to queries: no need to change the underlying SQL

Limitations

• Rewrite cost –  OPTIMIZE is an expensive operation
• Best for stable data – frequent updates reduce benefits
• Needs clustering columns chosen carefully – irrelevant columns waste effort

When to Use Z-Ordering

• Queries filter on two or more columns together
• Large datasets with many small files (data skipping potential)
• Relatively static historical data (to avoid constant reclustering)

Z-Ordering is like arranging books in a large library so that any combination of two categories (e.g., genre + author) puts related books on the same shelf.

It’s not free, but for the right datasets, it can turn multi-column queries from multi-minute scans into sub-second lookups.