DEA-C01 Practice Question 5: Small Files Compaction Athena

This is the classic small-files problem, and the symptoms name it precisely: planning-phase time dominates because Athena lists every partition location and pays per-object handling overhead, and SlowDown appears because Amazon S3 supports roughly 5,500 requests per second against a single index partition; thousands of tiny objects multiplied by concurrent queries exceeds that. Scanned bytes are unchanged because the data is already columnar and partitioned; the cost is in *file count*, not bytes. AWS guidance is to combine files, either by making the writer emit larger files or by using a tool such as AWS Glue ETL to merge them into larger objects and then moving those into the table's location. Roughly 128 MB also matches Parquet's default row group size, so each compacted file holds a full row group and the format's metadata skipping actually pays off; the docs note that "for small files, the overhead of the columnar file format outweighs the benefits".

Option A makes it worse. Adding a partition key multiplies the number of partitions by 24 while the same volume of data is spread across them, producing even more, even smaller files; the documentation calls out that "having too many partition keys can result in fragmented datasets with too many files and files that are too small".

Option C changes a ceiling that is never reached. The row group size is a maximum; a 300 KB file cannot contain a 128 MB row group, let alone a 512 MB one. Raising the setting leaves the written files exactly as they are.

Option D helps only a narrow case. Query result reuse returns a previously computed result for an *identical* query within the reuse window. New queries, new filters, and the first execution of any query still list and open all 1,400 objects per partition, so the planning cost and the S3 request pressure remain.

Reference: Optimize data - Amazon Athena performance tuning