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

Question 1

A company uses Amazon DynamoDB to store session data for a web application. During peak hours, they experience occasional ProvisionedThroughputExceededException errors. The table has a read capacity of 1000 RCU and a write capacity of 500 WCU. The application uses strongly consistent reads. The traffic pattern shows short bursts of reads exceeding 1000 RCU. What is the MOST cost-effective way to handle these bursts without changing the application?

Answer

Enable DynamoDB Auto Scaling to adjust RCU dynamically

Answer

Increase RCU to 2000 and enable Auto Scaling

Answer

Switch to eventually consistent reads

Answer

Use DynamoDB Accelerator (DAX) for caching reads

Question 2

A media company stores video metadata in Amazon Aurora MySQL. The application performs frequent range queries on a 'creation_date' column. The table has 10 million rows. The team notices that queries filtering on 'creation_date' are slow despite an index on that column. The query pattern is: SELECT * FROM videos WHERE creation_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY creation_date LIMIT 100. The execution plan shows a full index scan. What is the MOST likely cause?

Accepted Answer

The query selects all columns, causing excessive table access. A full index scan occurs when the query selects a large portion of the table; however, the issue may be that the index is not selective enough, leading to many rows being read. But the key problem is that the index on 'creation_date' is a B-tree, and the query uses LIMIT with ORDER BY, so the optimizer may decide to scan the entire index to find matching rows because the range is wide. However, the correct answer is that the query is selecting all columns (SELECT *), causing the database to fetch rows from the table after the index lookup, which is expensive. Option A is wrong because the index is being used (full index scan). Option B is wrong because partitioning would help if designed correctly, but the issue is more fundamental. Option D is wrong because a composite index with columns in wrong order may not help.

Answer

The index should be a composite index on (creation_date, id)

Answer

The table is not partitioned by creation_date

Answer

The index on creation_date is not being used

Question 3

A company runs a MySQL database on Amazon RDS for an e-commerce platform. The application performs frequent INSERT and UPDATE operations on the 'orders' table. The team notices an increase in disk I/O and CPU usage. They want to optimize the database for write-heavy workloads without changing the application. Which option is the MOST effective?

Accepted Answer

Increase the InnoDB buffer pool size. RDS provides enhanced monitoring and parameter groups. For write-heavy workloads, using InnoDB with appropriate buffer pool settings is key. Option A is wrong because switching to MyISAM is not recommended (no transaction support, table-level locking). Option B is wrong because enabling Multi-AZ does not improve write performance (synchronous replication adds latency). Option D is wrong because converting to Aurora MySQL would improve performance but is more expensive and not without changes.

Answer

Enable Multi-AZ deployment for redundancy

Answer

Change the storage engine to MyISAM

Answer

Upgrade to Amazon Aurora MySQL

Question 4

A company uses Amazon Redshift for data warehousing. They run a query that joins a large fact table (10 billion rows) with a small dimension table (1 million rows). The query is slow. The distribution style of the fact table is AUTO, and the dimension table has DISTSTYLE ALL. The join key is user_id. What is the MOST likely reason for the poor performance?

Accepted Answer

The fact table's distribution key is not user_id, causing redistribution. Redshift distributes data across slices. For joins, distribution keys should align to avoid data redistribution. With DISTSTYLE ALL on the dimension table, it is copied to all nodes, which is good. The fact table uses AUTO, which may choose a suboptimal distribution key. If the fact table is distributed by user_id, it should be fine. However, the issue may be that the dimension table is not sorted on the join key, or the fact table's distribution key is not user_id. Option B is wrong because DISTSTYLE ALL is actually beneficial for small tables. Option C is wrong because no sort key on dimension table is less critical. Option D is wrong because column compression helps scan performance but not joins.

Answer

The dimension table does not have a sort key on user_id

Answer

The dimension table uses DISTSTYLE ALL, which is inefficient for joins

Answer

The fact table should have column compression disabled for the join key

Question 5

