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

Question 1

A company is migrating its on-premises MySQL database to Amazon Aurora MySQL. The current database has a table of 500 GB that is accessed by a nightly batch job that updates 80% of the rows. The company wants to minimize downtime during migration. Which migration strategy is MOST appropriate?

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

Accepted Answer

Use AWS Database Migration Service (DMS) with Aurora as the target.. AWS DMS supports ongoing replication from an on-premises MySQL source to Amazon Aurora MySQL, allowing the nightly batch job to continue running during the initial full load. After the full load completes, DMS captures incremental changes and applies them to Aurora, enabling a cutover with minimal downtime. This approach is ideal for large tables (500 GB) with high update volumes because it avoids a lengthy offline export/import process.

Answer

Create an Aurora read replica from the on-premises database.

Answer

Export the data to Amazon S3 and load it into Aurora using the LOAD DATA FROM S3 command.

Answer

Use mysqldump to export the database and import it into Aurora.

Question 2

A social media application uses Amazon DynamoDB with a table that has a partition key of 'user_id' and a sort key of 'post_timestamp'. The application frequently queries for the 10 most recent posts by a specific user. The query pattern uses a 'begins_with' condition on the sort key with a timestamp prefix. Recently, the query latency has increased significantly for users with many posts. Which design change would improve query performance?

Accepted Answer

Create a local secondary index (LSI) with 'user_id' as partition key and 'post_timestamp' as sort key, and query using reverse order with a limit of 10.. The optimal approach to retrieve the 10 most recent posts for a user is to query the table or an index with the partition key 'user_id' and use ScanIndexForward=false with a Limit of 10. Option A achieves this by creating a Local Secondary Index (LSI) with the same partition key and sort key as the base table. While the base table itself can be queried in reverse order, creating an LSI dedicated to this query pattern can improve performance by offloading reads from the base table index, reducing contention and ensuring fast consistent reads. The LSI can be provisioned with its own read capacity to handle the frequent queries for the most recent posts per user, thus improving overall query performance. Options B, C, and D do not effectively address the specific requirement of per-user recent posts. Option B (DAX) may reduce latency but does not fix the underlying inefficient query pattern (scanning many items per user). Option C (GSI with timestamp as partition key) would allow querying posts globally by time, not per user. Option D (changing partition key to post_id) would break the ability to query all posts by a user. Therefore, Option A is the correct design change.

Answer

Enable DynamoDB Accelerator (DAX) to cache the query results.

Answer

Create a global secondary index (GSI) with 'post_timestamp' as partition key and 'user_id' as sort key.

Answer

Change the table's partition key to 'post_id' to distribute data more evenly.

Question 3

A company runs an online transaction processing (OLTP) workload on Amazon RDS for PostgreSQL. The database has grown to 2 TB and the company needs to run complex analytical queries that join multiple large tables. These analytical queries are slowing down the OLTP operations. What is the MOST cost-effective solution to separate the workloads?

Accepted Answer

Create an RDS for PostgreSQL read replica and route analytical queries to it.. Creating an RDS for PostgreSQL read replica is the most cost-effective solution because it offloads analytical queries to a separate read-only instance without requiring a full database migration. The read replica uses PostgreSQL's native streaming replication to maintain near-real-time data consistency, allowing complex joins on large tables without competing for the primary instance's I/O and CPU resources. This approach minimizes operational overhead and cost compared to migrating to a separate analytics platform like Redshift.

Answer

Use Amazon ElastiCache for caching analytical query results.

Answer

Migrate the analytical queries to Amazon Redshift.

Answer

Migrate the OLTP workload to Amazon DynamoDB.

Question 4

A gaming company uses Amazon DynamoDB for player profiles. The access pattern is to retrieve a player's profile by 'player_id'. Each profile includes a list of 'achievements' that can grow up to 400 KB. Recently, the application has been encountering 'ProvisionedThroughputExceededException' errors. The table has 1000 read capacity units (RCU) and 500 write capacity units (WCU). The average item size is 200 KB. What is the MOST likely cause of the throttling?

Accepted Answer

The application is using strongly consistent reads, which consume double the read capacity.. Strongly consistent reads in DynamoDB consume twice the read capacity units (RCUs) as eventually consistent reads. With an average item size of 200 KB, each strongly consistent read consumes 200 KB / 4 KB = 50 RCUs (rounded up). If the application is using strongly consistent reads, a single read of a 200 KB item uses 50 RCUs, which can quickly exhaust the 1000 RCU table capacity, especially under concurrent access, leading to ProvisionedThroughputExceededException.

