Composite primary key in DynamoDB - Step-by-Step Execution

Concept Flow - Composite primary key

Start: Insert Item

↓

Check Partition Key

↓

Check Sort Key

↓

Combine Partition + Sort Key

↓

Store Item with Composite Key

↓

Query by Partition Key

↓

Filter or Sort by Sort Key

↓

Return Matching Items

DynamoDB uses a composite primary key made of a partition key and a sort key to uniquely identify items and enable efficient queries.

Execution Sample

DynamoDB

Table: Orders
Partition Key: CustomerID
Sort Key: OrderDate

Insert: {CustomerID: 'C123', OrderDate: '2024-06-01', Amount: 100}
Insert: {CustomerID: 'C123', OrderDate: '2024-06-02', Amount: 150}
Query: CustomerID = 'C123' AND OrderDate >= '2024-06-01'

This example inserts two orders for the same customer with different order dates and queries orders by customer and date.

Execution Table

Step	Action	Partition Key	Sort Key	Composite Key	Result
1	Insert first item	C123	2024-06-01	C123#2024-06-01	Item stored
2	Insert second item	C123	2024-06-02	C123#2024-06-02	Item stored
3	Query items with Partition Key 'C123' and Sort Key >= '2024-06-01'	C123	>= 2024-06-01	C123#2024-06-01 and above	Returns both items
4	Query items with Partition Key 'C123' and Sort Key = '2024-06-01'	C123	= 2024-06-01	C123#2024-06-01	Returns first item only
5	Query items with Partition Key 'C123' and Sort Key < '2024-06-02'	C123	< 2024-06-02	C123# before 2024-06-02	Returns first item only
6	Query items with Partition Key 'C999'	C999	any	C999#any	Returns no items

💡 Queries stop when no more matching composite keys found or partition key does not exist.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6
Partition Key	None	C123	C123	C123	C123	C123	C999
Sort Key	None	2024-06-01	2024-06-02	>= 2024-06-01	= 2024-06-01	< 2024-06-02	any
Composite Key	None	C123#2024-06-01	C123#2024-06-02	C123#2024-06-01 and above	C123#2024-06-01	C123# before 2024-06-02	C999#any
Result	None	Item stored	Item stored	Returns both items	Returns first item only	Returns first item only	Returns no items

Key Moments - 3 Insights

Why do we need both partition key and sort key to uniquely identify an item?

Because the partition key alone groups items, but multiple items can share it. The sort key differentiates items within that group, as shown in execution_table rows 1 and 2.

What happens if we query only by partition key without specifying sort key?

DynamoDB returns all items with that partition key, as in execution_table row 3, which returns both orders for customer 'C123'.

Why does querying a non-existing partition key return no items?

Because no items are stored under that partition key, as shown in execution_table row 6 where 'C999' returns no results.

Visual Quiz - 3 Questions

Look at the execution_table row 3, what items are returned when querying CustomerID 'C123' with Sort Key >= '2024-06-01'?

A Only the item with OrderDate '2024-06-02'

B Both items with OrderDate '2024-06-01' and '2024-06-02'

C Only the item with OrderDate '2024-06-01'

D No items

💡 Hint

Check the Result column in row 3 of execution_table

At which step does the composite key 'C123#2024-06-02' get stored?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look at the Composite Key column in execution_table rows 1 and 2

If we query with Partition Key 'C123' and Sort Key = '2024-06-03', what will be the result?

A Returns the item with OrderDate '2024-06-02'

B Returns the item with OrderDate '2024-06-01'

C Returns no items

D Returns both items

💡 Hint

Refer to execution_table row 6 where querying a non-existing key returns no items

Concept Snapshot

Composite primary key in DynamoDB combines Partition Key and Sort Key.
Partition Key groups items; Sort Key orders and uniquely identifies within the group.
Together they form a unique composite key.
Queries use Partition Key and optionally Sort Key conditions.
This enables efficient data retrieval and sorting.

Full Transcript

In DynamoDB, a composite primary key consists of two parts: a partition key and a sort key. When inserting an item, both keys combine to form a unique identifier. For example, a table with CustomerID as partition key and OrderDate as sort key stores orders uniquely by customer and date. Queries can retrieve all items for a customer or filter by order date. The execution table shows inserting two orders for the same customer with different dates, then querying by customer and date ranges. Variable tracking shows how keys and results change step by step. Key moments clarify why both keys are needed and what happens when querying with partial keys. The visual quiz tests understanding of which items are returned for different queries. The concept snapshot summarizes the composite key's role in organizing and querying data efficiently.

Practice

1.

What is a composite primary key in DynamoDB?

easy

A. A key that allows duplicate items

B. A key made of only one attribute

C. A key made of two attributes: PartitionKey and SortKey

D. A key used only for indexing

Solution

1. Step 1: Understand primary key types in DynamoDB

   DynamoDB supports simple primary keys (one attribute) and composite primary keys (two attributes).

2. Step 2: Identify composite primary key components

   A composite primary key consists of a PartitionKey and a SortKey to uniquely identify items.