A company is migrating an on-premises Oracle database to Amazon Aurora PostgreSQL. The database is 2 TB and has a daily change rate of 10%. They have a 1 Gbps network connection to AWS. They want to minimize downtime during the migration. Which migration approach should they use?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication. AWS DMS can perform continuous replication from Oracle to Aurora PostgreSQL with minimal downtime. Option A is wrong because pg_dump/restore would require a long downtime for the initial load and catch-up. Option B is wrong because an Aurora read replica cannot be created from an external Oracle database. Option D is wrong because the Schema Conversion Tool (SCT) is for schema conversion, not data migration.

Answer

Use AWS Schema Conversion Tool (SCT) to convert schema, then import data

Answer

Use pg_dump to export the database and pg_restore to import

Answer

Create an Aurora read replica from the Oracle database

Question 6

A gaming company uses Amazon DynamoDB to store player scores. The table has a partition key of 'game_id' and a sort key of 'player_id'. The application needs to retrieve the top 10 players for a given game_id based on score (stored as an attribute). The game_id has high cardinality. The team wants to avoid full table scans. Which design pattern is MOST efficient?

Accepted Answer

Create a global secondary index with partition key game_id and sort key score. DynamoDB does not support ORDER BY on non-key attributes. To efficiently retrieve top scores, a global secondary index (GSI) with game_id as partition key and score as sort key allows descending queries. Option B is wrong because using scan with limit is inefficient. Option C is wrong because reading all items in the game partition and sorting in application is costly. Option D is wrong because a local secondary index (LSI) would require the same partition key but cannot have a different sort key if the table's sort key is player_id; also LSIs have size limits.

Answer

Query the table by game_id and sort the results in the application

Answer

Use a Scan operation with a filter expression and limit 10

Answer

Create a local secondary index with partition key game_id and sort key score

Question 7

An e-commerce company uses Amazon RDS for MySQL to store order data. They need to run complex analytical queries on the data without impacting the performance of the transactional workload. Which TWO solutions should they implement? (Choose TWO.)

Accepted Answer

Use Amazon Redshift for analytics and load data via AWS DMS. Creating a read replica offloads read queries from the primary database. Amazon Redshift is a data warehouse optimized for analytics; data can be loaded from RDS. Option B is wrong because enabling Multi-AZ does not provide a separate read-only endpoint. Option C is wrong because DynamoDB is not suitable for complex analytical queries. Option D is wrong because S3 alone cannot run analytical queries.

Answer

Migrate the transaction table to Amazon DynamoDB

Answer

Store order data in Amazon S3 and query with Amazon Athena

Answer

Enable Multi-AZ to use the standby for queries

Question 8

A company is designing a database for a global application that requires low-latency reads and writes across multiple AWS regions. The application data is key-value and does not require complex queries. The team needs strong consistency for critical data. Which TWO services should they consider? (Choose TWO.)

Accepted Answer

Amazon DynamoDB Global Tables. DynamoDB Global Tables provide multi-region replication with strong consistency option. Option A is wrong because RDS cross-region read replicas do not support writes in multiple regions. Option D is wrong because S3 is not a database for key-value low-latency. Option E is wrong because ElastiCache is a cache, not a durable database.

Answer

Amazon S3 with cross-region replication

Answer

Amazon ElastiCache for Redis with global datastore

Answer

Amazon RDS for PostgreSQL with cross-region read replicas

Question 9

A company runs a time-series application on Amazon DynamoDB. The data has a pattern of frequent writes for recent data and rare reads for older data. They want to optimize storage costs and query performance for the time-series data. Which THREE strategies should they implement? (Choose THREE.)

Accepted Answer

Use DynamoDB Time to Live (TTL) to automatically delete old data after a certain period. DynamoDB Time to Live (TTL) automatically deletes expired items, reducing storage. Archiving old data to S3 Glacier provides cheap storage. Using S3 and Athena for historical queries avoids scanning old data in DynamoDB. Option D is wrong because increasing RCU is expensive and not a storage optimization. Option E is wrong because decreasing WCU may cause throttling.

Answer

Increase DynamoDB read capacity units to improve query performance

Answer

Decrease DynamoDB write capacity units to reduce cost

Question 10

A company is designing a database for a global e-commerce application that requires low-latency reads and writes with strong consistency. The application is expected to handle millions of requests per second and requires high availability. Which AWS database service is most suitable for this workload?

Accepted Answer

