DBS-C01 Mock Exam 93 — 25 Questions

Question 1

A company uses Amazon DynamoDB for a real-time analytics platform. The table has a partition key of 'customer_id' and a sort key of 'event_timestamp'. The table receives 50,000 write requests per second, evenly distributed across 10,000 customers. The application frequently queries the last 10 events for a given customer. The company notices that some queries are throttled during peak hours. The table's write capacity is set to 50,000 WCUs, and read capacity to 10,000 RCUs. The throttled queries are read requests. What is the most likely cause of the throttling, and what should be done to resolve it?

Accepted Answer

Optimize the query by using Query with KeyConditionExpression on the sort key and Limit=10.. The throttling occurs because the application uses Scan or an inefficient query pattern that consumes excessive read capacity. Using Query with KeyConditionExpression on the sort key and Limit=10 retrieves only the last 10 events per customer efficiently, reducing read consumption and avoiding throttling without increasing RCUs.

Answer

Increase the write capacity units to handle the write load.

Answer

Increase the read capacity units to 20,000 RCUs.

Answer

Add a global secondary index with the same keys to distribute read load.

Question 2

A company runs a customer relationship management (CRM) application on Amazon RDS for PostgreSQL. The application stores customer data in a table with over 50 million rows. The company recently added a new query that searches for customers by their email domain (e.g., '@example.com'). The query uses a LIKE pattern: 'WHERE email LIKE ''%@example.com'''. The query takes over 30 seconds to complete. The DBA has already created a B-tree index on the email column, but it does not help. Which action should the database specialist recommend to improve query performance?

Accepted Answer

Create a trigram index (using pg_trgm extension) on the email column.. The query uses a leading wildcard LIKE pattern ('%@example.com'), which prevents a standard B-tree index from being used because the search string does not have a fixed prefix. A trigram index, provided by the pg_trgm extension, breaks strings into three-character substrings (trigrams) and allows the database to efficiently match patterns with leading wildcards. This index type is specifically designed for fuzzy text matching and LIKE queries with wildcards, reducing the query time from over 30 seconds to milliseconds.

Answer

Create a hash index on the email column.

Answer

Increase the shared_buffers parameter to improve caching.

Answer

Create a B-tree index on the reversed email string.

Question 3

A company is migrating a 3 TB Oracle database to Amazon RDS for Oracle. The migration uses AWS DMS with ongoing replication. The source database is actively used by applications. After the initial full load, the target RDS instance is in sync. However, during the ongoing replication phase, the replication task fails with an error: 'ORA-1555: snapshot too old.' The DMS task has been set up with a source endpoint that uses the 'Oracle TNS' connection method. The company needs to resolve the issue without stopping the source database. Which action should be taken?

Accepted Answer

Increase the undo retention period in the source Oracle database.. The ORA-1555 error indicates that undo data is overwritten before DMS can read it. Increasing undo retention ensures the snapshot data is available longer. Reducing batch size would not address the undo issue. Increasing logging frequency does not affect undo. Using a different connection method does not solve the underlying undo retention problem.

Answer

Change the source endpoint to use the 'Oracle SID' connection method.

Answer

Decrease the batch size of the DMS task.

Answer

Increase the frequency of the DMS task's log mining.

Question 4

A company is migrating a 2 TB on-premises Microsoft SQL Server database to Amazon RDS for SQL Server. The migration must have minimal downtime. The company uses AWS DMS with ongoing CDC. During the full load phase, the DMS task fails with an error indicating that the source table 'Orders' has a large number of LOB columns and the task is running out of memory. The DMS replication instance is a dms.c4.large with 4 GB RAM. The company needs to complete the migration successfully. What should the company do?

Accepted Answer

Increase the replication instance size to dms.c4.xlarge (8 GB RAM).. Option B is correct because increasing the replication instance size provides more memory to handle LOB columns. Option A is wrong because disabling LOB support would truncate LOB data. Option C is wrong because using a smaller LOB chunk size reduces memory per LOB but may not solve the memory issue; increasing instance size is more direct. Option D is wrong because using parallel tasks does not address memory constraints.

Answer

Set the LOB chunk size to 64 KB in the task settings.

