I85PREP
⌘ K
AWS Database study materials
AWS Database study resources

AWS Database practice questions

One question at a time. Build your AWS Database understanding.

First 50 of 199 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Workload-Specific Database Design

A team is building an OLTP application that requires complex multi-table joins, foreign key constraints, and ACID transactions. Which AWS database service is the most appropriate primary store?

  1. Amazon RDS Correct answer.
  2. Amazon DynamoDB
  3. Amazon Neptune
  4. Amazon Redshift

Explanation

Amazon RDS provides managed relational engines that support joins, foreign keys, and ACID transactions, which are core OLTP requirements. DynamoDB is a key-value/document store without joins, Redshift is an analytics warehouse, and Neptune is a graph database.

Question 2Workload-Specific Database Design

Which AWS service is designed for storing and querying highly connected data with traversals across relationships, such as social graphs and fraud detection rings?

  1. Amazon Neptune Correct answer.
  2. Amazon DocumentDB
  3. Amazon Timestream
  4. Amazon Redshift

Explanation

Amazon Neptune is a purpose-built graph database that supports property-graph and RDF models with efficient multi-hop traversal queries. Relational, time-series, and document databases are not optimized for deep relationship traversal.

Question 3Workload-Specific Database Design

A web application needs a sub-millisecond, in-memory session store shared across many stateless application servers. Which AWS service fits this requirement best?

  1. Amazon ElastiCache for Redis Correct answer.
  2. Amazon RDS
  3. Amazon S3
  4. Amazon Redshift

Explanation

ElastiCache for Redis is an in-memory data store that delivers sub-millisecond latency and supports shared session state across application servers. Disk-based relational databases, analytical warehouses, and object storage cannot meet that latency profile.

Question 4Workload-Specific Database Design

A company wants a petabyte-scale data warehouse for complex analytical queries over structured historical data. Which AWS service is purpose-built for this workload?

  1. Amazon Aurora
  2. Amazon Redshift Correct answer.
  3. Amazon DynamoDB
  4. Amazon Neptune

Explanation

Amazon Redshift is a columnar, massively parallel data warehouse designed for large-scale analytical SQL workloads. DynamoDB and Aurora are transactional stores, and Neptune is a graph database.

Question 5Workload-Specific Database Design

Which AWS service provides a transparent, immutable, and cryptographically verifiable transaction log for a ledger application?

  1. Amazon Keyspaces
  2. Amazon QLDB Correct answer.
  3. Amazon DynamoDB
  4. Amazon RDS

Explanation

Amazon QLDB maintains an append-only journal with cryptographic verification, making it suitable for ledger-style applications that require tamper-evident history. DynamoDB, RDS, and Keyspaces do not provide a native verifiable ledger journal.

Question 6Workload-Specific Database Design

An IoT platform ingests millions of timestamped sensor readings per second and runs time-windowed aggregations. Which database is purpose-built for this workload?

  1. Amazon DocumentDB
  2. Amazon Neptune
  3. Amazon Timestream Correct answer.
  4. Amazon Redshift

Explanation

Amazon Timestream is a purpose-built time-series database that handles high-volume timestamped ingestion and provides built-in time-window aggregation functions. Graph, document, and warehouse databases are not optimized for this ingestion and query pattern.

Question 7Workload-Specific Database Design

Which DynamoDB feature automatically deletes expired items based on a timestamp attribute without consuming write capacity?

  1. DynamoDB Accelerator
  2. Time to Live (TTL) Correct answer.
  3. Global Secondary Index
  4. DynamoDB Streams

Explanation

DynamoDB Time to Live deletes items after a specified timestamp attribute expires, and the deletion does not consume write capacity. Streams, DAX, and GSIs serve different purposes such as change capture, caching, and alternate query patterns.

Question 8Workload-Specific Database Design

An application uses MongoDB drivers and a document data model, and it needs a managed AWS service with MongoDB compatibility. Which service should be used?

  1. Amazon Keyspaces
  2. Amazon DynamoDB
  3. Amazon DocumentDB (with MongoDB compatibility) Correct answer.
  4. Amazon Neptune

