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

Question 1

Refer to the exhibit. A database specialist is troubleshooting an issue where an application cannot connect to an RDS for MySQL instance using IAM database authentication. The application uses the database user 'db_user1'. The IAM policy shown is attached to the IAM role used by the application. What is the most likely reason for the connection failure?

Accepted Answer

The resource ARN in the policy uses an incorrect RDS resource ID.. Option C is correct because IAM database authentication for RDS MySQL requires the resource ARN in the IAM policy to include the correct RDS resource ID (the 'db-xxxxx' identifier from the RDS console), not the DB instance name or endpoint. If the ARN uses an incorrect resource ID, the policy will not match the target RDS instance, causing the authentication to fail even if the user name and action are correct.

Answer

The action 'rds-db:connect' is not allowed for RDS MySQL.

Answer

The policy should have 'Deny' effect instead of 'Allow'.

Answer

The database user name in the ARN must be 'admin', not 'db_user1'.

Question 2

A company runs a financial analytics platform on Amazon DynamoDB. The table stores transaction records with a partition key of account_id and a sort key of transaction_timestamp. Each account has thousands of transactions. The application frequently queries the most recent transactions for a given account_id, sorted by timestamp in descending order. Recently, as the number of accounts grew, the query latency increased significantly. The DynamoDB table has provisioned read capacity of 10,000 RCUs, and CloudWatch metrics show that consumed read capacity is at 60%. The database specialist suspects that the issue is due to the query pattern. Which action should the database specialist take to reduce query latency?

Accepted Answer

Create a Global Secondary Index (GSI) with account_id as partition key and transaction_timestamp as sort key, and query the GSI with ScanIndexForward set to false.. Option B is correct because creating a Global Secondary Index (GSI) with account_id as the partition key and transaction_timestamp as the sort key allows the application to query the most recent transactions for a given account_id efficiently. By setting ScanIndexForward to false in the query, DynamoDB returns items in descending order by sort key, which directly matches the query pattern. This avoids the need to scan all transactions for an account and then sort them, significantly reducing latency. The existing table's sort key is transaction_timestamp, but the query pattern requires descending order; the GSI provides an optimized access path without changing the base table structure.

Answer

Migrate the table to Amazon Aurora PostgreSQL with a secondary index on account_id and transaction_timestamp.

Answer

Enable DynamoDB Accelerator (DAX) to cache query results.

Answer

Increase the provisioned read capacity to 20,000 RCUs to handle the load.

Question 3

A company runs a customer relationship management (CRM) application on Amazon RDS for PostgreSQL. The application stores customer data in a table with over 50 million rows. The company recently added a new query that searches for customers by their email domain (e.g., '@example.com'). The query uses a LIKE pattern: 'WHERE email LIKE ''%@example.com'''. The query takes over 30 seconds to complete. The DBA has already created a B-tree index on the email column, but it does not help. Which action should the database specialist recommend to improve query performance?

Accepted Answer

Create a trigram index (using pg_trgm extension) on the email column.. The query uses a leading wildcard LIKE pattern ('%@example.com'), which prevents a standard B-tree index from being used because the search string does not have a fixed prefix. A trigram index, provided by the pg_trgm extension, breaks strings into three-character substrings (trigrams) and allows the database to efficiently match patterns with leading wildcards. This index type is specifically designed for fuzzy text matching and LIKE queries with wildcards, reducing the query time from over 30 seconds to milliseconds.

Answer

Create a hash index on the email column.

Answer

Increase the shared_buffers parameter to improve caching.

Answer

Create a B-tree index on the reversed email string.

Question 4

A company is designing a database for a ride-sharing application that needs to store real-time driver locations and trip history. The application requires low-latency updates to driver locations (every few seconds) and the ability to query nearby drivers within a radius. The company expects millions of drivers and trips. Which AWS database service should the database specialist recommend for storing real-time driver locations and supporting proximity queries?

Accepted Answer

