Athena Query Optimization Techniques
A company runs a data lake on Amazon S3 with AWS Glue and Amazon Athena. The data engineer notices that queries are slow and scanning large amounts of data. Which THREE actions should the engineer take to optimize query performance and reduce costs?
Quick Answer
The answer is to implement partitioning, compression, and columnar storage formats like Parquet or ORC. These three actions directly reduce the amount of data scanned per query, which is the primary driver of both slow performance and high cost in Amazon Athena. Partitioning limits scans to relevant subdirectories, compression shrinks file sizes, and columnar formats allow Athena to read only the necessary columns instead of entire rows. On the AWS Certified Data Engineer Associate DEA-C01 exam, this question tests your understanding that Athena’s pricing and speed are tied to data volume scanned, not compute time—a common trap is choosing to increase workers or timeouts, which only adds cost without reducing scan size. A useful memory tip is “PCC” for Partition, Compress, Columnar—think of it as the three pillars of Athena optimization.
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
✓
Compress data files using gzip or snappy.
The correct actions to optimize query performance and reduce costs are C, D, and E. Compressing data (C) reduces the amount of data scanned, lowering costs and improving I/O. Partitioning (D) by frequently filtered columns (e.g., date, region) allows Athena to prune partitions, scanning only relevant data. Using columnar formats like Parquet or ORC (E) improves compression and enables column pruning, reducing scan size and improving performance. Option A (increasing query timeout) does not reduce data scanned or improve performance; it only allows queries to run longer before failing. Option B (increasing DPUs in a Glue job) is unrelated to Athena query performance; DPUs are for Glue ETL jobs, not Athena queries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the query timeout in Athena.
Why it's wrong here
Does not improve performance; may mask issues.
- ✗
Increase the number of DPUs in the Glue job.
Why it's wrong here
Increases cost, does not affect Athena queries.
- ✓
Compress data files using gzip or snappy.
Why this is correct
Reduces storage and data scanned.
- ✓
Partition the data by frequently filtered columns (e.g., date, region).
Why this is correct
Limits data scanned per query.
- ✓
Use columnar data formats like Parquet or ORC.
Why this is correct
Reduces I/O and improves compression.
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,711 original DEA-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 DEA-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 company uses Amazon S3 to store large CSV files and runs Amazon Athena queries on them. The queries are becoming slower as data grows. A data engineer suggests converting the files to Apache Parquet format and partitioning the data. What is the primary benefit of converting to Parquet?
medium- A.Parquet allows schema evolution without rewriting files.
- B.Parquet supports nested data structures that CSV cannot.
- ✓ C.Parquet stores data in a columnar format, reducing the amount of data scanned per query.
- D.Parquet is compressed by default, reducing storage costs.
Why C: Parquet is a columnar storage format that stores data by columns rather than rows. When Athena queries only a subset of columns, it can read just those columns from disk, drastically reducing the amount of data scanned per query. This directly addresses the performance slowdown because Athena charges by data scanned, and less scanning means faster queries and lower costs.
Variation 2. A data analyst needs to query a large Amazon S3 bucket containing CSV files using Amazon Athena. The bucket has millions of small files (less than 1 MB each). The analyst reports that queries are very slow and often time out. The data is partitioned by date and the partition columns are defined in the table. What is the most effective way to improve query performance?
easy- A.Convert the files to Apache Parquet format using an AWS Glue ETL job.
- ✓ B.Run a compaction job to consolidate small files into fewer larger files (e.g., 128 MB each).
- C.Add more partitions by including hour and minute as partition keys.
- D.Use S3 Select to push down filtering to S3 before Athena processes the data.
Why B: Many small files (under 1 MB) cause high overhead because each file requires a separate read operation and metadata call. Consolidating them into fewer larger files (e.g., 128 MB each) reduces the number of read operations and improves I/O efficiency, directly addressing the root cause of slowdowns and timeouts. Option A (converting to Parquet) improves storage efficiency and query performance but does not reduce the file count; it is a beneficial addition but not the most effective standalone fix for the small file problem. Option C (adding more partitions) would increase overhead by creating even more directories/files to scan. Option D (S3 Select) applies within individual files and does not mitigate overhead from file quantity.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DEA-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 DEA-C01 exam.