Explanation

Amazon DocumentDB is a managed document database that is compatible with MongoDB drivers and tooling. DynamoDB uses proprietary APIs, Neptune is a graph database, and Keyspaces is Cassandra-compatible.

Question 9Workload-Specific Database Design

A DynamoDB table uses a partition key with only a few distinct values, causing throttling on a small number of partitions. What is the most effective design change?

  1. Use a high-cardinality partition key that spreads items evenly Correct answer.
  2. Add a Global Secondary Index on the same attribute
  3. Enable DynamoDB Streams
  4. Increase the table's provisioned read capacity only

Explanation

DynamoDB distributes items by partition key hash, so a low-cardinality key concentrates traffic on a few partitions and creates hot partitions. A high-cardinality key distributes items and requests more evenly across partitions.

Question 10Workload-Specific Database Design

A DynamoDB table is keyed by CustomerId. The application must query by OrderDate, which is not part of the base table key. What should be created?

  1. A read replica
  2. A global secondary index with OrderDate as the partition key Correct answer.
  3. A local secondary index with OrderDate as the sort key
  4. A DynamoDB Stream

Explanation

A global secondary index can use a partition key and sort key that differ from the base table, allowing queries by OrderDate. A local secondary index must share the base table's partition key, which would not support querying by OrderDate alone.

Question 11Workload-Specific Database Design

An application requires microsecond read latency for repeated item reads from a DynamoDB table and is heavily read-oriented. Which solution should be added?

  1. DynamoDB Streams
  2. DynamoDB Accelerator (DAX) Correct answer.
  3. Amazon RDS read replica
  4. Amazon ElastiCache for Memcached

Explanation

DAX is an in-memory write-through cache for DynamoDB that reduces read latency to microseconds for repeated item reads. Memcached, Streams, and RDS replicas do not provide a DynamoDB-native caching layer.

Question 12Workload-Specific Database Design

A relational database must survive an Availability Zone failure with automatic failover and no data loss, without changing the application connection string. Which RDS feature should be used?

  1. Multi-AZ deployment Correct answer.
  2. Read replica
  3. Performance Insights
  4. Cross-region snapshot copy

Explanation

An RDS Multi-AZ deployment maintains a synchronous standby in another Availability Zone and automatically fails over using the same endpoint. Read replicas are asynchronous and do not provide automatic failover, snapshot copies are manual, and Performance Insights is monitoring.

Question 13Workload-Specific Database Design

An application needs a relational database that can accept writes in more than one Availability Zone simultaneously, avoiding a single writer bottleneck. Which Aurora feature should be used?

  1. Aurora Global Database
  2. Aurora Backtrack
  3. Aurora Multi-Master Correct answer.
  4. Aurora Serverless v2

Explanation

Aurora Multi-Master allows multiple writer instances across Availability Zones to accept writes concurrently. Global Database maintains a single writer region, Backtrack rewinds data, and Serverless v2 adjusts capacity.

Question 14Workload-Specific Database Design

In Amazon Redshift, a small dimension table is frequently joined to a very large fact table. Which distribution style generally minimizes data movement for those joins?

  1. EVEN
  2. ALL Correct answer.
  3. AUTO with no sort key
  4. KEY on the fact table's join column

Explanation

The ALL distribution style broadcasts the entire small table to every compute node, so joins with a large fact table do not require redistributing the fact table. EVEN and KEY can require data movement for these joins.

Question 15Workload-Specific Database Design

Many Lambda functions open short-lived database connections to an RDS instance, exhausting the database connection limit. Which service addresses this problem?

  1. RDS Multi-AZ
  2. Aurora Global Database
  3. Amazon RDS Proxy Correct answer.
  4. Read replicas

Explanation

Amazon RDS Proxy pools and shares database connections, reducing the number of direct connections from Lambda functions. Multi-AZ, Global Database, and read replicas address availability and read scaling rather than connection pooling.

Question 16Workload-Specific Database Design

An application must react to item-level changes in a DynamoDB table in near real time and trigger a Lambda function. Which feature should be enabled?

  1. DynamoDB Streams Correct answer.
  2. DynamoDB Accelerator
  3. DynamoDB global tables
  4. DynamoDB TTL

