Question

A data engineering team runs an Amazon Redshift data warehouse holding several years of historical sales facts. A separate Amazon Aurora PostgreSQL database stores the live customer profile table, which is updated continuously by the transactional application. Analysts need to run daily reports that join the historical sales data in Redshift with the most current customer profile data, but the team wants to avoid building and scheduling an ETL pipeline that copies the profile table into Redshift. Which approach best meets these requirements?

Accepted Answer

Use Amazon Redshift federated query to directly query the live customer profile table in Aurora PostgreSQL and join it with the historical sales data in Redshift.

Answer

Create an AWS Glue job that runs every 5 minutes to copy the Aurora customer profile table into a Redshift staging table before each report.

Answer

Enable Redshift Spectrum and query the Aurora table as an external table stored in the Glue Data Catalog.

Answer

Set up AWS DMS ongoing replication from Aurora to Redshift and join against the replicated table.