Amazon DynamoDB with a Geohash-based partition key and a Global Secondary Index. Amazon DynamoDB with a Geohash-based partition key and a Global Secondary Index is the correct choice because it provides the low-latency writes (single-digit milliseconds) required for updating driver locations every few seconds, while the Geohash-based key enables efficient proximity queries by grouping nearby drivers into the same partition. The Global Secondary Index allows querying by geohash prefix to find drivers within a radius, scaling to millions of drivers and trips with DynamoDB's auto-scaling and fully managed infrastructure.

Answer

Amazon RDS for PostgreSQL with PostGIS extension

Answer

Amazon ElastiCache for Redis with geospatial data types

Answer

Amazon Timestream

Question 5

A company runs a multi-tenant SaaS application on Amazon RDS for PostgreSQL. Each tenant has an isolated database. Recently, the application experienced a sudden increase in connection errors and slow query performance. Amazon RDS instance metrics show high CPU utilization and a high number of database connections. The application uses connection pooling with PgBouncer running on an EC2 instance. The team suspects the issue is due to a few noisy tenants opening too many connections. The current architecture uses one RDS instance per tenant. The company wants to optimize for workload-specific database design to handle noisy tenants without affecting other tenants. Which design should be implemented to isolate noisy tenants and reduce costs?

Accepted Answer

Create separate RDS instances for large tenants and use a single RDS instance for small tenants, with PgBouncer connection pooling per instance.. Option D is correct because it directly addresses the need to isolate noisy tenants by creating separate RDS instances for large (noisy) tenants while consolidating small tenants onto a single instance, each fronted by its own PgBouncer connection pool. This design prevents a single tenant's connection surge from affecting others, optimizes costs by avoiding over-provisioning for all tenants, and aligns with workload-specific database design principles for multi-tenant SaaS on RDS for PostgreSQL.

Answer

Use RDS for PostgreSQL with pg_partman to partition data by tenant and implement connection limits per tenant using PostgreSQL advisory locks.

Answer

Replace RDS with Amazon Aurora PostgreSQL and use Aurora Auto Scaling to handle connection spikes.

Answer

Move all tenants to a single RDS instance with separate schemas and use RDS Proxy to manage connections.

Question 6

A financial services company uses Amazon DynamoDB to store transaction records. Each transaction has a partition key of customer_id and a sort key of transaction_timestamp. The application queries transactions for a specific customer within a date range. Recently, the query latency increased significantly for customers with a large number of transactions. The company needs to improve query performance without changing the application code. The table is provisioned with 5000 RCUs and 2000 WCUs. Which design change should be made to optimize for this workload?

Accepted Answer

Create a global secondary index with customer_id as partition key and transaction_timestamp as sort key.. Option A is correct because creating a global secondary index (GSI) with customer_id as the partition key and transaction_timestamp as the sort key allows efficient querying of transactions for a specific customer within a date range. The existing table's sort key is transaction_timestamp, but the GSI provides a separate index optimized for this access pattern, avoiding full table scans on large customer partitions. This improves query performance without requiring application code changes, as the application can query the GSI directly.

Answer

Enable DynamoDB Accelerator (DAX) on the table.

Answer

Increase the provisioned RCUs to 10000.

Answer

Change the sort key to a composite key including a tenant identifier.

Question 7

A company is migrating an on-premises Oracle database to Amazon Aurora PostgreSQL. The database contains a large table that is partitioned by date. The company wants to minimize downtime during migration and ensure compatibility. Which TWO actions should the company take?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication to migrate the data.. AWS DMS with ongoing replication (change data capture) allows continuous synchronization from the source Oracle database to the target Aurora PostgreSQL, minimizing downtime by keeping the target nearly up-to-date until the final cutover. This approach supports large partitioned tables and reduces the migration window compared to a full load only.

Answer

Migrate the database using a full load only and accept downtime.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema automatically without any manual adjustments.

Answer

Replace the Oracle database with Amazon RDS for MySQL.

Question 8

