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
EMR requires cluster management and is not serverless for SQL.
- ✓
Amazon Athena
Why this is correct
Athena is serverless and can query S3 data with standard SQL, optimized by partitioning and columnar formats.
- ✗
Amazon QuickSight
Why it's wrong here
QuickSight is a BI tool, not a query engine for raw data.
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 — 1,660 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 correct because it is a serverless interactive query service that uses standard SQL to query data directly in Amazon S3 without requiring any infrastructure or data movement. Option B (AWS Glue) is primarily used for ETL and data cataloging, not for ad hoc querying. Option C (Amazon QuickSight) is a business intelligence tool for visualization, not direct SQL querying. Option D (Amazon Redshift Spectrum) can query S3 data but requires an active Amazon Redshift cluster, making it not as simple or serverless as Athena for this use case.
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.