How Partition Pruning and Data Skipping Work

The Core Mechanism: Partition pruning and data skipping are the two techniques that turn partitioning and clustering from storage strategies into query performance multipliers. They work at different stages of query execution and rely on metadata that tracks what data lives where. Partition Pruning at Planning Time: When you execute a query with a filter like WHERE event_date = '2024-01-15', the query planner reads partition metadata before touching any data. The metadata says partition 1 covers January 1 to 15, partition 2 covers January 16 to 31, and so on. The planner eliminates all partitions except partition 1 from the execution plan. This happens in milliseconds and is pure metadata lookup, no data scanning involved. Consider a clickstream table ingesting 5 million events per second, which is roughly 400 billion events per day. Daily partitions mean each partition holds about 2 TB compressed. A query for yesterday's data with partition pruning reads 2 TB instead of scanning weeks or months of history. At typical cloud storage throughput of 10 gigabytes per second (GB/s) per query with parallelism, that is 200 seconds of scan time instead of thousands. Data Skipping at Scan Time: Clustering enables data skipping within the chosen partitions. Modern warehouses store data in small chunks, typically 16 to 256 megabytes (MB) each. Each chunk has statistics: minimum value, maximum value, null count, and distinct count for each column. These stats are called zone maps or min max indexes. When your query filters by user_id = 50000 on a table clustered by user_id, the scan reads zone maps first. A chunk with min user_id of 1000 and max of 2000 cannot contain user 50000, so it is skipped without reading the actual data. Only chunks where 50000 falls within the min max range are scanned. Real Numbers from Production: BigQuery often reports 50x to 100x reduction in bytes scanned when clustering aligns with query filters. A join that would scan 1 TB might only touch 20 to 50 GB. Query latency drops from 60 seconds to under 10 seconds. For dashboards with 100+ concurrent users, this difference is between feasible and impossible. Snowflake uses similar mechanics with micro partitions that are typically 50 to 500 MB compressed. Clustering depth is a metric showing how well sorted your data is. Depth of 1 means perfectly clustered, higher numbers mean degradation. When depth exceeds 10 to 20, queries start scanning significantly more data, and performance degrades noticeably. The Interview Insight: When discussing partitioning in an interview, explicitly mention that the benefit comes from metadata driven pruning and statistics based skipping, not just from "organizing data." Quantify the reduction: instead of saying "it's faster," say "it reduces bytes scanned by 50x and cuts p99 latency from 60 seconds to 8 seconds."