Amazon DynamoDB with DynamoDB Accelerator (DAX). Amazon DynamoDB with DAX can provide single-digit millisecond latency at scale, supports strongly consistent reads, and is designed for high availability across multiple AWS regions. Option A (Amazon RDS MySQL with Multi-AZ) does not provide the same scalability for millions of requests per second. Option C (Amazon ElastiCache for Memcached) is a caching layer, not a primary database. Option D (Amazon S3) is object storage, not suitable for low-latency transactional workloads.

Answer

Amazon ElastiCache for Memcached

Answer

Amazon S3 with S3 Select

Answer

Amazon RDS for MySQL with Multi-AZ deployment

Question 11

A team is migrating an on-premises Microsoft SQL Server database to AWS. The database is used for reporting and analytics, with complex queries that join multiple tables. The team wants to minimize application changes and ensure compatibility. Which AWS service should they use?

Accepted Answer

Amazon RDS for SQL Server. Amazon RDS for SQL Server provides native SQL Server compatibility, minimizing application changes. Option B (Amazon RDS for MySQL) is a different database engine requiring SQL dialect changes. Option C (Amazon DynamoDB) is NoSQL and not suited for complex joins. Option D (Amazon Redshift) is a data warehouse optimized for analytics but requires re-engineering of queries and schema.

Answer

Amazon RDS for MySQL

Answer

Amazon Redshift

Answer

Amazon DynamoDB

Question 12

A company is building a real-time leaderboard for a gaming application. The leaderboard must update scores within seconds and support queries for top players and individual ranks. Which database design is most appropriate?

Accepted Answer

Amazon DynamoDB with a global secondary index on score. Amazon DynamoDB with a global secondary index (GSI) on score can efficiently query top players and individual ranks. Option A (Amazon RDS with ORDER BY) becomes slow at scale. Option C (Amazon ElastiCache sorted sets) is good for leaderboards but not durable. Option D (Amazon S3) is not suitable for real-time updates.

Answer

Amazon S3 with Range GET requests

Answer

Amazon ElastiCache for Redis with sorted sets

Answer

Amazon RDS for PostgreSQL with ORDER BY and LIMIT

Question 13

A startup is building a mobile application that needs to store user profiles and preferences. The data is schema-less and will grow rapidly. The application requires single-digit millisecond latency for reads and writes. Which AWS database should they choose?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is a fully managed NoSQL database designed for low-latency, schema-less data at any scale. Option A (Aurora) is relational and requires predefined schema. Option C (Redshift) is for analytics. Option D (RDS SQL Server) is relational and not optimized for rapid scaling of schema-less data.

Answer

Amazon Aurora (MySQL compatible)

Answer

Amazon Redshift

Answer

Amazon RDS for SQL Server

Question 14

A company uses Amazon Aurora MySQL for its online transaction processing (OLTP) application. Recently, read traffic has increased significantly, causing performance issues. The company wants to offload read traffic with minimal application changes. Which solution should they implement?

Accepted Answer

Create one or more Aurora Replicas. Aurora Replicas are asynchronous read replicas that can handle read traffic without application changes (just use different endpoint). Option A (Multi-AZ) is for high availability, not read scaling. Option C (ElastiCache) requires application changes. Option D (DynamoDB) would require a full redesign.

Answer

Enable Multi-AZ deployment

Answer

Migrate to Amazon DynamoDB

Answer

Add an Amazon ElastiCache cluster in front of Aurora

Question 15

A financial services company needs a database to store transaction records with strong consistency and the ability to run complex analytical queries. The data volume is in the terabytes and is expected to grow. The company also needs point-in-time recovery. Which AWS database solution meets these requirements?

Accepted Answer

Amazon Redshift with automated snapshots. Amazon Redshift with automated snapshots provides point-in-time recovery and is optimized for analytical queries on large datasets. Option A (DynamoDB) does not support complex SQL analytics. Option B (RDS MySQL) is for OLTP and may not handle terabytes of analytical queries efficiently. Option D (ElastiCache) is in-memory caching.

Answer

Amazon RDS for MySQL with read replicas

Answer

Amazon ElastiCache for Redis with AOF persistence

Answer

Amazon DynamoDB with on-demand backup