A gaming company uses Amazon ElastiCache for Redis as a leaderboard for real-time game scores. The leaderboard is updated frequently by millions of users. The application uses sorted sets with player scores. Recently, the leaderboard update latency increased and the cache evictions spiked. The company needs to ensure low-latency updates and high availability. The current setup is a single Redis node. Which design should be implemented?

Accepted Answer

Use a Redis Cluster with multiple shards. Enable AOF persistence and use a read replica for the leaderboard queries.. Option C is correct because Redis Cluster with multiple shards distributes the write load across shards, reducing per-node pressure and evictions. Enabling AOF persistence ensures durability, while using a read replica for leaderboard queries offloads read traffic from the primary shard, maintaining low-latency updates. This design provides both horizontal scaling and high availability, addressing the increased update latency and eviction spikes.

Answer

Upgrade to a larger single Redis node instance type to handle the load.

Answer

Replace Redis with DynamoDB for the leaderboard, using a global secondary index on score.

Answer

Use ElastiCache for Redis with cluster mode disabled and enable Multi-AZ.

Question 9

A company is designing a highly available e-commerce application using Amazon DynamoDB. The application requires strongly consistent reads for inventory data and eventual consistency for user session data. Which TWO design decisions should the company make?

Accepted Answer

Use strongly consistent reads for the inventory table by setting ConsistentRead=true in the query.. Option D is correct because DynamoDB supports strongly consistent reads by setting the `ConsistentRead=true` parameter in the GetItem, Query, or Scan API calls. This ensures that the application always reads the most recent write, which is critical for inventory data where accuracy is paramount. Strongly consistent reads come at the cost of higher latency and lower throughput compared to eventually consistent reads, but they meet the requirement for inventory consistency.

Answer

Enable DynamoDB Streams on the inventory table to replicate data for disaster recovery.

Answer

Use DynamoDB Accelerator (DAX) for the inventory table to provide strongly consistent reads.

Answer

Use a single DynamoDB table for both inventory and session data with different partition keys.

Question 10

A media company stores video metadata in Amazon DynamoDB. Each record has a partition key of video_id and a sort key of uploaded_timestamp. The application frequently queries videos by genre and upload date. The access pattern is read-heavy with occasional writes. The table is provisioned with 3000 RCUs and 1000 WCUs. The company notices that queries by genre are slow and consume many RCUs. Which design change should be made to optimize for this workload?

Accepted Answer

Create a global secondary index (GSI) with genre as partition key and uploaded_timestamp as sort key.. Option D is correct because creating a Global Secondary Index (GSI) with genre as the partition key and uploaded_timestamp as the sort key allows efficient querying by genre and date without scanning the entire table. This directly supports the access pattern, reducing RCU consumption by using index key lookups instead of full table scans. The GSI is ideal for read-heavy workloads with occasional writes, as it offloads query traffic from the main table.

Answer

Use DynamoDB Accelerator (DAX) to cache query results.

Answer

Create a local secondary index (LSI) with genre as sort key and uploaded_timestamp as partition key.

Answer

Increase the provisioned RCUs to 6000.

Question 11

A healthcare company stores patient records in Amazon DynamoDB. Each record includes patient_id (partition key), visit_date (sort key), and a large JSON attribute for medical history. The application frequently queries recent visits for a patient and scans historical data for analytics. The scans on the medical history attribute cause high RCU consumption. The company wants to reduce costs and improve query performance. Which design should be implemented?

Accepted Answer

Move the medical history attribute to a separate table with patient_id as partition key and visit_date as sort key. Use DynamoDB Streams to keep both tables in sync.. Option B is correct because it separates the large, infrequently accessed medical history attribute from the frequently queried core record, reducing the item size for common queries and thus lowering RCU consumption. By using DynamoDB Streams to synchronize the two tables, you maintain data consistency without adding complexity to the application, and queries against the main table become faster and cheaper since they no longer read the large JSON payload.

Answer

Compress the medical history attribute using gzip before storing in DynamoDB.

Answer

Enable DynamoDB Accelerator (DAX) for the table to cache frequent queries.

Answer

