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

Question 1

A company runs a data warehouse on Amazon Redshift. The workload has frequent DELETE and UPDATE operations on a large fact table. Over time, query performance degrades. Which maintenance operation should be scheduled regularly to optimize performance?

Accepted Answer

Run VACUUM and ANALYZE commands regularly. B is correct because frequent DELETE and UPDATE operations in Redshift create ghost rows and cause table bloat, degrading query performance. Running VACUUM reclaims space and re-sorts rows, while ANALYZE updates table statistics for the query optimizer; together they restore performance without requiring a full table rebuild.

Answer

Run VACUUM FULL during maintenance windows

Answer

Alter the table to use a different DISTKEY

Answer

Drop and recreate the table periodically

Question 2

Which TWO of the following are valid design considerations when using Amazon DynamoDB for a high-traffic e-commerce application? (Select TWO.)

Accepted Answer

Use atomic counters to maintain inventory counts. Option C is correct because atomic counters in DynamoDB allow you to increment or decrement a numeric attribute atomically using the UpdateItem operation with an UpdateExpression like 'SET inventory = inventory - :qty'. This is ideal for maintaining inventory counts in a high-traffic e-commerce application, as it avoids the need for read-modify-write cycles and reduces the risk of race conditions without consuming additional read capacity.

Answer

Use scan operations for frequent queries to reduce provisioned capacity

Answer

Use DynamoDB transactions to reduce read capacity consumption

Answer

Always use strongly consistent reads for all queries

Question 3

Which TWO of the following are recommended best practices for designing a relational database on Amazon RDS? (Select TWO.)

Accepted Answer

Use read replicas to scale read-heavy workloads. Amazon RDS read replicas offload read traffic from the primary DB instance, improving performance for read-heavy workloads by providing additional read-only endpoints. This is a core best practice for scaling read capacity without increasing load on the source instance.

Answer

Use LOB data types to store large text fields

Answer

Use non-EBS-optimized instances to reduce costs

Answer

Store large binary objects (BLOBs) in the database

Question 4

Which THREE of the following are key considerations when designing a time-series database using Amazon DynamoDB? (Select THREE.)

Accepted Answer

Enable Time to Live (TTL) to automatically expire old data. Option B is correct because Amazon DynamoDB's Time to Live (TTL) feature automatically deletes expired items without consuming write throughput, making it ideal for managing data retention in time-series workloads. This eliminates the need for custom cleanup scripts and reduces storage costs over time.

Answer

Always use strongly consistent reads for accurate time-series data

Answer

Use local secondary indexes for aggregating data across partitions

Question 5

A company is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database currently uses a custom extension that is not supported by RDS. The application relies heavily on this extension for advanced statistical analysis. Which design approach should the company take to minimize application changes?

Accepted Answer

Migrate to Amazon RDS for PostgreSQL and implement the extension's functionality using AWS Lambda functions called via triggers.. Option D is correct because it allows the company to offload the unsupported custom extension's statistical analysis logic to AWS Lambda functions, which can be invoked via RDS PostgreSQL triggers. This approach minimizes application changes by keeping the database schema and query patterns largely intact, while the Lambda functions handle the advanced computations externally. RDS does not allow custom extensions, so this pattern leverages RDS for PostgreSQL's native trigger support to integrate with Lambda without modifying the application's core database interactions.

Answer

Migrate to PostgreSQL on Amazon EC2 and install the custom extension.

Answer

Migrate to Amazon DynamoDB and implement statistical analysis using DynamoDB streams and Lambda.

Answer

Migrate to Amazon RDS for PostgreSQL and install the custom extension on the RDS instance.

Question 6

A startup is building a social media analytics application that ingests high-velocity streaming data from multiple sources. The data consists of JSON objects with varying schemas. The application needs to store this data for real-time querying and later batch processing. Which AWS database solution is most cost-effective and scalable for this workload?

Accepted Answer

Amazon DynamoDB with on-demand capacity.. Amazon DynamoDB with on-demand capacity is the most cost-effective and scalable solution for this workload because it is a fully managed NoSQL database that can handle high-velocity streaming data with varying JSON schemas without requiring schema definition or provisioning. Its on-demand capacity mode automatically scales to accommodate unpredictable traffic spikes, making it ideal for real-time querying and batch processing via features like DynamoDB Streams and integration with AWS Glue or EMR.