Explanation

DynamoDB Streams records item-level changes in a table and can invoke Lambda for near real-time processing. TTL expires items, DAX caches reads, and global tables replicate data across regions.

Question 17Workload-Specific Database Design

A workload has unpredictable, spiky relational demand and needs automatic capacity scaling in fine-grained increments without managing database instances. Which Aurora option fits best?

  1. Aurora Backtrack
  2. Aurora Provisioned with read replicas
  3. Aurora Serverless v2 Correct answer.
  4. Aurora Global Database

Explanation

Aurora Serverless v2 scales capacity automatically in fine-grained increments based on demand and removes instance-sizing responsibilities. Provisioned Aurora requires choosing instance sizes, Global Database targets cross-region replication, and Backtrack rewinds data.

Question 18Workload-Specific Database Design

A team wants a managed, Cassandra-compatible database that uses CQL and supports tunable consistency. Which AWS service should be used?

  1. Amazon Keyspaces (for Apache Cassandra) Correct answer.
  2. Amazon QLDB
  3. Amazon DocumentDB
  4. Amazon DynamoDB

Explanation

Amazon Keyspaces is a managed, serverless database that is compatible with Apache Cassandra and CQL. DynamoDB uses its own API, DocumentDB is MongoDB-compatible, and QLDB is a ledger database.

Question 19Workload-Specific Database Design

A company keeps years of data in Amazon S3 and wants to query it from Redshift without loading it into Redshift tables. Which feature should be used?

  1. Redshift sort keys
  2. Amazon Redshift Spectrum Correct answer.
  3. Redshift distribution styles
  4. Redshift concurrency scaling

Explanation

Redshift Spectrum lets Redshift queries read data directly from external tables in Amazon S3. Distribution styles and sort keys organize local tables, and concurrency scaling adds query compute capacity.

Question 20Workload-Specific Database Design

Analysts need to run ad hoc SQL over Parquet files in Amazon S3 with no cluster provisioning and pay-per-query pricing. Which service fits best?

  1. Amazon RDS
  2. Amazon EMR
  3. Amazon Athena Correct answer.
  4. Amazon Redshift

Explanation

Amazon Athena is a serverless query service that runs SQL directly against data in Amazon S3 and charges per query scanned. Redshift and EMR require provisioned clusters, and RDS is a transactional database.

Question 21Workload-Specific Database Design

An Aurora MySQL cluster needs to recover from an accidental logical data error by rewinding the existing cluster to a point in time within the retention window, without restoring a new cluster. Which feature should be used?

  1. Aurora Global Database
  2. Aurora Backtrack Correct answer.
  3. Aurora Serverless v2
  4. Aurora Multi-Master

Explanation

Aurora Backtrack rewinds an existing Aurora MySQL cluster to a specified point in time without creating a new cluster. Global Database replicates across regions, Serverless v2 scales capacity, and Multi-Master adds concurrent writers.

Question 22Workload-Specific Database Design

A DynamoDB table is write-heavy and has a global secondary index whose partition key is a low-cardinality status attribute. Writes to the base table are being throttled even though the base table has sufficient capacity. What is the most likely cause?

  1. TTL is deleting items faster than they are written
  2. The GSI develops hot partitions and its insufficient write capacity throttles base table writes Correct answer.
  3. DAX is caching stale items and blocking writes
  4. The base table partition key has too many distinct values

Explanation

GSI updates occur as part of the base table write path, so if the index cannot absorb writes, the base table write can be throttled. A low-cardinality GSI partition key concentrates writes into few index partitions, worsening the problem.

Question 23Workload-Specific Database Design

A large Redshift table is almost always queried with a range predicate on an event_time column. Which table design best allows the query to skip blocks using zone maps?

  1. A compound sort key with event_time as the leading column Correct answer.
  2. Distribution style EVEN with no sort key
  3. A sort key on an unrelated column
  4. Distribution style ALL with no sort key

Explanation