Use Amazon S3 to store the medical history as a separate object and reference it from DynamoDB.

Question 12

A company is designing a new application that requires a relational database with read replicas for reporting. The application has unpredictable traffic patterns. The company wants to minimize operational overhead and automatically scale compute capacity. Which THREE services should the company consider?

Accepted Answer

Amazon RDS for PostgreSQL with read replicas. Amazon RDS for PostgreSQL with read replicas is correct because it supports up to 15 read replicas for offloading reporting workloads, and with Multi-AZ deployment, it can automatically failover to a standby instance. However, for automatic scaling of compute capacity, you would need to combine it with a scaling solution like Aurora or use RDS for PostgreSQL with a manual scaling process, but the question specifically asks for automatic scaling, which is better addressed by Aurora Serverless v2. The read replicas provide the necessary separation for reporting without impacting the primary database.

Answer

Amazon DynamoDB Accelerator (DAX)

Answer

Amazon RDS for MySQL with Multi-AZ

Question 13

A startup is building a social media application that stores user posts in Amazon DynamoDB. The access pattern is to retrieve posts by user_id (partition key) sorted by post_timestamp (sort key) in descending order. The table has a global secondary index (GSI) with the same key structure but with different projection. The application reads from the GSI. Recently, the team noticed that writes to the base table are throttled during peak hours. The write capacity is balanced across partitions. Which design change should be made to reduce write throttling?

Accepted Answer

Add a write sharding pattern by appending a random suffix to the partition key.. The correct answer is D because the write throttling is caused by a hot partition, where a single partition key (user_id) receives a disproportionate number of writes. By appending a random suffix to the partition key, the writes are distributed evenly across multiple partitions, eliminating the hot spot. This is a well-known sharding pattern for DynamoDB when access patterns create uneven write traffic, and it does not require changing the read logic because the GSI can be queried with a sort key condition on post_timestamp.

Answer

Use DynamoDB Accelerator (DAX) for writes.

Answer

Increase the write capacity units (WCUs) on the base table.

Answer

Switch to on-demand capacity mode.

Question 14

A company runs an OLTP application on Amazon RDS for PostgreSQL. The database stores customer orders. The application frequently queries orders by customer_id and order_date. The orders table has 100 million rows. The query performance has degraded over time. The database has a single index on customer_id. The company needs to improve query performance without changing the application code. Which design change should be made?

Accepted Answer

Create a composite index on (customer_id, order_date).. The query performance has degraded because the existing single-column index on customer_id can filter by customer but still requires a full sort or scan within that customer's rows to satisfy the order_date condition. Creating a composite index on (customer_id, order_date) allows the database to use a single index seek to locate the exact rows matching both columns, eliminating the need for an additional sort or filter pass. This directly addresses the query pattern without any application code changes.

Answer

Partition the table by order_date using PostgreSQL declarative partitioning.

Answer

Upgrade to a larger RDS instance type.

Answer

Enable RDS Performance Insights to identify bottlenecks.

Question 15

A company uses Amazon DynamoDB to store IoT sensor data. Each sensor sends data every minute. The table has a partition key of sensor_id and a sort key of timestamp. The application queries data for a sensor over the last hour. The table uses on-demand capacity. Recently, the query latency increased for sensors that generate a high volume of data. The application retrieves all attributes for the sensor data. Which design change should be made to reduce latency?

Accepted Answer

Create a global secondary index (GSI) with the same key structure but projecting only the required attributes.. Option B is correct because creating a GSI with the same key structure but projecting only the required attributes reduces the amount of data read from the base table. Since the application retrieves all attributes, but the GSI can be designed to project only the necessary columns, this minimizes read I/O and reduces latency. This is especially effective for sensors with high data volume, as it avoids fetching large item payloads from the base table.

Answer

Use DynamoDB Accelerator (DAX) for the table.

Answer

Enable DynamoDB Streams to replicate data to Amazon ElastiCache.

Answer

Increase the read capacity units (RCUs) on the table.

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

Exhibit

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

Exhibit