Answer

The item size exceeds the DynamoDB item size limit of 400 KB.

Answer

The read capacity units are set too low for the number of partitions.

Answer

The partition key 'player_id' is causing hot partitions.

Question 5

An e-commerce application stores order data in Amazon RDS for MySQL. The database has grown to 1.5 TB and the company needs to retain data for 7 years for compliance. Current queries are becoming slow due to the large table size. The compliance requirement mandates that data older than 1 year must be retained but is rarely accessed. What strategy would reduce the active table size while maintaining compliance?

Accepted Answer

Partition the table by date and archive partitions older than 1 year to Amazon S3 using AWS DMS.. Partitioning the table by date allows you to efficiently archive older, rarely accessed data to Amazon S3 using AWS DMS, reducing the active table size while retaining data for 7 years as required. This approach maintains compliance by keeping the archived data accessible in S3, and it improves query performance on the active partition by reducing the volume of data scanned.

Answer

Create a read replica and run reports against it.

Answer

Delete data older than 1 year and use automated backups for compliance.

Answer

Vertically partition the table to separate frequently and infrequently accessed columns.

Question 6

A company is designing a document storage system using Amazon DynamoDB. Each document is up to 400 KB and is identified by a unique 'document_id'. The access pattern is to retrieve a document by its ID. Which DynamoDB table design is MOST efficient?

Accepted Answer

Use 'document_id' as the primary partition key (only).. DynamoDB can store items up to 400 KB in a single table, and using 'document_id' as the sole partition key directly supports the access pattern of retrieving a document by its ID with a single GetItem call, which is the most efficient operation. No secondary index or composite key is needed, as the primary key alone provides O(1) lookup performance for this use case.

Answer

Use 'document_id' as the partition key and create a GSI on 'document_id'.

Answer

Store documents in Amazon S3 and use DynamoDB to store metadata with a reference to S3.

Answer

Use a composite key: partition key 'document_id' and sort key 'version'.

Question 7

A company is migrating a large Oracle Data Warehouse (10 TB) to Amazon Redshift. The current system uses complex stored procedures, materialized views, and window functions. The company wants to minimize migration effort. Which approach is MOST suitable?

Accepted Answer

Migrate to Amazon Redshift and rewrite stored procedures as SQL scripts.. Amazon Redshift is purpose-built for large-scale data warehousing and analytics, making it the natural target for a 10 TB Oracle Data Warehouse. While stored procedures would need to be rewritten as SQL scripts (since Redshift uses a different procedural language), materialized views and window functions are natively supported, minimizing migration effort compared to other options.

Answer

Migrate to Amazon Aurora MySQL and use its parallel query feature.

Answer

Migrate to Amazon DynamoDB and use DAX for caching.

Answer

Migrate to Amazon RDS for PostgreSQL and use its foreign data wrappers.

Question 8

A company runs a MySQL database on Amazon RDS and wants to capture data changes to replicate to a separate reporting database. Which AWS service should be used to capture these changes with minimal impact on the source database?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication enabled.. AWS DMS with ongoing replication (change data capture) is the correct choice because it reads the binary logs (binlog) from the source RDS MySQL instance to capture inserts, updates, and deletes with minimal overhead. DMS uses a dedicated replication instance and can apply changes to a separate reporting database without requiring schema changes or additional load from application-level polling. This approach is fully managed and designed for low-impact, continuous replication.

Answer

Use Amazon DynamoDB Streams to capture changes from the RDS instance.

Answer

Enable MySQL native replication from the RDS instance to the reporting database.

Answer

Use Amazon Kinesis Data Streams with a custom application to poll the binlog.

Question 9

A company is designing a time-series database for IoT sensor data using Amazon DynamoDB. Each sensor sends a reading every second. The table uses 'sensor_id' as partition key and 'timestamp' as sort key. The application queries for the last hour of data for a specific sensor. The query uses 'KeyConditionExpression' with 'timestamp' between start and end time. The table has auto-scaling enabled. However, the query latency is high. What is the MOST likely cause?

Accepted Answer

The query is scanning from the beginning of time; use 'ScanIndexForward: false' and a 'Limit' parameter.. The high query latency is most likely due to the query scanning all historical data for the sensor before applying the filter on the sort key. By default, DynamoDB queries return results in ascending order of the sort key, and without `ScanIndexForward: false` and a `Limit` parameter, the query may process many irrelevant items before reaching the last hour of data. Setting `ScanIndexForward: false` returns the most recent items first, and adding a `Limit` reduces the number of items scanned, significantly improving latency.

Answer