Answer

Split the 'Orders' table into multiple DMS tasks running in parallel.

Answer

Change the DMS task settings to disable LOB support for the 'Orders' table.

Question 5

A company is migrating a 500 GB MySQL database from an on-premises server to Amazon RDS for MySQL. The company uses AWS DMS with ongoing replication. The initial full load completes successfully, and the target RDS instance is in sync. However, after a few hours, the replication task fails with an error: 'Last Error: Error executing source loop; The table 'orders' has a row size larger than the maximum payload size of the target endpoint.' The target RDS instance is configured with db.r5.large and the default parameter group. The company has already verified that the table does not have any BLOB or TEXT columns. What is the MOST likely cause of this error?

Accepted Answer

The source table has a row that exceeds the MySQL row size limit.. RDS for MySQL has a maximum row size limit (65,535 bytes). DMS attempts to insert a row that exceeds this limit. Increasing the instance size does not change the row size limit. Using a different storage engine might help, but InnoDB is default. Enabling compression reduces row size but is not a direct solution for the DMS error.

Answer

The DMS task is using a low-memory instance.

Answer

The target RDS instance's max_allowed_packet parameter is set too low.

Answer

The target RDS instance does not have enough storage allocated.

Question 6

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 7

A financial services company runs a critical application on Amazon RDS for PostgreSQL with Multi-AZ and automated backups enabled. The database stores sensitive customer data, and the security team recently audited the environment and found that the database master user password is stored in plain text in a configuration file on the application server. Additionally, the security team noted that the database security group allows inbound traffic from 0.0.0.0/0 on port 5432. The company needs to remediate these issues immediately without downtime and must ensure that access to the database is restricted to only the application servers, and that password management follows AWS best practices. The application connects to the database using the master user credentials. What should the database specialist do to meet these requirements?

Accepted Answer

Modify the security group to allow inbound traffic on port 5432 only from the private IP address of the application server. Store the database password in AWS Secrets Manager and configure automatic rotation. Update the application to retrieve the password from Secrets Manager.. Option A is correct because it restricts inbound traffic to the application server's private IP address, which is the secure approach for traffic within AWS, and it uses AWS Secrets Manager to store the master password with automatic rotation, following AWS best practices for secrets management. This eliminates the plain-text password exposure and ensures no downtime by updating the application to retrieve credentials dynamically from Secrets Manager.

Answer

Modify the security group to allow inbound traffic on port 5432 only from the public IP address of the application server. Rotate the password using the AWS CLI and store the new password in an encrypted file on the application server. Update the application to read the password from the encrypted file.

Answer

Modify the security group to allow inbound traffic on port 5432 only from the private IP address of the application server. Rotate the master password by modifying the DB instance and store the new password in AWS Systems Manager Parameter Store as a SecureString. Update the application to retrieve the password from Parameter Store.

Answer

Modify the security group to allow inbound traffic on port 5432 only from the private IP address of the application server. Rotate the password using the AWS CLI and store the new password in an encrypted file on the application server. Update the application to read the password from the encrypted file.

Question 8

A company is migrating its on-premises Oracle database to Amazon RDS for Oracle. The database is 2 TB in size and contains both transactional and analytical workloads. The company uses Oracle Data Pump for migration. During the migration, the database specialist notices that the export job on the source database is taking longer than expected and is generating significant I/O, impacting production performance. The company needs to minimize the impact on the source database while completing the migration within a 48-hour window. The source database is currently running on a dedicated server with sufficient CPU and memory. Which course of action should the database specialist take?

Accepted Answer

Use Oracle Data Pump in network_link mode to directly import the data from the source to the target RDS instance without intermediate files.. Option C is correct because Oracle Data Pump's network_link mode allows the source database to export data directly to the target RDS for Oracle instance over a database link, bypassing the need to write intermediate dump files to disk. This eliminates the I/O overhead on the source server's storage subsystem, which is the primary cause of the production performance impact, while still completing the migration within the 48-hour window.

Answer

Use AWS Database Migration Service (AWS DMS) with full load and change data capture (CDC) to migrate the database.

Answer

Use Data Pump to export to a networked file system (NFS) mount point to offload storage I/O to the network.

