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

Question 1

Which TWO AWS services can be used to cache database query results to improve read performance? (Select TWO.)

Accepted Answer

Amazon ElastiCache for Redis. Amazon ElastiCache for Redis and Amazon ElastiCache for Memcached are in-memory caching services that can store the results of database queries, allowing subsequent identical queries to be served from the cache instead of hitting the database. This reduces latency and improves read performance by offloading read traffic from the primary database.

Answer

Amazon DynamoDB Accelerator (DAX)

Answer

Amazon CloudFront

Answer

Amazon RDS read replica

Question 2

A company is designing a database for an IoT application that ingests millions of sensor readings per second. Each reading is a small JSON document (less than 1 KB) and must be stored with low latency. Queries are primarily by device ID and timestamp range. The team expects to rarely update or delete old data. Which AWS database solution is MOST cost-effective and performant?

Accepted Answer

Amazon DynamoDB with a composite primary key (device_id, timestamp). Amazon DynamoDB with a composite primary key (device_id, timestamp) is the most cost-effective and performant solution because it provides single-digit millisecond latency for point lookups and range queries, scales horizontally to handle millions of writes per second, and its on-demand or auto-scaling capacity model avoids over-provisioning. The access pattern of querying by device ID and timestamp range maps directly to DynamoDB's partition and sort key design, enabling efficient use of the Query API without scanning.

Answer

Amazon S3 with a partition prefix of device_id/timestamp/

Answer

Amazon Redshift with distribution key on device_id

Answer

Amazon RDS for MySQL with multiple read replicas

Question 3

A financial services company runs a critical PostgreSQL database on Amazon RDS. The database stores transaction records and requires point-in-time recovery (PITR) with a recovery window of 35 days. The database size is 500 GB and grows at 10 GB per day. The team wants to minimize storage costs while meeting the recovery SLA. Which backup strategy should they use?

Accepted Answer

Enable automated backups with a retention period of 35 days. Amazon RDS automated backups provide point-in-time recovery within the retention period (up to 35 days) by storing daily snapshots and transaction logs, allowing recovery to any second. Option A is incorrect because manual snapshots every hour would be costly and provide less granular recovery compared to automated backups with transaction logs. Option C is incorrect because pg_dump to S3 only provides daily full backups, not point-in-time recovery to an arbitrary second. Option D is incorrect because AWS Backup cross-region copies are for disaster recovery, not for local PITR, and would incur additional costs.

Answer

Take manual snapshots every hour and retain for 35 days

Answer

Disable automated backups and use pg_dump to S3 daily

Answer

Use AWS Backup to copy snapshots to another region daily

Question 4

A startup is building a social media application that requires storing user profiles, posts, comments, and likes. The workload has variable traffic, with spikes after marketing campaigns. The team expects to run complex JOIN queries to generate a user's feed. Which AWS database service is MOST suitable for this relational workload?

Accepted Answer

Amazon RDS for PostgreSQL. Amazon RDS for PostgreSQL is the most suitable choice because the workload requires complex JOIN queries on relational data (user profiles, posts, comments, likes). PostgreSQL provides full SQL support, ACID compliance, and robust indexing capabilities (e.g., B-tree, GiST, GIN) that efficiently handle multi-table joins. RDS also offers managed scaling, automated backups, and read replicas to accommodate traffic spikes after marketing campaigns.

Answer

Amazon Neptune

Answer

Amazon DynamoDB with global secondary indexes

Answer

Amazon ElastiCache for Redis

Question 5

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?

Accepted Answer

Enable DynamoDB Auto Scaling to adjust RCU dynamically. Enabling DynamoDB Auto Scaling (Option A) is the most cost-effective solution because it automatically adjusts read capacity in response to traffic patterns, handling short bursts without manual intervention or over-provisioning. It does not require application changes. Option B is more expensive due to higher static capacity. Option C requires switching to eventually consistent reads, which would change application behavior and may not be acceptable for session data requiring strong consistency. Option D adds cost and complexity with DAX without addressing read capacity limits directly.

Answer

Increase RCU to 2000 and enable Auto Scaling

Answer

Switch to eventually consistent reads

Answer

