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

Question 1

A company uses Amazon RDS for PostgreSQL for its CRM application. The application experiences intermittent spikes in read traffic. Which TWO actions can the company take to improve read scalability with minimal application changes?

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

Accepted Answer

Create one or more read replicas in the same region.. Creating read replicas in Amazon RDS for PostgreSQL offloads read traffic from the primary DB instance, directly addressing intermittent read spikes with minimal application changes. Read replicas are asynchronous replicas that can serve read queries, and the application only needs to update its connection string to point to the replica endpoint for read operations.

Answer

Enable Multi-AZ deployment for automatic failover.

Answer

Migrate to Amazon Aurora and enable Auto Scaling.

Answer

Upgrade to a larger DB instance class.

Question 2

A database administrator is troubleshooting a slow-performing query on an Amazon RDS for MySQL instance. The slow query log shows the above entry. Based on the exhibit, which index would most improve the query performance?

Accepted Answer

Composite index on (`status`, `created_at`).. The query filters on `status` and then sorts or filters on `created_at`. A composite index on (`status`, `created_at`) allows MySQL to use the index for both the equality condition on `status` and the range or sort on `created_at`, avoiding a filesort and reducing row scans. This is the most efficient index for this query pattern.

Answer

Index on `created_at` only.

Answer

Index on `status` only.

Answer

Full-text index on `status` and `created_at`.

Question 3

A security engineer created the IAM policy above for an application that reads from a DynamoDB table named UserSessions. The application reports that it cannot query the table using a Global Secondary Index (GSI). The table's GSI is named GSI_UserSessions. Why is the application unable to query the index?

Accepted Answer

The Query action is not allowed on the index because the Allow statement only applies to the table, not the index.. The IAM policy's Allow statement grants the Query action only on the table resource (arn:aws:dynamodb:...:table/UserSessions) but not on the index resource (arn:aws:dynamodb:...:table/UserSessions/index/GSI_UserSessions). In DynamoDB, a Global Secondary Index is a separate subresource, and IAM policies must explicitly include the index ARN to allow operations like Query on that index. Without an explicit Allow on the index, the default implicit deny prevents the query. Option B is incorrect because while a Deny statement would override any Allow, the primary reason the application fails is the lack of an Allow on the index; the Deny statement, if present, is an additional but not necessary condition.

Answer

The Deny statement explicitly denies all DynamoDB actions on the index resource, overriding the Allow statement.

Answer

The application is using GetItem instead of Query to access the index.

Answer

The policy allows Query on the table, which automatically includes the index.

Question 4

A company is running an Amazon RDS for MySQL instance as shown in the exhibit. The application is experiencing high write latency. The instance has a high number of write operations and the storage queue depth is consistently above 100. Which change would most effectively reduce write latency?

Accepted Answer

Modify the storage type to Provisioned IOPS (io1) with 3000 IOPS.. The instance is experiencing high write latency with a consistently high storage queue depth (above 100), which indicates that the current storage (likely gp2 or magnetic) cannot keep up with the write IOPS demand. Provisioned IOPS (io1) with 3000 IOPS guarantees a dedicated level of IOPS, reducing queue depth and write latency by ensuring the storage subsystem can handle the write workload without throttling.

Answer

Change the instance class to db.m5.xlarge.

Answer

Enable Multi-AZ to offload writes to a standby.

Answer

Increase the allocated storage to 200 GB.

Question 5

A company runs an e-commerce platform on Amazon RDS for PostgreSQL. During a flash sale, the database experiences high write load and read replicas lag significantly. The application uses read replicas for reporting queries. Which design change would most effectively reduce replica lag without compromising write performance?

Accepted Answer

Migrate to Amazon Aurora with read replicas.. Amazon Aurora's distributed storage architecture decouples compute from storage, allowing replicas to apply redo logs with minimal overhead compared to RDS for PostgreSQL's physical replication. Aurora's replicas share the same underlying storage volume, so replica lag is significantly reduced even under heavy write loads, while write performance on the primary remains unaffected due to the asynchronous, log-based replication mechanism.

Answer

Increase the instance size of the primary database.

Answer

Add more read replicas to distribute the reporting load.

Answer

Convert the RDS instance to a Multi-AZ deployment.

Question 6

A company uses Amazon DynamoDB as the primary database for a global gaming application. The application requires single-digit millisecond latency for user profile lookups by user ID. However, some queries need to retrieve all active users in a region (e.g., 'us-east-1') for administrative dashboards, and these queries currently perform full table scans, causing high costs and throttling. What design approach should be taken to optimize this?

