DEA-C01 practice question: File formats and conversion (2 of 2)
Data Ingestion and Transformation A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
A logistics broker's Athena tables sit on about 2 million small JSON files, each only a few kilobytes, written by an ingestion service every minute. Dashboards that filter by shipment date take minutes to run, and most of the time goes to opening files.
The team wants the data stored as partitioned, compressed Parquet files that are large enough to scan efficiently, and it wants the existing dashboards to keep working through the same table name pattern after a short cutover.
Which approach meets these requirements?
Choose one answer.
AA. Keep the JSON files, and add a daily Lambda function that gzips each small file in place so that the objects are smaller.
BB. Create one Athena table for each shipment day, and have the dashboards union the daily tables at query time.
CC. Increase the ingestion service's frequency so that each file holds more records, and keep the JSON format unchanged.
DD. Use an Athena CTAS query to write a new table in partitioned, compressed Parquet, then point the dashboards to that table after the cutover.
Show the answer and explanation
Answer: D
A. Wrong: Gzipping the small files does not reduce the number of objects, so the per-file overhead stays the same. The data also remains row-based JSON, which the team wants to move away from.
B. Wrong: One table per day multiplies the metadata to manage and makes every dashboard query more complex. The small files would still exist, so the scan cost stays high.
C. Wrong: Writing more often makes the files smaller, not larger. The option works against the goal and keeps the row-based format.
D. Right: Correct. CTAS reads the JSON once and writes compressed Parquet files partitioned by shipment date, with fewer and larger objects. Dashboards then query the new table, which lets Athena skip most partitions and read fewer files. Learn more: Create a table from query results (CTAS) (opens in a new tab)
Explanation
Many small files slow Athena because each file has overhead to open and read. A CTAS query rewrites the data once into fewer, larger, compressed Parquet files, partitioned by the column dashboards filter on. Gzipping small files, per-day tables and writing more often each leave the file count or format problem in place.
Want more on File formats and conversion? The 5 full-length practice sets have 9 more File formats and conversion questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →