DEA-C01 Question 347
Single answerA retail company stores its sales data in an Amazon S3 bucket as CSV files. The data includes columns for 'store_id', 'date', 'product_id', and 'sales_amount'. The company wants to calculate the weekly rolling average sales amount for each product and store, and then pivot the results to have each store as a column. Which combination of services and configuration should you choose to achieve this efficiently?
- A
Use AWS Glue to clean the data, Amazon Athena to calculate the weekly rolling average, and Amazon QuickSight to pivot the results.
- B
Use Amazon EMR with Apache Hive to calculate the rolling average and pivot the results, and then store the final output back in Amazon S3.
- C
Use AWS Glue to clean and process the data, and then use Amazon Redshift with a SQL query to calculate the rolling average and pivot the results.
- D
Use Amazon Kinesis Data Analytics to calculate the rolling average in real-time and store the results in Amazon S3 for further pivoting.
Show answer and explanation
Correct answer: C
Explanation
The combination of AWS Glue and Amazon Redshift provides an efficient and scalable solution for processing historical data stored in Amazon S3. AWS Glue can clean and prepare the data, while Amazon Redshift's SQL capabilities make it ideal for calculating rolling averages and performing pivot operations. This approach minimizes complexity and leverages the strengths of each service for the task at hand.
- A. Incorrect.
AWS Glue and Amazon Athena can work together for data cleaning and querying, but Athena does not natively support calculating rolling averages or pivoting efficiently for large datasets.
- B. Incorrect.
Amazon EMR with Apache Hive is capable of handling both rolling averages and pivoting; however, it is more complex to set up and manage compared to the other options, making it less efficient for this use case.
- C. Correct.
AWS Glue can be used to clean and prepare the data, while Amazon Redshift is well-suited for SQL-based operations, including calculating rolling averages and pivoting. This is the most efficient and scalable option.
- D. Incorrect.
Amazon Kinesis Data Analytics is designed for real-time data streams, not batch processing of historical data stored in Amazon S3, making it unsuitable for this use case.