Accepted Answer

Create a global secondary index (GSI) on the region attribute.. Creating a Global Secondary Index (GSI) on the 'region' attribute allows the administrative dashboard queries to retrieve all active users in a specific region using an efficient index scan instead of a full table scan. This reduces read capacity consumption, avoids throttling, and maintains single-digit millisecond latency for the indexed queries, while the base table remains optimized for user ID lookups.

Answer

Implement DynamoDB Accelerator (DAX) to cache the dashboard queries.

Answer

Increase the read capacity units (RCUs) on the base table.

Answer

Create a local secondary index (LSI) on the region attribute.

Question 7

A company is migrating a MySQL database from on-premises to Amazon RDS for MySQL. The current database has several stored procedures and triggers that use user-defined functions (UDFs) compiled as shared libraries. What is the best practice for handling these UDFs in RDS?

Accepted Answer

Refactor the stored procedures to avoid using the custom UDFs.. Amazon RDS for MySQL does not allow access to the underlying file system, so you cannot upload custom UDF shared libraries (.so files). The best practice is to refactor the stored procedures and triggers to remove dependencies on these UDFs, replacing their logic with native MySQL functions or application-level code. This ensures compatibility with the managed RDS environment without requiring custom binaries.

Answer

Use Amazon RDS Custom for MySQL to upload the UDF libraries.

Answer

Use AWS Lambda to replace the UDFs.

Answer

Migrate to Amazon Aurora MySQL, which supports custom UDFs.

Question 8

A company has a document database workload on Amazon DynamoDB that stores user session data. The application frequently updates session attributes (e.g., last activity timestamp). The current design stores the entire session as a single item and updates the entire item on each session activity. This is causing high write costs and throttling. Which design pattern would reduce write costs and improve performance?

Accepted Answer

Use UpdateItem with an update expression to modify only the changed attributes.. Using UpdateItem with an update expression allows you to modify only the specific attributes that changed (e.g., last activity timestamp) instead of rewriting the entire item. This reduces write consumption to a fraction of the original cost, since DynamoDB charges based on the size of the written data, and partial updates write only the changed attribute bytes. This directly addresses the high write costs and throttling caused by full-item overwrites.

Answer

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

Answer

Implement DynamoDB Accelerator (DAX) to cache the session data.

Answer

Split the session item into multiple items, one per attribute.

Question 9

A company is designing a multi-tenant SaaS application on Amazon RDS for PostgreSQL. Tenants have vastly different data sizes and access patterns. The current design uses a separate schema per tenant, but some tenants experience slow queries while others are fine. Which approach would best isolate tenant performance and simplify management?

Accepted Answer

Use a separate database per tenant.. Using a separate database per tenant provides the strongest resource isolation at the database instance level. This design prevents noisy neighbors—tenants with large data volumes or heavy access patterns from degrading the performance of other tenants—and simplifies management tasks such as backup, restore, and point-in-time recovery on a per-tenant basis. Amazon RDS for PostgreSQL supports multiple databases within a single DB instance, and each database operates with its own catalog, tables, and connection pool, ensuring that query execution and memory allocation are not shared across tenants.

Answer

Use row-level security (RLS) policies within a single schema to restrict tenant data access.

Answer

Keep the current schema design and add a tenant_id index to all tables.

Answer

Use Amazon RDS Proxy to manage connections and improve performance.

Question 10

A company is using Amazon ElastiCache for Redis as a caching layer for frequently accessed data. The application needs to support caching of session data that must be highly available across multiple Availability Zones. Which ElastiCache configuration should be used?

Accepted Answer

Deploy a Redis cluster with cluster mode enabled and replica nodes in a different Availability Zone.. Deploying a Redis cluster with cluster mode enabled and replica nodes in a different Availability Zone provides both high availability and automatic failover for session data. ElastiCache for Redis with cluster mode enabled supports sharding and replication, allowing replica nodes to be placed in a separate AZ to survive an AZ failure. This configuration ensures session data remains accessible even if the primary node or an entire AZ becomes unavailable, meeting the requirement for multi-AZ high availability.

Answer

Deploy a single Redis node in one Availability Zone.

Answer

Deploy a Redis cluster with cluster mode disabled.

Answer

Deploy a Memcached cluster with multiple nodes.

Question 11

