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

Question 1

A company uses Amazon Aurora MySQL-Compatible Edition for its e-commerce platform. During flash sales, the database experiences high write contention on the 'orders' table, causing slow inserts and deadlocks. The development team wants to reduce contention without changing the application code. Which database design strategy is MOST effective?

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

Accepted Answer

Enable Aurora Multi-Master to allow multiple write nodes. Aurora Multi-Master enables multiple writer nodes to accept write operations concurrently, which directly reduces write contention on the 'orders' table during high-volume flash sales. Unlike single-master Aurora, Multi-Master allows each writer to handle inserts independently, minimizing deadlocks and improving throughput without requiring application code changes.

Answer

Implement manual sharding across multiple Aurora clusters

Answer

Add more read replicas to offload read traffic

Answer

Use a larger instance type with higher IOPS

Question 2

A healthcare application requires storing patient records that include structured data (e.g., name, age) and unstructured data (e.g., medical images). The application needs to query structured data with SQL and serve images via HTTPS. Which combination of AWS services provides the MOST efficient design?

Accepted Answer

Amazon RDS for PostgreSQL for structured data and Amazon S3 for images with Amazon CloudFront. Amazon RDS for PostgreSQL provides full SQL querying capabilities for structured patient data, while Amazon S3 with Amazon CloudFront offers scalable, low-latency HTTPS serving of medical images. CloudFront caches images at edge locations, reducing latency and offloading S3, which is the most efficient design for mixed structured/unstructured workloads.

Answer

Amazon DynamoDB for structured data and Amazon S3 for images with DynamoDB Accelerator (DAX)

Answer

Amazon RDS for MySQL for structured data and Amazon EFS for images

Answer

Amazon Redshift for structured data and Amazon S3 for images

Question 3

A startup is building a real-time chat application that requires storing messages with high write throughput and low-latency reads. The data model is simple: each message has a conversation ID, timestamp, and content. Which database design is MOST appropriate?

Accepted Answer

Amazon DynamoDB with conversation_id as partition key and timestamp as sort key. Amazon DynamoDB with conversation_id as partition key and timestamp as sort key is the most appropriate design because it directly supports high write throughput and low-latency reads for a real-time chat application. The partition key enables even distribution of writes across partitions, while the sort key allows efficient range queries for messages within a conversation ordered by time, matching the access pattern perfectly.

Answer

Amazon RDS for MySQL with a single table and indexes on conversation_id and timestamp

Answer

Amazon Timestream to store messages as time-series data

Answer

Amazon Redshift with columnar storage and compression

Question 4

A company is running an Oracle database on Amazon RDS. The database has a large table that is frequently accessed by multiple applications. The DBA notices that the table has a high number of index scans but the queries are still slow. Upon investigation, the buffer cache hit ratio is low. Which design change would BEST improve performance?

Accepted Answer

Increase the instance size to provide more memory. The low buffer cache hit ratio indicates that the database's memory (buffer cache) is insufficient to cache frequently accessed data blocks, causing excessive physical I/O. Increasing the instance size provides more memory, which expands the buffer cache and allows more data to be cached, reducing disk reads and improving query performance. This directly addresses the root cause of the slow queries despite efficient index scans.

Answer

Convert the table to columnar storage using Amazon Redshift

Answer

Add a read replica to offload queries

Answer

Migrate the table to Amazon DynamoDB with DAX

Question 5

Which TWO factors should be considered when designing a database for an IoT workload that ingests millions of sensor readings per second? (Choose 2.)

Accepted Answer

Implement data retention and aggregation to reduce storage costs. IoT workloads generate massive volumes of data, and implementing data retention policies (e.g., automatically deleting raw data after a set period) combined with aggregation (e.g., downsampling sensor readings into hourly or daily averages) directly reduces storage costs. This is a core design pattern for time-series databases like Amazon Timestream, which supports automatic retention and aggregation via scheduled queries or rollups.

Answer

Ensure strong consistency for all reads

Answer

Enforce ACID transactions for all writes

Answer

Use a graph database to model relationships between sensors

Question 6

Which TWO database design considerations are critical when migrating a high-traffic e-commerce website from Oracle to Amazon Aurora MySQL? (Choose 2.)

Accepted Answer

Review and adapt application SQL queries for MySQL compatibility. Oracle and MySQL use different SQL dialects, data types, and functions. Migrating a high-traffic e-commerce application requires reviewing and adapting all SQL queries to ensure compatibility with Aurora MySQL, including handling Oracle-specific features like sequences, hierarchical queries (CONNECT BY), and PL/SQL stored procedures. Failure to do so will cause runtime errors or degraded performance.