Sorting rows by event_time causes related values to be stored together, so zone maps can eliminate blocks outside the predicate range. A sort key on an unrelated column or no sort key does not provide this pruning benefit.

Question 24Workload-Specific Database Design

Aurora Global Database replicates from a primary region to secondary regions. What recovery point objective does it typically provide for cross-region replication?

  1. About 1 second Correct answer.
  2. Zero, because replication is synchronous
  3. About 1 hour
  4. About 15 minutes

Explanation

Aurora Global Database uses storage-level replication and typically provides a recovery point objective of about one second across regions. Replication is asynchronous, so a zero RPO is not provided.

Question 25Workload-Specific Database Design

An application queries a DynamoDB global secondary index immediately after writing an item to the base table and sometimes does not see the new item. What explains this behavior?

  1. GSIs are always strongly consistent
  2. DAX is disabled
  3. The item was deleted by TTL
  4. GSI updates are asynchronous, so reads are eventually consistent Correct answer.

Explanation

DynamoDB propagates base table writes to global secondary indexes asynchronously, so GSI reads are eventually consistent and may briefly miss a just-written item. Strong consistency is available only on the base table, not on GSIs.

Question 26Workload-Specific Database Design

A social networking workload stores users and their friendships as highly connected data and must run traversal queries that follow relationships several hops deep. Which AWS database service is purpose-built for this access pattern?

  1. Amazon Redshift with a denormalized friend table
  2. Amazon Neptune with nodes and edges stored as a graph Correct answer.
  3. Amazon DynamoDB with a global secondary index per hop
  4. Amazon RDS for PostgreSQL with a normalized friendship table

Explanation

Amazon Neptune is a purpose-built graph database that stores data as nodes and edges and traverses relationships without repeated index lookups or joins. Multi-hop traversal is a native operation, so latency stays low as depth increases.

Question 27Workload-Specific Database Design

A product catalog must support free-text search with relevance ranking, fuzzy matching, and faceted filtering across millions of documents. Which AWS service best fits this requirement?

  1. Amazon ElastiCache for Redis with a hash per product
  2. Amazon OpenSearch Service Correct answer.
  3. Amazon Redshift with a LIKE predicate
  4. Amazon DynamoDB with a scan on a keyword attribute

Explanation

OpenSearch Service is a search engine built on an inverted index that provides relevance scoring, fuzzy matching, and aggregations for facets. Key-value and relational engines cannot rank or tokenize text this way.

Question 28Workload-Specific Database Design

A read against a DynamoDB table must always return the most recently committed value, regardless of which storage replica serves the request. Which read option provides this?

  1. A read from a global secondary index
  2. A read with ReturnConsumedCapacity set to TOTAL
  3. An eventually consistent read
  4. A strongly consistent read Correct answer.

Explanation

DynamoDB replicates items across multiple Availability Zones, and eventually consistent reads may hit a replica that has not yet applied the latest write. A strongly consistent read is served from the leader node and therefore reflects all prior successful writes.

Question 29Workload-Specific Database Design

In an Amazon Redshift cluster, two multi-gigabyte tables are frequently joined on a shared customer_id column. Which design colocates the matching rows on the same slice?

  1. Assign DISTKEY customer_id to both tables Correct answer.
  2. Assign DISTSTYLE ALL to both tables
  3. Assign SORTKEY customer_id to both tables
  4. Assign DISTSTYLE EVEN to both tables

Explanation

When both tables use the same DISTKEY, rows with equal join-key values land on the same slice, so the join executes locally without network redistribution. Sort keys affect block pruning on range predicates, not row placement.

Question 30Workload-Specific Database Design

A production Amazon RDS for MySQL instance is saturated by a nightly read-only reporting job, while the transactional workload must stay responsive. Which change solves this with the least disruption?

  1. Create a read replica and point the reporting job at it Correct answer.
  2. Enable Multi-AZ and point the reporting job at the standby
  3. Increase the backup retention period on the instance
  4. Convert the instance to a Single-AZ deployment

Explanation

A read replica serves read-only traffic from a separate database instance, moving reporting load off the primary. Multi-AZ standbys exist only for failover and cannot be read from.

Question 31Workload-Specific Database Design