Enable DynamoDB Accelerator (DAX) to cache the query results.

Answer

The sort key should be 'sensor_id' and partition key should be 'timestamp'.

Answer

The table does not have enough read capacity units; increase the base capacity.

Question 10

A company is designing a database for a global e-commerce platform. The application requires single-digit millisecond read and write latency for user sessions, and must handle millions of requests per second. The data is key-value in nature. Which TWO AWS services should the company consider? (Choose two.)

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is a fully managed NoSQL key-value and document database that delivers single-digit millisecond latency at any scale. It is designed for high-traffic applications requiring millions of requests per second, making it ideal for the global e-commerce platform's user session data.

Answer

Amazon Neptune

Answer

Amazon Redshift

Answer

Amazon RDS for MySQL

Question 11

A company is migrating a 3 TB Oracle database to Amazon Aurora PostgreSQL. The database has a heavy OLTP workload with many small transactions. The migration must have minimal downtime. Which TWO strategies should the company use? (Choose two.)

Accepted Answer

Use AWS DMS with ongoing replication to capture and apply changes.. AWS DMS with ongoing replication (CDC) is the correct strategy because it allows continuous capture and application of changes from the source Oracle database to the target Aurora PostgreSQL, enabling minimal downtime during migration. The heavy OLTP workload with many small transactions is well-suited for DMS's change data capture, which can handle high-volume transactional changes efficiently.

Answer

Convert the database to Amazon Aurora MySQL instead.

Answer

Create an Aurora read replica from the Oracle database.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema.

Question 12

A company is building a real-time leaderboard for a mobile game using Amazon DynamoDB. The leaderboard displays the top 100 players by score. The table has 'game_id' as partition key and 'player_id' as sort key. The score is updated frequently. Which THREE design patterns should the company implement to ensure low-latency reads for the leaderboard? (Choose three.)

Accepted Answer

Enable DynamoDB Accelerator (DAX) to cache the leaderboard query.. DynamoDB Accelerator (DAX) provides an in-memory cache that can serve repeated leaderboard queries with microsecond latency, reducing the read load on the base table and avoiding throttling. For a real-time leaderboard that is read frequently, DAX ensures low-latency responses without needing to query DynamoDB directly each time.

Answer

Use 'ScanIndexForward: false' on the base table to get top players.

Answer

Use 'begins_with' operator on the sort key to filter by score range.

Question 13

A gaming company runs a global leaderboard on Amazon DynamoDB. The leaderboard is updated frequently and must return the top 100 scores in milliseconds. The current design uses a single table with a Global Secondary Index (GSI) on score. However, the query to retrieve top scores often throttles under load. Which design change would best improve performance?

Accepted Answer

Implement a write shard pattern using a random suffix on the partition key and a GSI on score.. The write shard pattern distributes high-frequency writes across multiple partition keys by appending a random suffix, preventing hot partitions. The GSI on score still allows efficient top-N queries by scanning the index in descending order. This avoids throttling by spreading write capacity evenly, while the GSI remains a sparse index that can be queried without impacting the base table's write throughput.

Answer

Use a scan operation with a filter to retrieve top scores.

Answer

Add DynamoDB Accelerator (DAX) in front of the table.

Answer

Switch to strongly consistent reads for the leaderboard query.

Question 14

A startup is building a social media application with a news feed feature. The feed must be personalized and updated in real-time as users post. Which AWS database service is best suited for this workload?

Accepted Answer

Amazon ElastiCache for Redis with sorted sets and pub/sub. Amazon ElastiCache for Redis is the best choice because it provides in-memory data structures like sorted sets for ranking and scoring personalized feeds, and pub/sub for real-time notifications when new posts are published. This combination enables low-latency, real-time feed updates without the overhead of disk-based storage, making it ideal for a social media news feed that must be both personalized and updated in real-time.

Answer

Amazon DynamoDB with Global Secondary Indexes

Answer

Amazon S3 with Select and Glacier

Answer

Amazon RDS for PostgreSQL with read replicas

Question 15

A company wants to store session state for a web application that runs on Amazon EC2 instances behind an Application Load Balancer. The session data is ephemeral and must be highly available. Which two AWS services are suitable for this use case? (Choose two.)

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is a fully managed, serverless NoSQL key-value and document database that offers single-digit millisecond latency at any scale. It is ideal for storing ephemeral session state because it provides built-in high availability and durability by replicating data across multiple Availability Zones (AZs) automatically, without requiring manual failover or replication configuration.

Answer

Amazon Redshift

Answer

Amazon S3

Answer

Amazon RDS for MySQL