Partitioning order events in a data lake

WHAT THIS TESTS 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.

A GOOD ANSWER COVERS 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.

COMMON WRONG ANSWERS 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.

LIKELY FOLLOW-UPS How do you handle skewed categories. What is partition projection. How does this interact with the small-files problem.

ONE 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.