Solving Athena Query Planning Bottlenecks with Partition Indexing and Projection
Automate data processing by using AWS services.
Manage the lifecycle of data.
Answer
Correct answer: A, C — Use AWS Glue partition index with partition filtering and Athena partition projection to reduce query planning time.
A data engineer runs Amazon Athena queries on data that is in an Amazon S3 bucket. The Athena queries use AWS Glue Data Catalog as a metadata table. The data engineer notices that the Athena query plans are experiencing a performance bottleneck. The data engineer determines that the cause of the performance bottleneck is the large number of partitions that are in the S3 bucket. The data engineer must resolve the performance bottleneck and reduce Athena query planning time. Which solutions will meet these requirements? (Choose two.)
Community Votes
100% of anonymous learners picked answer AC. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
The question tests the distinction between metadata management overhead (planning time) and data processing efficiency (execution time).
This guide explains how to resolve Amazon Athena query planning bottlenecks caused by a high volume of S3 partitions using Glue partition indexing and partition projection.
Many learners incorrectly choose D because they confuse reducing I/O during execution with reducing metadata scanning during planning.
Community Discussion (14 comments)
rralucard_
👍 7
Selected: AC
https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/ Optimizing Partition Processing using partition projection Processing partition information can be a bottleneck for Athena queries when you have a very large number of partitions and aren’t using AWS Glue partition indexing. You can use partition projection in Athena to speed up query processing of highly partitioned tables and automate partition management. Partition projection helps minimize this overhead by allowing you to query partitions by calculating partition information rather than retrieving it from a metastore. It eliminates the need to add partitions’ metadata to the AWS Glue table.
Mahidbdwh
👍 2
Selected: AC
Bucketing not address the problem of having a large number of partitions in the metadata, which is the root cause of the query planning bottleneck. Converting to a columnar format like Apache Parquet will not directly reduce the overhead associated with managing a large number of partitions. Combining small objects will not mitigate the planning overhead that comes from a large number of partitions in the data catalog. Hence A and C
SMALLAM
👍 1
Selected: AE
https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/
pypelyncar
👍 1
Selected: AC
Creating an AWS Glue partition index and enabling partition filtering can significantly improve query performance when dealing with large datasets with many partitions. The partition index allows Athena to quickly identify the relevant partitions for a query, reducing the time spent scanning unnecessary data. Partition filtering further optimizes the query by only scanning the partitions that match the filter conditions. Athena partition projection based on the S3 bucket prefix is another effective technique to improve query performance. By leveraging the bucket prefix structure, Athena can prune partitions that are not relevant to the query, reducing the amount of data that needs to be scanned and processed. This approach is particularly useful when the data is organized in a hierarchical structure within the S3 bucket.
VerRi
👍 1
Selected: AC
D is not correct because the issue is related to partitioning.
HunkyBunky
👍 1
Selected: AC
I guess A / C, beucase we faced with - query plans performance bottleneck, so indexing should be improved
khchan123
👍 2
A. Creating an AWS Glue partition index and enabling partition filtering can help improve query performance by allowing Athena to prune unnecessary partitions from the query plan. This can reduce the number of partitions that need to be scanned, resulting in faster query planning times. C. Athena partition projection allows you to define a partition scheme based on the S3 bucket prefix. This can help reduce the number of partitions that need to be scanned, as Athena can use the prefix to determine which partitions are relevant to the query. This can also help improve query performance and reduce planning times.
okechi
👍 1
The right answer is BD
Christina666
👍 3
Selected: AD
A. Create an AWS Glue partition index. Enable partition filtering. Targeted Optimization: Partition indexes within the Glue Data Catalog help Athena efficiently identify the relevant partitions, significantly reducing query planning time. Partition filtering further refines the search during query execution. D. Transform the data that is in the S3 bucket to Apache Parquet format. Efficient Columnar Format: Parquet's columnar storage and built-in metadata often allow Athena to skip over large portions of data irrelevant to the query, leading to faster query planning and execution.
fceb2c1
👍 4
Selected: AC
Keyword: Athena query planning time See explanation in the link: https://www.myexamcollection.com/Data-Engineer-Associate-vce-questions.htm B & D are related to analytical queries performance, not about "query planning" performance.
ottarg
👍 2
Just finished the exam and I went with AD. I agree with GiorgioGss, but the reason why I picked A over C was becaues the table is already using Glue catalog. If we use the indexes, there's no reason to use C as we already have the partitions indexed. No reason to pick B if we have C selected. Thus I picked D with this to optimize the query e.g. if I'm only selecting a subset of the columns.
GiorgioGss
👍 1
Strange questions.... it can be ABCD
rralucard_
👍 1
If your table stored in an AWS Glue Data Catalog has tens and hundreds of thousands and millions of partitions, you can enable partition indexes on the table. With partition indexes, only the metadata for the partition value in the query’s filter is retrieved from the catalog instead of retrieving all the partitions’ metadata. The result is faster queries for such highly partitioned tables. The following table compares query runtimes between a partitioned table with no partition indexing and with partition indexing. The table contains approximately 100,000 partitions and uncompressed text data. The orders table is partitioned by the o_custkey column.
[Removed]
👍 2
Selected: BD
https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Athena's query planner must scan the Hive metastore (Glue Data Catalog) to identify all partitions that match a query. When there are millions of partitions, this process becomes a bottleneck. Option A is correct because creating an AWS Glue partition index allows Athena to use a specialized index structure to quickly locate relevant partitions without scanning the entire catalog. Option C is correct because partition projection eliminates the need to query the external catalog entirely by calculating partition locations dynamically based on the S3 prefix pattern, which scales infinitely without performance degradation.Why the Other Options Are Wrong
Option B (Bucketing) is incorrect because bucketing optimizes for specific equality queries and data distribution, not for reducing the overhead of managing a large number of distinct partition keys in the metadata. Option D (Parquet format) is incorrect because while it improves query execution speed by reading fewer bytes, it does not reduce the planning time required to discover which files exist in the catalog. Option E (S3DistCP) addresses small file problems (too many objects), but the prompt specifically cites the 'large number of partitions' as the cause, making metadata optimization the direct solution.Community Comment Notes
Community consensus strongly supports AC, with several users noting that partition projection is the modern best practice for handling massive scale. As user rralucard_ noted, "Optimizing Partition Processing using partition projection... can be a bottleneck... when you have a very large number of partitions." User Mahidbdwh clarified that "Converting to a columnar format like Apache Parquet will not directly reduce the overhead associated with managing a large number of partitions," highlighting why D is a distractor.Official Reference
Exam Strategy
Always distinguish between 'query planning' (metadata lookup) and 'query execution' (data reading) in Athena questions. If the problem is 'planning time' or 'catalog size', look for Partition Index or Partition Projection.
Frequently Asked Questions
Why isn't converting to Parquet (D) the right answer?
Parquet reduces data scanned during execution, but does not reduce the time Athena spends scanning the Glue catalog to find those files.
When should I use Partition Projection vs Partition Index?
Use Partition Index for existing tables with manageable growth; use Partition Projection for new tables or massive datasets where catalog scaling is a concern.
Related Analysis
Practice All DEA-C01 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DEA-C01 Practice Test →