A company uses Amazon DynamoDB for a time-series IoT workload. Each device sends a data point every minute. The primary key consists of device_id (partition key) and timestamp (sort key). The company wants to efficiently retrieve the latest 10 data points for a specific device. Which query design is most efficient?

Accepted Answer

Query the table with ScanIndexForward=false and Limit=10.. Query with ScanIndexForward=false retrieves items in descending order by the sort key (timestamp), and Limit=10 stops after the first 10 items, which are the most recent 10 data points for the given device_id. This is the most efficient design as it reads only the 10 items needed, leveraging the DynamoDB local secondary index or table's sort key order without any post-processing.

Answer

Use GetItem on the device_id partition key with the maximum timestamp.

Answer

Query the table with ScanIndexForward=true and Limit=10, then reverse the result set.

Answer

Scan the entire table and filter by device_id, then sort by timestamp.

Question 12

A company is using Amazon RDS for Oracle with a very large database (10 TB). They need to migrate to Amazon Aurora PostgreSQL with minimal downtime. The source database is heavily used with constant writes. Which migration strategy is most appropriate?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication to migrate from Oracle to Aurora PostgreSQL.. AWS DMS with ongoing replication (change data capture) is the most appropriate strategy for migrating a heavily written 10 TB Oracle database to Aurora PostgreSQL with minimal downtime. DMS can perform a full load of the existing data and then continuously replicate changes from Oracle's redo logs to Aurora PostgreSQL, allowing the source to remain fully operational until a brief cutover window. This approach minimizes downtime compared to offline export/import methods and is natively supported by AWS.

Answer

Export the Oracle database using expdp and import into Aurora PostgreSQL using pg_restore.

Answer

Create a read replica of the RDS Oracle instance and promote it to an Aurora PostgreSQL instance.

Answer

Use Oracle GoldenGate to replicate data to an Aurora PostgreSQL instance.

Question 13

A company is migrating an on-premises MongoDB database to Amazon DocumentDB (with MongoDB compatibility). The application uses MongoDB's aggregation pipeline with $lookup and $unwind stages. What should the company verify before migration?

Accepted Answer

Check if all aggregation pipeline stages used by the application are supported in DocumentDB.. Amazon DocumentDB (with MongoDB compatibility) does not support all MongoDB aggregation pipeline stages. Specifically, the `$lookup` and `$unwind` stages are supported, but other stages like `$merge`, `$facet`, `$bucket`, `$bucketAuto`, `$graphLookup`, and `$search` are not. Before migration, the company must verify that every stage used in their application's aggregation pipelines is fully supported in DocumentDB to avoid runtime failures.

Answer

Verify that DocumentDB supports sharding for the collection.

Answer

Ensure the application uses the latest MongoDB driver.

Answer

Confirm that the application's secondary indexes can be migrated.

Question 14

A company is designing a database solution for a global user base that requires single-digit millisecond read latency for user profile data. The data is eventually consistent and can tolerate a few seconds of staleness. Which TWO AWS services or features should be combined to achieve this latency?

Accepted Answer

Amazon DynamoDB Global Tables.. Amazon DynamoDB Global Tables provides a fully managed, multi-Region, multi-active database that replicates data across AWS Regions with sub-second latency, enabling single-digit millisecond reads for user profile data. Combined with Amazon CloudFront as a CDN, you can cache DynamoDB responses at edge locations, further reducing read latency for a global user base while tolerating eventual consistency and a few seconds of staleness.

Answer

Amazon ElastiCache for Redis with global datastore.

Answer

Amazon RDS for MySQL with cross-Region read replicas.

Answer

Amazon Aurora Global Database.

Question 15

A company is migrating a legacy Oracle data warehouse to Amazon Redshift. The source uses complex stored procedures with cursors, temporary tables, and PL/SQL. Which THREE design considerations should the company evaluate?

Accepted Answer

Redshift does not support procedural languages like PL/SQL; stored procedures must be rewritten in SQL or Python.. Amazon Redshift does not support Oracle's PL/SQL procedural language; stored procedures must be rewritten using Redshift's PL/pgSQL or Python UDFs (option A). Redshift supports temporary tables, but they are session-scoped and must be explicitly dropped or they persist until session ends (option B). Redshift does not support cursors as in Oracle; result sets must be handled using alternative methods such as fetching into arrays or using LIMIT/OFFSET (option D). These are key migration considerations.

Answer

Redshift Concurrency Scaling can handle thousands of concurrent queries.

Answer

Redshift automatically scales compute capacity based on workload.