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.