Delta query still scans every file: fix data skipping, the first-32-columns stats limit and filter expressions

#Symptom

A query filters to a tiny part of a huge table but still reads nearly all of it. OPTIMIZE didn't help, or you've z-ordered and see no difference.

SELECT count(*)
FROM main.sales.events
WHERE customer_id = 'C-104233' AND event_ts >= '2026-11-01';

Delta stores each file's minimum and maximum values per column. The engine skips any file whose range can't contain your value. Three things have to be true:

1. Stats exist for the column.
2. The filter can use them.
3. The data is laid out so that file ranges don't all overlap.

Work out which one has failed.

#Step 1: measure pruning

In Databricks SQL, open the query's profile from Query History. Look at the scan node for files read and files pruned, and bytes read. You can also run this:

EXPLAIN FORMATTED
SELECT count(*) FROM main.sales.events WHERE customer_id = 'C-104233';

Check that your condition appears as a pushed filter on the scan. Run the query with your filter and with a column that you know has good layout, then compare them.

Get the baseline for the table:

DESCRIBE DETAIL main.sales.events;

Note numFiles, sizeInBytes, clusteringColumns and partitionColumns. If the table has a million tiny files, begin with small files.

#Step 2: check whether stats exist for your column

By default, Delta collects stats on only the first 32 columns in the table's schema. If your filter column is number 40, it has no stats, and there's nothing to skip on. Nested fields count toward the limit, too.

Find the column's position:

fields = spark.table("main.sales.events").schema.fieldNames()
print(fields.index("customer_id") + 1, "of", len(fields))

Then check the table's settings:

SHOW TBLPROPERTIES main.sales.events ('delta.dataSkippingNumIndexedCols');
SHOW TBLPROPERTIES main.sales.events ('delta.dataSkippingStatsColumns');

See the stats themselves, in the recent transaction log files:

location = spark.sql("DESCRIBE DETAIL main.sales.events").first()["location"]

log = (spark.read.json(f"{location}/_delta_log/*.json")
       .where("add IS NOT NULL")
       .selectExpr("add.path", "add.stats")
       .orderBy("path")
       .limit(5))

for row in log.collect():
    print(row.path, row.stats[:400])

The stats JSON has minValues, maxValues and nullCount. If your column is missing from minValues, you've found the problem. Managed tables with predictive optimization may behave differently, so inspect what's actually there.

#Step 3: fix the stats columns

Name exactly the columns you need. This is usually better than raising the count:

ALTER TABLE main.sales.events
SET TBLPROPERTIES (
  'delta.dataSkippingStatsColumns' = 'event_ts,customer_id,event_type,region'
);

Don't simply set -1 on a wide table. Stats on every column make writes slower and enlarge the transaction log, and long strings don't produce useful ranges anyway.

This applies only to files written from now on. Old files still have their old stats. To recompute them:

ANALYZE TABLE main.sales.events COMPUTE DELTA STATISTICS;

This needs a supported runtime (14.3 LTS or later in current documentation). Run it outside of busy periods on large tables. A rewrite also creates new stats, but it costs a lot more.

#Step 4: fix the filter

A filter has to compare the column itself. Wrapping the column in a function generally stops the skipping.

Implicit casts can hide this. Compare a string column with a number, and the engine may add a cast. Check the plan, and match the column's type:

DESCRIBE TABLE main.sales.events;

BI tools and views can add functions you didn't write. Look at the actual SQL in Query History.

#Step 5: fix the layout

Stats exist and the filter is fine, but pruning is still poor? Then each file probably contains a wide range of values. If every file has customer IDs from C-000000 to C-999999, no file can be skipped.

Check how much the ranges overlap:

from pyspark.sql import functions as F

stats_schema = "struct<minValues:struct<customer_id:string>,maxValues:struct<customer_id:string>>"

ranges = (spark.read.json(f"{location}/_delta_log/*.json")
          .where("add IS NOT NULL")
          .select(F.from_json("add.stats", stats_schema).alias("s"))
          .selectExpr("s.minValues.customer_id AS min_v", "s.maxValues.customer_id AS max_v"))

target = "C-104233"
total = ranges.count()
match = ranges.filter((F.col("min_v") <= target) & (F.col("max_v") >= target)).count()
print(f"{match} of {total} files could contain {target}")

This looks only at recent log files, so treat it as a sample, particularly once checkpoints have replaced older entries. To improve layout, cluster by the columns you filter on:

-- Preferred for new work
ALTER TABLE main.sales.events CLUSTER BY (customer_id, event_ts);
OPTIMIZE main.sales.events;

Or, if you're not using liquid clustering:

OPTIMIZE main.sales.events ZORDER BY (customer_id);

Z-order on a column without stats won't work. Databricks raises an error in this case, so fix Step 3 first.

Tip

Choose clustering keys from real queries, not guesses. Pick a few columns that filter often. Each additional column makes each one less effective. See liquid clustering. Also, you can't use liquid clustering together with partitioning or ZORDER on the same table.

For point lookups on very high-cardinality columns, where layout can't help enough, see Bloom filter indexes. Check current guidance before adding them.

#Step 6: test correctly

Test results can mislead if you're not careful:

See disk cache vs Spark cache. Record files read, bytes read and time for a fixed set of queries, before and after.

#Quick diagnosis

#Prevent it next time

• Put the columns you filter on near the start of the schema, or list them in dataSkippingStatsColumns.
• Set this when you create wide tables, since old files need extra work later.
• Keep track of files read per query for your key dashboards.
• Run OPTIMIZE, or let predictive optimization do it. See table inspection for how to check.