Answer

Increase the parallel workers for the Data Pump export job to 8 to speed up the export and reduce the time window.

Question 9

A company runs a production Amazon DynamoDB table with on-demand capacity. The table stores session data for a web application. Recently, users have reported occasional slow response times. The operations team notices that the table's ConsumedWriteCapacityUnits metric shows occasional spikes that exceed the provisioned throughput (though on-demand auto-scales), and ThrottledWriteEvents metrics show occasional throttling. The application uses the AWS SDK with default retry logic. The database specialist is asked to investigate. Upon reviewing the table configuration, the specialist finds that the table has a simple primary key (partition key only) and the data access pattern is heavily skewed toward a small number of partition keys. The application writes in batches of 25 items using the BatchWriteItem API. What should the specialist recommend to reduce throttling and improve performance?

Accepted Answer

Implement write sharding by adding a random suffix to the partition key to distribute writes more evenly.. The correct answer is A because the throttling is caused by a hot partition: the table uses a single partition key, and writes are heavily skewed toward a few keys. By adding a random suffix to the partition key (write sharding), the writes are distributed evenly across all partitions, eliminating hot spots and reducing throttling. The on-demand capacity mode already handles overall throughput, but it cannot prevent throttling on individual partitions when access is skewed.

Answer

Increase the provisioned read capacity units to handle the load.

Answer

Switch the table to provisioned capacity mode and increase write capacity.

Answer

Enable DynamoDB Accelerator (DAX) to cache write operations.

Question 10

A company runs an e-commerce application on AWS using an Aurora MySQL database cluster. The security team mandates that all database audit logs must be stored in Amazon S3 for at least one year for compliance. The database is currently configured to publish audit logs to Amazon CloudWatch Logs. The security team wants to use AWS Database Migration Service (DMS) to migrate the database to a new Aurora MySQL cluster, and during the migration, the audit logs must continue to be captured uninterrupted. Which solution meets these requirements with the LEAST operational overhead?

Accepted Answer

Enable the Aurora MySQL advanced audit feature with file-based output, and configure the DMS task to use these log files as a source for ongoing replication.. Option D is correct because the Aurora MySQL advanced audit feature can output audit logs directly to files on the DB instance, and DMS can be configured to read these files as a source for ongoing replication. This ensures that audit logs are continuously captured and stored in S3 (via file-based output or subsequent upload) without interruption during the migration, minimizing operational overhead by avoiding additional services or manual log forwarding.

Answer

Enable Performance Insights on the source cluster and export the data to S3.

Answer

Create an Aurora MySQL read replica and enable audit logs on the replica, then migrate from the replica.

Answer

Use AWS CloudTrail to capture SQL queries and deliver them to S3.

Question 11

A logistics company uses Amazon RDS for MySQL to track package shipments. The 'shipments' table contains 200 million rows and has a primary key on 'shipment_id' (UUID). The application frequently queries for shipments by 'tracking_number', which is a unique string of 20 characters. The DBA created a B-tree index on tracking_number. The queries by tracking_number are fast, but inserts are becoming slower over time. The table has 50 GB of data. The company plans to double the insert rate next month. The database is a db.r5.large instance with 500 GB of Provisioned IOPS SSD storage. The instance's CPU utilization is below 30%, and there is no lock contention. What should the database specialist do to improve insert performance?

Accepted Answer

Change the primary key from UUID to an auto-increment integer, and keep the tracking_number index.. Option C is correct because UUID primary keys cause random writes and index fragmentation, degrading insert performance as the table grows. Switching to an auto-increment integer primary key allows sequential writes to the clustered index, reducing page splits and improving insert throughput. The B-tree index on tracking_number remains to support fast queries, while the new primary key eliminates the UUID write overhead.

Answer

Add a read replica and route insert queries to the replica.

Answer

Drop the index on tracking_number to reduce write overhead.

Answer

Increase the provisioned IOPS to 20,000.

Question 12

