DEA-C01 Question 350
Single answerA retail company stores sales data in an Amazon S3 bucket as CSV files. The data includes columns for 'StoreID', 'ProductID', 'SaleDate', and 'Revenue'. The company wants to calculate the rolling 7-day average revenue for each store and product to analyze trends. Which AWS service and approach would be the most appropriate to achieve this efficiently?
- A
Use Amazon Athena with a SQL query to calculate the rolling 7-day average by using window functions.
- B
Use AWS Glue to preprocess the data and calculate the rolling 7-day average during the ETL process.
- C
Use Amazon Redshift with a SQL query to calculate the rolling 7-day average using GROUP BY and aggregation functions.
- D
Use AWS Lambda to read each file, process the data, and calculate the rolling 7-day average programmatically.
Show answer and explanation
Correct answer: A
Explanation
Amazon Athena is a serverless, scalable solution that allows querying data directly from Amazon S3 using SQL. It supports window functions, which are ideal for calculating rolling averages. This approach avoids the overhead of setting up and managing infrastructure, making it the most efficient and cost-effective solution for this scenario.
- A. Correct.
Amazon Athena supports SQL queries, including window functions, that can efficiently calculate rolling averages on datasets stored in Amazon S3. This is the most efficient and serverless solution for this case.
- B. Incorrect.
While AWS Glue can preprocess data during ETL, calculating rolling averages in Glue would require custom logic and is less efficient than using a SQL-based solution like Athena.
- C. Incorrect.
Amazon Redshift can calculate rolling averages, but moving the data to Redshift involves additional steps like setting up a cluster, ingesting the data, and managing resources, which is unnecessary for a simpler query task.
- D. Incorrect.
AWS Lambda can process the data programmatically, but it is not optimized for large-scale data transformations like rolling averages. This approach would also require custom code and possibly additional storage for interim results.