How to Optimize Athena Queries on Partitioned S3 CSV Data?

DEA-C01 By islever Editorial Last updated

Analyze data by using AWS services. Transform and process data.

Short answer

CE Query Athena data from an S3 bucket in the same Region and convert the CSV metrics to Parquet so predicate pushdown reads only needed column blocks.

Question

An airline company is collecting metrics about flight activities for analytics. The company is conducting a proof of concept (POC) test to show how analytics can provide insights that the company can use to increase on-time departures. The POC test uses objects in Amazon S3 that contain the metrics in .csv format. The POC test uses Amazon Athena to query the data. The data is partitioned in the S3 bucket by date. As the amount of data increases, the company wants to optimize the storage solution to improve query performance. Which combination of solutions will meet these requirements? (Choose two.)
  1. Add a randomized string to the beginning of the keys in Amazon S3 to get more throughput across partitions.
  2. Use an S3 bucket that is in the same account that uses Athena to query the data.
  3. Use an S3 bucket that is in the same AWS Region where the company runs Athena queries. Correct Answer
  4. Preprocess the .csv data to JSON format by fetching only the document keys that the query requires.
  5. Preprocess the .csv data to Apache Parquet format by fetching only the data blocks that are needed for predicates. Correct Answer

Key Takeaways

The exam tests Athena performance tuning—columnar Parquet and same-Region S3 access—while the common trap is applying legacy S3 random-prefix advice or treating bucket account placement as a query optimization.

This DEA-C01 question tests how to optimize Athena queries over date-partitioned CSV flight metrics in Amazon S3. The correct combination is same-Region S3 storage and conversion to Apache Parquet with predicate pushdown, not random key prefixes or JSON preprocessing.

Common mistake: Many learners choose A because they remember S3 high-request-rate guidance about randomizing key prefixes, but that does not improve Athena scan efficiency and can interfere with date-based partition pruning.

Expert Analysis

Why the Answer Is Correct

C and E directly target Athena's two largest cost/performance levers in this POC. Keeping the S3 bucket in the same AWS Region as Athena avoids cross-Region data transfer latency and charges when the service scans the flight-metrics objects. Converting the CSV metrics to Apache Parquet changes the storage layout to columnar, so Athena can read only the columns named in the query and use predicate pushdown to skip data blocks that cannot satisfy date or flight filters. Date partitioning remains useful, but Parquet amplifies partition pruning by reducing the bytes scanned inside each partition.

Why the Other Options Are Wrong

A reflects older S3 request-rate advice, not Athena tuning: prepending a random string can obscure the date partition structure and does nothing to reduce column or block reads. B is irrelevant because Athena can query an S3 bucket in another account with the right permissions; account placement is not a performance lever. D moves to JSON and "document keys," but JSON is row-oriented and does not give Athena the same column pruning or block-level predicate pushdown as Parquet, so it may scan more data and parse more overhead.

Community Comment Notes

rralucard_ pointed to the Athena performance-tuning documentation, which is the right doctrinal source for columnar formats and partition-aware query design. Ramdi1 explained that same-Region S3 access reduces latency and network cost, and that Parquet lets queries fetch only needed columns, which matches C and E. matasejem observed that C "is not mentioned anywhere" on that particular tuning page; even so, same-Region storage is an established Athena/S3 best practice, and E remains the stronger documented tuning step. tgv and damaldon also selected C and E, consistent with the technical analysis.

Official Reference

https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html

This link opens the official vendor documentation in a new tab.

Exam Strategy

For Athena performance questions, look for direct scan-reduction levers such as columnar formats, compression, partitioning, and same-Region S3 access. Reject legacy S3 random-prefix advice unless the question is explicitly about S3 request-rate throttling, not query performance.

Frequently Asked Questions

Why is a randomized prefix on S3 keys wrong for the Athena CSV POC?

It targets S3 request-rate throughput, not Athena scan efficiency; it can also break the date-based partition path Athena relies on for pruning.

Why convert the flight metrics CSV to Parquet instead of JSON?

Parquet is columnar, so Athena reads only needed columns and predicate blocks; JSON still scans more data and lacks the same block-level pruning benefits.

More DEA-C01 FAQ →

Community Votes

CE
100%

100% of anonymous learners picked answer CE. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 5 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 5

  1. rralucard_ 👍 6
    https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html
  2. Ramdi1 👍 1
    C - Reduces latency and network costs → When Athena queries S3 data in the same AWS Region, data does not cross AWS Regions, improving performance. Lower query execution time → No inter-region data transfer delays. Cost-Effective → AWS charges for cross-region data transfers, but querying within the same region avoids these costs. E - Parquet is a columnar storage format → Queries can fetch only needed columns, reducing scanning costs.
  3. matasejem 👍 1
    C is not mentioned anywhere in the https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html
  4. tgv 👍 1
    I will go with C and E.
  5. damaldon 👍 1
    Answer C and E

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide