DEA-C01 Practice Question 35: Athena Partition Key Choice

Partitioning pays off on the predicate that nearly every query carries: "If you query a partitioned table and specify the partition in the WHERE clause, Athena scans the data only from that partition." Date is that predicate here, and the docs note that "a common practice is to partition the data based on time, often leading to a multi-level partitioning scheme" of year, month, and day. Roughly 365 partitions a year keeps the catalog small and each partition holds a healthy volume of data, so files stay large enough to read efficiently. The queries that also filter on customer_id still benefit: they prune by date first, then let Parquet predicate pushdown handle the customer within the day's files.

Option A partitions on the column almost nobody filters on alone. Four million partitions per table is unmanageable (Athena "cannot read more than 1 million partitions in a single scan"), and every date-range query, which is nearly all of them, would have to touch every one of those partitions. It also shatters each day into millions of tiny files.

Option D is worse, not a compromise. Combining a 4-million-value key with a daily key multiplies partition counts into the billions and produces partitions containing a handful of rows each. The per-file and per-partition overhead would dominate every query.

Option B throws away the cheapest optimization available. Column statistics let Parquet skip row groups *within* files, but Athena must still list and open every object in the table for every query, so a one-day query pays to touch years of data.

Reference: Partition your data in Athena