Archiving Old Data from RDS MySQL for Compliance
A financial services company runs a critical application on Amazon RDS for MySQL. The database stores transaction data that must be retained for 7 years for regulatory compliance. The current retention policy stores all data in the same table, causing performance degradation on the main transactional table. The company needs to archive data older than 1 year while keeping it queryable. Which design should they implement?
Quick Answer
The correct answer is to use Amazon RDS for MySQL with a read replica for reporting, and set up a Lambda function to export partitions older than 1 year to Amazon S3 in Parquet format, queryable via Amazon Athena. This design directly addresses the need for RDS MySQL data archiving for compliance by offloading read traffic to the read replica, which keeps the primary transactional table lean and prevents performance degradation. The Lambda function automates the archival process, moving cold data to S3 where Parquet’s columnar format reduces storage costs and enables efficient querying through Athena, satisfying the seven-year retention requirement without sacrificing queryability. On the AWS Certified Database Specialty DBS-C01 exam, this scenario tests your understanding of hybrid storage architectures that balance performance, cost, and compliance; a common trap is choosing a simple backup-to-S3 solution, which fails the “queryable” requirement. Remember the mnemonic “R3AP” (Read replica, Lambda, S3, Athena, Parquet) to recall the key components for a compliant, queryable archive.
⚠ Common exam trap
Many candidates think DynamoDB TTL is suitable for archiving, but TTL only deletes data, not retains it, and they may overlook the need for a queryable archive solution like Athena on S3.
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
✓
Use Amazon RDS for MySQL with a read replica for reporting, and set up a Lambda function to export partitions older than 1 year to Amazon S3 in Parquet format, queryable via Amazon Athena.
It uses RDS for MySQL read replicas to offload reporting traffic, while a Lambda function archives partitions older than 1 year to Amazon S3 in Parquet format. This keeps the main transactional table lean, improves performance, and retains data for 7 years in a cost-effective, queryable format via Amazon Athena, meeting both compliance and queryability requirements.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Migrate to Amazon Redshift and use workload management to prioritize transactions.
Why it's wrong here
Redshift is not designed for transactional workloads.
- ✗
Use Amazon ElastiCache for Redis to cache recent data and move old data to S3.
Why it's wrong here
Not durable for primary storage; data loss risk.
- ✓
Use Amazon RDS for MySQL with a read replica for reporting, and set up a Lambda function to export partitions older than 1 year to Amazon S3 in Parquet format, queryable via Amazon Athena.
Why this is correct
Preserves relational structure for recent data, archives to S3 for cost-effective storage, and allows querying via Athena.
- ✗
Migrate to Amazon DynamoDB with TTL to automatically expire old data.
Why it's wrong here
DynamoDB TTL deletes data, does not archive it; also not relational.
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
One of 1,663 original DBS-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DBS-C01
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. An e-commerce application stores order data in Amazon RDS for MySQL. The database has grown to 1.5 TB and the company needs to retain data for 7 years for compliance. Current queries are becoming slow due to the large table size. The compliance requirement mandates that data older than 1 year must be retained but is rarely accessed. What strategy would reduce the active table size while maintaining compliance?
hard- A.Create a read replica and run reports against it.
- ✓ B.Partition the table by date and archive partitions older than 1 year to Amazon S3 using AWS DMS.
- C.Delete data older than 1 year and use automated backups for compliance.
- D.Vertically partition the table to separate frequently and infrequently accessed columns.
Why B: Partitioning the table by date allows you to efficiently archive older, rarely accessed data to Amazon S3 using AWS DMS, reducing the active table size while retaining data for 7 years as required. This approach maintains compliance by keeping the archived data accessible in S3, and it improves query performance on the active partition by reducing the volume of data scanned.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DBS-C01 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 DBS-C01 exam.