DBS-C01 Workload-Specific Database Design Practice Test 11 — 15 Questions

Question 1

A company is migrating an on-premises Oracle data warehouse to AWS. The warehouse contains 20 TB of data and supports complex SQL queries with joins and aggregations. The migration should minimize downtime and require minimal changes to existing SQL queries. Which database service is MOST appropriate?

Accepted Answer

Amazon Redshift. Amazon Redshift is the most appropriate choice because it is a fully managed, petabyte-scale data warehouse service designed for complex SQL queries with joins and aggregations. It supports standard SQL with minimal changes to existing queries, and its columnar storage and massively parallel processing (MPP) architecture are optimized for analytical workloads. The 20 TB data size and requirement to minimize downtime align with Redshift's ability to perform online resizing and use features like RA3 nodes with managed storage for elastic scaling.

Answer

Amazon RDS for Oracle

Answer

Amazon DynamoDB

Answer

Amazon ElastiCache for Redis

Question 2

Which TWO are valid considerations when designing a global database with Amazon Aurora Global Database? (Select TWO.)

Accepted Answer

Secondary regions can forward write operations to the primary region.. Option D is correct because Amazon Aurora Global Database uses a primary-region architecture where secondary regions are fully readable and can forward write operations to the primary region. This is achieved through a dedicated replication channel that allows secondary clusters to accept write requests and asynchronously forward them to the primary, ensuring low-latency local writes while maintaining global consistency.

Answer

Secondary regions cannot have their own reader instances.

Answer

Failover requires promoting the secondary cluster to a standalone cluster.

Answer

Cross-region replication latency is typically under 100 milliseconds.

Question 3

Which TWO are best practices for designing a DynamoDB table for high-traffic e-commerce application? (Select TWO.)

Accepted Answer

Create Global Secondary Indexes to support different access patterns.. Option A is correct because Global Secondary Indexes (GSIs) allow you to support multiple query patterns without duplicating data or redesigning the base table. In a high-traffic e-commerce application, you might need to query orders by customer ID, by status, or by date; GSIs provide alternative access patterns with their own partition and sort keys, enabling efficient queries without full table scans.

Answer

Use a constantly increasing value (e.g., timestamp) as the partition key.

Answer

Design the table to use scan operations for most queries.

Answer

Use a single attribute as the partition key with low cardinality.

Question 4

Which THREE are factors to consider when choosing between Amazon RDS and Amazon DynamoDB? (Select THREE.)

Accepted Answer

Serverless capacity management. Option A is correct because Amazon RDS requires manual or auto-scaling of compute and storage capacity, while DynamoDB offers serverless capacity management with on-demand mode that automatically scales throughput based on traffic. This is a key differentiator when deciding between provisioned capacity (RDS) and fully managed, pay-per-request scaling (DynamoDB).

Answer

Encryption at rest requirements

Answer

Need for in-memory caching

Question 5

Refer to the exhibit. An application team notices that the MySQL RDS instance 'mydb' is running at 80% CPU utilization during peak hours. They need to improve read performance without increasing the CPU load on the primary instance. Which action should they take?

Accepted Answer

Create a Read Replica in the same region. Creating a Read Replica offloads read traffic from the primary MySQL RDS instance, reducing CPU load on the primary while improving read performance for applications. Read Replicas asynchronously replicate data using MySQL’s native binlog-based replication, allowing the primary to focus on write operations without additional CPU overhead from serving reads.

Answer

Increase the DB instance class to db.r5.xlarge

Answer

Change storage type to io1 with higher IOPS

Answer

Enable Multi-AZ deployment

Question 6

Refer to the exhibit. An IAM policy is attached to a role used by an application that accesses the DynamoDB 'Orders' table. The application needs to perform a Scan operation on the table. According to the policy, is the Scan operation allowed?

Accepted Answer

Yes, because the policy explicitly allows Scan. The IAM policy includes an explicit Allow statement for the `dynamodb:Scan` action on the `Orders` table. In IAM policy evaluation logic, an explicit Allow overrides any default implicit Deny, and the Deny statement in the policy only blocks actions that match its `NotAction` element, which does not include Scan. Therefore, the Scan operation is allowed.

Answer

Yes, but only if the scan uses a filter expression

Answer

No, because the Deny statement blocks all actions

Answer

No, because the policy does not specify a condition

Question 7

Refer to the exhibit. A CloudFormation template creates a DynamoDB table. The application team needs to query orders by customer ID (which is not a key attribute). Which change to the template would enable efficient querying by customer ID?

Accepted Answer

Add a GlobalSecondaryIndex with CustomerID as the hash key and OrderDate as the range key. Option C is correct because a GlobalSecondaryIndex (GSI) allows querying on a non-key attribute (CustomerID) with a different key schema than the base table. By specifying CustomerID as the hash key and OrderDate as the range key, the application can efficiently query orders by CustomerID and optionally sort by OrderDate, without affecting the base table's primary key structure.

Answer

Change the KeySchema to use CustomerID as the hash key

Answer

Add a LocalSecondaryIndex on CustomerID

Answer

Enable DynamoDB Streams and use Lambda to populate a separate table

Question 8

A company is designing a database for an e-commerce application that requires high availability and automatic failover. The application performs mainly read-heavy workloads with occasional write spikes during flash sales. Which AWS database service is most suitable for this workload?

Accepted Answer

