AWS Certified Data Engineer – Associate (DEA-C01) — Question 241

A retail company stores point-of-sale transaction data in an Amazon RDS for MySQL database. The company maintains historical sales analytics in Amazon Redshift. The company needs to create daily reports that combine the current day's transactions with historical sales patterns for trend analysis. The company requires a solution that provides near real-time insights while minimizing data transfer costs and maintenance overhead.

Which solution will meet these requirements?

Answer options

• A. Configure AWS Database Migration Service (AWS DMS) to continuously replicate data from RDS for MySQL to Amazon Redshift. Use Redshift queries to create consolidated reports.
• B. Implement Amazon Redshift federated queries to directly access RDS for MySQL data and join it with existing Redshift tables in a single query.
• C. Use AWS Glue to create an extract, transform, and load (ETL) pipeline that runs every hour to copy incremental data from RDS for MySQL to Amazon Redshift. Generate reports.
• D. Export RDS for MySQL data to an Amazon S3 bucket on a regular schedule. Use the COPY command to load the data into Amazon Redshift staging tables. Join the data with historical data.

Correct answer: A

Explanation

The correct answer, A, effectively utilizes AWS Database Migration Service (AWS DMS) for continuous replication, ensuring a near real-time flow of data from RDS for MySQL to Amazon Redshift, which is essential for timely reporting. Option B does not provide the same level of real-time data integration as AWS DMS. Option C's hourly ETL process may introduce delays, making it less suitable for near real-time insights. Option D involves a scheduled export which could also lead to outdated data in reports.