SAP-C02 Design for New Solutions Practice Question
A healthcare startup is building a HIPAA-compliant application on AWS. The application uses Amazon RDS for MySQL to store patient data. The compliance team requires that all database changes be audited, including SELECT statements. The current solution enables general query logs on the RDS instance, but the logs are stored locally and are lost when the instance is rebooted. Additionally, the logs are consuming significant storage on the instance. The startup needs a durable, scalable, and cost-effective solution for storing and querying database audit logs. Which solution meets these requirements?
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
✓
Configure RDS to publish audit logs to Amazon CloudWatch Logs, then export logs to Amazon S3 using a subscription filter and Lambda. Use Athena to query the logs in S3.
The most suitable solution. RDS for MySQL supports publishing audit logs to CloudWatch Logs. From there, you can set up a subscription filter to a Lambda function that exports logs to Amazon S3 for durable storage. Athena can then be used to query the logs cost-effectively. This approach is durable, scalable, and cost-effective, meeting the compliance requirement for auditing changes including SELECT statements. Option A: While Kinesis Data Firehose can stream to S3, enabling audit logs on RDS for MySQL does not directly integrate with Kinesis; this option is more complex and unnecessary. Option C: Storing logs in a table on RDS consumes instance storage and does not provide durable, scalable storage; logs are still lost on reboot. Option D: CloudWatch Logs Insights is not cost-effective for long-term querying of large volumes and does not offer the same query flexibility as Athena on S3.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable audit logs on RDS and use Amazon Kinesis Data Firehose to stream logs to Amazon S3. Use Amazon Athena to query the logs.
Why it's wrong here
Enabling audit logs on RDS and using Kinesis Data Firehose to stream to S3 is not a direct integration for MySQL. RDS for MySQL does not natively stream audit logs to Kinesis. This adds unnecessary complexity and is not the best fit.
- ✓
Configure RDS to publish audit logs to Amazon CloudWatch Logs, then export logs to Amazon S3 using a subscription filter and Lambda. Use Athena to query the logs in S3.
Why this is correct
This solution is correct. RDS for MySQL can publish audit logs to CloudWatch Logs. A subscription filter and Lambda function can export those logs to S3. Athena provides a cost-effective, serverless query service for the S3 data, meeting durability, scalability, and cost requirements.
- ✗
Enable the general query log on RDS and set the log_output to TABLE. Write a scheduled script to copy the log table to Amazon S3.
Why it's wrong here
Setting log_output to TABLE stores logs in a database table on the RDS instance, consuming storage and risking data loss on reboot. A scheduled script to copy to S3 is not real-time and still depends on the instance storage.
- ✗
Enable audit logs on RDS and stream them to Amazon CloudWatch Logs. Use CloudWatch Logs Insights to query logs.
Why it's wrong here
Streaming audit logs to CloudWatch Logs is feasible, but using CloudWatch Logs Insights for querying can be expensive for large volumes. Long-term storage and ad-hoc querying are better handled by S3 and Athena.
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
This SAP-C02 question is part of Courseiva's 1,660-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
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.