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.
⚠ Common exam trap
The trap is selecting scaling options (timeout, DPUs) instead of data organization techniques; the exam tests that Athena performance is primarily about reducing data scanned via partitioning, columnar formats, and compression.
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.
Option C is correct because compressing data files with gzip or snappy reduces the total bytes stored and scanned, and Athena charges and performs based on data scanned, so smaller files lower both query latency and cost. Option D is correct because partitioning the S3 data by frequently filtered columns such as date or region enables partition pruning, so Athena reads only the relevant prefixes instead of scanning the entire table. Option E is correct because columnar formats like Parquet or ORC let Athena read only the columns referenced in the query and provide better compression and predicate pushdown, dramatically reducing scanned data compared to row-based formats like CSV or JSON. Option A is not appropriate because increasing the Athena query timeout only allows long-running queries to finish; it does not reduce the amount of data scanned or improve performance. Option B is not appropriate because adding DPUs to the Glue job speeds up ETL processing, not Athena query performance or the volume of data scanned at query time.
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
Raising the Athena query timeout only lets a slow full-table scan run longer; it neither reduces bytes scanned nor lowers cost. It is tempting when queries fail on timeout, but the real fixes are partitioning, converting to columnar Parquet or ORC, and using Glue crawlers or compaction.
- ✗
Increase the number of DPUs in the Glue job.
Why it's wrong here
Adding DPUs speeds up the Glue ETL job itself, not Athena query scans over the existing S3 data. It is tempting because Glue performance feels related, but slow queries and high scan volumes are addressed by partitioning, columnar formats such as Parquet, and compression or compaction.
- ✓
Compress data files using gzip or snappy.
Why this is correct
Compression shrinks file sizes on S3, so Athena scans fewer bytes per query, cutting both runtime and per-terabyte scan costs. This directly addresses the large-data-scanning problem in the stem while remaining readable by Glue and Athena.
- ✓
Partition the data by frequently filtered columns (e.g., date, region).
Why this is correct
Partitioning by frequently filtered columns enables partition pruning, so Athena reads only relevant S3 prefixes rather than the full dataset. This directly reduces the bytes scanned that cause the slow, costly queries described in the stem.
- ✓
Use columnar data formats like Parquet or ORC.
Why this is correct
Columnar formats store data by column, so Athena reads only the columns referenced in a query rather than entire rows. This reduces bytes scanned and cost, directly addressing the large scans described in the stem.
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,321 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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Amazon Web Services exam blueprint
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.