Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Partitioning enables partition pruning—skip irrelevant data, reduce I/O and cost. **Mechanism**: Data split by partition column (e.g., date); stored in separate physical units. Query with `WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31'` reads only...
Architectural Logic: Partitioning enables partition pruning—skip irrelevant data, reduce I/O and cost. Mechanism: Data split by partition column (e.g., date); stored in separate physical units. Query with WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31' reads only January partitions. Why: Full scan of 1TB → scan 30GB for one month; 30x reduction. Scalability: Partition count matters—10K+ partitions can hurt metadata overhead. Cost: BigQuery/Snowflake charge per byte scanned; pruning directly reduces cost. Trade-off: Over-partitioning = many small files, slow. Best: partition by high-filter, moderate-cardinality columns.
Red Flag: Partitioning by low-cardinality column (e.g., country when 90% queries filter US)—weak pruning. Pro-Move: Align partition key with query patterns; cite scan reduction (e.g., 80% cost reduction).
According to DataEngPrep.tech, this is one of the most frequently asked SQL interview questions, reported at 1 company. DataEngPrep.tech maintains an editor-reviewed database of 1,863 data engineering interview questions across 7 categories.