Z-ordering and Delta Data Skipping

Nobody runs OPTIMIZE … ZORDER BY and measures nothing. The job takes forty minutes, rewrites the table, reports a healthy numFilesAdded, and the dashboard query is exactly as slow as it was. The command worked. The layout it produced does not answer the question you are asking it.

Delta does not have an index. What it has is four numbers per column per file — minValues, maxValues, nullCount, numRecords — written into the transaction log beside every add action. A query reads that list, discards every file whose range cannot contain a matching row, and scans the rest. Everything called "clustering" is an attempt to make those ranges narrow.

Z-ordering makes them narrow in several dimensions at once, and that is not free. Add a second key and every file's range on the first key widens. The panel builds a real table, lays it out with the real algorithm, computes the real per-file statistics and runs a predicate against them. Add and remove keys and watch the file count move.

Start with ZORDER BY (event_date, user_id) and a point lookup on user_id: ten files of thirty-two are opened. Now untick event_date. The layout collapses to a single dimension and the query reads one file. You did not make Z-ordering better; you stopped asking it to serve a second query. Then tick region_id and device_model_id on as well, and four keys turn the same point lookup into half the table.

Why the second key costs the first one

ZORDER BY (a, b) does not sort by a then b. It does this, and you can read it in the Delta source:

• range_partition_id(col, 1000) replaces each key value with the rank bucket it falls in — a number from 0 to 999, derived from the distribution, not from the value.
• interleave_bits(id_a, id_b, …) braids those numbers together bit by bit: the top bit of a, then the top bit of b, then the second bit of a, and so on.
• The rows are range-partitioned and sorted on that single braided value, and written out as files.

Sorting on the braid means every key gets every other bit. Call the number of files F. With one key, each file covers about 1/F of that key's range — as tight as the file size allows. With two keys, each file is a square-ish tile of a two-dimensional space, so it covers about 1/√F of each key's range. With four keys it is a hypercube and each side is 1/F^¼. At 1,024 files that is the difference between one file per key value and roughly a sixth of the table. It is what the Databricks guidance means by "the effectiveness drops with each extra column," and why liquid clustering caps you at four keys and warns that on tables under 10 TB, filtering with four keys performs worse than filtering with two.

The panel prints the measured span for each key, so you do not have to take the geometry on faith. Watch the span line as you tick keys on: each key you add widens the ranges of the keys already there. Nothing is lost — the query on the new key gets faster — but the budget is fixed and you are dividing it.

This is also why ORDER BY is not simply worse. Pick the ORDER BY (first key only) layout and point the query at that same key: two files of thirty-two, tighter than any multi-key Z-order will manage. Then point it at a different column and nothing is skipped at all — thirty-two of thirty-two. One-dimensional sort is the right answer when one column dominates your predicates; Z-ordering is the right answer when two or three do, and it is strictly the wrong answer when one does.

The column that has no statistics at all

Set the filter column to device_model_id. Every file is read, no matter what the layout is or which keys you chose. Now drag dataSkippingNumIndexedCols from 32 to 34 and the query becomes selective.

By default Delta writes min/max statistics for the first 32 columns of the schema, in schema order. Column 33 onwards gets nothing. A predicate on such a column cannot skip a single file, and — this is the part that wastes afternoons — ZORDER BY on such a column still runs, still takes forty minutes, still reports success, and still produces a table where that column skips nothing. The layout is correct; there is no statistic to read it with. Databricks' own guidance is explicit: do not Z-order columns that have no statistics collected.

Three things follow that are worth knowing before you debug this:

• delta.dataSkippingNumIndexedCols depends on column order. Adding a column to the middle of a wide schema can silently push another one past the boundary. Since DBR 13.3 LTS, delta.dataSkippingStatsColumns names columns explicitly and supersedes the count, which is what you want on a wide table.
• Changing either property does not recompute anything. It only affects files written afterwards. On DBR 14.3 LTS and above, ANALYZE TABLE t COMPUTE DELTA STATISTICS backfills the existing files.
• Long strings are truncated during statistics collection, so a min/max on a wide text column is a prefix comparison. It still skips, but far less than the cardinality suggests, and it costs bytes in every commit.

