Partitioning order events in a data lake
partition design for query pruning.
partition by the columns queries filter on, typically date hierarchy and category, balancing granularity to avoid too many tiny files.
partitioning on high-cardinality keys like order ID.
What's really being asked
This assesses whether you can design a partition layout that aligns with query predicates to minimize data scanned, while avoiding the failure modes of over- or under-partitioning.
The full answer
Partitioning physically groups data into object-store prefixes by key values so a query that filters on those keys reads only matching prefixes, an effect called partition pruning. Choose keys that match the common filter dimensions. Since queries analyze monthly sales, partition by a date hierarchy such as year then month, or year/month/day, so a single month's analysis touches only that month's data. Because queries also slice by product category and category is relatively low cardinality, adding category as a secondary partition can further prune, giving paths like year=2026/month=06/category=electronics. Balance granularity: too coarse and queries scan too much, too fine and you create an enormous number of tiny files, hurting throughput and metadata performance. Avoid partitioning on high-cardinality fields like order ID or customer ID, which would create millions of partitions and a severe small-files problem. Pair partitioning with a columnar format like Parquet and register partitions in a catalog such as Glue so engines discover them, ideally using partition projection or regular catalog updates.
The mistakes people make
Partitioning on a unique or near-unique key, exploding partition count. Over-partitioning down to the hour or minute when queries are monthly, producing tiny files. Ignoring the dominant query pattern and partitioning on a field rarely filtered. Forgetting to update the catalog so new partitions are invisible.
What usually comes next
How do you handle skewed categories. What is partition projection. How does this interact with the small-files problem.
A concrete example
Order events are written under year=YYYY/month=MM/category=NAME as Parquet. A query for June electronics sales reads only that prefix, scanning a tiny slice of the lake and returning quickly, whereas an unpartitioned layout would scan everything.
Interview question
Why is partitioning order events by order ID a poor choice for the monthly-by-category query pattern?
- a.It creates extreme high-cardinality partitions and tiny files without aiding the queryCorrect
- b.It prevents using a columnar format
- c.Object stores forbid more than ten partition levels
- d.Order ID is not a valid partition data type
Why? this is the answer
Order ID is near-unique, so partitioning on it yields millions of tiny partitions and does not help filters on date or category. Partition keys should match the columns queries actually filter on.
Just read this? Test yourself on what you have been reading.
Read the original → aws.amazon.com
- #cloud
- #partitioning
- #data-lake
- #s3
- #query-optimization
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on cloud — each one lists the topics its interview covers.
See open roles