What is data partitioning in a cloud data warehouse?
Tests physical data layout and cost/performance tradeoffs. Strong answers define time-based or integer-range partitioning, explain partition pruning avoids full scans, and warn against high-cardinality keys.
What's really being asked
Whether you understand that a partition is a physical division of a table into segments based on a column value, typically time or integer range, and that this division directly controls how much data the query engine reads. Interviewers want to see you connect physical layout to the billing model of cloud warehouses, where scanned bytes or slot hours drive cost.
The full answer
First, define partitioning as splitting a large table into discrete chunks stored separately, such as daily or hourly partitions on an event timestamp. Second, explain partition pruning: when a query filters on the partition key, the optimizer skips every partition that does not match, which reduces bytes scanned and improves latency. Third, discuss the cost angle in concrete terms: in BigQuery you pay for bytes scanned, so pruning a 10 TB table down to 100 GB cuts cost by roughly 99 percent; in Snowflake or Redshift, pruning reduces compute time and therefore slot or node costs. Fourth, describe what makes a key effective: low to moderate cardinality, high query selectivity, and alignment with common filter predicates. Fifth, contrast partitioning with clustering or sorting, which are complementary techniques that optimize the layout within partitions rather than eliminating whole partitions.
The mistakes people make
Treating partitioning and clustering as interchangeable. Picking a high-cardinality column like user UUID that creates millions of tiny partitions, which increases metadata overhead and can degrade performance. Claiming that partitioning always speeds up joins without mentioning that both sides of the join must be partitioned on compatible keys. Ignoring the cost dimension entirely and only talking about speed. Suggesting partitioning on a column that is rarely filtered, which adds management overhead with no pruning benefit.
What usually comes next
How would you handle late-arriving data in a daily partitioned table? When would you choose ingestion-time partitioning over a timestamp column? How do you combine partitioning with clustering, and in what order? What happens if you partition on a column that you later transform in a query predicate, such as casting a timestamp to date in the WHERE clause?
A concrete example
Imagine a 50 terabyte clickstream table. Without partitioning, a query for yesterday's events scans all 50 TB and costs about 250 dollars in BigQuery on-demand pricing. Partitioning by event_date creates roughly 365 chunks per year. The same query touches only one partition, scanning roughly 137 GB and dropping the cost to about 68 cents while cutting runtime from minutes to seconds.
Interview question
What is the main drawback of partitioning a large table on a high-cardinality column such as user UUID?
- a.Storage costs rise significantly because UUID partitions do not compress well
- b.Partition pruning fails because the engine cannot match individual UUID values efficiently
- c.It creates millions of tiny partitions, increasing metadata overhead and degrading performanceCorrect
- d.The query engine must scan all partitions when joining to other tables on user_id
Why? this is the answer
The card explicitly warns that high-cardinality keys like user UUID create millions of tiny partitions, increasing metadata overhead and degrading performance. Option B is tempting because beginners may assume pruning breaks with too many values, but pruning still occurs—the overhead is the issue.
Just read this? Test yourself on what you have been reading.
Read the original → docs.cloud.google.com
- #data-warehousing
- #bigquery
- #partitioning
- #query-optimization
- #cost-optimization
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles