Data skipping &
Z-ordering.

What it is

Data skipping is how Delta makes a query fast by not reading files it doesn't need to. Every write records four per-file statistics per column in the transaction log: the MIN, the MAX, the NULL count, and the file's total record count. When you run WHERE amount > 5000, the engine checks each file's amount range first — if a file only ranges 0–100, it skips the whole file without opening it.

The catch: skipping only works if matching rows are physically near each other. If they're scattered, every file's range overlaps your predicate and nothing can be skipped. Z-ordering (ZORDER BY) fixes that — it rewrites the data along a space-filling curve so related rows land in the same files, giving tight, non-overlapping min/max ranges.

Analogy
Data skipping is the index tabs on a filing cabinet: before pulling a folder you glance at the label range ("IDs 1000–1999"); if you want 5500 you don't even open that drawer. Z-ordering is re-shelving the warehouse so items bought together sit on one shelf — the picker walks far fewer aisles.

Real-world use case
An 8 TB orders table queried daily with WHERE order_status = 'RETURNED' AND region = 'EU'. OPTIMIZE … ZORDER BY (order_status, region) colocates those rows into a handful of files so the rest are skipped. (For a new table you'd use liquid clustering instead.)

Why it matters

• Reading less data is the biggest query win. On a billion-row table, scanning 5% of files vs 100% is the difference between seconds and minutes — and between a small bill and a large one.
• Stats are free and already there. Delta collects MIN/MAX/NULL/count automatically on every write. Data skipping is on by default — you don't enable it, you just give it a layout that lets it work.
• Layout decides how much you can skip. Stats tell the engine whether a file can match; colocation (Z-order, or better, liquid clustering) decides how tightly matching rows are packed — which decides how many files are skippable.

See it work — data-skipping simulator

Below is a table stored as 64 files. Pick a layout, then run a WHERE col = value filter and watch which files get scanned vs skipped. The fewer files scanned, the faster and cheaper the query.

Simplified illustration of the concept. Real data skipping uses per-file min/max statistics; results depend on data distribution and file sizes.

See it work — min/max stats inspector

This is the mechanism behind the grid above. Six files each carry a [min, max] range for amount. Type a predicate value: a file is pruned if its range can't contain the value, and read if it could. This is exactly what the optimizer does before opening any file.

Simplified illustration. Real stats include MIN, MAX, NULL count and record count per column; only the first 32 columns are indexed by default on UC EXTERNAL tables, and long strings are truncated.

How it works — sub-topic by sub-topic

1 · Per-file statistics: MIN, MAX, NULL count, record count

On every write, Delta stores four stats per file, per column — MIN, MAX, NULL count, and the file's total record count — in the _delta_log. The engine reads these tiny stats first and prunes any provably non-matching file without opening it. Helps range-comparable predicates (=, <, >, BETWEEN, IN); does nothing for, e.g., LIKE '%x%'. Skipping is best-effort — never wrong, just sometimes conservative.

SQL-- Data skipping is automatic — no syntax to "turn it on". Stats collected on write.
-- The engine prunes any file whose amount MIN/MAX can't reach 5000:
SELECT * FROM main.delta_opt_demo.orders
WHERE amount > 5000;

-- File-level view (numFiles, sizeInBytes):
DESCRIBE DETAIL main.delta_opt_demo.orders;

PySpark# Skipping happens transparently under the hood.
(spark.table("main.delta_opt_demo.orders")
      .where("amount > 5000")   # min/max pruning kicks in automatically
      .count())

2 · How the optimizer prunes files at query time

The planner intersects each predicate with every file's min/max range. A file is kept only if its range could overlap the predicate; otherwise it's skipped — all before any data file is opened. The win scales with how non-overlapping the ranges are. Scattered data ⇒ ranges overlap everywhere ⇒ nothing skippable despite perfect stats. That's the problem Z-order / clustering fixes.

3 · Stats columns = first 32 columns by default (UC EXTERNAL)

By default Delta collects skipping stats only on the first 32 columns (UC EXTERNAL tables). Columns past the 32nd get no min/max, so predicates on them can't drive skipping. The cap keeps writes fast and the log small — but a hot filter column past position 32 is silently un-indexed. Fix by reordering it earlier, raising dataSkippingNumIndexedCols, or naming it in dataSkippingStatsColumns.

4 · UC MANAGED tables: predictive optimization handles stats

Analogy Managed = self-tuning

For UC MANAGED tables, predictive optimization provides intelligent stats — no 32-column limit — and runs ANALYZE to maintain the stats it judges useful. You generally don't tune stats columns on managed tables. (Managed-only; EXTERNAL tables still get the 32-col default. See Lesson 09.)

SQL-- On UC MANAGED tables, predictive optimization manages stats (runs ANALYZE for you).
DESCRIBE EXTENDED main.delta_opt_demo.orders;  -- look for "Predictive Optimization"

5 · dataSkippingNumIndexedCols vs dataSkippingStatsColumns

delta.dataSkippingNumIndexedCols (all DBR) sets how many leading columns get stats — order-dependent, default 32. delta.dataSkippingStatsColumns (DBR 13.3 LTS+) is an explicit column list regardless of position and supersedes the numeric knob. Prefer the explicit list — name exactly the columns you filter on (and exclude expensive long-string ones). Indexing more columns costs more on write, so index what you actually filter on.

SQL-- PREFERRED (DBR 13.3 LTS+): name exactly the columns to collect stats on.
-- Supersedes dataSkippingNumIndexedCols.
ALTER TABLE main.delta_opt_demo.orders
  SET TBLPROPERTIES (
    'delta.dataSkippingStatsColumns' = 'order_status, region, amount'
  );

-- OLDER / order-dependent knob (all DBR): index the first N columns.
ALTER TABLE main.delta_opt_demo.orders
  SET TBLPROPERTIES ('delta.dataSkippingNumIndexedCols' = '8');

6 · Changing these props does NOT recompute existing stats

Setting either property affects future writes only. Already-written files keep their old stats — the change does not retroactively recompute them. You're left with a mixed table (new files have new stats, old files don't) until you recompute or rewrite.