A company runs a multi-tenant SaaS application on Amazon DynamoDB. Each tenant's data is stored in a separate table named with a tenant-specific prefix (e.g., tenant1_orders, tenant2_orders). The application uses DynamoDB Streams to replicate data to a central analytics table. Recently, the company added a new large tenant that generates 10x more write traffic than any other tenant. The DynamoDB Streams for the large tenant's table is falling behind by several hours, causing stale data in the analytics table. The company has already increased the write capacity of the large tenant's table to 50,000 WCUs, but the streams lag persists. The analytics table is also in DynamoDB and uses a Global Secondary Index (GSI) for querying. The streams processing Lambda function performs simple transformations and writes to the analytics table. The Lambda function is not throttled. Which action would resolve the streams lag?

Accepted Answer

Enable DynamoDB on-demand mode for the large tenant's table to allow automatic scaling of stream shards.. Option A is correct because DynamoDB Streams shards are directly tied to the physical partitions of the table. When a table is in provisioned mode, the number of stream shards is fixed and determined by the table's partitions, which cannot scale independently. Enabling on-demand mode allows DynamoDB to automatically split partitions and thus increase the number of stream shards, enabling higher stream throughput to keep up with the large tenant's write volume. This directly addresses the root cause of the streams lag without requiring manual partition management.

Answer

Remove the GSI from the analytics table to reduce write amplification.

Answer

Increase the Lambda function's reserved concurrency to the maximum.

Answer

Increase the write capacity of the large tenant's table to 100,000 WCUs.

Question 13

You are managing an Amazon RDS for PostgreSQL Multi-AZ DB instance that handles a high-traffic e-commerce application. Recently, the database has been experiencing intermittent slowdowns during peak hours. You have enabled Enhanced Monitoring and Performance Insights. After reviewing the Performance Insights dashboard, you notice that the 'db.sql.queries.avg_latency' metric spikes during the slowdowns, and the top SQL queries are all simple SELECT statements on a frequently accessed 'orders' table. The table has over 10 million rows and is indexed on 'order_id', 'customer_id', and 'order_date'. The average query latency for these SELECT statements jumps from 5 ms to over 500 ms during the spikes. You also observe that the 'ReadIOPS' metric on the DB instance is consistently below the provisioned IOPS limit of the gp2 storage. The DB instance type is db.r5.large with 16 GB memory. The 'DatabaseConnections' metric shows that the number of connections is well within the max_connections limit (set to 200). However, the 'CPUCreditBalance' for the underlying EC2 instance, which is a T3 medium, drops to near zero during the spikes. The 'CPUUtilization' metric is below 50%. Which of the following is the MOST likely cause and the appropriate action to resolve the issue?

Accepted Answer

The issue is due to CPU credit exhaustion on the T3 instance. Change the DB instance class from db.t3.medium to a dedicated CPU instance type such as db.r5.large.. The correct answer is A. The 'CPUCreditBalance' dropping to near zero on a T3 instance indicates CPU credit exhaustion, which throttles the baseline CPU performance and causes query latency spikes. Even though 'CPUUtilization' is below 50%, T3 instances rely on CPU credits for burstable performance; when credits are exhausted, the instance is limited to the baseline (e.g., 10% for t3.medium), causing severe latency increases for simple SELECT statements. Moving to a dedicated CPU instance like db.r5.large eliminates credit-based throttling and provides consistent performance.

Answer

The issue is due to a memory bottleneck causing queries to spill to disk. Increase the instance memory by moving to a db.r5.xlarge instance.

Answer

The issue is caused by a connection pool exhaustion. Increase the max_connections parameter and use connection pooling.

Answer

The issue is caused by a missing index on the 'orders' table. Add a composite index on the columns used in the WHERE clauses.

Question 14

A company runs a critical e-commerce application on Amazon RDS for PostgreSQL with a db.r5.2xlarge instance and 500 GB of gp2 storage. The application experiences periodic write spikes during flash sales. During these events, the WriteIOPS metric exceeds the provisioned baseline IOPS of 1,500, and the database becomes unresponsive for several seconds. The DBA has configured a CloudWatch alarm on WriteIOPS, but the alarm triggers after the performance issue occurs. The company needs to ensure that the database can handle these spikes without downtime. The budget allows for moderate cost increases. What should the DBA do?

Accepted Answer