An application must serve low-latency reads and accept writes in three AWS Regions at the same time against the same key-value data set. Which DynamoDB capability delivers this?

  1. DynamoDB Accelerator (DAX) with a cluster per Region
  2. DynamoDB global tables with multi-Region replication Correct answer.
  3. On-demand backup and restore into each Region
  4. Read replicas built from DynamoDB Streams consumers

Explanation

Global tables create a multi-Region, multi-active replica set where each Region accepts writes and replicates them to the others. Reads and writes are served locally in every participating Region.

Question 32Workload-Specific Database Design

A real-time gaming leaderboard must return the top 100 players and an individual player's rank in milliseconds. Which store and data structure best fits?

  1. Amazon ElastiCache for Redis using a sorted set Correct answer.
  2. Amazon S3 with a precomputed leaderboard object
  3. Amazon DynamoDB with a scan and client-side sorting
  4. Amazon RDS with an ORDER BY query on a players table

Explanation

A Redis sorted set keeps members ordered by score and returns rank and range queries in logarithmic time from memory. This is the canonical structure for live leaderboards.

Question 33Workload-Specific Database Design

A geospatial application needs spatial indexes along with geometry functions such as ST_Distance and ST_Contains inside a managed relational database. Which choice provides this?

  1. Amazon DynamoDB with a geohash partition key
  2. Amazon Aurora PostgreSQL with the PostGIS extension Correct answer.
  3. Amazon ElastiCache for Memcached with binary blobs
  4. Amazon Redshift with a sort key on latitude

Explanation

Aurora PostgreSQL supports PostgreSQL extensions, and PostGIS adds spatial data types, GiST spatial indexes, and geometry functions executed inside the database. The other engines either lack spatial types or cannot execute these functions at all.

Question 34Workload-Specific Database Design

A DynamoDB table must store an organization hierarchy so that one request returns a manager record and all of that manager's direct reports. Which key design supports this?

  1. Use the manager id as the partition key and keep reports in a separate table per manager
  2. Store the manager and report items under the same partition key per organization Correct answer.
  3. Use a random partition key for every item and scan the table for reports
  4. Use the employee id as the partition key and the manager id as the sort key

Explanation

The adjacency-list pattern places the parent item and its child items in the same item collection by sharing a partition key, so a single Query returns the whole related set. Item collections are the DynamoDB mechanism for one-to-many relationships.

Question 35Workload-Specific Database Design

A DynamoDB table uses partition key deviceId and sort key reading#<ISO-8601 timestamp>. An application needs all readings for one device on one calendar day. Which request retrieves them efficiently?

  1. A Scan with a FilterExpression matching the timestamp prefix
  2. A Query on the device key with begins_with on the sort key Correct answer.
  3. A Query on a global secondary index partitioned by timestamp
  4. A GetItem using the device id and the day's first timestamp

Explanation

A Query targets one partition key and can restrict the sort key with a condition such as begins_with, so only the matching prefix range is read. This keeps the read efficient and proportional to the day's data.

Question 36Workload-Specific Database Design

Some items in a DynamoDB table carry a 900 KB JSON document, which exceeds the maximum item size. Which design keeps the record retrievable?

  1. Increase the table's provisioned write capacity to allow larger items
  2. Compress the document so that it fits under the item size limit
  3. Keep the document in Amazon S3 and store the key in the item Correct answer.
  4. Convert the attribute to a binary type, which allows larger items

Explanation

DynamoDB items are capped at 400 KB regardless of attribute type, so large payloads are conventionally stored in Amazon S3 with the object key held as an item attribute. This keeps items small while preserving the full document.

Question 37Workload-Specific Database Design

Two application instances may update the same DynamoDB item concurrently, and lost updates must be prevented without locking the table. Which approach achieves this?

  1. Use a Scan followed by an unconditional PutItem
  2. Use a conditional UpdateItem that checks a version attribute Correct answer.
  3. Enable DynamoDB Streams and reconcile conflicting writes later
  4. Use a strongly consistent read before each write

Explanation

