A good partition column is one that queries filter on constantly, has a moderate number of distinct values, and produces files that are neither tiny nor enormous.
Golden candidates
- Event or business date (
dt,order_date) for time-series facts - Sometimes a low-cardinality second key (
region) if almost every query includes it
Usually bad candidates
user_id,order_id,request_id(huge cardinality → millions of folders)- Timestamps at second granularity (same explosion)
- Columns rarely used in
WHERE(no pruning benefit)
Good: partition by dt → ~365 folders/year, fat daily files Risky: partition by dt, hour, user_id → tiny files, listing nightmare
Checklist interviewers like
1. What filters appear in 80% of queries? 2. How many partition values per day/month? 3. Will each partition hold healthy file sizes after compaction? 4. Can table-format clustering handle the high-cardinality filters instead?
Interview tip: "Partition for common, low-to-medium cardinality filters (usually date). Use clustering/Z-order for selective columns like user_id." Show you fear partition explosion as much as you love pruning.