High cardinality, incidentally, is a reason to Z-order a column, not a reason to avoid it. A column with six distinct values cannot produce narrow file ranges no matter how you sort — tick only region_id in the panel and see how little a perfect layout buys. Cardinality below the file count is the real disqualifier, and for a column like that, a partition column or nothing at all is the better answer.

Compaction and clustering are different jobs

OPTIMIZE without ZORDER BY is bin-packing: it coalesces small files into larger ones and does not reorder rows. It is idempotent, so running it twice does nothing the second time. Drag rows per file upwards in the panel to see what it buys and what it costs: fewer, larger files mean less metadata and fewer object-store round trips, but each file's min/max range is wider, so a selective query reads more rows to find the same answers. There is no file size that is right for both a scan-heavy and a lookup-heavy workload on the same table.

OPTIMIZE … ZORDER BY is a different operation wearing the same verb. It rewrites row placement, it is not idempotent, and it rewrites files that plain bin-packing would have left alone. It is also the one maintenance command that conflicts with everything: because it moves rows between files, a concurrent MERGE loses even on a table with row-level concurrency enabled. That is the single row in the Databricks conflict matrix that reads "can conflict when ZORDER BY is used" — see optimistic concurrency for why the row-level machinery cannot rescue it.

Liquid clustering replaces both partitioning and Z-ordering, and the difference that matters here is incrementality. Z-ordering rewrites the whole partition it touches; clustering tracks which files have already been clustered and only rewrites what is new, which is why Databricks can recommend running OPTIMIZE hourly on a clustered table and daily on a Z-ordered one. It also uses a Hilbert curve rather than a Z-curve for more than one key — Hilbert has no long jumps between adjacent points, so locality is better for the same number of dimensions — and it lets you change keys with ALTER TABLE … CLUSTER BY without rewriting history. The dimensionality tax in the panel is not repealed by any of that. Four clustering keys dilute exactly the way four Z-order keys do; the Databricks documentation says so directly.

Checking it yourself

The number you want is in the query profile, not in the OPTIMIZE output. In the Databricks SQL query profile, open the scan node and read files pruned against files read. If pruned is near zero on a selective predicate, skipping is not happening and the layout is irrelevant until you find out why.

To check whether the statistics exist at all, read them straight out of the log:

SELECT path, get_json_object(stats, '$.numRecords') AS rows,
       get_json_object(stats, '$.minValues.user_id') AS min_user,
       get_json_object(stats, '$.maxValues.user_id') AS max_user
FROM   json.`/path/to/table/_delta_log/*.json`
WHERE  add IS NOT NULL

A NULL in min_user means no statistic was written for that column — check the column's ordinal position against SHOW TBLPROPERTIES t. If the values are present but nearly identical across every file, the statistics exist and the layout is the problem. Those two diagnoses look the same from the query plan and have completely different fixes.

One more trap worth naming: none of this applies to a join. Skipping needs a literal to compare against, and a join key is not known until runtime. Databricks closes that gap with dynamic file pruning, which pushes the build side's observed range down into the probe side's file filter — the same runtime-statistics idea as adaptive query execution, applied to file lists instead of shuffle partitions. If your slow query is a join and not a filter, Z-ordering the join key does nothing until dynamic file pruning kicks in — and it will not kick in unless the probe-side table clears spark.databricks.optimizer.deltaTableSizeThreshold (10 GB by default) and deltaTableFilesThreshold (10 files). On a two-gigabyte dimension table that you just Z-ordered, the feature is simply switched off.

Next: liquid clustering, which is where this is all heading, and why your OPTIMIZE window breaks your MERGE window. For the statistics that drive plan choice rather than file pruning, see Spark's table statistics.