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

Question 1

A company runs a MySQL-compatible database on Amazon RDS for a mission-critical application. The database experiences high write latency due to frequent index updates. The team wants to redesign the database to reduce write amplification and improve insert performance. Which TWO design changes could help?

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

Accepted Answer

Use batch INSERT statements instead of single-row inserts. Batch INSERT statements reduce the overhead of per-row index updates by combining multiple rows into a single transaction. This minimizes the number of index tree traversals and log flushes, directly lowering write amplification and improving insert throughput in InnoDB.

Answer

Switch the storage engine from InnoDB to MyISAM

Answer

Upgrade to a larger RDS instance class

Answer

Normalize the database schema to reduce data redundancy

Question 2

A company is migrating an on-premises MongoDB workload to Amazon DocumentDB. The workload includes aggregation pipelines with $lookup and $group operations. The team wants to ensure minimal performance impact. Which THREE steps should they take?

Accepted Answer

Create appropriate indexes on fields used in $lookup and $group. Creating appropriate indexes on fields used in $lookup (local and foreign fields) and $group (the _id field and any sort fields) allows Amazon DocumentDB to avoid full collection scans, significantly reducing query latency and resource consumption during aggregation pipeline execution.

Answer

Disable journaling to reduce I/O overhead

Answer

Enable TLS for all connections

Question 3

Refer to the exhibit. A CloudFormation stack was deployed to create an RDS instance. The application team reports they cannot connect to the database using the endpoint provided. The security group allows inbound traffic on port 3306 from the application's security group. What is the most likely cause?

Accepted Answer

The database password is not provided in the stack outputs; the team may be using the wrong password. The CloudFormation stack outputs typically do not include the database password for security reasons. The application team may be using an incorrect password, which would cause authentication failures even if the network connectivity and security group rules are properly configured. Without the correct password, the RDS instance will reject the connection attempt at the MySQL/MariaDB protocol level.

Answer

The RDS instance is in a Multi-AZ deployment and the endpoint is for the standby

Answer

The security group inbound rule is missing the port 3306

Answer

The RDS endpoint is incorrect; it should include the port number

Question 4

Refer to the exhibit. The exhibit shows CloudWatch metrics from an Amazon RDS for PostgreSQL instance. The application is experiencing slow query performance. Which is the most likely cause?

Accepted Answer

High storage latency due to provisioned IOPS being fully utilized or EBS volume contention. The exhibit shows that the 'Write IOPS' metric is consistently at or near the provisioned IOPS limit, while 'Write Latency' is elevated. When provisioned IOPS are fully utilized, Amazon EBS throttles I/O, causing increased storage latency. This directly degrades query performance for PostgreSQL, especially for write-heavy workloads, as each write operation must wait for the storage layer to complete.

Answer

High CPU utilization due to complex queries

Answer

Too many database connections causing context switching

Answer

Insufficient memory causing disk swaps

Question 5

A company is designing a relational database for an e-commerce application that requires high availability and automated failover across AWS Regions. Which AWS database service should they use?

Accepted Answer

Amazon Aurora Global Database. Amazon Aurora Global Database is the correct choice because it is designed for cross-Region disaster recovery and high availability, replicating data with a typical latency of under one second across multiple AWS Regions. It supports automated failover from the primary Region to one of the secondary Regions, meeting the requirement for automated cross-Region failover without manual intervention.

Answer

Amazon DynamoDB Global Tables

Answer

Amazon RDS with Multi-AZ deployment

Answer

Amazon Redshift with cross-Region snapshot copy

Question 6

A financial services company needs to enforce row-level security on a MySQL database hosted on Amazon RDS. They want to restrict access so that each application user can only see their own data. Which approach should they take?

Accepted Answer

Use a MySQL proxy that injects session context variables and enable row-level security in the application queries. MySQL does not natively support row-level security (RLS) like PostgreSQL or SQL Server. Instead, you can implement RLS by using a MySQL proxy (e.g., ProxySQL, Amazon RDS Proxy) that injects session context variables (e.g., user_id) at connection time. Application queries then reference these variables in WHERE clauses (e.g., WHERE user_id = @@session.user_id), effectively restricting each user to their own data without modifying the underlying schema.

Answer

Place each user's data in a separate database and use VPC endpoints to isolate access

Answer

Create separate database views for each user

Answer

Use IAM database authentication and define fine-grained access policies

Question 7

A company is migrating an on-premises Oracle OLTP workload to AWS. The database has complex stored procedures and requires minimal code changes. Which AWS database service is the most suitable target?

Accepted Answer

Amazon RDS for Oracle. Amazon RDS for Oracle is the most suitable target because it provides native Oracle compatibility, allowing the existing complex stored procedures and PL/SQL code to run with minimal or no changes. This minimizes migration risk and effort, which is the primary requirement for an OLTP workload with complex stored procedures.

Answer

Amazon Redshift

Answer

Amazon DynamoDB

Answer

Amazon Aurora PostgreSQL

Question 8