3. Final Answer:

   A key made of two attributes: PartitionKey and SortKey -> Option C

4. Quick Check:

   Composite primary key = PartitionKey + SortKey [OK]

Hint: Composite key always has PartitionKey and SortKey [OK]

Common Mistakes:

• Thinking composite key has only one attribute
• Confusing composite key with secondary indexes
• Believing composite key allows duplicates

2.

Which of the following is the correct way to define a composite primary key in DynamoDB table creation?

{
  "TableName": "Orders",
  "KeySchema": [
    {"AttributeName": "CustomerId", "KeyType": "HASH"},
    {"AttributeName": "OrderDate", "KeyType": "RANGE"}
  ]
}

easy

A. Use KeyType RANGE for both attributes

B. Use KeyType RANGE for PartitionKey and HASH for SortKey

C. Use KeyType HASH for both attributes

D. Use KeyType HASH for PartitionKey and RANGE for SortKey

Solution

1. Step 1: Recall KeyType meanings

   In DynamoDB, HASH means PartitionKey and RANGE means SortKey.

2. Step 2: Match KeyType to attributes

   PartitionKey must be HASH and SortKey must be RANGE for composite keys.

3. Final Answer:

   Use KeyType HASH for PartitionKey and RANGE for SortKey -> Option D

4. Quick Check:

   PartitionKey=HASH, SortKey=RANGE [OK]

Hint: HASH = PartitionKey, RANGE = SortKey in KeySchema [OK]

Common Mistakes:

• Swapping HASH and RANGE roles
• Using same KeyType for both keys
• Confusing attribute names with KeyType

3.

Given a DynamoDB table with composite primary key (UserId as PartitionKey, Timestamp as SortKey), what will this query return?

{
  "TableName": "UserActivity",
  "KeyConditionExpression": "UserId = :uid and Timestamp > :time",
  "ExpressionAttributeValues": {
    ":uid": {"S": "user123"},
    ":time": {"N": "1609459200"}
  }
}

medium

A. All activities of user123 with Timestamp greater than 1609459200

B. All activities of all users with Timestamp greater than 1609459200

C. All activities of user123 regardless of Timestamp

D. Syntax error due to missing SortKey in query

Solution

1. Step 1: Understand KeyConditionExpression

   It filters items by PartitionKey equality and SortKey condition.

2. Step 2: Analyze the query conditions

   UserId = :uid restricts to user123; Timestamp > :time filters timestamps after 1609459200.

3. Final Answer:

   All activities of user123 with Timestamp greater than 1609459200 -> Option A

4. Quick Check:

   PartitionKey + SortKey condition filters items [OK]

Hint: PartitionKey = value AND SortKey condition filters items [OK]

Common Mistakes:

• Thinking query returns all users' data
• Ignoring SortKey condition
• Assuming syntax error without SortKey equality

4.

What is wrong with this DynamoDB query using composite primary key (UserId as PartitionKey, OrderId as SortKey)?

{
  "TableName": "Orders",
  "KeyConditionExpression": "OrderId = :oid",
  "ExpressionAttributeValues": {
    ":oid": {"S": "order789"}
  }
}

medium

A. PartitionKey UserId is missing in KeyConditionExpression

B. SortKey OrderId cannot be used in KeyConditionExpression

C. ExpressionAttributeValues syntax is incorrect

D. TableName is invalid

Solution

1. Step 1: Recall query requirements for composite keys

   Queries must specify PartitionKey equality in KeyConditionExpression.

2. Step 2: Check the given query

   Only SortKey OrderId is used; PartitionKey UserId is missing, causing error.

3. Final Answer:

   PartitionKey UserId is missing in KeyConditionExpression -> Option A

4. Quick Check:

   PartitionKey must be in query condition [OK]

Hint: Always include PartitionKey equality in query [OK]

Common Mistakes:

• Using only SortKey in query condition
• Assuming SortKey alone can identify items
• Ignoring required PartitionKey in queries

5.

You want to store blog posts in DynamoDB. Each post has a AuthorId and a PostDate. You want to quickly find all posts by an author sorted by date. Which composite primary key design is best?

hard

A. PartitionKey: PostDate, SortKey: AuthorId

B. PartitionKey: AuthorId, SortKey: PostDate

C. PartitionKey: AuthorId only, no SortKey

D. PartitionKey: PostDate only, no SortKey

Solution

1. Step 1: Identify query pattern

   You want to find all posts by an author, sorted by date.

2. Step 2: Choose PartitionKey and SortKey accordingly

   PartitionKey should be AuthorId to group posts by author; SortKey should be PostDate to sort posts by date.

3. Final Answer:

   PartitionKey: AuthorId, SortKey: PostDate -> Option B

4. Quick Check:

   Group by author, sort by date = AuthorId + PostDate [OK]

Hint: PartitionKey groups, SortKey sorts related items [OK]

Common Mistakes:

• Swapping PartitionKey and SortKey roles
• Using only one key losing sorting ability
• Choosing SortKey that doesn't support query pattern