The 32nd Column: Why Your Z-Order or Clustering Key May Be Doing Nothing

Islam_hoti — Wed, 30 Sep 2026 13:39:18 GMT

You cluster a table on a column, run the query, and nothing improves. The layout command completed successfully. The column is definitely in the filter. The file count in the query profile has not moved.

Before assuming the clustering did not work, check whether statistics exist for that column at all. In a wide table they may not, and nothing in the process will tell you.

How skipping actually works

Delta records statistics for each data file: the minimum value, the maximum value, and the null count per column, plus the record count. At query time the engine compares your predicate against those numbers and discards files that cannot possibly contain a match. A file is never opened, so the cost of that file drops to zero.

Clustering exists to make those ranges narrow. If a file spans the entire range of a column, the engine cannot rule it out. If the file covers a small slice, most queries can discard it immediately.

The whole mechanism rests on one assumption: that statistics exist for the column you are filtering on.

Where that assumption breaks

For Unity Catalog external tables, statistics are collected on the first 32 columns defined in your schema. Not the 32 most useful columns. Not the columns you filter on. The first 32, in declaration order.

Column 33 onward has no statistics. A filter on one of those columns cannot skip a single file. The query returns correct results, runs at full scan cost, and reports no error.

This is easy to walk into. Wide tables grow by accretion. Someone adds a field, then another, then a nested struct whose fields also count toward the limit. Two years later a new filter column sits at position 41 and the table quietly stops being skippable on it.

The documentation is explicit on the consequence for Z-order: Databricks recommends not using ZORDER BY on columns that do not have statistics collected, because it is ineffective and consumes compute for nothing. Liquid clustering reads the same per-file statistics, so the same reasoning applies to a clustering key that sits past the boundary.

One important exception

For Unity Catalog managed tables, predictive optimization does not use a fixed 32-column list. It runs ANALYZE and collects skipping statistics on the columns that actually appear most often in your query filters, so the limit does not apply.

This matters because it means two tables in the same workspace can behave completely differently, and the difference is invisible in the query text. Before debugging, establish which regime your table is in. If it is a managed table with predictive optimization enabled, the column position is not your problem and you should look elsewhere.

How to check

Start with what the table is configured to collect:

SHOW TBLPROPERTIES my_catalog.my_schema.my_table;

Look for delta.dataSkippingStatsColumns and delta.dataSkippingNumIndexedCols. If neither is set and the table is external, you are on the 32-column default.

To see what was actually written rather than what is configured, read the statistics out of the transaction log. Get the path first, then read the add actions:

DESCRIBE DETAIL my_catalog.my_schema.my_table;

Take the location value, then:

SELECT add.stats FROM json.<location>/_delta_log/*.json WHERE add IS NOT NULL LIMIT 5;

The stats field is a JSON document with minValues, maxValues and nullCount objects. The column you care about either appears in there or it does not, and that single check settles the question.

The most direct behavioural evidence is in the query profile. Compare files pruned against files read for a filter on the suspect column, then run the same shape of query filtered on a column you know is early in the schema. If one prunes and the other does not, you have your answer.

Three ways to fix it

Name the columns explicitly. On DBR 13.3 LTS and above this is the cleanest option, because it decouples statistics from schema position entirely:

ALTER TABLE my_table SET TBLPROPERTIES ('delta.dataSkippingStatsColumns' = 'region, order_date, promo_code');

This property supersedes the numeric limit. Be deliberate about the list, since statistics are not free to compute or store.

Raise the count. Works on all runtimes, but remains order dependent, so it is a blunter instrument:

ALTER TABLE my_table SET TBLPROPERTIES ('delta.dataSkippingNumIndexedCols' = '48');

Reorder the schema. Move the columns you filter on to the front. Correct, but it usually means rewriting the table, so it is rarely the pragmatic choice on an existing large table.

The step almost everyone misses

Changing either property does not recompute anything. It changes the behaviour of future writes only. Your existing files keep whatever statistics they were written with, and until they are rewritten your queries will not improve. People make the change, see no difference, and conclude the setting does not work.

On DBR 14.3 LTS and above you can force the recomputation:

ANALYZE TABLE my_table COMPUTE DELTA STATISTICS;

Two related traps

Long string columns are truncated during statistics collection. A column holding URLs, JSON blobs or free text will have min and max values that are effectively useless for skipping, while still costing you to collect. These are good candidates to exclude from the statistics column list rather than to include.

Nested fields count toward the limit individually. For statistics purposes each scalar field inside a struct is treated as its own column, so a struct with fifteen fields consumes fifteen of your slots. A table with twelve top-level columns can already be past the boundary if several of them are nested. Map and array columns cannot have statistics collected at all.

The takeaway

Clustering and statistics are two halves of the same mechanism, and only one of them is visible in the command you run. Layout decides whether a file can be ruled out. Statistics decide whether the engine is able to ask the question in the first place. If the second half is missing, the first half is doing nothing, and nothing in the platform will raise its hand to tell you.

Before choosing clustering keys on a wide table, check that statistics exist for those columns. It takes one command and it occasionally explains months of confusing results.

Has anyone else lost time to this one? And for teams on managed tables with predictive optimization, has letting it choose statistics columns from query history worked better than the columns you would have picked yourself?

topic The 32nd Column: Why Your Z-Order or Clustering Key May Be Doing Nothing in Community Articles

The 32nd Column: Why Your Z-Order or Clustering Key May Be Doing Nothing