Data Engineer interview question
How do you choose a data partitioning strategy?
Partition by columns that match common filters and distribute volume reasonably, while avoiding tiny partitions and skew. Plan retention, compaction, late data, metadata cost, and how queries prune partitions.
Direct answer
What a strong answer covers
How it works
Partitioning splits a table into pieces the engine can skip. The partition key should match the predicates most queries use, usually an event date or a tenant, so a query for one week reads one week of files instead of everything. Partitions must be large enough to avoid metadata and small-file overhead and balanced enough that one hot value does not hold most of the data. The design also covers late-arriving data that belongs in an older partition, compaction of small files written by streaming, retention by dropping whole partitions, and clustering or sorting within partitions for secondary filters.
Example
An events table partitioned by event_date with clustering on customer_id lets the daily report scan one partition and the customer support lookup skip most of it. An earlier attempt partitioned by customer_id produced 40,000 tiny partitions and one huge one for the largest customer; queries got slower and the metastore struggled until the scheme was changed.
Common mistakes
- Partitioning by a high-cardinality column and creating thousands of tiny partitions.
- Choosing a key that queries never filter on so nothing is pruned.
- Ignoring late data and silently dropping events that arrive after the partition was closed.
A useful answer structure
Answer from your own work.
Do not memorise a script: use only examples you can explain honestly in follow-up questions.
Open the desktop workspace and rehearse a clear answer in your own words.