Examkin

Question

Difficulty: MediumHigh-Performing Database Solutions

A company is migrating a business intelligence (BI) application to AWS. The application runs complex analytical queries, including multi-table joins and aggregations, on a 10 TB10\text{ TB} relational database containing historical sales data. The BI analysts run reports throughout the day, and they require query results in less than 55 seconds. The database is updated once per day during a nightly batch loading process. Which database solution should a solutions architect recommend to meet these performance requirements?

  1. An Amazon Redshift cluster with distribution keys and sort keys optimized for the common query patterns.Answer
  2. B
    An Amazon RDS for PostgreSQL DB instance with a Multi-AZ deployment, routing the analytical queries to read replicas that are configured to serve as primary failover targets.
  3. C
    An Amazon DynamoDB table with DynamoDB Accelerator (DAX) configured, using a monotonically increasing timestamp as the partition key.
  4. D
    An Amazon DynamoDB table configured in Provisioned Capacity Mode, using the sales date timestamp as the partition key and performing client-side joins.

Answer

An Amazon Redshift cluster with distribution keys and sort keys optimized for the common query patterns.
The correct option is the Amazon Redshift cluster configuration. Amazon Redshift is a columnar data warehouse optimized for online analytical processing (OLAP) that can efficiently perform complex joins and aggregations on a 10 TB10\text{ TB} dataset in under 55 seconds. By defining appropriate distribution and sort keys, data movement between compute nodes is minimized, and query performance is maximized.

Step-by-Step Solution

1
Analyze the workload characteristics and requirements.
The application requires executing complex analytical queries, joins, and aggregations on a large historical dataset (10 TB10\text{ TB}) with low latency (under 55 seconds) for BI reporting.
Understanding whether the database is used for transaction processing (OLTP) or analytical processing (OLAP) guides the architectural choice.
2
Evaluate the database service options based on the workload type.
Amazon Redshift is optimized for OLAP/data warehousing workloads, whereas Amazon RDS is designed for OLTP workloads and Amazon DynamoDB is a NoSQL database that does not natively support multi-table joins.
Selecting a database model designed for high-performance analytics is critical for query efficiency at a 10 TB10\text{ TB} scale.
3
Determine the optimal performance configuration within the chosen service.
An Amazon Redshift cluster configured with proper distribution keys (to minimize data movement between nodes) and sort keys (to optimize scan performance) will meet the latency requirements.
Distribution and sort keys are key performance tuning mechanisms in Amazon Redshift that prevent network bottlenecks and minimize disk I/O.

Key Concept

Selecting and configuring the optimal AWS database service based on workload patterns (OLAP vs. OLTP) to achieve high performance at scale.
Rate this question

Topics

High-Performing Database Solutions

More questions from this topic