A company runs a critical PostgreSQL database on Amazon RDS and needs to implement point-in-time recovery (PITR) with a recovery window of the last 35 days. Currently, automated backups are set to retain for 35 days. The database size is 500 GB. What additional configuration is required?

Accepted Answer

No additional configuration is required; automated backups enable PITR. Amazon RDS for PostgreSQL automated backups include transaction logs, which are required for point-in-time recovery. With automated backups retained for 35 days, RDS can restore the database to any second within that retention period by replaying transaction logs against a full backup. No additional configuration is needed because PITR is an inherent feature of automated backups when the retention period is set to 35 days.

Answer

Enable manual snapshots every hour

Answer

Set up a replication slot and stream transaction logs to S3

Answer

Enable Multi-AZ deployment

Question 9

A social media startup is selecting a database for user profiles with a flexible schema and high write throughput. The application is built on Node.js and requires low-latency access. Which database should they choose?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is the correct choice because it is a fully managed NoSQL key-value and document database that offers flexible schema (schemaless), single-digit millisecond latency at any scale, and is designed for high write throughput. It integrates natively with Node.js via the AWS SDK and supports auto-scaling to handle unpredictable write loads, making it ideal for a social media startup's user profile store.

Answer

Amazon Aurora

Answer

Amazon ElastiCache for Redis

Answer

Amazon RDS for MySQL

Question 10

A company needs to run complex analytical queries on structured data in Amazon S3 without loading data into a database. The queries must execute quickly and support standard SQL. Which service should they use?

Accepted Answer

Amazon Athena. Amazon Athena is a serverless interactive query service that allows you to run standard SQL directly on data stored in Amazon S3 without loading or transforming it. It uses Presto under the hood and supports complex analytical queries on structured data with fast execution, making it the ideal choice for this use case.

Answer

Amazon QuickSight

Answer

AWS Glue ETL jobs

Answer

Amazon Redshift Spectrum

Question 11

A company is designing a global application that requires a database with multi-master writes across multiple AWS Regions with sub-second conflict resolution. Which AWS database service should they choose?

Accepted Answer

Amazon DynamoDB Global Tables. Amazon DynamoDB Global Tables is the correct choice because it provides a fully managed, multi-Region, multi-master database that replicates data across AWS Regions with sub-second conflict resolution using last-writer-wins (LWW) or application-defined conflict resolution. This meets the requirement for multi-master writes and sub-second conflict resolution, which is not achievable with the other options.

Answer

Amazon ElastiCache for Redis Global Datastore

Answer

Amazon RDS with Multi-AZ

Answer

Amazon Aurora Global Database

Question 12

A company needs to store JSON documents that require complex querying on nested attributes. The database must support ACID transactions and be fully managed. Which service should they use?

Accepted Answer

Amazon Aurora MySQL. Amazon Aurora MySQL is correct because it supports JSON documents with complex querying on nested attributes via MySQL's JSON data type and JSON path expressions, while also providing full ACID transaction support through its MySQL-compatible engine. As a fully managed service, Aurora handles provisioning, backups, and patching, meeting all stated requirements.

Answer

Amazon DocumentDB (with MongoDB compatibility)

Answer

Amazon DynamoDB

Answer

Amazon Neptune

Question 13

A company has an Amazon RDS for PostgreSQL instance that is running out of storage. The database is 2 TB in size and growing at 10 GB per day. They need a solution that allows automatic storage scaling with minimal downtime. What should they do?

Accepted Answer

Enable Storage Auto Scaling. Amazon RDS Storage Auto Scaling automatically increases storage when free space drops below a threshold, with no downtime. For a 2 TB database growing at 10 GB/day, this feature dynamically adds storage in predefined increments (e.g., 5 GB, 10 GB, or 10% of current storage) up to the maximum limit, preventing manual intervention. This directly addresses the requirement for automatic scaling with minimal disruption.

Answer

Purchase reserved instances for cost savings

Answer

Create a read replica and promote it

Answer

Take a manual snapshot and restore to a larger instance

Question 14

Which TWO database services are suitable for storing time-series data with high ingestion rates? (Select TWO.)

Accepted Answer

Amazon Timestream. Amazon Timestream is a purpose-built time-series database that automatically scales to handle high ingestion rates, with built-in functions for time-series data analysis and storage tiering. Amazon DynamoDB with TTL is suitable because it can ingest high-velocity time-series data at scale, and the TTL feature automatically expires old records, making it a cost-effective choice for time-series workloads that require fast point-in-time lookups.

Answer

Amazon RDS for MySQL

Answer

Amazon ElastiCache for Memcached

Answer

Amazon Redshift

Question 15

Which THREE factors should be considered when choosing between Amazon DynamoDB and Amazon Aurora for a new application? (Select THREE.)

Accepted Answer

Access patterns (key-value vs relational queries). DynamoDB is optimized for key-value and document access patterns with single-digit millisecond latency at any scale, while Aurora is a relational database designed for SQL queries with joins and complex filtering. The choice between them hinges on whether the application requires simple key-based lookups (DynamoDB) or relational queries with multiple access patterns (Aurora).

Answer

Cost per GB of storage

Answer

Maximum data size per table