DBS-C01 Practice Test 25 — 15 Questions

Question 1

A company is designing a database for an IoT application that ingests millions of small sensor readings per second. The data is time-series and queries are mostly range scans over time. The company needs a cost-effective solution with high write throughput. Which AWS service should the database specialist recommend?

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

Accepted Answer

Amazon Timestream. Amazon Timestream is a purpose-built time-series database designed for IoT and operational applications that ingest millions of data points per second. It automatically manages storage tiers (in-memory and magnetic) to optimize cost, and its query engine is optimized for range scans over time, making it the most cost-effective and high-throughput choice for this workload.

Answer

Amazon Redshift with auto-ingest

Answer

Amazon RDS for PostgreSQL with pg_partman extension

Answer

Amazon DynamoDB with time-series data modeling

Question 2

A company runs an OLTP application on Amazon RDS for PostgreSQL. The database stores customer orders. The application frequently queries orders by customer_id and order_date. The orders table has 100 million rows. The query performance has degraded over time. The database has a single index on customer_id. The company needs to improve query performance without changing the application code. Which design change should be made?

Accepted Answer

Create a composite index on (customer_id, order_date).. The query performance has degraded because the existing single-column index on customer_id can filter by customer but still requires a full sort or scan within that customer's rows to satisfy the order_date condition. Creating a composite index on (customer_id, order_date) allows the database to use a single index seek to locate the exact rows matching both columns, eliminating the need for an additional sort or filter pass. This directly addresses the query pattern without any application code changes.

Answer

Partition the table by order_date using PostgreSQL declarative partitioning.

Answer

Upgrade to a larger RDS instance type.

Answer

Enable RDS Performance Insights to identify bottlenecks.

Question 3

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 4

A company is migrating a 100 GB Microsoft SQL Server database from an on-premises data center to Amazon RDS for SQL Server. The migration uses AWS DMS with full load only (no ongoing replication). The full load completes successfully, but the company's application team reports that some data in the target database is missing. The source database was not modified during the migration. The DMS task logs show no errors. What is the MOST likely cause of the missing data?

Accepted Answer

The DMS task did not use transactional consistency.. DMS full load captures a snapshot of the source database. If the source database has active transactions during the snapshot, some changes may not be included. Using transactional consistency ensures a consistent snapshot. Disabling foreign keys is not recommended. Using a larger instance does not affect consistency.

Answer

The DMS task used the 'Change Data Capture' mode instead of full load.

Answer

The target RDS instance was not large enough to store all data.

Answer

The source database had foreign key constraints that were not migrated.

Question 5

A healthcare company stores patient records in Amazon DynamoDB. Each record includes a 'patient_id' (partition key) and 'visit_date' (sort key). The company needs to run ad-hoc queries to find all patients seen by a specific doctor within a date range. Which design approach minimizes cost and latency for this query pattern?

Accepted Answer

Create a global secondary index (GSI) with doctor_id as partition key and visit_date as sort key.. A Global Secondary Index (GSI) with doctor_id as the partition key and visit_date as the sort key allows efficient key-based queries for all patients seen by a specific doctor within a date range. This avoids full table scans and filter operations, minimizing both cost (read capacity units) and latency. DynamoDB can directly retrieve the indexed items without scanning the base table.

Answer

Query the base table using a filter expression on doctor_id.

Answer

Create a local secondary index (LSI) with doctor_id as sort key.

Answer

Use a Scan operation with a filter expression for doctor_id and visit_date.

Question 6

A database team is troubleshooting a performance issue on an Amazon RDS for PostgreSQL instance. They notice that the 'DiskQueueDepth' metric is consistently high. Which TWO actions should the team take to resolve this issue? (Choose TWO.)

Accepted Answer

Enable storage auto scaling.. Options B and C are correct. A high disk queue depth indicates an I/O bottleneck on the RDS instance. Enabling storage auto scaling (B) allows the storage to automatically scale up when I/O demand increases, which can reduce queue depth. Increasing provisioned IOPS (C) directly improves the I/O performance by providing more throughput. Option A is incorrect because increasing connections can increase I/O contention, not reduce it. Option D is incorrect because PostgreSQL does not support query caching in the same way as MySQL, and it is not a solution to an I/O bottleneck. Option E is incorrect because Multi-AZ provides high availability but does not directly improve I/O performance.

Answer

Increase the number of database connections.

Answer

Enable query caching in PostgreSQL.

Answer

Enable Multi-AZ deployment.

Question 7

A company is migrating an on-premises Oracle database to Amazon RDS for Oracle. The database size is 2 TB and the network bandwidth is limited. The company needs to minimize downtime during migration. Which AWS service should be used?

Accepted Answer

AWS Database Migration Service (DMS). AWS DMS is the correct choice because it supports ongoing replication from Oracle to Amazon RDS for Oracle, allowing you to perform a live migration with minimal downtime. With a 2 TB database and limited bandwidth, DMS can use Change Data Capture (CDC) to keep the target synchronized while the initial full load runs, then switch over with only a brief outage. This makes it ideal for minimizing downtime compared to offline methods.

Answer

Oracle Data Pump export and import

Answer

AWS Schema Conversion Tool (SCT)

Answer

AWS Snowball Edge

Question 8

A company is designing a new application that requires a relational database with sub-millisecond read latency for a global user base. The workload is read-heavy with occasional writes. Which database solution should they choose?

Accepted Answer

Amazon Aurora with Auto Scaling. Amazon Aurora with Auto Scaling is the correct choice because it provides a relational database (MySQL/PostgreSQL-compatible) with sub-millisecond read latency via its distributed storage layer and read replicas. The read-heavy workload benefits from Aurora's automatic scaling of read capacity, while occasional writes are efficiently handled by the cluster volume. Aurora's architecture decouples compute and storage, enabling fast failover and consistent performance for global users.

Answer

Amazon DynamoDB with DAX

Answer

Amazon RDS for MySQL with Multi-AZ

Answer

Amazon ElastiCache for Redis

Question 9

A company runs a critical e-commerce application on Amazon Aurora MySQL with a single DB instance. The database has 8 TB of data and uses the default writer endpoint. Recently, the application experienced a 10-minute outage during a primary instance failover. The failover was triggered by an underlying hardware issue. The database specialist needs to minimize downtime during future failovers. The application team is unwilling to modify the application code to handle connection retries. The company has a 99.99% SLA requirement. Which solution should the database specialist implement to meet the SLA with minimal application changes?

Accepted Answer

Create an Amazon RDS Proxy and configure the application to connect to the proxy endpoint. Amazon RDS Proxy sits between the application and the database, pooling and reusing database connections. During a failover, RDS Proxy maintains the client connections and transparently reconnects to the new primary instance, so the application does not experience a connection loss and does not need to implement retry logic. This directly addresses the 10-minute outage by reducing failover downtime to seconds, meeting the 99.99% SLA without application code changes.

Answer

Increase the DB instance class to a larger size to improve performance and reduce failover time

Answer

Enable Multi-AZ deployment with automatic failover

Answer

Create a cross-Region read replica and promote it to primary during failover

Question 10

A company is designing a database for an IoT application that ingests millions of small sensor readings per second. The data is time-series and queries are mostly range scans over time. The company needs a cost-effective solution with high write throughput. Which AWS service should the database specialist recommend?

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

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is best suited because it is a fully managed NoSQL key-value database that delivers single-digit millisecond latency at any scale. The primary query pattern—retrieving the most recent reading for a given device—maps directly to a DynamoDB table with a composite primary key (device_id as partition key, timestamp as sort key) and a Query operation with ScanIndexForward=false and Limit=1. This design handles millions of writes per second with consistent performance, unlike relational databases that struggle with such high-velocity ingestion.

Answer

Amazon Aurora

Answer

Amazon ElastiCache for Redis

Answer

Amazon RDS for MySQL

Question 11

A company is migrating a 3 TB PostgreSQL database from on-premises to Amazon RDS for PostgreSQL. The migration must have minimal downtime. The on-premises network bandwidth to AWS is 1 Gbps. The database supports a critical application that cannot be stopped for more than 30 minutes. The migration team has set up AWS DMS with ongoing replication using CDC. The initial full load is expected to take 8 hours. During the migration, the DMS task fails with an error indicating that the source database's transaction log is being cleaned before DMS can capture changes. The source database has a transaction log retention period of 24 hours. The migration team notices that the CDC phase falls behind during peak hours. What should the team do to complete the migration successfully within the downtime window?

Accepted Answer

Increase the transaction log retention period on the source database and use a larger DMS replication instance. The issue is that DMS cannot keep up with changes during peak hours, causing the transaction logs to be cleaned before DMS reads them. Increasing the source database's transaction log retention period gives DMS more time to catch up. Using a larger DMS replication instance will increase processing capacity. Option C is the best combination. Option A (Snowball) would take too long. Option B (Direct Connect) doesn't solve the log retention issue. Option D (decrease retention) makes the problem worse.

Answer

Use AWS Snowball to transfer the full database and then use DMS for CDC

Answer

Increase the network bandwidth using AWS Direct Connect

Answer

Decrease the transaction log retention period to free up space

Question 12

A company uses Amazon RDS for SQL Server with a 4 TB database for a financial reporting application. The database performs nightly batch updates that take 6 hours. The company needs to reduce the batch update time to under 2 hours. The current instance is db.r5.8xlarge with 64 vCPUs and 512 GB memory. The batch process is I/O-bound with high write throughput. Which change will MOST effectively reduce the batch update time?

Accepted Answer

Switch to Provisioned IOPS (io2) with 80,000 IOPS.. The batch process is I/O-bound with high write throughput, so the bottleneck is disk I/O, not compute or memory. Switching to Provisioned IOPS (io2) with 80,000 IOPS provides a predictable, high-performance storage tier that can sustain the required write throughput, directly reducing the batch update time from 6 hours to under 2 hours. RDS for SQL Server on io2 volumes delivers consistent low-latency I/O, which is critical for write-heavy workloads.

Answer

Upgrade to db.r5.16xlarge with 128 vCPUs.

Answer

Increase the instance memory to 1024 GB.

Answer

Enable Multi-AZ deployment.

Question 13

A company is migrating a 3 TB on-premises Oracle database to Amazon Aurora PostgreSQL. The source database runs Oracle 12c on a Linux server with a 1 Gbps network connection to AWS via Direct Connect. The migration must have minimal downtime and be completed within a 2-day window. The database is heavily used during business hours (9 AM - 5 PM) and has low activity overnight. The company has a test environment on AWS already. The migration team plans to use AWS DMS with CDC for ongoing replication. They also plan to use AWS SCT for schema conversion. They start the migration on a Friday evening. On Saturday morning, the CDC replication lag is increasing, and the target Aurora instance is struggling to keep up. The team notices that the source database has a high number of write transactions even during the weekend, and the DMS replication instance is a small instance type. Which action should the team take to complete the migration on time?

Accepted Answer

Upgrade the DMS replication instance to a larger size to increase throughput.. Upgrading the DMS replication instance increases its capacity to handle high write volume. Option A is wrong because it doesn't address the bottleneck. Option C is wrong because it adds complexity and may not help. Option D is wrong because it doesn't solve the replication lag.

Answer

Reduce the number of tables being migrated to lower the load.

Answer

Pause the migration and restart it on Monday during low activity.

Answer

Switch to use AWS Snowball to transfer data and bypass DMS.

Question 14

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 15

A startup is building a social media application that stores user posts in Amazon DynamoDB. The access pattern is to retrieve posts by user_id (partition key) sorted by post_timestamp (sort key) in descending order. The table has a global secondary index (GSI) with the same key structure but with different projection. The application reads from the GSI. Recently, the team noticed that writes to the base table are throttled during peak hours. The write capacity is balanced across partitions. Which design change should be made to reduce write throttling?

Accepted Answer

Add a write sharding pattern by appending a random suffix to the partition key.. The write throttling is caused by a hot partition, where a single partition key (user_id) receives a disproportionate number of writes. By appending a random suffix to the partition key, the writes are distributed evenly across multiple partitions, eliminating the hot spot. This is a well-known sharding pattern for DynamoDB when access patterns create uneven write traffic, and it does not require changing the read logic because the GSI can be queried with a sort key condition on post_timestamp.

Answer

Use DynamoDB Accelerator (DAX) for writes.

Answer

Increase the write capacity units (WCUs) on the base table.

Answer

Switch to on-demand capacity mode.