Many-to-many with GSI overloading in DynamoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Query to find all orders for a given customer using GSI overloading

Given a DynamoDB table with a many-to-many relationship between Customers and Orders using a Global Secondary Index (GSI) that overloads the partition key to store both customer and order data, which query will return all orders for customer with ID C123?

DynamoDB

Table: Orders
Partition Key: PK
Sort Key: SK
GSI1 Partition Key: GSI1PK
GSI1 Sort Key: GSI1SK

Data example:
PK = 'CUSTOMER#C123', SK = 'PROFILE'
PK = 'ORDER#O456', SK = 'DETAILS'
GSI1PK = 'CUSTOMER#C123', GSI1SK = 'ORDER#O456'

Query options:

A Scan main table filtering PK = 'CUSTOMER#C123' and SK begins_with 'ORDER#'

B Query GSI1 with KeyConditionExpression: GSI1PK = 'CUSTOMER#C123' and begins_with(GSI1SK, 'ORDER#')

C Query main table with KeyConditionExpression: PK = 'CUSTOMER#C123' and SK begins_with 'ORDER#'

D Query GSI1 with KeyConditionExpression: GSI1PK = 'ORDER#O456' and GSI1SK begins_with 'CUSTOMER#'

💡 Hint

Remember that GSI1 is designed to link customers to their orders by overloading the partition key.

Option B correctly queries the GSI where the partition key is the customer ID and the sort key starts with 'ORDER#', returning all orders for that customer. Option B queries the main table but orders are not stored under the customer's PK. Option B is inefficient and incorrect because orders are not stored under the customer's PK in the main table. Option B reverses the keys and will not return the correct data.

🧠 Conceptual

intermediate

Understanding GSI overloading for many-to-many relationships

In a many-to-many relationship modeled in DynamoDB using GSI overloading, what is the main reason for overloading the GSI partition key with different entity prefixes?

A To automatically replicate data across regions

B To reduce the size of the main table by storing data only in the GSI

C To enforce uniqueness of items across different tables

D To enable querying multiple entity types using the same GSI by distinguishing them with prefixes

💡 Hint

Think about how the GSI can be used to query different entities efficiently.

Overloading the GSI partition key with prefixes like 'CUSTOMER#' or 'ORDER#' allows the same GSI to index multiple entity types and enables efficient queries by filtering on these prefixes. The other options do not describe the purpose of overloading.

📝 Syntax

advanced

Identify the syntax error in this DynamoDB query using GSI overloading

Which option contains a syntax error in the DynamoDB query expression to fetch all orders for customer C789 using GSI1?

DynamoDB

Query parameters example:
IndexName: 'GSI1'
KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :skprefix)'
ExpressionAttributeValues: { ':pk': 'CUSTOMER#C789', ':skprefix': 'ORDER#' }

A KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :skprefix)'

B ')xiferpks: ,KS1ISG(htiw_snigeb dna kp: = KP1ISG' :noisserpxEnoitidnoCyeK

C KeyConditionExpression: 'GSI1PK = :pk and GSI1SK begins_with :skprefix'

D KeyConditionExpression: 'GSI1PK = :pk and GSI1SK = :skprefix'

💡 Hint

Check the syntax for the begins_with function in DynamoDB expressions.

Option C is invalid because begins_with is a function and must be called as begins_with(attribute, value). Writing GSI1SK begins_with :skprefix is a syntax error. Options A and C are correct syntax. Option C is valid but does not filter by prefix.

❓ optimization

advanced

Optimizing queries for many-to-many relationships with GSI overloading

You want to optimize read performance when querying all customers linked to a specific order using a GSI that overloads keys. Which design choice improves query efficiency?

A Use a GSI with partition key as 'ORDER#orderId' and sort key as 'CUSTOMER#customerId' to query customers by order

B Use a scan operation on the main table filtering by order ID prefix

C Store customer and order data in separate tables to avoid GSI overloading

D Use a GSI with partition key as 'CUSTOMER#customerId' and sort key as 'ORDER#orderId' to query customers by order

💡 Hint

Think about how partition keys affect query speed and filtering.

Option A is correct because it allows querying all customers for a given order by partitioning on the order ID, which is efficient. Option A is inefficient because scans are costly. Option A avoids GSI overloading but adds complexity and does not optimize queries. Option A is reversed and does not efficiently query customers by order.

🔧 Debug

expert

Debugging missing results in many-to-many GSI queries

You have a DynamoDB table with many-to-many relationships using GSI overloading. You run this query on GSI1 to get all orders for customer C555:

QueryIndex: 'GSI1'
KeyConditionExpression: 'GSI1PK = :pk and begins_with(GSI1SK, :prefix)'
ExpressionAttributeValues: { ':pk': 'CUSTOMER#C555', ':prefix': 'ORDER#' }

But the query returns no results, even though orders exist. What is the most likely cause?

A The GSI1PK attribute values for orders linked to customer C555 are missing or incorrect in the table items

B The KeyConditionExpression syntax is invalid and causes the query to fail silently

C The main table partition key is used instead of the GSI partition key in the query

D The ExpressionAttributeValues use wrong prefixes like 'ORDER-' instead of 'ORDER#'

💡 Hint

Check if the GSI attributes are correctly populated for linked items.

If the GSI1PK attribute is missing or not set to 'CUSTOMER#C555' on the order items, the query on GSI1 will return no results. The syntax in the query is correct, so option A is unlikely. Option A is about the main table but the query uses GSI1 correctly. Option A is a possible typo but less likely if the query runs without error.