Answer

Enable eventual consistency for read replicas to reduce latency

Answer

Use Aurora Multi-Master to distribute write load

Answer

Compress all tables to reduce storage costs

Question 7

A company is investigating a performance issue with an Amazon Aurora MySQL database. The output of the describe-db-instances command is shown. The application experiences intermittent slowdowns during write-heavy periods. Which change would MOST likely improve write performance?

Accepted Answer

Increase the provisioned IOPS to 10000. Increasing provisioned IOPS directly improves write throughput by reducing I/O latency. In this scenario, write-heavy periods cause intermittent slowdowns, which are often due to hitting the IOPS limit of the current instance configuration. Option A is incorrect because Aurora Replicas only offload reads and have no effect on write performance. The writer instance still handles all writes regardless of replicas.

Answer

Add an Aurora Replica to distribute read traffic and reduce load on the writer

Answer

Change StorageType to gp2 and increase AllocatedStorage to 200 GB

Answer

Set StorageEncrypted to false to reduce encryption overhead

Question 8

A developer is configuring IAM permissions for a Lambda function that accesses a DynamoDB table named 'Orders'. The policy shown is attached to the Lambda execution role. The function needs to delete items but only if the item contains only 'order_id' and 'status' attributes. Which statement about this policy is correct?

Accepted Answer

The function can only delete items that contain exactly the attributes 'order_id' and 'status'. The IAM policy includes a condition key `dynamodb:Attributes` that restricts the `DeleteItem` action to items containing exactly the attributes 'order_id' and 'status'. This condition ensures the function can only delete items that match the specified attribute set, enforcing a fine-grained access control at the item attribute level.

Answer

The function can delete any item in the Orders table because the condition is on the resource

Answer

The function cannot delete any items because the DeleteItem action is not allowed

Answer

The function cannot call Query on the table because it is not listed in the actions

Question 9

A developer runs the command `aws rds describe-db-instances --db-instance-identifier mydb` and gets the output containing `Source_Region: us-east-1` and `Replica_Mode: async`. Which conclusion can be drawn about the database configuration?

Accepted Answer

The database is a read replica of another instance. The output from the describe-db-instances command indicates that this database is configured as a read replica. Read replicas in Amazon RDS are identified by the presence of a source region and asynchronous replication mode, which are characteristic of read replica configurations. Multi-AZ deployments use synchronous replication and do not expose these fields, making option D the only viable conclusion.

Answer

The database is a Multi-AZ read replica

Answer

The database engine is Aurora MySQL

Answer

The database is a primary instance in a Multi-AZ deployment

Question 10

A startup is building a real-time analytics dashboard on AWS. The data arrives as time-series events from IoT devices at a rate of 10,000 writes per second. Each event is approximately 1 KB. The dashboard requires sub-second query latency for the last hour of data and must support ad-hoc analytical queries on historical data spanning months. The team needs to design a cost-effective database solution. Which combination of AWS services should be used?

Accepted Answer

Amazon DynamoDB with DynamoDB Accelerator (DAX) for real-time queries, and Amazon S3 with Amazon Athena for historical analytics.. DynamoDB with DAX provides microsecond to sub-millisecond latency for real-time queries on the last hour of data, while S3 with Athena offers a cost-effective serverless solution for ad-hoc analytical queries on historical data spanning months. DynamoDB's time-to-live (TTL) feature can automatically expire data older than one hour, keeping the hot dataset small and performant, and Athena's pay-per-query pricing avoids the cost of maintaining a separate analytics cluster.

Answer

Amazon ElastiCache for Redis for real-time queries, and Amazon OpenSearch Service for historical analytics.

Answer

Amazon Redshift for both real-time and historical queries, using auto-scaling and materialized views.

Answer

Amazon RDS for PostgreSQL with read replicas for real-time queries, and Amazon Redshift for historical analytics.

Question 11

A company is migrating an on-premises Oracle OLTP database to Amazon Aurora PostgreSQL. The database has a complex schema with stored procedures, triggers, and sequences. During the migration, the team notices that the conversion tool reports several incompatibilities. Which strategy should the team use to handle the database schema changes with minimal downtime?

Accepted Answer

