SAP-C02 Design for New Solutions Practice Question
A company is designing a new data lake on Amazon S3. They need to query the data using standard SQL and expect to run complex queries that scan large datasets. The query performance should be optimized to minimize data scanned. Which service should they use?
⚠ Common exam trap
Candidates often confuse Amazon Redshift Spectrum with Athena because both query S3 data, but Redshift Spectrum requires a running Redshift cluster and is not serverless, while Athena is fully serverless and designed specifically for minimizing data scanned in a data lake scenario.
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
✓
Amazon Athena
Amazon Athena is a serverless interactive query service that uses standard SQL to analyze data directly in Amazon S3. It is optimized for querying large datasets with a pay-per-query model, and it automatically minimizes data scanned by leveraging features like columnar data formats (Parquet, ORC), partitioning, and compression to reduce the amount of data read per query.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Amazon Redshift Spectrum
Why it's wrong here
Amazon Redshift Spectrum allows querying data directly on S3 using standard SQL, which is tempting given the scenario's requirements for large datasets. However, it necessitates an existing Amazon Redshift cluster for query processing and metadata management, introducing infrastructure overhead not implied by the need to query data lake data directly on S3. This service is appropriate when an organisation already utilises Amazon Redshift and aims to extend its analytical capabilities to S3-resident data without ingestion, leveraging the Redshift engine.
- ✗
Amazon EMR
Why it's wrong here
Amazon EMR runs big-data frameworks such as Spark and Hadoop for processing, but it does not itself provide the serverless SQL query engine with partition pruning and columnar scan reduction that Athena delivers over S3. EMR suits custom transformation pipelines, not ad-hoc SQL minimising bytes scanned.
- ✓
Amazon Athena
Why this is correct
Athena queries S3 data using standard SQL and is serverless, and its columnar Parquet support with partition pruning minimises bytes scanned. This directly satisfies the requirement for complex SQL queries over large datasets with optimised scan performance.
- ✗
Amazon QuickSight
Why it's wrong here
QuickSight is a BI visualisation layer that renders dashboards from prepared datasets; it does not itself execute ad-hoc SQL directly against S3 with scan minimisation. Athena queries S3 objects in place using partition pruning and columnar formats, which is the requirement here.
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
Courseiva writes every SAP-C02 question from scratch — 984 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
2 more ways this is tested on SAP-C02
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 is designing a data lake on AWS using Amazon S3. They need to run SQL queries on the data without moving it to a separate database. Which AWS service should they use?
medium- A.Amazon EMR
- ✓ B.Amazon Athena
- C.Amazon Redshift
- D.AWS Glue
Why B: Amazon Athena is a serverless, interactive query service that allows you to run standard SQL queries directly against data stored in Amazon S3 without needing to move or transform the data. It uses Presto under the hood and charges only for the data scanned per query, making it ideal for ad-hoc SQL analysis on a data lake.
Variation 2. A company is designing a data lake on AWS using Amazon S3. They need to query the data using standard SQL without moving it to a separate analytics store. Which AWS service should they use?
medium- ✓ A.Amazon Athena
- B.AWS Glue
- C.Amazon QuickSight
- D.Amazon Redshift Spectrum
Why A: Amazon Athena is a serverless query service that runs standard SQL directly against data in Amazon S3 without loading it into a separate analytics store. It uses the AWS Glue Data Catalog for schema and charges per TB scanned, making it the correct choice for ad-hoc SQL on a data lake. The other services either catalog data, visualize it, or require a Redshift cluster.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This SAP-C02 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 SAP-C02 exam.