In Delta Lake, what does ZORDER BY add to an OPTIMIZE command?

## The problem: skipping, not scanning Every data file in a Delta table has an `add` action in `_delta_log` that carries per-file statistics: row count, and min, max and null count for each indexed column. When a query says `WHERE user_id = 8812`, the planner reads those statistics and drops any file whose `user_id` range cannot contain 8812. That is **data skipping**, and it is the main reason a Delta query on a big table can finish quickly. Skipping only works if the ranges are narrow. If rows arrive in event-time order, each file holds a near-random spread of `user_id` values, so nearly every file's min/max range spans nearly the whole domain and no file can be excluded. The physical layout, not the statistics mechanism, is the limiting factor. ## What ZORDER BY does about it ```sql OPTIMIZE events WHERE event_date = '2026-08-20' ZORDER BY (user_id, device_id); ``` Z-ordering interleaves the bits of the values of the listed columns to produce a single ordering key — a space-filling curve — and writes rows out in that order. The effect is that rows close in *any* of the Z-ordered dimensions tend to end up in the same file. A one-dimensional sort would make one column's ranges perfect and leave the others as scattered as before; Z-order accepts slightly worse skipping on each column in exchange for useful skipping on all of them. After the rewrite, the new files' statistics are much tighter on the Z-ordered columns, and filters on those columns prune far more files. ## Choosing the columns Three rules decide whether Z-ordering earns its cost: 1. **Pick columns that appear in query filters.** Clustering on a column nobody filters on rewrites the table for nothing. 2. **Pick high-cardinality columns.** A boolean or a five-value status column has nothing to gain from clustering; if you need coarse separation on a low-cardinality column, partition on it instead. Z-order and partitioning compose — you typically partition by date and Z-order within the partition. 3. **Keep the list short.** Each additional column dilutes the curve: the ordering must satisfy more dimensions at once, so per-file ranges widen for all of them. One to three columns is the practical range; five is usually worse than two. ## The statistics trap Delta collects min/max statistics only for the first `delta.dataSkippingNumIndexedCols` columns of the schema — 32 by default. Z-ordering by a column past that boundary produces a beautifully clustered layout with **no statistics to prove it**, so the planner still cannot skip anything. The fixes are to raise the property, or to move the column earlier in the schema (a Delta column-order change), then rewrite. This is a very common "we Z-ordered and nothing improved" cause, and a good thing to mention in an interview. Long string columns are also a poor fit: their statistics are truncated, and high-entropy identifiers dominate the curve. ## Cost, and why re-running is expensive Bin-packing only touches files below the target size, so re-running `OPTIMIZE` is nearly free. Z-ordering is different: to place a new file's rows correctly relative to existing rows, it must re-cluster the whole partition. If nothing changed in a partition since the last run, re-running is a no-op — but as soon as a partition receives new data, the next Z-order run rewrites that partition end to end. Cost therefore scales with **partition size**, not with the size of the increment, which is exactly the wrong shape for a table receiving continuous small writes. That is the operational reason to scope the command with a `WHERE` on partition columns, run it on a cadence that matches how often queries actually benefit, and account for the write amplification (and, for hourly-partitioned tables, the sheer number of partitions). ## Relationship to liquid clustering Newer Delta versions offer liquid clustering, defined with `CLUSTER BY` on the table rather than as an argument to a single command. It clusters incrementally, lets you change the clustering keys without rewriting existing data, and is applied by running plain `OPTIMIZE`. It is mutually exclusive with both `ZORDER BY` and partitioning on the same table. For a table under constant maintenance, that incremental property is precisely what Z-ordering lacks, so on a runtime that supports it, liquid clustering is now the default recommendation and `ZORDER BY` is the older mechanism you will still meet in existing pipelines. ## How to verify Compare files scanned before and after for a representative filter — the query plan reports files read versus files pruned. Do not judge Z-ordering by wall-clock time on a warm cache; judge it by the number of files the planner eliminated.