A finance team needs to run BI queries that aggregate billions of rows of historical transaction data across many columns for quarterly reporting, and the workload is analytical, scanning and aggregating large columns, rather than transactional reads and writes of individual records. Which AWS database service is purpose-built for this workload, and why?
- Amazon RDS for PostgreSQL, because its row-based storage engine is optimized for scanning and aggregating large historical datasets
- Amazon DynamoDB, because its single-digit-millisecond key-based lookups make full-table aggregation queries fast at any scale
- Amazon Redshift, a petabyte-scale data warehouse using columnar storage and massively parallel processing to make large-scale analytical aggregation queries fast
- Amazon Aurora Serverless v2, because its automatic compute scaling makes ad hoc analytical queries over billions of rows fast without any dedicated warehouse
Why C? And why not the others?
Correct answer: C. Amazon Redshift, a petabyte-scale data warehouse using columnar storage and massively parallel processing to make large-scale analytical aggregation queries fast
Amazon Redshift is a petabyte-scale, fully managed data warehouse that stores data in a columnar format and distributes query execution across many nodes through massively parallel processing, an architecture purpose-built for scanning and aggregating enormous datasets efficiently, which is exactly the quarterly-reporting workload described. The option describing RDS for PostgreSQL is wrong because its row-based storage engine is tuned for transactional, record-at-a-time access rather than the wide columnar scans that large-scale aggregation across many rows and few columns benefits from. The option describing DynamoDB is wrong because DynamoDB is optimized for fast key-based item retrieval, not for scanning and aggregating across an entire dataset, and it has no native SQL aggregation engine suited to historical, multi-billion-row analysis. The option describing Aurora Serverless v2 is wrong because it only automates compute scaling for a standard relational, OLTP-oriented engine; it does not add the columnar storage or parallel-processing architecture that makes large-scale analytical aggregation fast.
Source: AWS Redshift documentation: What is Amazon Redshift? (Redshift Management Guide)