How data skipping actually works
Databricks collects per-file statistics automatically when data is written to a Delta Lake or managed Apache Iceberg table: minimum and maximum values, null counts, and total record counts, all stored at the file level. At query time, the optimizer compares a filter against those per-file ranges and skips any file it can prove contains no matching rows -- it never has to open or read the file at all. The whole mechanism depends on three things being true simultaneously: the filtered column actually has statistics collected, the matching rows are physically clustered into a small number of files rather than spread evenly across all of them, and those statistics are precise enough to rule files out. Lose any one of the three and the optimizer falls back to opening files to check, no matter how selective the filter looks in the query text.
Which gap caused it?
Answer three questions about the table and the filter
Walk through a query that scanned far more than it should have, and see which gap is the likely cause.
1. Is the filtered column among the table's first 32 columns, or explicitly listed in dataSkippingStatsColumns / covered by predictive optimization?
2. Has the table been Z-ordered or liquid-clustered on that specific column?
3. Is it a long string/text column, or were stats config properties changed without an ANALYZE TABLE ... COMPUTE DELTA STATISTICS run afterward?
Cause 1: no statistics on the filtered column
If the filtered column isn't among the first 32 columns in the table's schema, and isn't explicitly covered by dataSkippingStatsColumns or a predictive-optimization managed table, there's simply no min/max data for the optimizer to check that column against. The filter still runs correctly -- it just can't rule any file out before opening it, so every file is read and checked row by row. This is the most common gap, and the fix is either reordering the schema so the column lands in the first 32, or explicitly adding it via dataSkippingStatsColumns on Databricks Runtime 13.3 LTS and above.
Cause 2: statistics exist, but files aren't clustered
Having a min/max range per file doesn't help if every file's range overlaps with every other file's range -- which is exactly what happens when matching rows for a given value are scattered evenly across the whole table instead of grouped together. Z-ordering (or liquid clustering, which Databricks now recommends for new tables and which can't be combined with ZORDER) physically colocates related values into the same files so their ranges become tight and non-overlapping. Z-ordering a column with no statistics collected on it is documented as ineffective -- both the clustering and the statistics have to target the same column for either to matter.
Cause 3: truncated or stale statistics
Long string values are truncated during statistics collection, so a file's recorded min/max range for a long text column can be less precise than for a number or date column, weakening how confidently the optimizer can rule it out. Separately, changing a stats-related table property like dataSkippingNumIndexedCols doesn't retroactively recompute statistics for data already written -- only newly written files benefit, until an explicit ANALYZE TABLE table_name COMPUTE DELTA STATISTICS (Databricks Runtime 14.3 LTS and above) recomputes them for existing data too.
When it's not a statistics problem at all
If all three conditions check out -- the column has statistics, the table is clustered on it, and the values aren't long strings or stale -- and the query is still reading everything, look at the filter's shape in the query text itself. A filter wrapped in a function call on the column (rather than a plain comparison against a literal) can't be matched against a raw min/max range, so the optimizer has no choice but to open the file and evaluate the expression directly. That's a query-authoring issue, not a statistics or clustering gap.
Key takeaways
- Data skipping needs statistics, clustering, and precise (untruncated) values on the SAME column, all at once -- missing any one defeats it.
- Only the first 32 columns get statistics by default on non-predictive-optimization tables -- a filtered column outside that range gets no skipping benefit at all.
- Z-ordering or liquid-clustering a column with no collected statistics is documented as ineffective -- check both together, not separately.
- Changing stats configuration doesn't retroactively fix existing files -- an explicit ANALYZE TABLE ... COMPUTE DELTA STATISTICS run is required to backfill them.
dataSkippingNumIndexedCols
Changes how many leading schema columns get statistics, on any runtime. Column-order dependent -- reordering the schema changes which columns are covered.
dataSkippingStatsColumns
Databricks Runtime 13.3 LTS+. Explicitly lists which columns get statistics, superseding the 32-column default regardless of schema order.
ANALYZE TABLE ... COMPUTE DELTA STATISTICS
Databricks Runtime 14.3 LTS+. Backfills statistics for data already written, after a stats configuration change.
Diagnostic checklist
When a query scans far more rows than its filter's selectivity suggests, check in order: is the filtered column within the stats-collection scope (first 32 columns, or explicitly listed)? Is the table Z-ordered or liquid-clustered on that same column? Is the column a long string, or were stats settings changed without a follow-up ANALYZE TABLE? The downloadable script below checks table-level stats configuration and file counts against the documented thresholds.