Answer

Amazon ElastiCache for Redis with persistence enabled.

Answer

Amazon RDS for MySQL with multiple read replicas.

Answer

Amazon Redshift with auto-ingest from Kinesis.

Question 7

A company is designing a multi-tenant SaaS application using Amazon Aurora PostgreSQL. Each tenant's data must be isolated for security and compliance. The application has a few large tenants and many small tenants. Queries must be able to access data across tenants for reporting, but with strict access controls. Which design best meets these requirements?

Accepted Answer

Use a single Aurora cluster with a separate schema per tenant and implement row-level security policies.. Option A is correct because it uses a single Aurora cluster with a separate schema per tenant and row-level security (RLS) policies. This design provides strong tenant isolation at the schema level while allowing cross-tenant reporting queries with strict access controls enforced by RLS policies, which automatically filter rows based on the current session's tenant context. It balances security, compliance, and operational efficiency for a mix of large and small tenants.

Answer

Create a separate database per tenant in the same Aurora cluster.

Answer

Create a separate Aurora cluster per tenant.

Answer

Use a single Aurora cluster with a single schema but add a tenant_id column to every table.

Question 8

A gaming company uses Amazon DynamoDB to store player profiles and game state. The access patterns include: (1) lookup by player ID, (2) query by game ID for recent games, and (3) leaderboard queries sorted by score. The current single-table design is causing hot partitions on the leaderboard queries. What design change should the company implement to resolve hot partitions?

Accepted Answer

Create a GSI with the game ID as the partition key and a composite sort key of score and timestamp.. Option C is correct because creating a Global Secondary Index (GSI) with game ID as the partition key and a composite sort key of score and timestamp allows efficient leaderboard queries without hot partitions. This design distributes write activity across multiple partitions by game ID, while the composite sort key enables sorted queries by score and timestamp within each game, avoiding the hot partition issue caused by the original single-table design.

Answer

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

Answer

Enable DynamoDB Accelerator (DAX) to cache frequent leaderboard queries.

Answer

Shard the table by player ID and use application-level aggregation for leaderboards.

Question 9

A company is designing a document management system using Amazon S3 and needs to store metadata such as document ID, owner, creation date, and tags. The metadata must be searchable with low latency, supporting queries like 'Find all documents owned by user X with tag Y created after date Z'. Which AWS database service is most suitable for storing and querying this metadata?

Accepted Answer

Amazon DynamoDB with a GSI on (owner, creation_date) and a filter on tags.. Amazon DynamoDB is the most suitable choice because it provides single-digit millisecond latency for queries at any scale, which meets the low-latency search requirement. By creating a Global Secondary Index (GSI) on (owner, creation_date), you can efficiently query documents by owner and date range, and then apply a filter expression on tags to narrow results. This schema avoids the overhead of joins and normalization, making it ideal for high-throughput metadata lookups.

Answer

Amazon Redshift Spectrum querying metadata stored in S3 as CSV.

Answer

Amazon RDS for PostgreSQL with a normalized schema.

Answer

Amazon ElastiCache for Redis with sorted sets for tags.

Question 10

A financial services company runs a critical application on Amazon RDS for MySQL. The database stores transaction data that must be retained for 7 years for regulatory compliance. The current retention policy stores all data in the same table, causing performance degradation on the main transactional table. The company needs to archive data older than 1 year while keeping it queryable. Which design should they implement?

Accepted Answer

Use Amazon RDS for MySQL with a read replica for reporting, and set up a Lambda function to export partitions older than 1 year to Amazon S3 in Parquet format, queryable via Amazon Athena.. Option C is correct because it uses RDS for MySQL read replicas to offload reporting traffic, while a Lambda function archives partitions older than 1 year to Amazon S3 in Parquet format. This keeps the main transactional table lean, improves performance, and retains data for 7 years in a cost-effective, queryable format via Amazon Athena, meeting both compliance and queryability requirements.

Answer

Migrate to Amazon Redshift and use workload management to prioritize transactions.

Answer

Use Amazon ElastiCache for Redis to cache recent data and move old data to S3.

Answer

Migrate to Amazon DynamoDB with TTL to automatically expire old data.

Question 11

A company is building a real-time chat application using Amazon DynamoDB. Each message has a conversation ID, timestamp, sender, and content. The primary access pattern is to retrieve the most recent 50 messages for a given conversation, ordered by timestamp. Which table design minimizes cost and latency?

