Optimize Amazon Athena Queries for AWS Machine Learning Specialty
A company uses Amazon Athena to query a data lake in Amazon S3. The data is partitioned by year, month, day, and hour. The team notices that queries are slow and expensive. The team wants to improve performance and reduce costs. Which THREE actions should the team take?
Quick Answer
The correct actions are converting to columnar formats like Parquet, using partition pruning with WHERE clauses, and compressing data to reduce scan volume. These three techniques directly improve Amazon Athena query performance by minimizing the amount of data read from S3, which lowers both latency and cost. Parquet’s columnar storage allows Athena to skip irrelevant columns, while partition pruning on year, month, day, and hour columns limits scans to only the necessary partitions, and compression reduces storage footprint and I/O. On the AWS Certified Machine Learning Specialty MLS-C01 exam, this question tests your understanding of data optimization for analytics pipelines, a common scenario when working with large-scale ML training datasets in S3. A frequent trap is assuming more partitions always help—in reality, excessive partitions increase metadata overhead and slow queries. Remember the mnemonic “PCC” for Partition, Columnar, Compress to lock in the three pillars of Athena cost and performance optimization.
⚠ Common exam trap
A common mix-up: candidates think more granular partitions (e.g., minute) always improve performance, but in Athena, excessive partitions increase metadata overhead and can slow down queries due to the overhead of listing many small S3 prefixes.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Ensure queries filter on partition columns (year, month, day, hour).
Athena charges based on the amount of data scanned per query. By filtering on partition columns (year, month, day, hour), Athena uses partition pruning to skip reading irrelevant S3 prefixes, drastically reducing the data scanned and thus lowering both cost and query latency.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Ensure queries filter on partition columns (year, month, day, hour).
Why this is correct
Partition pruning reduces scanned data.
- ✗
Increase the number of partitions by adding a partition for minute.
Why it's wrong here
More partitions increase metadata overhead; not beneficial.
- ✓
Convert data from CSV to Parquet format.
Why this is correct
Parquet is columnar and reduces scanned data.
- ✗
Use CSV format with GZIP compression.
Why it's wrong here
CSV is not columnar; still scans entire rows.
- ✓
Use S3 storage classes like S3 Intelligent-Tiering for cost savings.
Why this is correct
Intelligent-Tiering can reduce storage costs for data lake.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
Go deeper
Related to this question
About these practice questions
One of 1,672 original MLS-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on MLS-C01
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A data engineer is investigating why an Athena query against the my-data-lake bucket is slow. The query filters on year, month, and day. The exhibit shows the metadata of one Parquet file. What is the MOST likely cause of the slow query?
hard- A.The version ID is null, causing data inconsistency
- ✓ B.The file is too large, causing Athena to process it in a single task
- C.The partition columns are not being used in the query
- D.The storage class is STANDARD, which is slower than GLACIER
Why B: The Parquet file is 1 GB in size, which is too large for efficient processing in Athena. Athena splits data into tasks for parallel execution, but a single large file cannot be split, causing the query to run slowly. Partitioning on year, month, and day is already applied and is not the issue. The other options are incorrect: version ID null is irrelevant, the query does use partition columns, and standard storage is faster than Glacier.
Variation 2. A company stores IoT sensor data in Amazon S3 and uses Amazon Athena for ad-hoc queries. The data is partitioned by date, but queries are still slow and expensive. Which TWO actions can improve query performance and reduce cost? (Choose TWO.)
easy- ✓ A.Use S3 lifecycle policies to compact small files into larger ones
- ✓ B.Convert the data from CSV to Parquet format
- C.Disable server-side encryption on the S3 bucket
- D.Use AWS Glue instead of Athena for querying
- E.Increase the number of partitions to hour-level granularity
Why A: Compacts small files into larger ones, reducing the number of objects and minimizing metadata overhead, which improves query performance. Option B converts data from CSV to Parquet, a columnar format that reduces the amount of data scanned by Athena, lowering cost and speeding up queries. Option C (disabling encryption) does not affect performance and is not recommended. Option D (using Glue) is a different service and not a direct improvement for Athena queries. Option E (increasing partitions to hour-level) can create many small files, degrading performance.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This MLS-C01 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the MLS-C01 exam.