Single-table design methodology in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Single-Table Design Basics

In DynamoDB single-table design, what is the main reason to use composite primary keys (partition key + sort key)?

A To allow automatic scaling of read and write capacity without manual configuration

B To store multiple unrelated tables in one physical table without any key constraints

C To uniquely identify each item and enable efficient querying of related items using sort key ranges

D To enforce foreign key constraints between items in the table

💡 Hint

Think about how DynamoDB retrieves items efficiently when you query by partition key and sort key.

Composite primary keys combine partition key and sort key to uniquely identify items and allow queries to fetch related items by sorting within the partition.

❓ query_result

intermediate

Query Result with Composite Keys

Given a DynamoDB table with partition key 'PK' and sort key 'SK', which query will return all orders for customer 'C123' sorted by order date?

Assume items have PK='CUSTOMER#C123' and SK='ORDER#2023-01-01', 'ORDER#2023-02-01', etc.

DynamoDB

Query:
{
  TableName: 'Orders',
  KeyConditionExpression: 'PK = :pk and begins_with(SK, :skprefix)',
  ExpressionAttributeValues: {
    ':pk': 'CUSTOMER#C123',
    ':skprefix': 'ORDER#'
  }
}

A Returns all customers with orders starting with 'ORDER#' prefix

B Returns all orders for customer C123 sorted by date ascending

C Returns all orders for all customers sorted by order date

D Returns no items because begins_with cannot be used with sort key

💡 Hint

Remember that begins_with works on the sort key to filter items within a partition.

The query filters items with PK='CUSTOMER#C123' and SK starting with 'ORDER#', returning all that customer's orders sorted by SK.

📝 Syntax

advanced

Identify the Syntax Error in a DynamoDB Update Expression

Which option contains a syntax error in the DynamoDB UpdateExpression?

UpdateExpression examples:

DynamoDB

A: "SET #status = :newStatus, #count = #count + :inc"
B: "SET #status = :newStatus ADD #count :inc"
C: "SET #status = :newStatus, ADD #count :inc"
D: "SET #status = :newStatus, #count = #count + :inc"

A Syntax error: mixing SET and ADD without separating them properly

B Valid syntax: sets status and increments count with comma separator

C Valid syntax: sets status and adds increment to count using ADD keyword

D Valid syntax: same as A, sets status and increments count

💡 Hint

Check how SET and ADD clauses are combined in UpdateExpression.

In DynamoDB UpdateExpression, multiple actions like SET and ADD must be separated by spaces, not commas. Option A lacks the space between the SET and ADD actions.

❓ optimization

advanced

Optimizing Access Patterns in Single-Table Design

You want to efficiently query all comments for a blog post and also fetch all posts by an author. Which single-table design pattern helps achieve both with minimal queries?

A Use a composite key with PK='POST#postId' and SK='COMMENT#commentId' for comments, and a GSI with PK='AUTHOR#authorId' and SK='POST#postId' for posts

B Create separate tables for posts and comments to avoid complex keys

C Store all comments as a list attribute inside the post item to fetch in one read

D Use a single partition key for all items and filter by type in queries

💡 Hint

Think about how GSIs can support different query patterns in single-table design.

Using a composite key for comments and a GSI keyed by author allows efficient queries for both comments by post and posts by author.

🔧 Debug

expert

Debugging a Query Returning No Results

A query on a DynamoDB single-table design returns no results when trying to fetch all orders for customer 'C789'. The query is:

{
  TableName: 'Sales',
  KeyConditionExpression: 'PK = :pk and SK BETWEEN :start and :end',
  ExpressionAttributeValues: {
    ':pk': 'CUSTOMER#C789',
    ':start': 'ORDER#2023-01-01',
    ':end': 'ORDER#2023-12-31'
  }
}

What is the most likely reason for no results?

A ExpressionAttributeValues keys must start with a letter, so ':start' and ':end' are invalid

B The partition key 'CUSTOMER#C789' does not exist in the table

C BETWEEN operator cannot be used with sort keys in DynamoDB

D The SK values are stored as timestamps without the 'ORDER#' prefix, so BETWEEN does not match

💡 Hint

Check how the sort key values are stored and how BETWEEN compares strings.

If SK values do not include the 'ORDER#' prefix but the query uses it, the BETWEEN condition won't match any items.