Optimistic locking stores a version attribute and issues a conditional update that succeeds only if the stored version still matches the one read. A failed condition signals a concurrent change, and the caller retries with fresh data.

Question 38Workload-Specific Database Design

An order write must decrement an inventory item and create the order item, and both changes must succeed or neither. Which DynamoDB capability is required?

  1. BatchWriteItem with both items in the same request
  2. A TransactWriteItems call containing both item writes Correct answer.
  3. A DynamoDB Streams consumer that reverts on failure
  4. Two PutItem calls wrapped in a retry loop

Explanation

TransactWriteItems groups up to 100 item actions into a single all-or-nothing ACID transaction, optionally with condition checks. This is the only DynamoDB primitive that guarantees atomicity across multiple items.

Question 39Workload-Specific Database Design

A Redshift dashboard runs the same expensive multi-table aggregation every few minutes over slowly changing data. Which feature reduces repeated compute while keeping results current?

  1. A stored procedure that truncates and reloads a staging table
  2. A materialized view with automatic refresh Correct answer.
  3. A late-binding view over the base tables
  4. A DISTSTYLE ALL copy of the fact table

Explanation

A materialized view stores the precomputed result set and can refresh automatically or on demand, so repeated dashboard queries read the stored result instead of re-running the aggregation. Late-binding views only store the query text.

Question 40Workload-Specific Database Design

A team wants a Redis-compatible, in-memory database that can serve as the durable primary data store with microsecond reads and survive a node failure without losing data. Which service fits?

  1. Amazon MemoryDB for Redis with a Multi-AZ transactional log Correct answer.
  2. Amazon ElastiCache for Memcached with multiple nodes
  3. Amazon DynamoDB Accelerator (DAX) in front of a DynamoDB table
  4. Amazon ElastiCache for Redis with cluster mode disabled and no replication

Explanation

MemoryDB for Redis is a durable in-memory database that writes to a Multi-AZ transactional log, so data survives node and AZ failures while reads are served from memory in microseconds. ElastiCache is a cache tier and does not provide that durability guarantee.

Question 41Workload-Specific Database Design

Which statement about Amazon DynamoDB local secondary indexes is accurate?

  1. They can be created with any partition key chosen by the designer
  2. They have their own provisioned write capacity separate from the base table
  3. They may be added to an existing table at any time
  4. They must be created with the table and share its partition key Correct answer.

Explanation

A local secondary index must be defined when the table is created and uses the same partition key as the base table with a different sort key. Because it is stored in the same partition, one partition key's indexed data is limited to 10 GB.

Question 42Workload-Specific Database Design

Query concurrency on a Redshift cluster spikes sharply during business hours and queries queue, although average utilization across the day stays modest. Which feature absorbs the bursts without permanently provisioning peak capacity?

  1. Redshift concurrency scaling Correct answer.
  2. Redshift Spectrum external tables
  3. Redshift cross-Region snapshot copy
  4. Redshift short query acceleration for every query

Explanation

Concurrency scaling adds transient compute clusters when queues build and removes them when demand falls, billed only for the extra time used. This matches bursty concurrency without sizing for peak permanently.

Question 43Workload-Specific Database Design

A web application's session state must be shared across hundreds of stateless application servers and must expire automatically after a period of inactivity. Which store fits best?

  1. Amazon DynamoDB Streams with a Lambda consumer
  2. Amazon ElastiCache for Redis with TTL on session keys Correct answer.
  3. Amazon RDS for MySQL with a sessions table
  4. Amazon Redshift with a sessions table

Explanation

ElastiCache for Redis keeps session data in memory for sub-millisecond access across all application servers and expires keys automatically through per-key TTL. This avoids a per-request disk round trip to a relational database.

Question 44Workload-Specific Database Design

A new DynamoDB table will back a product launch whose traffic pattern is completely unknown and highly variable, and throttling must be avoided without manual tuning. Which capacity mode fits?

  1. On-demand capacity mode Correct answer.
  2. Provisioned mode with purchased reserved capacity
  3. Provisioned mode with fixed capacity sized for peak
  4. Provisioned mode with auto scaling that reacts every 15 minutes

Explanation