Use AWS Database Migration Service (DMS) with the AWS Schema Conversion Tool (SCT) to convert the schema and migrate data, then handle remaining incompatibilities during a cutover window.. AWS DMS with SCT is the recommended approach for heterogeneous migrations like Oracle to Aurora PostgreSQL. SCT converts the schema (including stored procedures, triggers, and sequences) and identifies incompatibilities, while DMS handles ongoing replication to minimize downtime. The remaining incompatibilities can be resolved during a planned cutover window, which is the standard strategy for complex schema migrations with minimal downtime.

Answer

Deploy Amazon RDS for PostgreSQL with Babelfish to run Oracle PL/SQL code natively.

Answer

Use pg_dump and pg_restore to migrate the schema, and then test and fix any errors.

Answer

Manually rewrite all stored procedures and triggers to PostgreSQL syntax before migration.

Question 12

A retail company uses Amazon DynamoDB to store shopping cart data. The cart items are frequently updated as users add or remove products. The application reads the entire cart each time the user views it. The cart size averages 50 KB but can reach up to 400 KB. The company wants to reduce read costs and improve performance. Which design change would be most effective?

Accepted Answer

Compress the cart items before storing them in DynamoDB and decompress on read.. Compressing cart items before storing them in DynamoDB reduces the item size, which directly lowers read capacity unit (RCU) consumption since DynamoDB charges based on read item size rounded up to 4 KB increments. For a 400 KB item, compression can shrink it significantly, reducing the number of 4 KB blocks read and thus cutting costs. Decompression on read adds minimal CPU overhead but yields substantial performance gains by reducing network transfer time and read latency.

Answer

Switch to larger DynamoDB instance types to handle larger items.

Answer

Use DynamoDB Accelerator (DAX) to cache the cart data.

Answer

Normalize the cart data into separate tables for cart headers and line items.

Question 13

An e-commerce platform uses Amazon RDS for PostgreSQL to store order data. The database has a table "orders" with 500 million rows. The application runs a report query that aggregates daily sales for the last 30 days. The query currently scans the entire table and takes 15 minutes to complete. The team needs to reduce the query time to under 30 seconds. Which solution is MOST cost-effective?

Accepted Answer

Create a materialized view that stores daily sales aggregates and refresh it nightly.. A materialized view precomputes and stores the daily sales aggregates, allowing the application to query the precomputed result set directly instead of scanning 500 million rows. Refreshing the materialized view nightly (e.g., using pg_cron or a scheduled lambda) ensures the data is fresh enough for the report while keeping query time under 30 seconds. This approach avoids the cost of larger instances or complex partitioning and is the most cost-effective solution for a read-heavy, periodic aggregation workload.

Answer

Partition the table by month and query only the relevant partitions.

Answer

Add a composite index on the date column and the sales amount column.

Answer

Upgrade the RDS instance to a larger size with more vCPUs and memory.

Question 14

A company uses Amazon DynamoDB to store application logs. The logs have a TTL of 7 days. The operations team needs to run ad-hoc analytical queries on logs older than 7 days, which are automatically deleted by TTL. Which solution should the team implement to retain and analyze old logs?

Accepted Answer

Use DynamoDB Streams to export logs to Amazon S3 and query with Amazon Athena.. DynamoDB Streams can capture item deletions (including TTL-based deletions) and export them to Amazon S3. Once the logs are in S3, Amazon Athena can run ad-hoc SQL queries directly on the data, providing a cost-effective and serverless analytical solution for logs older than 7 days.

Answer

Disable TTL and use a scheduled Lambda function to archive logs to Amazon S3.

Answer

Increase the TTL to 30 days and query the logs directly in DynamoDB.

Answer

Use AWS Glue to crawl DynamoDB tables and create a data catalog for Athena queries.

Question 15

A financial services company uses Amazon Redshift for analytics. The workload consists of a mix of short-running queries from dashboards and long-running ETL jobs. The company notices that during peak hours, short queries experience high latency due to queueing behind ETL jobs. How can the company reduce the impact of ETL jobs on dashboard queries?

Accepted Answer

Configure workload management (WLM) queues to separate ETL and dashboard queries, and assign different concurrency levels.. Amazon Redshift's Workload Management (WLM) allows you to create separate queues for different query types, such as ETL jobs and dashboard queries. By assigning different concurrency levels to each queue, you prevent long-running ETL jobs from consuming all available slots and blocking short dashboard queries, thereby reducing latency during peak hours.

Answer

Enable concurrency scaling to handle bursts of queries.

Answer

Enable short query acceleration (SQA) to prioritize queries that run under a certain time threshold.

Answer

Increase the number of nodes in the Redshift cluster.