7 · ANALYZE TABLE … COMPUTE DELTA STATISTICS (DBR 14.3 LTS+)

This recomputes data-skipping stats for existing files using the current stats config, without rewriting the data files. It's how you backfill stats after changing the stats-columns property so old files start skipping on the newly-chosen columns. It reads the files (a cost), but far less than rewriting the table.

SQL-- Backfill stats on EXISTING files after changing the stats-columns property.
-- DBR 14.3 LTS+.
ANALYZE TABLE main.delta_opt_demo.orders COMPUTE DELTA STATISTICS;

8 · Long strings are truncated during stats collection

When collecting MIN/MAX for a long string column, Delta truncates the value (stores a prefix). Coarser min/max ⇒ weaker skipping. So consider excluding long string columns from stats and spending the budget on selective columns.

SQL-- Spend the stats budget on selective columns; EXCLUDE a long free-text column
-- (its truncated min/max barely helps skipping anyway -- 'notes_text' omitted on purpose).
ALTER TABLE main.delta_opt_demo.orders
  SET TBLPROPERTIES (
    'delta.dataSkippingStatsColumns' = 'order_status, region, amount'
  );

9 · Z-ordering: OPTIMIZE … ZORDER BY (cols) and the Z-order curve

Analogy: re-shelve by what's bought together High-cardinality filter columns

OPTIMIZE t [WHERE …] ZORDER BY (colA, colB) rewrites data so rows with nearby values colocate in the same files, using a space-filling (Z-order) curve that keeps multiple columns clustered at once. Result: tight, non-overlapping min/max ranges = aggressive skipping. Use it for high-cardinality columns common in predicates. Multiple columns are allowed, but effectiveness drops per extra column — Z-order on the 1–2 columns that dominate your filters.

SQL-- Compact AND colocate by the columns you filter on most (high-cardinality is fine).
-- WHERE scopes the rewrite to one partition (cheaper) on a partitioned table.
OPTIMIZE main.delta_opt_demo.orders
WHERE order_date >= DATE'2026-06-01'
ZORDER BY (order_status, region);   -- 1-2 dominant cols; more = less benefit each

PySpark# DeltaTable API equivalent of OPTIMIZE ... ZORDER BY.
from delta.tables import DeltaTable
dt = DeltaTable.forName(spark, "main.delta_opt_demo.orders")
(dt.optimize()
   .where("order_date >= '2026-06-01'")   # scope the rewrite (optional)
   .executeZOrderBy("order_status", "region"))

10 · ZORDER constraints: stats required · not idempotent · no partition cols