Amazon Aurora MySQL. Amazon Aurora MySQL is the most suitable choice because it is designed for high availability with automatic failover (typically under 30 seconds) and provides up to 15 low-latency read replicas that can handle read-heavy workloads. During write spikes like flash sales, Aurora's distributed storage subsystem automatically scales I/O capacity without manual intervention, and its Multi-AZ deployment ensures continuous availability even if the primary instance fails.

Answer

Amazon DynamoDB with global tables

Answer

Amazon ElastiCache for Redis

Answer

Amazon RDS for MySQL with Multi-AZ

Question 9

A social media startup stores user posts in Amazon DynamoDB with a partition key of user_id and sort key of post_timestamp. The application frequently queries the five most recent posts for a given user. Which design pattern improves query performance and reduces cost?

Accepted Answer

Create a local secondary index on user_id and post_timestamp. Option D is correct because a Local Secondary Index (LSI) on user_id (hash key) and post_timestamp (range key) allows DynamoDB to efficiently query the five most recent posts for a given user without scanning the entire table. Since the LSI shares the same partition key as the base table, it provides strongly consistent reads and avoids the overhead of a separate index, reducing both latency and consumed read capacity.

Answer

Enable DynamoDB Accelerator (DAX) for the table

Answer

Increase the read capacity units on the table

Answer

Use a global secondary index on post_timestamp

Question 10

A company runs a time-series application on Amazon RDS for PostgreSQL. The table 'events' has 500 million rows and is queried by event_time and event_type. Queries for the last hour are slow despite indexing. Which design change would most improve query performance?

Accepted Answer

Partition the table by month using PostgreSQL declarative partitioning. Option B is correct because partitioning the 'events' table by month using PostgreSQL declarative partitioning allows the query planner to prune partitions that do not contain data for the last hour. This dramatically reduces the number of rows scanned, even with a large table of 500 million rows, and directly addresses the slow query performance for time-range queries. Indexing alone cannot overcome the overhead of scanning a massive monolithic table for a narrow time window.

Answer

Add a composite index on (event_type, event_time)

Answer

Migrate to Amazon DynamoDB with TTL

Answer

Upgrade to a larger RDS instance

Question 11

A company needs to store session data for millions of users with sub-millisecond latency. The data is key-value in nature and can tolerate eventual consistency. Which database service is best suited?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is the best choice because it is a fully managed NoSQL key-value and document database designed for single-digit millisecond latency at any scale, making it ideal for session data storage. It supports eventual consistency, which is acceptable for this use case, and can handle millions of users with sub-millisecond read and write performance using its SSD-backed storage and distributed architecture.

Answer

Amazon Redshift

Answer

Amazon Neptune

Answer

Amazon RDS for MySQL

Question 12

A financial services company runs a critical application on Amazon RDS for Oracle. The workload is write-heavy with frequent small transactions. The DBA notices high latency during peak hours. Which design change would best address this?

Accepted Answer

Switch to Provisioned IOPS (io1 or io2) volume type. Option C is correct because the workload is write-heavy with frequent small transactions, and high latency during peak hours indicates that the current storage volume is not meeting the IOPS demands. Provisioned IOPS (io1 or io2) volumes provide consistent, low-latency performance by guaranteeing a specific number of IOPS, which directly addresses the bottleneck caused by insufficient I/O capacity for write-intensive operations.

Answer

Implement ElastiCache for Redis

Answer

Enable Multi-AZ deployment

Answer

Add a read replica

Question 13

A gaming company uses Amazon DynamoDB to store player profiles with partition key player_id. The access pattern is to retrieve profiles for multiple players in a single request. The application currently makes separate GetItem calls, causing high latency. Which design pattern reduces latency and cost?

Accepted Answer

Use BatchGetItem to retrieve multiple items in one request. BatchGetItem allows you to retrieve up to 100 items or 16 MB of data from multiple tables in a single API call, reducing the number of network round trips compared to individual GetItem calls. This directly addresses the high latency caused by multiple sequential requests and also reduces cost because you pay for read capacity units (RCUs) based on the total item size, not per request overhead.

Answer

Enable DynamoDB Accelerator (DAX)

Answer

Redesign to a single-table design

Answer

Create a global secondary index on player_id

Question 14

A startup needs a fully managed, serverless database for a new web application with unpredictable traffic. The application requires ACID transactions and SQL queries. Which AWS database service should they use?

Accepted Answer

Amazon Aurora Serverless v2. Amazon Aurora Serverless v2 is the correct choice because it provides a fully managed, serverless relational database that automatically scales capacity based on application demand, supports ACID transactions, and uses standard SQL queries. It is ideal for unpredictable traffic patterns as it can scale from zero to hundreds of thousands of transactions per minute without manual intervention.

Answer

Amazon Neptune

Answer

Amazon DynamoDB

Answer

Amazon Redshift

Question 15

An IoT company ingests sensor data into Amazon DynamoDB. The data has a partition key of device_id and sort key of timestamp. Queries often filter by device_id and a date range. Which design pattern improves query performance and reduces cost?

Accepted Answer

Create a local secondary index on device_id and timestamp. Option B is correct because a local secondary index (LSI) on device_id and timestamp allows efficient querying by device_id and a date range without scanning the entire table. Since the base table already uses device_id as the partition key, the LSI shares the same partition key but provides an alternate sort key (timestamp), enabling range queries on timestamp for each device_id. This reduces read capacity consumption and improves latency by avoiding full table scans or expensive filter expressions.

Answer

Use DynamoDB Time to Live (TTL) to expire old data

Answer

Enable DynamoDB Accelerator (DAX)

Answer

Create a global secondary index on timestamp