DEA-C01 practice question 19 of 25

A company receives marketing campaign data from a vendor. The company ingests the data into an Amazon S3 bucket every 40 to 60 minutes. The data is…

A company receives marketing campaign data from a vendor. The company ingests the data into an Amazon S3 bucket every 40 to 60 minutes. The data is in CSV format. File sizes are between 100 KB and 300 KB. A data engineer needs to set-up an extract, transform, and load (ETL) pipeline to upload the content of each file to Amazon Redshift. Which solution will meet these requirements with the LEAST operational overhead?

Answer options

A: Create an AWS Lambda function that connects to Amazon Redshift and runs a COPY command. Use Amazon EventBridge to invoke the Lambda function based on an Amazon S3 upload trigger. Correct answer

B: Create an Amazon Data Firehose stream. Configure the stream to use an AWS Lambda function as a source to pull data from the S3 bucket. Set Amazon Redshift as the destination.

C: Use Amazon Redshift Spectrum to query the S3 bucket. Configure an AWS Glue Crawler for the S3 bucket to update metadata in an AWS Glue Data Catalog.

D: Creates an AWS Database Migration Service (AWS DMS) task. Specify an appropriate data schema to migrate. Specify the appropriate type of migration to use.

Correct Answer:

A. Create an AWS Lambda function that connects to Amazon Redshift and runs a COPY command. Use Amazon EventBridge to invoke the Lambda function based on an Amazon S3 upload trigger.

Explanation

This solution represents a classic serverless, event-driven ETL pattern with minimal operational overhead. An AWS Lambda function is triggered automatically by an Amazon S3 s3:ObjectCreated: event. The function's code can then execute the Amazon Redshift COPY command, which is the most performant method for loading data from S3 into Redshift. This approach requires no server provisioning or management, scales automatically with the number of incoming files, and is cost-effective as you only pay for the compute time used. The entire process is automated, directly addressing the requirements for low operational overhead and frequent data ingestion.

Why Incorrect

B. Amazon Data Firehose is designed for streaming data ingestion, not for processing batch files that have already landed in an S3 bucket. This is an incorrect application of the service.
C. Amazon Redshift Spectrum is for querying data in place on S3, not for loading it into Redshift storage. The requirement is to upload the content, which Spectrum does not do.
D. AWS DMS is a service for migrating data between databases. It is not designed to use S3 files as a source for a continuous ETL pipeline into Amazon Redshift.

References

1. AWS Documentation. (2023). Tutorial: Using an Amazon S3 trigger to invoke a Lambda function. AWS Lambda Developer Guide. Retrieved from https://docs.aws.amazon.com/lambda/latest/dg/with-s3-example.html (This guide shows the S3 trigger mechanism).

2. AWS Documentation. (2023). COPY command. Amazon Redshift Database Developer Guide. Retrieved from https://docs.aws.amazon.com/redshift/latest/dg/rCOPY.html (Section: "Loading data from Amazon S3").

3. AWS Documentation. (2023). Using AWS Lambda with Amazon Redshift. Amazon Redshift Management Guide. Retrieved from https://docs.aws.amazon.com/redshift/latest/mgmt/invoking-lambda-functions-from-amazon-redshift.html (While this discusses invoking Lambda from Redshift, the principles of Lambda interacting with Redshift are relevant).

Keep practicing

Take the free DEA-C01 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test