Hierarchical data modeling in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query to retrieve all child items of a parent

You have a DynamoDB table storing categories and subcategories. Each item has a PK as the parent category ID and a SK as the child category ID.

Which query will return all subcategories of the parent category with PK = 'CAT#100'?

DynamoDB

TableName: Categories
PartitionKey: PK
SortKey: SK
Example item: { PK: 'CAT#100', SK: 'SUB#200', Name: 'Subcategory A' }

A Query with KeyConditionExpression: "PK = :pkval" and ExpressionAttributeValues: {":pkval": "CAT#100"}

B Scan with FilterExpression: "PK = :pkval" and ExpressionAttributeValues: {":pkval": "CAT#100"}

C Scan with FilterExpression: "SK = :skval" and ExpressionAttributeValues: {":skval": "SUB#200"}

D Query with KeyConditionExpression: "SK = :skval" and ExpressionAttributeValues: {":skval": "SUB#200"}

💡 Hint

Remember, Query operations use the partition key to efficiently find items.

Querying by the partition key PK returns all items with that parent ID. Scans are less efficient and not needed here. Querying by SK alone is invalid because SK is the sort key, not the partition key.

🧠 Conceptual

intermediate

Best way to model parent-child relationships in DynamoDB

Which DynamoDB design pattern is best suited to model hierarchical data with multiple levels of parent-child relationships?

A Use a single table with only a partition key and no sort key

B Use multiple tables, one per hierarchy level, linked by foreign keys

C Use a single table with composite keys where <code>PK</code> is the root ancestor and <code>SK</code> encodes the path to the child

D Use a global secondary index on a timestamp attribute

💡 Hint

Think about how DynamoDB's single-table design supports hierarchical queries.

Using composite keys with PK as the root ancestor and SK encoding the path allows efficient queries for all descendants. Multiple tables or missing sort keys do not support hierarchical queries well. GSI on timestamp is unrelated.

📝 Syntax

advanced

Identify the syntax error in this DynamoDB query expression

What is wrong with this DynamoDB query KeyConditionExpression?

KeyConditionExpression: "PK = :pkval AND begins_with(SK, :skprefix)"
ExpressionAttributeValues: {":pkval": "CAT#100", ":skprefix": "SUB#"}

A PK must be compared with SK in KeyConditionExpression

B begins_with function requires two attribute names, not attribute value

C ExpressionAttributeValues keys must not start with a colon

D The AND operator cannot be used in KeyConditionExpression

💡 Hint

Check the rules for KeyConditionExpression syntax in DynamoDB.

In DynamoDB, KeyConditionExpression supports equality condition on the partition key optionally connected with lowercase 'and' to one condition on the sort key using operators like begins_with. Uppercase 'AND' is invalid syntax.

❓ optimization

advanced

Optimize hierarchical queries with large datasets

You have a DynamoDB table with millions of hierarchical items. Queries for all descendants of a parent are slow. Which optimization will improve query performance?

A Use Scan operation with FilterExpression to find descendants

B Use a composite sort key encoding the full path and query with begins_with on SK

C Add a global secondary index on the child item name attribute

D Store all descendants in a single item as a JSON array

💡 Hint

Think about how sort keys and begins_with can limit query scope.

Encoding the full path in the sort key and querying with begins_with limits the query to relevant items efficiently. GSI on name won't help hierarchical queries. Scan is slow for large data. Storing all descendants in one item can cause size limits and slow reads.

🔧 Debug

expert

Why does this hierarchical query return no results?

Given this DynamoDB query:

KeyConditionExpression: "PK = :pkval and SK > :skval"
ExpressionAttributeValues: {":pkval": "CAT#100", ":skval": "SUB#100"}

But no items are returned, even though items with SK values greater than "SUB#100" exist. What is the most likely cause?

A The SK values are strings but the comparison is lexicographical and does not match expected order

B SK attribute is stored as a number, but compared as a string

C The comparison operator > is not supported in KeyConditionExpression

D The partition key value "CAT#100" does not exist in the table

💡 Hint

Consider how string comparisons work in DynamoDB sort keys.

DynamoDB compares sort keys lexicographically (alphabetical order). If SK values are strings like "SUB#100", "SUB#2", lex order means "SUB#2" > "SUB#100" is false because '1' < '2' in string comparison. This causes unexpected query results.

Practice

1. What is the main purpose of hierarchical data modeling in DynamoDB?

easy

