Composite sort key pattern in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Querying items with composite sort key prefix

Given a DynamoDB table with a composite sort key in the format TYPE#ID, which query will return all items where the sort key starts with ORDER# for a given partition key USER#123?

DynamoDB

PartitionKey = 'USER#123'
SortKey begins with 'ORDER#'

A KeyConditionExpression: 'PartitionKey = :pk AND SortKey = :prefix', ExpressionAttributeValues: { ':pk': 'USER#123', ':prefix': 'ORDER#' }

B FilterExpression: 'begins_with(SortKey, :prefix)', ExpressionAttributeValues: { ':prefix': 'ORDER#' }

C KeyConditionExpression: 'PartitionKey = :pk AND begins_with(SortKey, :prefix)', ExpressionAttributeValues: { ':pk': 'USER#123', ':prefix': 'ORDER#' }

D KeyConditionExpression: 'PartitionKey = :pk AND SortKey > :prefix', ExpressionAttributeValues: { ':pk': 'USER#123', ':prefix': 'ORDER#' }

💡 Hint

Use begins_with in the KeyConditionExpression to filter sort keys by prefix.

Option C correctly uses begins_with in the KeyConditionExpression to query items with sort keys starting with ORDER# for the given partition key.

Option C looks for exact match, which won't return all prefixed items.

Option C uses FilterExpression, which filters after fetching all partition key items, less efficient.

Option C uses a greater than comparison, which may return unwanted items.

🧠 Conceptual

intermediate

Understanding composite sort key structure

Why do we use a composite sort key like TYPE#ID in DynamoDB tables?

A To combine multiple attributes into one key to enable efficient querying and sorting by type and id.

B To store multiple values in a single attribute to save storage space.

C To encrypt the sort key for security reasons.

D To avoid using partition keys altogether.

💡 Hint

Think about how combining values helps with queries.

Composite sort keys combine multiple pieces of information into one attribute, allowing queries to filter and sort efficiently by parts of the key.

Options A, B, and D do not describe the purpose of composite sort keys.

📝 Syntax

advanced

Correct syntax for querying composite sort key with range

Which of the following DynamoDB query snippets correctly retrieves items with partition key USER#456 and sort keys between ORDER#100 and ORDER#200 inclusive?

A FilterExpression: 'SortKey BETWEEN :start AND :end', ExpressionAttributeValues: { ':start': 'ORDER#100', ':end': 'ORDER#200' }

B KeyConditionExpression: 'PartitionKey = :pk AND SortKey BETWEEN :start AND :end', ExpressionAttributeValues: { ':pk': 'USER#456', ':start': 'ORDER#100', ':end': 'ORDER#200' }

C KeyConditionExpression: 'PartitionKey = :pk AND SortKey >= :start AND SortKey <= :end', ExpressionAttributeValues: { ':pk': 'USER#456', ':start': 'ORDER#100', ':end': 'ORDER#200' }

D KeyConditionExpression: 'PartitionKey = :pk AND SortKey > :start AND SortKey < :end', ExpressionAttributeValues: { ':pk': 'USER#456', ':start': 'ORDER#100', ':end': 'ORDER#200' }

💡 Hint

Use BETWEEN in KeyConditionExpression for inclusive range queries.

Option B uses BETWEEN in the KeyConditionExpression which is the correct syntax for inclusive range queries on sort keys.

Option B uses multiple conditions which is invalid in KeyConditionExpression.

Option B uses FilterExpression which filters after fetching all partition key items, less efficient.

Option B uses exclusive range operators which exclude boundary values.

🔧 Debug

advanced

Debugging incorrect query results with composite sort key

A developer queries a DynamoDB table with partition key USER#789 and sort key prefix INVOICE# using this expression:

KeyConditionExpression: 'PartitionKey = :pk AND SortKey = :prefix', ExpressionAttributeValues: { ':pk': 'USER#789', ':prefix': 'INVOICE#' }

Why does this query return no results even though items with sort keys like INVOICE#001 exist?

A Because the query uses equality on the sort key instead of a prefix match, so it looks for an exact match to 'INVOICE#'.

B Because the partition key value is incorrect and does not match any items.

C Because the sort key attribute name is misspelled in the query.

D Because DynamoDB does not support querying by sort key.

💡 Hint

Think about how equality works versus prefix matching.

The query uses SortKey = :prefix which looks for an exact match to 'INVOICE#'. Since items have sort keys like 'INVOICE#001', they do not match exactly.

Option A is incorrect because the partition key is assumed correct.

Option A is incorrect because attribute names are correct.

Option A is false; DynamoDB supports querying by sort key.

❓ optimization

expert

Optimizing queries with composite sort keys for multiple types

You have a DynamoDB table where the sort key combines type and id like TYPE#ID. You want to efficiently query all items of types ORDER and INVOICE for partition key USER#999. Which approach is best?

A Use a single query with KeyConditionExpression 'PartitionKey = :pk' and FilterExpression 'begins_with(SortKey, :order) OR begins_with(SortKey, :invoice)'.

B Create a Global Secondary Index with partition key as type and sort key as id to query by type directly.

C Scan the entire table and filter items with sort keys starting with 'ORDER#' or 'INVOICE#'.

D Run two separate queries with KeyConditionExpression using begins_with for 'ORDER#' and 'INVOICE#' respectively, then merge results in application code.

