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

Question 1

A company runs a web application that uses Amazon RDS for MySQL. The database frequently experiences high CPU utilization due to many concurrent connections. The application uses connection pooling at the application layer. Which TWO approaches can reduce CPU load on the database?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use Amazon RDS Proxy to manage database connections.. Two approaches can reduce CPU load on the database. First, Amazon RDS Proxy manages database connections by pooling and reusing them, reducing the overhead of connection establishment and context switching, thus lowering CPU utilization. Second, Amazon ElastiCache caches frequently accessed data, offloading read queries from the database and reducing CPU usage from repeated query executions.

Answer

Enable Multi-AZ to distribute the load.

Answer

Create read replicas to offload read traffic.

Answer

Increase the DB instance size to a larger instance type.

Question 2

Refer to the exhibit. An application uses Cognito identity pools to authenticate users and dynamodb:LeadingKeys condition to restrict access to items where the partition key matches the user's sub. Some users report that they can see items belonging to other users. What is the most likely cause?

Accepted Answer

The Cognito identity pool is not configured correctly. If the Cognito identity pool is not configured correctly, users may receive incorrect or shared identity IDs (sub claims). Since the access policy uses dynamodb:LeadingKeys with the sub, incorrect identity IDs lead to users accessing items that belong to other users. Options A and D are incorrect: A) The IAM policy includes the LeadingKeys condition, but the issue is the identity source, not the policy structure. D) Using ForAllValues:StringEquals is AWS best practice; switching to ForAnyValue would be less restrictive and not recommended. Option C is unlikely as an incorrect table ARN would cause access denial, not cross-user access.

Answer

The IAM policy does not include a condition for the table

Answer

The table ARN is incorrect

Answer

The ForAllValues:StringEquals should be ForAnyValue:StringEquals

Question 3

Which THREE design patterns are appropriate for improving write performance in Amazon DynamoDB? (Choose 3.)

Accepted Answer

Partition data using a high-cardinality partition key. A high-cardinality partition key distributes write operations evenly across DynamoDB partitions, preventing hot partitions and maximizing write throughput. DynamoDB adaptive capacity automatically adjusts partition throughput to handle uneven or bursty write patterns, reducing throttling. In contrast, DynamoDB Accelerator (DAX) is a read cache that does not improve write performance; it can add latency to writes. Therefore, only options A and B are appropriate for improving write performance.

Answer

Use DynamoDB Accelerator (DAX) to cache write operations

Answer

Enable DynamoDB global tables to distribute writes across regions

Answer

Implement TTL to automatically delete old items

Question 4

A social media company uses Amazon ElastiCache for Redis to store user session data. The application experiences occasional spikes in traffic, causing cache misses and increased latency. The operations team notices that the cache hit ratio drops to 60% during spikes. Which action would most effectively improve the cache hit ratio?

Accepted Answer

Upgrade to a larger cache node type to store more data.. The cache hit ratio drops during traffic spikes because the cache runs out of memory and evicts entries. Upgrading to a larger cache node type increases the available memory, allowing more data to be stored without eviction. This directly improves the hit ratio. Options like adding a read replica (C) or enabling cluster mode (A) do not increase cache capacity, and decreasing TTL (D) would worsen the hit ratio by expiring keys faster.

Answer

Enable ElastiCache for Redis Cluster Mode to distribute data across shards.

Answer

Enable Redis persistence (AOF) and configure a replication group with a read replica.

Answer

Decrease the TTL of session keys to force more frequent refreshes.

Question 5

A company is selecting a database for a time-series application that collects sensor data from thousands of devices. The data is written at a high velocity (millions of data points per second). The application needs to query recent data (last hour) with sub-second latency and perform long-term analysis on months of data. Which TWO AWS database services best meet these requirements?

Accepted Answer

Amazon Timestream for both real-time and historical queries.. Amazon Timestream is purpose-built for time-series data, with separate storage tiers for recent (in-memory) and historical (magnetic) data. It automatically moves data between tiers, enabling sub-second queries on the last hour via the memory store and efficient long-term analysis on months of data via the magnetic store, meeting both velocity and latency requirements. Amazon DynamoDB with TTL and export to S3 also meets the requirements: DynamoDB provides sub-second latency for writes and queries on recent data (by using TTL to expire old data), and exporting data to S3 via DynamoDB Streams and AWS Glue or Athena allows cost-effective long-term analysis. This combination handles high-velocity ingestion and provides real-time access to the last hour, while S3-based analytics support historical queries.

Answer

Amazon ElastiCache for Redis with Time Series module.

Answer

Amazon Redshift for real-time queries and historical analysis.

Answer

Amazon Quantum Ledger Database (QLDB) for immutable time-series records.

Question 6

A company runs an online gaming platform that uses Amazon DynamoDB to store player profiles and game state. The platform experiences sudden spikes in write traffic when popular events occur. During these spikes, some write requests fail with ProvisionedThroughputExceededException. The operations team has configured DynamoDB Auto Scaling with a maximum write capacity of 10000 WCU, but during spikes, the write rate exceeds 10000 WCU for short bursts. The team needs to handle these bursts without losing data or sacrificing performance. What is the MOST effective solution?

Accepted Answer

Implement an Amazon SQS queue to buffer write requests and process them in batches, with a Lambda function writing to DynamoDB.. During sudden spikes exceeding the Auto Scaling maximum, DynamoDB throws ProvisionedThroughputExceededException. Auto Scaling reacts slowly (minutes), so it cannot handle short bursts. Option A (DAX) is a read cache and does not help with write bursts. Option B (manual sharding) can help distribute writes but requires significant application changes and might not fully absorb unpredictable spikes. Option C (increasing max WCU) raises the ceiling but still depends on Auto Scaling's reaction time; also, costs may increase unnecessarily if spikes are rare. Option D (SQS queue) decouples the application: writes are sent to SQS, then a Lambda function reads from the queue and writes to DynamoDB at a controlled rate, effectively buffering the spike and preventing throttling. This pattern ensures no data loss and maintains performance during bursts.

Answer

Enable DynamoDB Accelerator (DAX) to cache write requests and reduce the load on the table.

Answer

Shard the DynamoDB table manually by adding a random suffix to the partition key to distribute writes more evenly.

Answer

Increase the maximum write capacity for Auto Scaling to a higher value, such as 20000 WCU.

Question 7

A company is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database has a large table that is frequently accessed by reporting queries. The reporting queries filter on a column that has a high cardinality but low selectivity. To optimize query performance on this table, which design choice should the database specialist recommend?

Accepted Answer

Create a covering index on the filter column. A covering index includes all columns needed by the reporting queries, allowing PostgreSQL to satisfy the query entirely from the index without accessing the heap (table) pages. This eliminates the overhead of random I/O for row lookups, which is especially beneficial when filtering on a high-cardinality, low-selectivity column where many rows match but the index scan alone can return the required data. In Amazon RDS for PostgreSQL, this reduces read IOPS consumption and improves query latency.

Answer

Partition the table by the filter column

Answer

Use a read replica to offload reporting queries

Answer

Increase the provisioned read IOPS for the RDS instance

Question 8

A company is designing a new e-commerce platform using Amazon DynamoDB. The workload requires single-digit millisecond latency for user session data, which is accessed by session token. The session data is temporary and should be automatically deleted after 24 hours. Which DynamoDB design should the database specialist recommend?

Accepted Answer

Enable DynamoDB Time to Live (TTL) on the session token attribute. DynamoDB Time to Live (TTL) automatically deletes expired items after a specified timestamp, making it ideal for session data that must be removed after 24 hours. This approach requires no additional infrastructure, meets the single-digit millisecond latency requirement by using the session token as the primary key, and ensures automatic cleanup without manual intervention or added cost.

Answer

Create an AWS Lambda function that runs every hour and deletes expired session data

Answer

Store session data in Amazon S3 with a lifecycle policy to delete objects after 24 hours

Answer

Use DynamoDB Accelerator (DAX) to cache session data and set a 24-hour TTL on the cache

Question 9

A financial services company is migrating its Oracle database to Amazon Aurora PostgreSQL. The database runs a critical batch processing job every night that updates millions of rows. The company needs the migration to minimize downtime and ensure data integrity. Which AWS service should the database specialist use to perform the migration?

Accepted Answer

AWS Database Migration Service (AWS DMS) with ongoing replication from Oracle to Aurora PostgreSQL. AWS DMS with ongoing replication (change data capture, CDC) is the correct choice because it enables a near-zero-downtime migration by continuously replicating changes from the source Oracle database to the target Aurora PostgreSQL while the source remains fully operational. After the initial full load, DMS applies ongoing transactions, allowing you to cut over with minimal interruption. This directly addresses the requirement to minimize downtime for the nightly batch job and ensures data integrity through transactional consistency.

Answer

AWS Data Pipeline to export data from Oracle and import into Aurora PostgreSQL

Answer

AWS Schema Conversion Tool (AWS SCT) to convert the schema and then use native PostgreSQL tools to migrate data

Answer

AWS Glue to extract data from Oracle and load into Aurora PostgreSQL

Question 10

A social media application uses Amazon DynamoDB as its primary data store. The application stores user posts and allows users to retrieve the most recent 10 posts of users they follow. The access pattern is a followee-based query that needs to be highly scalable and low-latency. Which DynamoDB table design should the database specialist recommend?

Accepted Answer

Use a composite primary key with a partition key of follower ID and a sort key of timestamp, and store the followee ID as an attribute. It models the access pattern directly: the follower ID as the partition key ensures all posts from followed users are co-located, and the sort key of timestamp allows efficient retrieval of the most recent 10 posts via a Query with a limit of 10 and descending order. This design avoids expensive scans or secondary index lookups, meeting the low-latency and scalability requirements.

Answer

Use a partition key of post ID and a local secondary index on the followee ID

Answer

Use a single table with a scan operation and filter on the followee attribute

Answer

Design the table with a partition key of user ID and a sort key of timestamp, and create a global secondary index (GSI) on followee ID

Question 11

A company is running a MySQL database on Amazon RDS and needs to store JSON documents that are frequently queried by fields within the JSON. The company wants to reduce development complexity and improve query performance. Which RDS MySQL feature should the database specialist recommend?

Accepted Answer

Use the JSON data type in MySQL 8.0 and utilize JSON path expressions in queries. MySQL 8.0's native JSON data type stores JSON documents in an optimized binary format, enabling efficient indexing and querying via JSON path expressions (e.g., `JSON_EXTRACT`, `->`, `->>`). This reduces development complexity by allowing direct SQL access to JSON fields without application-level parsing, and improves query performance through generated columns and virtual indexes.

Answer

Migrate the JSON data to Amazon DynamoDB and use DynamoDB's document model

Answer

Store JSON documents in a VARCHAR(MAX) column and use LIKE operations for queries

Answer

Store JSON documents as BLOBs and parse them in application code

Question 12

A company is designing a multi-tenant SaaS application on Amazon Aurora MySQL. Each tenant has its own database, but some tenants are very large and generate high write traffic. The company wants to isolate tenant workloads to prevent a noisy neighbor from affecting other tenants. Which TWO design strategies should the database specialist recommend?

Accepted Answer

Use Aurora Serverless for tenants with variable workloads. Aurora Serverless automatically scales compute capacity based on application demand, which is ideal for tenants with variable workloads. This prevents a noisy neighbor scenario by ensuring that a tenant's burst of write traffic does not consume shared resources that would degrade performance for other tenants.

Answer

Use a single Aurora cluster with read replicas for each tenant

Answer

Migrate all tenants to Amazon DynamoDB and use DynamoDB Accelerator (DAX) for caching

Answer

Use Amazon RDS Proxy to pool connections and limit throughput per tenant

Question 13

A company is running a critical application on Amazon DynamoDB. The table has a partition key of 'user_id' and a sort key of 'timestamp'. The application frequently queries for all items for a given user within a date range. The read capacity is often throttled during peak hours. Which THREE steps should the database specialist take to resolve the throttling?

Accepted Answer

Enable DynamoDB adaptive capacity to automatically adjust throughput. DynamoDB adaptive capacity automatically manages throughput to accommodate uneven access patterns, such as when a single 'user_id' partition receives more reads than provisioned. It allows the table to absorb throttling by redistributing unused capacity from other partitions, which directly addresses the peak-hour throttling without manual intervention.

Answer

Change the partition key to 'timestamp' to improve read distribution

Answer

Decrease the provisioned write capacity units (WCU) to free up resources for reads

Question 14

A database specialist is analyzing an Aurora MySQL error log and finds the above deadlock error. The application performs an update on the orders table and then updates the inventory table within the same transaction. The deadlock occurs when two concurrent transactions try to update orders and inventory in different orders. Which design change should the database specialist recommend to reduce deadlocks?

Accepted Answer

Ensure all transactions update tables in the same order (e.g., always update inventory first, then orders). Deadlocks in Aurora MySQL often occur when concurrent transactions acquire row-level locks on tables in different orders. By enforcing a consistent lock order (e.g., always updating inventory first, then orders), the database can avoid circular wait conditions, which are a necessary condition for deadlocks. This is a standard best practice for reducing deadlocks in InnoDB, which uses row-level locking and two-phase locking.

Answer

Combine the orders and inventory tables into a single table to avoid multiple table locks

Answer

Use SELECT ... FOR UPDATE on both tables before updating

Answer

Change the transaction isolation level to READ UNCOMMITTED

Question 15

A company is implementing fine-grained access control for a DynamoDB table named UserSessions. The table has a partition key of 'user_id'. The above IAM policy is attached to an IAM role assumed by the application. What does this policy achieve?

Accepted Answer

Restricts the application to access only items where the partition key matches the user's AWS user ID. The IAM policy uses a condition key `dynamodb:LeadingKeys` with a value of `${aws:userid}`. This restricts access to items in the DynamoDB table where the partition key (`user_id`) matches the unique identifier of the IAM user or role that is making the request. This implements fine-grained access control, ensuring the application can only read or write items belonging to the authenticated user.

Answer

Allows the application to perform all operations on the UserSessions table without restrictions

Answer

Allows the application to read but not write items in the UserSessions table

Answer

Allows the application to access only the UserSessions table but not other tables