The full answer
The table is laid out for one filter and not the other. Databricks skips files using the minimum and maximum of each column in each file. If the table is partitioned or ordered by date, a date filter skips most files; but every file holds a wide spread of customer IDs, so a customer filter has to read nearly all of them. The fix is a layout that groups both columns, which on Databricks is liquid clustering on the columns people filter by.
1. Confirm it in the query profile
Run the slow query and open its profile. On the scan, compare files and bytes read with the table's total, and look at the share pruned. A customer filter that prunes almost nothing is a layout problem. A scan that prunes well but is followed by a huge join or sort is a different problem.
2. Check the usual causes
| Check | What goes wrong |
|---|---|
| Statistics on the column | On external tables, statistics cover only the first 32 columns by default. A filter column in position 40 cannot skip anything. |
| Data layout | Rows arrive in date order, so each file covers a few days and every customer. Min and max for customer_id span almost the whole range in every file. |
| Partitioning | Partitioned by date (or worse, by a high-cardinality column), which fixes one access path and leaves the other. |
| Small files | Thousands of tiny files: more files to check and open, even with good skipping. |
3. Fix the layout
-- partitioned table, Databricks Runtime 18.1 and above
ALTER TABLE events REPLACE PARTITIONED BY WITH CLUSTER BY (event_date, customer_id);
-- unpartitioned table
ALTER TABLE events CLUSTER BY (event_date, customer_id);
OPTIMIZE events;
Liquid clustering co-locates rows by both keys, so files have narrow ranges for each. Keys can be changed later without rewriting the table, and OPTIMIZE clusters new data incrementally. On Unity Catalog managed tables, CLUSTER BY AUTO lets predictive optimization pick keys from the actual queries. Keep the key list short: on smaller tables, extra keys weaken skipping for single-column filters.
If statistics were the issue, set delta.dataSkippingStatsColumns to the filter columns and run ANALYZE TABLE events COMPUTE DELTA STATISTICS so existing files get them too.
4. Measure
Rerun the same query and compare files read, bytes read and time. Keep a few representative queries as a benchmark, since a layout that suits today's filters may not suit next year's.
What interviewers want: file-level min/max skipping as the mechanism, reading pruning from the query profile, the 32-column statistics default, why date-ordered data cannot skip on customer ID, and liquid clustering instead of more partitions.
Last updated 16 October 2026
Sources