💡 Hint

Consider query efficiency and DynamoDB limitations on OR conditions in KeyConditionExpression.

Option D is best because DynamoDB does not support OR in KeyConditionExpression, so separate queries with begins_with are efficient and precise.

Option D uses FilterExpression with OR, which filters after fetching all partition key items, less efficient.

Option D scans the entire table, very inefficient.

Option D requires schema changes and may not be feasible.

Practice

1. What is the main purpose of using a composite sort key in DynamoDB?

easy

A. To create a unique partition key for each item

B. To increase the read capacity of the table

C. To encrypt data stored in the table

D. To combine multiple attributes into one key for better sorting and grouping

Solution

1. Step 1: Understand composite sort key concept

   A composite sort key combines multiple values into one string to organize data efficiently.

2. Step 2: Identify its main use

   This helps group related items and sort them within the same partition key.

3. Final Answer:

   To combine multiple attributes into one key for better sorting and grouping -> Option D

4. Quick Check:

   Composite sort key = combine attributes [OK]

Hint: Composite sort keys combine values to sort and group data [OK]

Common Mistakes:

• Confusing composite sort key with partition key
• Thinking it encrypts data
• Assuming it increases capacity

2. Which of the following is a correct example of a composite sort key format?

easy

A. 2023-04-01_Customer123_Order

B. Customer123|Order|2023-04-01

C. Order:2023-04-01:Customer123

D. Order#2023-04-01#Customer123

Solution

1. Step 1: Identify proper delimiter usage

   Composite sort keys use clear delimiters like colons to separate parts for easy parsing.

2. Step 2: Check each option's delimiter consistency

   Order:2023-04-01:Customer123 uses colons consistently, making it a clear composite key format.

3. Final Answer:

   Order:2023-04-01:Customer123 -> Option C

4. Quick Check:

   Clear delimiters = Order:2023-04-01:Customer123 [OK]

Hint: Use consistent delimiters like ':' in composite keys [OK]

Common Mistakes:

• Using inconsistent or unclear delimiters
• Mixing delimiters without pattern
• Not separating parts clearly

3. Given a DynamoDB table with partition key UserID and composite sort key OrderDate#OrderID, what will be the result of querying for UserID = 'U123' and sort key begins with '2023-05-'?

medium

A. All orders by user U123 placed in May 2023

B. All orders by user U123 regardless of date

C. Orders from all users placed in May 2023

D. No results because sort key format is invalid

Solution

1. Step 1: Understand the composite sort key structure

   The sort key combines OrderDate and OrderID separated by '#', so prefix '2023-05-' matches May 2023 orders.

2. Step 2: Query with partition key and sort key prefix

   Querying UserID='U123' and sort key begins with '2023-05-' returns all U123's orders in May 2023.

3. Final Answer:

   All orders by user U123 placed in May 2023 -> Option A

4. Quick Check:

   Partition + sort key prefix = filtered orders [OK]

Hint: Use sort key prefix to filter by date in composite keys [OK]

Common Mistakes:

• Ignoring partition key in query
• Assuming all users' data is returned
• Misunderstanding sort key prefix filtering

4. You wrote a composite sort key as OrderDate-OrderID but your queries using begins_with('2023-06') return no results. What is the likely problem?

medium

A. The sort key should use a consistent delimiter like ':' or '#' instead of '-'

B. The query must use contains instead of begins_with

C. The delimiter '-' is missing in the query filter

D. The partition key is incorrect

Solution

1. Step 1: Check delimiter consistency in composite keys

   Using '-' as delimiter can cause confusion if date format also contains '-'.

2. Step 2: Understand query prefix matching

   Because date and delimiter both use '-', the prefix '2023-06' may not match correctly; consistent delimiters like ':' or '#' avoid this.

3. Final Answer:

   The sort key should use a consistent delimiter like ':' or '#' instead of '-' -> Option A

4. Quick Check:

   Delimiter confusion breaks prefix queries [OK]

Hint: Avoid delimiters that appear in data parts [OK]

Common Mistakes:

• Using delimiters that clash with data format
• Wrong query function for prefix
• Ignoring partition key correctness

5. You want to design a DynamoDB table to store chat messages with partition key ChatRoomID and composite sort key combining MessageDate and MessageID. Which composite sort key format is best to efficiently query messages by date and maintain uniqueness?

hard

A. MessageID#MessageDate with date in any format

B. MessageDate#MessageID with date in ISO format (YYYY-MM-DDTHH:mm:ss)

C. MessageDate-MessageID with date in MM/DD/YYYY

D. MessageDate_MessageID with date as Unix timestamp

Solution

1. Step 1: Choose delimiter and date format for sorting

   Using '#' as delimiter is clear and ISO date format sorts lexicographically by date/time.

2. Step 2: Ensure uniqueness and query efficiency

   Placing MessageDate first allows range queries by date; MessageID ensures uniqueness within same timestamp.

3. Final Answer:

   MessageDate#MessageID with date in ISO format (YYYY-MM-DDTHH:mm:ss) -> Option B

4. Quick Check:

   ISO date + clear delimiter = best composite key [OK]

Hint: Use ISO date + clear delimiter for sortable composite keys [OK]

Common Mistakes:

• Using ambiguous delimiters
• Placing unique ID before date
• Using non-lexicographically sortable date formats