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.