DEA-C01 Question 346
Single answerYou are working as a data engineer for an e-commerce platform. The sales data for each transaction is stored in an Amazon S3 bucket in CSV format. You need to calculate the rolling 7-day average of daily sales for each product and generate a pivot table to display the results, with products as columns and dates as rows. Which AWS service and features should you use to accomplish this task efficiently?
- A
Use Amazon Athena with window functions for the rolling average and the PIVOT SQL extension for the pivot table
- B
Use AWS Glue ETL with PySpark to calculate the rolling average and pandas to create the pivot table
- C
Use Amazon Redshift with SQL window functions for the rolling average and a stored procedure to generate the pivot table
- D
Use Amazon QuickSight to calculate the rolling average and create the pivot table using its visualization capabilities
Show answer and explanation
Correct answer: A
Explanation
Amazon Athena is the best choice for this scenario because it supports SQL queries directly on data stored in Amazon S3. It enables the use of window functions to calculate the rolling average and the PIVOT SQL extension to create the pivot table. This minimizes operational overhead and avoids complex custom coding required by other options.
- A. Correct.
Correct: Amazon Athena supports SQL queries, including window functions for rolling averages and PIVOT SQL extensions for creating pivot tables. It is serverless and works directly with data stored in S3, making it suitable for this task.
- B. Incorrect.
Incorrect: AWS Glue ETL with PySpark can calculate the rolling average, but it would require significant custom coding to create the pivot table, making it less efficient for the task than Athena.
- C. Incorrect.
Incorrect: Amazon Redshift can handle SQL window functions for rolling averages, but using a stored procedure to create a pivot table is complex and less optimal when working with data stored in Amazon S3.
- D. Incorrect.
Incorrect: Amazon QuickSight is a visualization tool and cannot perform advanced data aggregation like rolling averages or pivot table transformations directly in its pipeline.