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

Question 1

A company is migrating a self-managed MongoDB database to Amazon DocumentDB. The database stores user profiles and activity logs. The activity logs are write-heavy and accessed infrequently. The company wants to optimize cost and performance for the workload. Which THREE design decisions should the company make?

Accepted Answer

Enable encryption at rest for both databases.. Option A is correct because enabling encryption at rest for Amazon DocumentDB is a security best practice that ensures data is encrypted using AWS Key Management Service (KMS) keys. This is a mandatory design consideration for compliance and data protection, and it does not significantly impact cost or performance for the workload described.

Answer

Use Amazon S3 for storing activity logs instead of DocumentDB.

Answer

Use a single DocumentDB cluster for both workloads with appropriate read replicas.

Question 2

A company runs a reporting application on Amazon Redshift. The application queries a large fact table that is distributed by a key. The report queries filter on a date column. The report performance is slow. The database has 10 nodes. The company wants to improve query performance by optimizing the table design. Which design change should be made?

Accepted Answer

Set the sort key to the date column.. Setting the sort key to the date column improves query performance by enabling range-restricted scans. When queries filter on a date column, Redshift uses zone maps to skip blocks that do not contain relevant data, drastically reducing the number of rows scanned. This is the most direct and cost-effective optimization for filter-heavy workloads on large fact tables.

Answer

Increase the number of nodes in the cluster.

Answer

Change the distribution style to ALL to avoid data redistribution.

Answer

Change the distribution style to KEY on the date column.

Question 3

A company uses Amazon RDS for MySQL to store e-commerce order data. The orders table has millions of rows and is frequently queried by order_id. The company also runs periodic reports that aggregate data by order_date. The reports are slow. The database has a primary key on order_id. The company needs to improve report performance without affecting OLTP queries. Which design change should be made?

Accepted Answer

Create a secondary index on order_date.. Creating a secondary index on order_date allows MySQL to quickly locate rows matching the report's date range without scanning the entire table, significantly improving aggregation performance. This index is separate from the primary key on order_id, so OLTP queries that filter by order_id remain unaffected. The index provides a balanced approach: it accelerates read-heavy reporting while adding minimal overhead to write operations.

Answer

Upgrade to a larger instance type.

Answer

Create a read replica and run reports on the replica.

Answer

Partition the table by order_date.

Question 4

A company is designing a disaster recovery strategy for an Amazon RDS for PostgreSQL database. The database is 2 TB in size. The company wants to recover to a different AWS Region with minimal data loss. Which TWO options meet these requirements?

Accepted Answer

Create a read replica in the other Region.. Option A is correct because Amazon RDS for PostgreSQL supports creating a cross-Region read replica, which uses PostgreSQL's native streaming replication to keep the replica nearly synchronized with the source database. This provides a Recovery Point Objective (RPO) of seconds to minutes, minimizing data loss in a disaster scenario. The replica can be promoted to a standalone primary in the other Region for failover.

Answer

Use AWS Database Migration Service (DMS) with ongoing replication to a target in the other Region.

Answer

Take a manual snapshot and copy it to the other Region. Restore from the snapshot.

Answer

Use AWS Backup to schedule cross-Region backups.

Question 5

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?

Accepted Answer

Use AWS Database Migration Service (DMS) with Aurora as the target.. Option B is correct because using AWS DMS with Aurora MySQL as the target allows a full load plus ongoing replication to minimize downtime. Option A is wrong because mysqldump would require application downtime during the dump and restore. Option C is wrong because an Aurora read replica can only be created from an existing Aurora instance, not from on-premises. Option D is wrong because S3 export/import is for data warehousing, not for minimal downtime migration.

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 6

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.. Option D is correct because creating a secondary index with 'user_id' as partition key and 'post_timestamp' as sort key, and using a 'Query' with reverse order and limit 10, avoids scanning all posts. Option A is wrong because a global secondary index still requires scanning if not optimized. Option B is wrong because changing partition key to 'post_id' would break the query pattern. Option C is wrong because DynamoDB Accelerator (DAX) caches results but doesn't reduce the read capacity consumed per query; the query still scans all items for a user.

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 7

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.. Option A is correct because creating a read replica offloads analytical queries from the primary instance, and it's cost-effective. Option B is wrong because Amazon Redshift is a data warehouse, more expensive and overkill for this use case. Option C is wrong because DynamoDB is NoSQL and not suitable for complex joins. Option D is wrong because ElastiCache is an in-memory cache, not for analytical queries.

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 8

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 9

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.. Option C is correct because partitioning the table by date and archiving old partitions to a separate table or S3 meets compliance and reduces active table size. Option A is wrong because vertical partitioning (splitting columns) doesn't address the row count issue. Option B is wrong because read replicas do not reduce storage size. Option D is wrong because deleting data violates compliance.

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 10

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

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

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.. Option D is correct because AWS DMS with ongoing replication (change data capture) captures changes from the source RDS MySQL binlog with minimal impact. Option A is wrong because RDS does not support native MySQL replication to external targets easily. Option B is wrong because DynamoDB Streams is for DynamoDB, not RDS. Option C is wrong because Kinesis Data Streams is not designed to directly capture RDS changes; requires additional configuration.

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 13

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.. Option B is correct because the sort key 'timestamp' is in ascending order, so the most recent data is at the end of the sorted range. When querying for the last hour, DynamoDB may scan a large number of items before finding the recent ones, especially if the sensor has been running for a long time. Using 'ScanIndexForward: false' with a limit would reverse the order and retrieve recent items first. Option A is wrong because auto-scaling would add capacity if needed, but the issue is query design. Option C is wrong because the sort key is already 'timestamp'. Option D is wrong because DAX would reduce latency but not the underlying read consumption.

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 14

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. Options A and D are correct. DynamoDB is a key-value and document database that provides single-digit millisecond latency at any scale. ElastiCache for Redis is an in-memory data store that can achieve sub-millisecond latency for key-value data. Option B (RDS) is relational and not designed for single-digit millisecond at millions of requests per second without heavy caching. Option C (Redshift) is a data warehouse. Option E (Neptune) is a graph database.

Answer

Amazon Neptune

Answer

Amazon Redshift

Answer

Amazon RDS for MySQL

Question 15

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.. Options A and D are correct. AWS DMS with ongoing replication allows minimal downtime by replicating changes from Oracle to Aurora. Using Aurora PostgreSQL provides better compatibility with Oracle via the Babelfish feature? Actually, Babelfish is for SQL Server, not Oracle. But Aurora PostgreSQL supports many Oracle features. Option B is wrong because SCT helps assess but doesn't handle ongoing replication. Option C is wrong because converting to Aurora MySQL would require additional compatibility changes. Option E is wrong because a read replica cannot be created from Oracle.

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.