Accepted Answer

Use conversation ID as partition key and timestamp as sort key; query with ScanIndexForward=false and Limit=50.. Option C is correct because using conversation ID as the partition key and timestamp as the sort key allows a single Query operation with ScanIndexForward=false to retrieve items in reverse chronological order, and Limit=50 ensures only the most recent 50 messages are returned. This design minimizes cost by avoiding full table scans or additional indexes, and minimizes latency by leveraging DynamoDB's native sort key ordering without needing external caching or streams.

Answer

Use a composite primary key of conversation ID and message ID, and enable DynamoDB Streams to process messages.

Answer

Use DynamoDB Accelerator (DAX) to cache the most recent messages.

Answer

Use conversation ID as partition key and a GSI on timestamp.

Question 12

A company is migrating an on-premises MongoDB database to AWS. The application uses MongoDB's aggregation pipeline for real-time analytics. Which AWS database service is most compatible and provides the least application changes?

Accepted Answer

Amazon DocumentDB (with MongoDB compatibility).. Amazon DocumentDB is designed to be MongoDB-compatible, supporting the MongoDB aggregation pipeline with minimal changes. This allows the company to migrate the existing MongoDB database and continue using the same aggregation pipeline for real-time analytics without rewriting application code, making it the most compatible option.

Answer

Amazon ElastiCache for Redis with RedisJSON module.

Answer

Amazon DynamoDB with DynamoDB Streams and Lambda for aggregation.

Answer

Amazon Aurora with JSON data type.

Question 13

A company uses Amazon RDS for Oracle with a custom application that generates complex hierarchical queries using CONNECT BY. They want to migrate to Amazon Aurora to reduce licensing costs. Which migration strategy requires the fewest application changes?

Accepted Answer

Migrate to Amazon Aurora PostgreSQL and rewrite hierarchical queries to use recursive CTEs.. Option C is correct because Aurora PostgreSQL supports recursive Common Table Expressions (CTEs) via the WITH RECURSIVE clause, which can be used to rewrite Oracle's CONNECT BY hierarchical queries with minimal application changes. This approach avoids the licensing costs of Oracle while preserving the hierarchical query logic, requiring only a syntax rewrite rather than a complete redesign. Aurora MySQL does not support CONNECT BY or recursive CTEs, making PostgreSQL the only Aurora engine that can handle this workload without significant application restructuring.

Answer

Use AWS Database Migration Service (DMS) to migrate directly to Aurora with no application changes.

Answer

Migrate to Amazon Aurora MySQL and use its hierarchical query features.

Answer

Migrate to Amazon RDS for SQL Server and use its recursive CTEs.

Question 14

A company is designing a global e-commerce platform using Amazon DynamoDB. The platform must support strong consistency for inventory updates and eventual consistency for product catalog reads. Which TWO design patterns should the company implement to meet these consistency requirements?

Accepted Answer

Configure DynamoDB Accelerator (DAX) for product catalog queries with eventual consistency.. Option A is correct because DynamoDB Accelerator (DAX) is an in-memory cache that can be configured to return eventually consistent results for read-heavy workloads like product catalog queries, reducing read latency and cost while meeting the eventual consistency requirement. Option E is correct because DynamoDB global tables replicate data across regions, and using strongly consistent reads for inventory queries ensures that the most recent write is returned, which is critical for inventory accuracy.

Answer

Use Amazon ElastiCache for Redis to cache inventory data with strong consistency.

Answer

Use DynamoDB transactions for all inventory operations.

Answer

Use DynamoDB Streams to replicate inventory changes to a separate table for reads.

Question 15

A company is designing a data lake on Amazon S3 with Amazon Redshift Spectrum for analytics. The data includes JSON logs from web servers. Which THREE design practices should the company follow to optimize query performance and cost?

Accepted Answer

Compress files using gzip or snappy.. Option A is correct because compressing JSON files with gzip or Snappy reduces the data size stored in S3, lowering storage costs and minimizing the amount of data that Redshift Spectrum must scan over the network. Redshift Spectrum can read compressed files directly, and compression often improves query performance by reducing I/O, even though it adds a small CPU overhead for decompression.

Answer

Use many small files to maximize parallelism.

Answer

Create indexes on the S3 data using AWS Glue.