A. To organize data with parent-child relationships

B. To store unrelated data in separate tables

C. To improve data encryption

D. To increase the size of the database

Solution

1. Step 1: Understand hierarchical data modeling

   Hierarchical data modeling organizes data by showing relationships where one item is the parent and others are children.

2. Step 2: Relate to DynamoDB usage

   DynamoDB uses this model to efficiently store and query related items using keys.

3. Final Answer:

   To organize data with parent-child relationships -> Option A

4. Quick Check:

   Hierarchical data = parent-child organization [OK]

Hint: Think parent and child data linked by keys [OK]

Common Mistakes:

• Confusing hierarchical with unrelated data storage
• Thinking it improves encryption
• Assuming it increases database size

2. Which key should be used as the partition key to model a parent item in DynamoDB hierarchical data?

easy

A. Child ID

B. Parent ID

C. Timestamp

D. Random UUID

Solution

1. Step 1: Identify the role of partition key

   The partition key groups related items together in DynamoDB.

2. Step 2: Choose the key representing the parent

   Since the parent is the main group, its ID should be the partition key to link children under it.

3. Final Answer:

   Parent ID -> Option B

4. Quick Check:

   Partition key = Parent ID for grouping [OK]

Hint: Partition key groups parents; use Parent ID [OK]

Common Mistakes:

• Using Child ID as partition key
• Choosing timestamp which is unrelated
• Using random UUID that breaks grouping

3. Given a DynamoDB table with partition key ParentID and sort key ChildID, what will the query KeyConditionExpression: ParentID = 'P1' return?

medium

A. Only child items with ChildID 'P1'

B. Only the parent item with ID 'P1'

C. All items with ParentID 'P1' including parent and children

D. No items, syntax error in query

Solution

1. Step 1: Understand the query condition

   The query filters items where partition key ParentID equals 'P1'.

2. Step 2: Recall hierarchical data structure

   All items with ParentID 'P1' include the parent and its children because they share the same partition key.

3. Final Answer:

   All items with ParentID 'P1' including parent and children -> Option C

4. Quick Check:

   Query by ParentID returns parent + children [OK]

Hint: Query by partition key returns all related items [OK]

Common Mistakes:

• Thinking only parent is returned
• Confusing sort key with partition key
• Assuming syntax error without cause

4. You wrote a DynamoDB query to get all children of a parent using KeyConditionExpression: ParentID = :pid AND ChildID = :cid. It returns only one child instead of all children. What is the mistake?

medium

A. Using '=' on ChildID filters to one child only

B. ParentID should be in filter expression, not key condition

C. Missing a sort key in the table schema

D. ChildID cannot be used in KeyConditionExpression

Solution

1. Step 1: Analyze the key condition

   The condition ChildID = :cid restricts results to one specific child.

2. Step 2: Understand how to get all children

   To get all children, use a range operator like begins_with or omit ChildID condition.

3. Final Answer:

   Using '=' on ChildID filters to one child only -> Option A

4. Quick Check:

   Equals on sort key limits to one item [OK]

Hint: Use range operators, not '=' on sort key for multiple children [OK]

Common Mistakes:

• Thinking ParentID should be filter, not key condition
• Assuming ChildID can't be in key condition
• Ignoring sort key usage in queries

5. You want to model a company hierarchy in DynamoDB where each employee can have multiple subordinates. Which key design best supports efficient queries for all subordinates of a manager?

hard

A. Partition key: EmployeeID, Sort key: Timestamp

B. Partition key: EmployeeID, Sort key: ManagerID

C. Partition key: DepartmentID, Sort key: EmployeeID

D. Partition key: ManagerID, Sort key: EmployeeID

Solution

1. Step 1: Identify parent-child relationship

   Manager is the parent; employees are children (subordinates).

2. Step 2: Choose keys to group subordinates

   Using ManagerID as partition key groups all subordinates under one manager.

3. Step 3: Use EmployeeID as sort key

   Sort key EmployeeID uniquely identifies each subordinate under the manager.

4. Final Answer:

   Partition key: ManagerID, Sort key: EmployeeID -> Option D

5. Quick Check:

   Parent key = ManagerID groups subordinates [OK]

Hint: Partition by parent (manager) to group children (employees) [OK]

Common Mistakes:

• Using EmployeeID as partition key breaks grouping
• Using DepartmentID ignores manager-subordinate relation
• Using timestamp unrelated to hierarchy