File formats & storage · Extra High-Value

Choosing a good partition column

Mediumpartition columncardinalitylayoutbest practices

Interview question

How do you choose a good partition column for a lake table?

How to answer

The short answer

A good 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 → millions of folders)
  • Timestamps at second granularity (same explosion)
  • Columns rarely used in WHERE (no pruning benefit)

Checklist interviewers like

  1. 1What filters appear in 80% of queries?
  2. 2How many partition values per day/month?
  3. 3Will each partition hold healthy file sizes after compaction?
  4. 4Can 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.