SAP-C02 Design for New Solutions Practice Question
A company is designing a new data lake on AWS. The data lake will store petabytes of data from various sources, including IoT devices, application logs, and streaming data. The data must be stored cost-effectively, and access patterns vary from frequently accessed recent data to rarely accessed historical data. The company also needs to run SQL queries on the data. Which solution should the architect recommend?
⚠ Common exam trap
Many exam-takers confuse S3 Select with Athena, not realizing S3 Select is limited to single-object filtering and cannot perform joins or aggregations across multiple files, which is required for SQL queries on a data lake.
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
✓
Store data in S3 with lifecycle policies to transition between storage classes, and use Amazon Athena for queries
Amazon S3 lifecycle policies allow automatic transition of objects between storage classes (e.g., S3 Standard to S3 Standard-IA to S3 Glacier Deep Archive) based on age, optimizing cost for varying access patterns. Amazon Athena enables serverless SQL querying directly on data stored in S3, making it ideal for a petabyte-scale data lake without managing infrastructure.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Store data in S3 with lifecycle policies to transition between storage classes, and use Amazon Athena for queries
Why this is correct
S3 lifecycle policies transition objects between storage classes, matching the varying access patterns cost-effectively at petabyte scale, while Athena runs SQL directly against S3 without loading data. This satisfies both the cost and query requirements in one serverless design.
- ✗
Store data in S3 with lifecycle policies to transition to Glacier, and use S3 Select for queries
Why it's wrong here
S3 Select runs SQL over individual objects, not across the whole data lake, so it cannot satisfy the query requirement. Lifecycle transitions to Glacier also make rarely accessed data non-queryable without restore. It is tempting because S3 with lifecycle policies is genuinely the cost-effective storage foundation for a petabyte data lake.
- ✗
Store data in Amazon EMR on EC2 and query with Hive
Why it's wrong here
EMR on EC2 requires provisioning and managing clusters, and Hive queries add operational overhead rather than a serverless SQL layer over S3. It is tempting because EMR genuinely processes large datasets and Hive provides SQL-like querying, which fits batch analytics on a data lake.
- ✗
Store data in Amazon Redshift and query with Redshift Spectrum
Why it's wrong here
Redshift stores data in its own managed warehouse, so it is not a cost-effective petabyte data lake store, and Redshift Spectrum queries only external data in S3, not data held inside Redshift clusters. It is tempting because Redshift Spectrum does run SQL against S3, which suits querying a data lake.
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
1 more way 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 new data lake on AWS. The data lake must support SQL queries using Amazon Athena and also allow Amazon SageMaker to access training data. The solution must minimize storage costs for infrequently accessed data while providing immediate access when needed. Which storage tier should be used for the data lake?
hard- A.Amazon S3 Glacier Deep Archive
- ✓ B.Amazon S3 Intelligent-Tiering
- C.Amazon S3 Standard
- D.Amazon S3 One Zone-Infrequent Access
Why B: Amazon S3 Intelligent-Tiering is the correct choice because it automatically moves data between access tiers (frequent, infrequent, and archive instant access) based on changing access patterns, optimizing storage costs without compromising performance. This meets the requirement for infrequently accessed data to be cost-effective while still providing immediate access for Athena queries and SageMaker training, as data in the archive instant access tier can be retrieved within milliseconds.
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.