Partitioning and Clustering
Make the warehouse scan less to find more
Partitioning splits tables by a column like date; clustering sorts rows within partitions. Together they make warehouse queries scan a sliver instead of the whole table — the difference between seconds and minutes.
What you will learn
- Distinguish partitioning from clustering
- Choose what to partition on for common analytics
- Measure the query-cost impact of a layout
What is this?
Partitioning physically divides a table by a low-cardinality column (typically date), so queries filter to one partition instead of scanning all rows. Clustering orders rows within partitions by another column (e.g., customer_id or region) so range filters and joins hit contiguous blocks. Both are table-layout choices that trade a little write complexity for a lot of scan savings.
Why do you need it?
- Most analytics queries are time-filtered — partitioning by date cuts scanned data by orders of magnitude.
- Clustering turns point lookups into block-skipping fast paths.
- Pay-per-scan warehouses make these layouts a direct cost decision.
Mental model
A filing cabinet with a folder per month
A partitioned table
You do not open all forty drawers to read January's folder; partitioning is the drawer, and clustering sorts the papers inside so you flip to the right section.
How it works
- Choose the partition column with low cardinality and heavy query use (usually date).
- Define the granularity (day, month) and let queries filter by it.
- Add clustering on the columns that high-cardinality filters or joins use.
- Let your warehouse list scanned partitions to verify the query is pruning.
Filters push down to partitions and clustering prune the scan.
In production
- A 2-billion-row events table partitions by day; dashboards filter to the last 30 partitions.
- A customer table clusters by region so regional filters skip rows.
- Monthly aggregates rebuild only the current partition instead of the full table.
Best practices
- Partition by the column your dominant queries filter on — usually date.
- Cluster on the columns used in joins or high-cardinality equality filters.
- Verify pruning by checking scanned partitions after each layout change.
Common mistakes
- Partitioning by a high-cardinality column into thousands of tiny partitions.
- Clustering on a column nobody filters by, adding upkeep with no gain.
- Forgetting that unbounded scans defeat the entire layout.
Debugging tips
- If query time barely improves, confirm the filter actually prunes the partitioned column.
- If clustering does nothing, sort after knowing you filter equality/range on that key.
Interview checkpoint
Quick quiz
Partitioning helps most when queries
Clustering orders rows