Use DynamoDB Accelerator (DAX) for caching reads

Question 6

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. The query uses SELECT *, which forces the database engine to retrieve all columns from the table. Even though the index on creation_date is used for sorting and filtering, the query optimizer may choose a full index scan because it still needs to access the table rows for the non-indexed columns. This is often more efficient than random lookups for a large range, but it still results in scanning many index entries and performing table lookups, causing the observed slowness.

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 7

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. Increasing the InnoDB buffer pool size allows more data and indexes to be cached in memory, reducing disk I/O for write operations by delaying writes and enabling more efficient page merging. This directly addresses the high disk I/O and CPU usage from frequent INSERT and UPDATE operations without requiring application changes.

Answer

Enable Multi-AZ deployment for redundancy

Answer

Change the storage engine to MyISAM

Answer

Upgrade to Amazon Aurora MySQL

Question 8

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. When the fact table uses DISTSTYLE AUTO, Redshift may choose a distribution key that is not user_id. When the fact table is distributed on a different key, joining on user_id requires Redshift to redistribute the fact table rows across nodes to match the dimension table's distribution, causing significant network traffic and slower performance. The dimension table with DISTSTYLE ALL is already replicated to all nodes, so the bottleneck is the fact table's distribution mismatch.

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 9

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 with ongoing replication (change data capture) is the correct choice because it allows continuous replication of changes from the source Oracle database to the target Aurora PostgreSQL, minimizing downtime by keeping the target nearly in sync until a final cutover. The 2 TB size and 10% daily change rate make a full offline export impractical, and DMS handles both schema conversion (via SCT integration) and data migration with minimal interruption.

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 10

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. A global secondary index (GSI) with partition key 'game_id' and sort key 'score' allows DynamoDB to efficiently retrieve the top 10 players for a given game_id by querying the index with the Query API, using ScanIndexForward=false to get items in descending order of score, and Limit=10. This avoids full table scans and leverages GSI's separate throughput capacity. Option C (LSI) could theoretically be used if the table was created with the LSI, but GSIs are preferred because they can be added after table creation, have dedicated throughput, and are more scalable. Additionally, querying an LSI still consumes base table capacity, which may affect performance.

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 11

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. Amazon Redshift is purpose-built for complex analytical queries on large datasets, and AWS DMS can continuously replicate data from RDS for MySQL into Redshift without impacting the source transactional workload. This separates OLTP and OLAP environments, ensuring performance isolation.

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 12

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. Amazon DynamoDB Global Tables is correct because it provides a fully managed, multi-region, multi-active database solution that replicates data across AWS Regions with low-latency reads and writes. It supports strongly consistent reads for critical data when using the `ConsistentRead` parameter, which returns the most up-to-date data from the source region. This makes it ideal for key-value workloads requiring global scalability and strong consistency.

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 13

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 expires and deletes old items after a defined timestamp, reducing storage costs without manual intervention. This is ideal for time-series data where older records are rarely accessed, as TTL offloads the deletion process to DynamoDB in the background, freeing up provisioned throughput.

Answer

Increase DynamoDB read capacity units to improve query performance

Answer

Decrease DynamoDB write capacity units to reduce cost

Question 14

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 is the most suitable choice because DynamoDB itself provides single-digit millisecond latency at any scale, supports strong consistency for both reads and writes (via ConsistentRead=true for reads), and is designed for high availability and millions of requests per second. DAX is an in-memory cache that accelerates reads, but it only supports eventual consistency; however, reads requiring strong consistency can be served directly from DynamoDB while DAX handles the majority of eventually consistent reads, thereby meeting the low-latency and strong consistency requirements of the application.

Answer

Amazon ElastiCache for Memcached

Answer

Amazon S3 with S3 Select

Answer

Amazon RDS for MySQL with Multi-AZ deployment

Question 15

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 is the correct choice because it provides a managed SQL Server database engine that is fully compatible with on-premises SQL Server, minimizing application changes. The service supports complex queries with joins and reporting workloads without requiring code modifications, as it uses the same T-SQL dialect and features.

Answer

Amazon RDS for MySQL

Answer

Amazon Redshift

Answer

Amazon DynamoDB