• Stats required: a Z-ordered column must have stats collected — skipping reads min/max, so a column with no stats gains nothing.
• Not idempotent: unlike plain OPTIMIZE (bin-packing), OPTIMIZE … ZORDER BY may rewrite files again on a re-run — schedule it deliberately.
• No partition columns: you can't Z-order a partition column; Z-order only reorganizes within a partition.

SQL-- Ensure Z-order columns are in the stats set FIRST, recompute on existing files,
-- THEN Z-order. Don't Z-order a partition column.
ALTER TABLE main.delta_opt_demo.orders
  SET TBLPROPERTIES ('delta.dataSkippingStatsColumns' = 'order_status, region, amount');
ANALYZE TABLE main.delta_opt_demo.orders COMPUTE DELTA STATISTICS;  -- DBR 14.3 LTS+
OPTIMIZE main.delta_opt_demo.orders ZORDER BY (order_status, region);  -- NOT idempotent

11 · The modern recommendation: liquid clustering instead of Z-order

Since DBR 13.3+, Databricks recommends liquid clustering (Lesson 08) instead of Z-order: same colocation-for-skipping benefit, but incremental (no full rewrite per run) and you can change keys with no rewrite. Hard rule: ZORDER is NOT compatible with liquid clustering — use one or the other. Reach for Z-order only on legacy / external tables you're not converting.

SQL-- MODERN DEFAULT for a NEW table: liquid clustering, NOT ZORDER (Lesson 08).
CREATE OR REPLACE TABLE main.delta_opt_demo.orders (
  order_id     BIGINT,
  order_status STRING,
  region       STRING,
  amount       DOUBLE,
  order_date   DATE
)
CLUSTER BY (order_status, region);   -- do NOT also ZORDER -- they're incompatible

Comparison — choosing a skipping strategy

Uses, edge cases & limitations

Uses

• Data skipping: always — it's automatic. Your lever is layout and stats columns.
• dataSkippingStatsColumns: when a hot filter column is past position 32, or to exclude a long-string column and focus the stats budget.
• ANALYZE … COMPUTE DELTA STATISTICS: to backfill stats on existing files after changing the config (DBR 14.3 LTS+).
• ZORDER BY: legacy / external Delta tables filtered on a high-cardinality column you won't convert to liquid clustering. For new tables, use liquid clustering.

Edge cases an interviewer probes

• Hot filter column past column 32 → no stats, no skipping; reorder, raise the numeric knob, or list it explicitly.
• Scattered data, perfect stats → ranges overlap everywhere → nothing skippable; you need Z-order / clustering.
• Long free-text column → truncated min/max → weak skipping; exclude it from stats.
• Z-order too many columns → per-column locality drops; keep to 1–2 dominant cols.
• Re-running OPTIMIZE … ZORDER → NOT a no-op (not idempotent); it can rewrite again.
• Changing stats props and expecting old files to improve → they won't until you ANALYZE … COMPUTE DELTA STATISTICS (or rewrite).
• Z-order on a partition column → not allowed; Z-order works only within a partition.

Limitations

• Stats default to the first 32 columns on UC EXTERNAL tables; columns past 32 are un-indexed unless configured.
• dataSkippingStatsColumns requires DBR 13.3 LTS+; ANALYZE … COMPUTE DELTA STATISTICS requires DBR 14.3 LTS+.
• Changing the stats properties does not recompute existing files' stats.
• Long strings are truncated during stats collection.
• ZORDER BY is not idempotent, cannot target partition columns, requires its columns to have stats, and is not compatible with liquid clustering.

Common gotchas

• Skipping needs locality, not just stats. Perfect min/max does nothing if rows are scattered — colocate with Z-order or (preferred) liquid clustering.
• Check the first 32 columns. On UC EXTERNAL tables a hot filter column past 32 gets no stats; reorder, raise dataSkippingNumIndexedCols, or list it.
• Prefer the explicit list. On DBR 13.3 LTS+, dataSkippingStatsColumns supersedes the numeric knob and lets you exclude long-string columns.
• Property changes don't backfill. Run ANALYZE … COMPUTE DELTA STATISTICS (DBR 14.3 LTS+) to update existing files.
• Z-order is not idempotent and not free. It rewrites data; schedule it deliberately. Its columns must have stats.
• You can't Z-order a partition column. It works only within a partition.
• Don't mix Z-order with liquid clustering — they're incompatible. For new tables, use liquid clustering (Lesson 08).
• UC MANAGED: let predictive optimization handle stats — no 32-col limit; it runs ANALYZE for you (Lesson 09).