Modify the DB instance to use gp3 storage with provisioned IOPS of 5,000.. Option B is correct because gp3 storage provides a baseline of 3,000 IOPS regardless of storage size, and allows provisioning up to 16,000 IOPS independently. By setting provisioned IOPS to 5,000, the database can handle write spikes without exceeding the IOPS limit, preventing unresponsiveness. This solution fits the moderate cost increase budget, as gp3 is typically 20% cheaper than gp2 for equivalent performance.

Answer

Increase the allocated storage to 1,000 GB to increase baseline IOPS to 3,000.

Answer

Enable Performance Insights to identify the problematic queries and tune them.

Answer

Add a read replica and redirect read traffic to it to reduce write contention.

Question 15

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 16

A company has a production Amazon RDS for SQL Server database that stores financial data. The database administrator wants to audit all access to sensitive columns (e.g., credit card numbers) using the SQL Server Audit feature. The database is part of a Multi-AZ deployment. The administrator has enabled audit logging to the 'DEFAULT' file audit target, but the audit files are being written to the local instance storage and are not being retained after failover. The compliance team requires that audit logs be stored in Amazon S3 for at least 7 years. The administrator has set up an event subscription to send database events to an S3 bucket using AWS DMS, but the audit logs are not being captured. What should the administrator do to meet the compliance requirements?

Accepted Answer

Create a new SQL Server Audit target using the Amazon S3 option and configure the audit to write to an S3 bucket.. Option C is correct because Amazon RDS for SQL Server supports writing SQL Server Audit logs directly to an Amazon S3 bucket as an audit target. This is the only native method that persists audit logs beyond the instance lifecycle, ensuring they survive Multi-AZ failover and meet the 7-year retention requirement. The DEFAULT file target writes to ephemeral instance storage, which is lost on failover, and RDS event subscriptions or DMS cannot capture SQL Server Audit output.

Answer

Use RDS event subscriptions to send database audit logs to an S3 bucket.

Answer

Configure AWS DMS to continuously replicate the audit database to an S3 bucket.

Answer

Enable RDS Enhanced Monitoring and configure it to send logs to CloudWatch Logs.

Question 17

A company is building a real-time chat application that requires storing messages with a maximum of 10,000 characters per message. The application needs sub-millisecond latency for reads and writes. The data must be durable and replicated across three Availability Zones. The development team wants to minimize operational overhead. Which AWS database service is most appropriate?

Accepted Answer

Amazon DynamoDB with DAX. Amazon DynamoDB with DAX is the most appropriate choice because it provides single-digit millisecond latency for reads and writes, supports up to 400 KB per item (easily accommodating 10,000 characters), and offers built-in replication across three Availability Zones for durability. DAX (DynamoDB Accelerator) further reduces read latency to sub-millisecond by serving as an in-memory cache, while DynamoDB itself handles write durability and replication automatically, minimizing operational overhead.

Answer

Amazon ElastiCache for Redis with replication

Answer

Amazon RDS for PostgreSQL with Multi-AZ

Answer

Amazon Aurora MySQL with Multi-AZ

Question 18

A gaming company uses Amazon ElastiCache for Redis as a leaderboard for real-time game scores. The leaderboard is updated frequently by millions of users. The application uses sorted sets with player scores. Recently, the leaderboard update latency increased and the cache evictions spiked. The company needs to ensure low-latency updates and high availability. The current setup is a single Redis node. Which design should be implemented?

Accepted Answer

Use a Redis Cluster with multiple shards. Enable AOF persistence and use a read replica for the leaderboard queries.. Option C is correct because Redis Cluster with multiple shards distributes the write load across shards, reducing per-node pressure and evictions. Enabling AOF persistence ensures durability, while using a read replica for leaderboard queries offloads read traffic from the primary shard, maintaining low-latency updates. This design provides both horizontal scaling and high availability, addressing the increased update latency and eviction spikes.

Answer

Upgrade to a larger single Redis node instance type to handle the load.

Answer

Replace Redis with DynamoDB for the leaderboard, using a global secondary index on score.

Answer

Use ElastiCache for Redis with cluster mode disabled and enable Multi-AZ.

Question 19

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 20

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.