What is data partitioning in a cloud data warehouse?

What's really being asked

Your ability to connect a specific data modeling technique (partitioning) to core business drivers: speed (query performance) and money (query cost). Interviewers want to see you've moved beyond textbook definitions and can articulate the real-world impact of your technical choices on a large-scale system. This is a test of practical system design and cost optimization, not just database theory.

The full answer

Four key points in order. First, define partitioning as physically dividing a table's data into smaller segments based on the values in a specific column, like a date or region. Second, explain the performance benefit: "partition pruning," where the query engine scans only the necessary partitions, ignoring the rest. Third, explicitly link this to cost savings, noting that cloud warehouses like BigQuery often charge based on bytes scanned, so pruning directly reduces cost. Finally, discuss choosing an effective partition key—typically a low-cardinality column that is frequently used in query filters, like a daily timestamp for event data.

The mistakes people make

A major red flag is confusing partitioning with clustering. Partitioning separates data into different storage segments; clustering sorts data within those segments. Another mistake is suggesting a high-cardinality column (e.g., user_id in a table with millions of users) as a partition key. This leads to "over-partitioning" with too many small partitions (BigQuery has a limit of 4,000 per table), which increases metadata overhead and can hurt performance more than it helps. Simply defining partitioning without mentioning cost is a junior-level answer.

What usually comes next

"What is the difference between partitioning and clustering?" "When would you use an integer range partition instead of a time-unit partition?" "Tell me about a time you had to change a table's partitioning strategy. What was the process and impact?" "How does partitioning affect data ingestion and streaming writes?"

A concrete example

Imagine a 10 TB user_events table with 3 years of data. A query for events from a single day, without partitioning, would scan all 10 TB. In BigQuery, at roughly 6 per TB, that's a 60 query. By partitioning the table by day (PARTITION BY DATE(event_timestamp)), the same query would only scan that single day's partition, which might be around 9 GB (10 TB / 1095 days). The cost drops to a few cents, and the query returns in seconds instead of minutes.