AWS Certified Data Engineer – AssociateData Ingestion and TransformationMedium
A retail company needs to analyze customer purchase data to identify trends and personalize recommendations. The data is currently stored in a relational database (Amazon RDS for PostgreSQL) and needs to be extracted, transformed, and loaded into an Amazon Redshift data warehouse on a daily basis. The transformation involves aggregating sales, joining with customer demographics, and cleaning inconsistent product descriptions. The solution must be fully managed, scalable, and support complex SQL-based transformations. Which AWS service is the MOST appropriate for this transformation?
- AAmazon Athena
- BAWS Step Functions with AWS Lambda
- CAWS Database Migration Service (DMS)
- DAWS Glue ETL
Show answer & explanationAnswer & explanation
Correct answer: D. AWS Glue ETL
AWS Glue ETL is a fully managed, serverless ETL service that can connect to RDS, perform complex transformations (aggregation, joins, data cleaning) using Apache Spark, and load data into Amazon Redshift. Its Data Catalog helps manage schema, and it's designed for daily, scheduled batch processing, aligning perfectly with the requirements.
Why the other options are wrong
- A. Amazon Athena is a query service for S3 data, not an ETL tool for extracting from RDS and performing complex transformations and loading into Redshift.
- B. While AWS Step Functions and Lambda can orchestrate and execute transformations, building a robust, scalable, and fully managed ETL pipeline with complex SQL-based transformations for this scenario would be significantly more complex and require more custom code compared to using AWS Glue ETL.
- C. AWS Database Migration Service (DMS) is primarily for migrating databases, not for ongoing complex ETL transformations like aggregation and data cleaning.
AWS Glue ETL
A serverless data integration service that makes it easy to discover, prepare, and combine data for analytics, machine learning, and application development.
- Serverless and scalable
- Supports various data sources and targets (RDS, S3, Redshift)
- Apache Spark-based for powerful transformations
- Automated schema discovery with Glue Data Catalog
Memory trick: Glue ETL connects your database to your data warehouse, cleaning and enriching data along the way.