Reviewed by Aditya Kumar · Last reviewed 2026-03-24
**Architectural Logic**: Partitioning enables partition pruningâcritical for cost and performance at scale. **BigQuery**: `CREATE TABLE dataset.sales (...) PARTITION BY sale_date` or `PARTITION BY DATE(created_at)`. **Snowflake**: `PARTITION BY (sale_date)` or expression....
Architectural Logic: Partitioning enables partition pruningâcritical for cost and performance at scale. BigQuery: CREATE TABLE dataset.sales (...) PARTITION BY sale_date or PARTITION BY DATE(created_at). Snowflake: PARTITION BY (sale_date) or expression. Hive/Spark: PARTITIONED BY (sale_date DATE). Why Partition: Predicate on partition column skips non-matching partitions; 90% cost reduction on date-filtered queries. Scalability Trade-off: Over-partitioning (e.g., by user_id) creates thousands of small filesâslow metadata, poor scan. Cost: BigQuery partition expiration; avoid scanning stale data. Best Practice: Partition by high-filter, moderate-cardinality columns (date, region).
Red Flag: Partitioning by high-cardinality key (user_id)âfile explosion. Pro-Move: Composite partition (dt, region) with max ~10K partitions; use clustering for further organization within partitions.
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.