What is data partitioning in a cloud data warehouse?

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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?

ONE 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.

Source: docs.cloud.google.com