On-demand capacity mode automatically accommodates whatever request rate arrives and bills per request, so no capacity planning is needed for unknown workloads. Provisioned modes require a throughput estimate and may throttle before scaling catches up.

Question 45Workload-Specific Database Design

An Aurora MySQL cluster contains replicas of different instance sizes, and reporting queries must be routed only to the two largest replicas. Which feature accomplishes this?

  1. The cluster's reader endpoint
  2. A custom endpoint containing only the chosen replicas Correct answer.
  3. A Route 53 latency alias pointing at each replica
  4. The cluster's writer endpoint with read splitting

Explanation

An Aurora custom endpoint is a named set of DB instances that you define, and clients connecting to it are load-balanced across only those instances. This isolates a workload from the rest of the cluster's readers.

Question 46Workload-Specific Database Design

A 2 TB Redshift table is distributed with DISTKEY on a column where 80 percent of rows share one value, and one slice is consistently overfull while the others sit idle. Which change addresses the root cause?

  1. Increase the number of nodes in the cluster to spread the skew
  2. Add a compound sort key on the skewed column
  3. Change the table to DISTSTYLE ALL so every node holds a full copy
  4. Choose a higher-cardinality column that is also used in joins as the DISTKEY Correct answer.

Explanation

Distribution skew occurs when the distribution key has low cardinality, so most rows hash to one slice and that slice becomes the bottleneck for every query. Re-keying on a high-cardinality column used in joins restores even distribution and preserves join colocation.

Question 47Workload-Specific Database Design

A DynamoDB table stores many item types, but only about 2 percent of items are flagged for manual review, and reviewers need a cheap query that returns just those items. Which design minimizes index storage and write cost?

  1. A local secondary index on the flag attribute
  2. A full-table scan with a filter expression on the flag
  3. A global secondary index on a boolean flagged attribute
  4. A sparse global secondary index whose keys exist only on flagged items Correct answer.

Explanation

A global secondary index is sparse: an item appears in it only if it contains the index's key attributes. Writing those attributes onto flagged items alone yields a small index with no cost for the other 98 percent of items.

Question 48Workload-Specific Database Design

A platform needs ACID transactions with multi-table joins for billing, millisecond key-value access at very high scale for shopping carts, and relevance-ranked full-text search for products. Which combination reflects polyglot persistence?

  1. A single Aurora cluster serving billing, cart, and search
  2. DynamoDB for billing, Aurora for carts, Redshift for search
  3. Aurora for billing, DynamoDB for carts, OpenSearch for search Correct answer.
  4. Redshift for billing, OpenSearch for carts, Aurora for search

Explanation

Polyglot persistence assigns each access pattern to the engine built for it: a relational engine for transactional joins, a key-value store for high-volume item access, and a search engine for relevance-ranked text. The result is that no workload is forced onto an engine that is poor at it.

Question 49Workload-Specific Database Design

A single logical operation must atomically modify 300 DynamoDB items totalling roughly 2 MB. What is the correct design response?

  1. Split into two TransactWriteItems calls and retry until both succeed
  2. Split into chunks of at most 100 items and 4 MB Correct answer.
  3. Send all 300 items in a single TransactWriteItems call
  4. Use BatchWriteItem, which applies all 500 items atomically

Explanation

TransactWriteItems accepts at most 100 item actions and 4 MB of data per transaction, so a 300-item atomic unit is not directly expressible. The workload must be re-modelled into smaller transactions or aggregated into fewer, larger items.

Question 50Workload-Specific Database Design

A SaaS platform has thousands of small tenants plus a handful of very large tenants, and the small tenants must not contend with the large ones for database resources. Which tenancy model fits best?

  1. Pool all tenants in one database sharded only by tenant id
  2. Give each tenant a dedicated Redshift cluster for analytics
  3. Give every tenant its own dedicated database for uniform isolation
  4. Pool small tenants and give large tenants dedicated databases Correct answer.

Explanation

A hybrid model pools many small tenants into shared infrastructure to control cost while isolating very large tenants whose load could dominate shared resources. It balances per-tenant isolation against the operational overhead of one database per tenant.