A logistics broker stores shipment events in Amazon S3 as Parquet, partitioned first by customer_id and then by day. It has about 30,000 customers, so each day adds 30,000 partitions, and most of them hold a single file of a few kilobytes.
Most Athena queries filter on a date range across all customers, and some also filter on one customer_id. Queries spend most of their time listing partitions and opening tiny files.
The team wants fewer, larger files while keeping both query patterns fast.
Which change meets these requirements?
Choose one answer.
AA. Add an hour level under each day partition, so that each query can prune its data more finely within the day.
BB. Rewrite the data with CTAS partitioned by day only, and bucket each day by customer_id into a fixed number of files.
CC. Keep the layout, and turn on partition projection with an injected type for customer_id, so that Athena skips the partition lookups.
DD. Rewrite the files as gzip-compressed JSON in the same partitions, so that each file takes less space and less time to read.
Show the answer and explanation
Answer: B
A. Wrong: A finer partition level multiplies the partition count and makes each file even smaller. Date-range queries would list and open more objects, not fewer.
B. Right: Correct. Partitioning by day keeps date-range pruning with far fewer partitions, and bucketing places each customer's rows in one of a fixed set of larger files. For a filter on one customer_id, Athena reads only the files in that customer's bucket. Learn more: What is bucketing? (opens in a new tab)
D. Wrong: The partition and file counts stay the same, and JSON is a row format that makes Athena read every column. The option keeps the real problem and loses columnar reads.
Explanation
Partitioning on a high-cardinality column such as customer_id creates huge numbers of tiny partitions and files. Partitioning by the column most queries filter on, here the day, and bucketing by the high-cardinality column gives fewer, larger files while equality filters on customer_id still read only one bucket. Finer partitions, injected projection and compressed JSON keep the small-file problem.
Want more on Partitioning, compression and optimization? The 5 full-length practice sets have 5 more Partitioning, compression and optimization questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →