DBS-C01 Workload-Specific Database Design — 100 Questions

Question 1

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. Option D is correct because 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 2

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 3

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 4

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. Option C is correct because 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 5

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. Option B is correct because 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 6

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. Option A is correct because 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 7

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. Option A is correct because 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 8

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). Option B is correct because 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 9

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

Question 10

A company is designing a multi-tenant SaaS application on Amazon RDS for PostgreSQL. Each tenant's data must be isolated for security and performance. The application has millions of tenants, with most tenants having small datasets (under 100 MB). Which database design pattern is MOST cost-effective and operationally efficient?

Accepted Answer

Use a single RDS instance with a shared schema and implement Row-Level Security (RLS) policies based on tenant_id.. Option B is correct because using a single RDS for PostgreSQL instance with Row-Level Security (RLS) allows you to isolate tenant data at the row level based on a tenant_id column, without the overhead of managing millions of separate schemas or tables. This design is both cost-effective (single instance, no per-tenant provisioning) and operationally efficient (simple schema management, no connection pooling issues), while still meeting security and performance isolation requirements for small datasets under 100 MB.

Answer

Use Amazon DynamoDB with a separate table per tenant.

Answer

Use a single RDS instance with a separate schema per tenant.

Answer

Use a separate Amazon RDS for PostgreSQL instance per tenant.

Question 11

A gaming company uses Amazon DynamoDB for player session data. Each session has a partition key of `game_id` and a sort key of `session_id`. The table has a global secondary index (GSI) on `player_id` for leaderboard queries. Recently, the company noticed that write traffic to the GSI is causing throttling on the base table, even though the base table's write capacity is not fully utilized. What is the MOST likely cause?

Accepted Answer

The GSI is not designed with a high-cardinality partition key, causing write hot spots on the GSI.. The correct answer is B. A global secondary index (GSI) has its own provisioned read and write capacity, separate from the base table. If the GSI's partition key (player_id) has low cardinality (e.g., only a few distinct player_id values), writes to the base table will concentrate on a small number of GSI partitions, causing throttling on the GSI. This throttling on the GSI then back-pressures the base table, resulting in write throttling on the base table even if its own write capacity is underutilized.

Answer

The application is using strongly consistent reads on the GSI, which consumes double the read capacity.

Answer

Point-in-time recovery (PITR) is enabled, consuming extra write capacity.

Answer

The table's auto-scaling settings are not configured correctly for the GSI.

Question 12

A company is migrating a MySQL database to Amazon Aurora MySQL. The current database uses multi-statement transactions with read committed isolation level. The application frequently encounters deadlocks on the source database. Which Aurora MySQL feature can help reduce deadlocks without application changes?

Accepted Answer

Use Amazon RDS Proxy to pool and share database connections.. RDS Proxy helps reduce deadlocks by pooling and reusing database connections, which minimizes the overhead of establishing new connections and reduces contention on database resources. In MySQL, deadlocks often occur when multiple transactions compete for the same resources under high connection churn; by maintaining a stable pool of connections, RDS Proxy lowers the probability of concurrent conflicting locks. Since the proxy is transparent to the application, no code changes are required to benefit from this behavior.

Answer

Use Amazon Aurora Auto Scaling to automatically adjust the number of replicas.

Answer

Use Amazon Aurora Global Database to replicate data to multiple regions.

Answer

Use Amazon Aurora Backtrack to quickly revert transactions.

Question 13

A social media startup is using Amazon ElastiCache for Redis to cache user profiles. The cache currently has a 24-hour TTL. The application experiences a sudden spike in traffic after a celebrity mentions the service, causing the cache to be flooded with requests for uncached profiles. This results in high latency and database load. Which design pattern should the company implement to prevent this in the future?

Accepted Answer

Implement a local cache in each application instance to reduce load on the centralized Redis cluster.. Option B is correct because implementing a local cache (e.g., using a library like Caffeine or Guava) in each application instance reduces the number of requests hitting the centralized Redis cluster during a traffic spike. This pattern, often called a multi-tier or near-cache, absorbs repeated reads for the same uncached profiles locally, preventing cache flooding and database overload without relying solely on Redis TTL adjustments.

Answer

Use a read-through cache with a longer TTL (e.g., 48 hours).

Answer

Use a write-through cache with a longer TTL (e.g., 48 hours).

Answer

Use a write-through cache with a shorter TTL (e.g., 1 hour).

Question 14

A financial services company is designing a ledger system using Amazon QLDB. The application records transactions that must never be modified or deleted. The company expects high write throughput and needs to ensure that the ledger can handle the load without throttling. Which design consideration is MOST important to achieve this?

Accepted Answer

Batch multiple document inserts into a single transaction to reduce the number of transactions.. Option D is correct because Amazon QLDB charges per transaction (document insert, update, or delete) and has a maximum throughput limit of 1,000 transactions per second per ledger. By batching multiple document inserts into a single transaction, you reduce the number of transactions, thereby staying within the throughput limit while still achieving high write throughput. This approach directly addresses the need to avoid throttling without sacrificing the immutability requirements of the ledger system.

Answer

Partition the ledger table by transaction date to distribute write load.

Answer

Create multiple ledgers and distribute writes across them.

Answer

Enable auto-scaling on the ledger to handle bursts of traffic.

Question 15

A company is using Amazon DynamoDB for a shopping cart application. The table has a partition key of `user_id` and a sort key of `item_id`. The application performs frequent updates to the `quantity` attribute. The company notices that write requests are being throttled during peak hours. Which TWO actions would help reduce throttling? (Choose two.)

Accepted Answer

Increase the provisioned write capacity for the table.. Option A is correct because increasing the provisioned write capacity directly raises the number of write capacity units (WCUs) available per second, allowing more write requests to succeed without being throttled. Since the application performs frequent updates to the `quantity` attribute, which consumes write capacity, adding more capacity alleviates throttling during peak hours.

Answer

Use conditional writes to prevent overwrites.

Answer

Enable DynamoDB Streams to process writes asynchronously.

Answer

Enable DynamoDB Accelerator (DAX) for the table.

Question 16

A company is migrating an on-premises MongoDB database to Amazon DocumentDB. The database stores IoT sensor data with time-series characteristics. The application performs range queries on timestamp fields and updates recent documents frequently. Which THREE aspects of DocumentDB should the company consider to optimize performance for this workload? (Choose three.)

Accepted Answer

Implement sharding to distribute write load across multiple instances.. Option B is correct because DocumentDB does not support native sharding like MongoDB; however, for workloads with high write throughput, you can distribute writes by using multiple DocumentDB instances and routing writes based on a shard key in the application layer. This helps avoid write bottlenecks on a single instance and scales write capacity horizontally.

Answer

Use global secondary indexes to speed up queries on the timestamp field.

Answer

Use a single large instance class to avoid sharding complexity.

Question 17

A gaming company uses Amazon DynamoDB as the primary database for their player sessions. The player sessions table has a partition key of 'player_id' and a sort key of 'session_start_time'. The application frequently queries for recent sessions of a specific player, using the query API with 'player_id' and a filter on 'session_start_time' for the last 24 hours. The average item size is 5 KB. The company notices high latency on these queries during peak hours. The table has 10 Read Capacity Units (RCUs) provisioned. There are no indexes. Which design change would MOST improve query performance?

Accepted Answer

Create a Local Secondary Index (LSI) with the same partition key and a sort key of 'session_start_time', and query the index instead of the table.. Option C is correct because creating a Local Secondary Index (LSI) with the same partition key (player_id) and sort key (session_start_time) allows DynamoDB to efficiently retrieve items for a specific player sorted by session_start_time without scanning and filtering. The current query uses a filter on session_start_time after retrieving all sessions for the player, which wastes read capacity and increases latency. Querying the LSI directly uses the sort key to limit the data read to only the last 24 hours, reducing the read footprint and improving performance.

Answer

Increase the RCUs to 100.

Answer

Add a random suffix to the partition key values to distribute writes across partitions.

Answer

Create a Global Secondary Index (GSI) with partition key 'event_type' and sort key 'session_start_time' and query the GSI.

Question 18

A financial services company uses Amazon RDS for MySQL to store transaction data. The database has a single table 'transactions' with 500 million rows. The table has an auto-increment primary key and an index on 'transaction_date'. The company runs a monthly report that aggregates transactions by account_id and transaction_date. The report query uses a GROUP BY on account_id and transaction_date, and scans the entire table. The query takes over 2 hours to complete and often times out. The DBA suggests creating a materialized view. However, the company wants to minimize operational overhead. Which solution meets the requirements with the LEAST operational overhead?

Accepted Answer

Migrate the reporting workload to Amazon Redshift by loading the transactions table into Redshift and running the report query there.. Option B is correct because Amazon Redshift is purpose-built for large-scale analytical queries. By migrating the reporting workload to Redshift, the company offloads the heavy aggregation from the transactional RDS instance to a columnar storage engine that can scan and aggregate 500 million rows efficiently using massively parallel processing (MPP). This approach requires no changes to the existing RDS database and minimizes operational overhead compared to managing a materialized view or manual indexing.

Answer

Increase the RDS instance size to the largest available to improve performance.

Answer

Create a materialized view in MySQL that pre-aggregates the data and refreshes it nightly.

Answer

Add a composite index on (account_id, transaction_date) to speed up the GROUP BY.

Question 19

A company is designing a database for an e-commerce application that needs to store product catalog data. The data is highly structured with relationships between products, categories, and suppliers. The application requires ACID transactions to maintain data integrity when updating inventory. The company expects moderate read and write traffic. Which AWS database service is MOST suitable for this workload?

Accepted Answer

Amazon RDS for MySQL. Amazon RDS for MySQL is the most suitable choice because it provides a fully managed relational database that supports ACID transactions, which are essential for maintaining data integrity during inventory updates. The highly structured nature of product catalog data with relationships between products, categories, and suppliers maps naturally to MySQL's relational model with foreign keys and joins. RDS for MySQL handles moderate read/write traffic efficiently and offers features like Multi-AZ for high availability and read replicas for scaling reads.

Answer

Amazon ElastiCache for Redis

Answer

Amazon Neptune

Answer

Amazon DynamoDB

Question 20

A social media company uses Amazon DynamoDB to store user posts. The table has a partition key of 'user_id' and a sort key of 'post_timestamp'. Each item is about 10 KB. The application needs to retrieve all posts for a given user within a date range. The company recently added a new feature that allows users to 'like' posts, and they store the like count as an attribute in the post item. The like count is updated frequently. The application experiences high write throttling on the table. The table has 1000 WCUs provisioned. The write pattern is bursty. Which design change would MOST effectively reduce write throttling?

Accepted Answer

Add a random suffix to the user_id partition key to distribute writes across multiple partitions.. Option C is correct because the write throttling is caused by a 'hot partition' — all writes for a given user_id go to the same partition, and the bursty write pattern (e.g., many likes on a single post) exceeds the partition's 1,000 WCU limit (1,000 write capacity units per partition). Adding a random suffix to the user_id partition key distributes writes across multiple partitions, effectively increasing the write throughput for that logical user's data. This is a common design pattern for DynamoDB to handle high-traffic items without increasing provisioned capacity.

Answer

Increase the WCUs to 5000.

Answer

Enable DynamoDB Accelerator (DAX) to cache writes.

Answer

Create a Global Secondary Index (GSI) on the like count attribute.