Sort key purpose and usage in DynamoDB - Step-by-Step Execution

Concept Flow - Sort key purpose and usage

Start: Table with Partition Key

↓

Add Sort Key to Table

↓

Partition Key groups items

↓

Sort Key orders items within group

↓

Query by Partition Key + Sort Key range

↓

Retrieve ordered subset of items

The sort key organizes items within each partition key group, enabling ordered queries and efficient data retrieval.

Execution Sample

DynamoDB

PartitionKey = 'User#123'
SortKey = 'Order#001'
Query: PartitionKey = 'User#123' AND SortKey BETWEEN 'Order#001' AND 'Order#005'

This query fetches orders for User#123 sorted by order number from 001 to 005.

Execution Table

Step	Action	Partition Key	Sort Key Condition	Resulting Items
1	Start query	User#123	None	All items with PartitionKey 'User#123'
2	Apply Sort Key filter	User#123	SortKey BETWEEN 'Order#001' AND 'Order#005'	Items with SortKey Order#001 to Order#005
3	Return results	User#123	SortKey BETWEEN 'Order#001' AND 'Order#005'	Ordered list of 5 orders
4	End	User#123	SortKey BETWEEN 'Order#001' AND 'Order#005'	Query complete

💡 Query ends after retrieving all items matching PartitionKey and SortKey range.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
PartitionKey	None	User#123	User#123	User#123	User#123
SortKey Condition	None	None	BETWEEN 'Order#001' AND 'Order#005'	BETWEEN 'Order#001' AND 'Order#005'	BETWEEN 'Order#001' AND 'Order#005'
Resulting Items	None	All User#123 items	Filtered to Order#001 to Order#005	Ordered 5 items	Returned 5 items

Key Moments - 2 Insights

Why do we need a sort key if we already have a partition key?

The partition key groups items, but the sort key orders items within that group. Without a sort key, items in the same partition are unordered, making range queries or ordered retrieval impossible. See execution_table step 2 where the sort key filter narrows results.

Can we query only by sort key without partition key?

No, DynamoDB requires the partition key to locate the data partition first. The sort key only works to order or filter items within that partition. This is shown in execution_table step 1 where the partition key is mandatory.

Visual Quiz - 3 Questions

Look at the execution_table, what is the Partition Key value used at step 2?

A Order#001

B Order#005

C User#123

D None

💡 Hint

Check the Partition Key column at step 2 in the execution_table.

At which step does the query apply the Sort Key filter?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the Action column in execution_table to find when the Sort Key condition is applied.

If we remove the Sort Key condition, how would the Resulting Items change at step 2?

A Only one item would be returned

B All items with PartitionKey 'User#123' would be returned

C No items would be returned

D Items from other partitions would be included

💡 Hint

Refer to execution_table step 1 and 2 Resulting Items column to compare.

Concept Snapshot

Sort key organizes items within the same partition key.
It enables ordered queries and range filtering.
Partition key locates the group; sort key orders inside it.
Queries need partition key; sort key refines results.
Use sort key for efficient data retrieval and sorting.

Full Transcript

In DynamoDB, the sort key is used together with the partition key to organize data. The partition key groups items into partitions, and the sort key orders items within each partition. When querying, you must specify the partition key to find the right group. Then you can use the sort key to filter or order items, like getting orders between certain numbers for a user. This makes queries efficient and results ordered. The execution table shows how a query starts with the partition key, applies the sort key filter, and returns ordered results. Beginners often wonder why the sort key is needed or if it can be used alone. The sort key cannot be used without the partition key because DynamoDB needs to find the partition first. Removing the sort key condition returns all items in the partition. Understanding this helps write better queries and design tables well.

Practice

1. What is the main purpose of a sort key in a DynamoDB table?

easy

A. To store large binary data efficiently

B. To uniquely identify each item across all partitions

C. To organize and order items within the same partition key

D. To encrypt data at rest automatically

Solution

1. Step 1: Understand partition and sort keys roles

   The partition key groups items, and the sort key orders items within that group.

2. Step 2: Identify the sort key's purpose

   The sort key organizes related items so they can be retrieved in order and filtered efficiently.

3. Final Answer:

   To organize and order items within the same partition key -> Option C

4. Quick Check:

   Sort key = Organize items in partition [OK]

Hint: Sort key orders items inside a partition [OK]

Common Mistakes:

• Confusing sort key with partition key uniqueness
• Thinking sort key encrypts data
• Assuming sort key stores large binary data

2. Which of the following is the correct way to define a DynamoDB table with a partition key named UserID and a sort key named Timestamp using AWS CLI?

easy

A. aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=Timestamp,KeyType=HASH --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

B. aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=UserID,KeyType=HASH AttributeName=Timestamp,KeyType=HASH --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

C. aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S --key-schema AttributeName=UserID,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

D. aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=UserID,KeyType=HASH AttributeName=Timestamp,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

Solution

1. Step 1: Check attribute definitions and key schema

   Partition key must have KeyType=HASH and sort key must have KeyType=RANGE.

2. Step 2: Verify aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=UserID,KeyType=HASH AttributeName=Timestamp,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 matches correct key types

   aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=UserID,KeyType=HASH AttributeName=Timestamp,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 uses UserID as HASH and Timestamp as RANGE, which is correct.

3. Final Answer:

   aws dynamodb create-table --table-name MyTable --attribute-definitions AttributeName=UserID,AttributeType=S AttributeName=Timestamp,AttributeType=N --key-schema AttributeName=UserID,KeyType=HASH AttributeName=Timestamp,KeyType=RANGE --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5 -> Option D

4. Quick Check:

   Partition=HASH, Sort=RANGE in CLI [OK]

Hint: Sort key uses KeyType=RANGE in key schema [OK]

Common Mistakes:

• Using KeyType=HASH for both keys
• Missing sort key in key schema
• Defining only partition key without sort key

3. Given a DynamoDB table with partition key UserID and sort key OrderDate, what will the following query return?

aws dynamodb query --table-name Orders --key-condition-expression "UserID = :uid AND OrderDate > :date" --expression-attribute-values '{":uid":{"S":"user123"}, ":date":{"S":"2023-01-01"}}'

medium

A. All orders for user123 with OrderDate after 2023-01-01, sorted by OrderDate

B. All orders for user123 regardless of OrderDate

C. All orders with OrderDate after 2023-01-01 for all users

D. Syntax error due to missing partition key in condition

Solution

1. Step 1: Analyze the key condition expression

   The query filters items where UserID equals 'user123' and OrderDate is greater than '2023-01-01'.

2. Step 2: Understand query behavior with partition and sort keys

   Query returns items for one partition key, ordered by sort key, filtered by the condition on sort key.

3. Final Answer:

   All orders for user123 with OrderDate after 2023-01-01, sorted by OrderDate -> Option A

4. Quick Check:

   Query filters by partition and sort key conditions [OK]

Hint: Query needs partition key equality and sort key condition [OK]

Common Mistakes:

• Expecting query to filter across all partitions
• Ignoring sort key condition in query
• Confusing query with scan operation

4. You wrote this DynamoDB query but it returns an error:

aws dynamodb query --table-name Sales --key-condition-expression "OrderID > :oid" --expression-attribute-values '{":oid":{"S":"1000"}}'

What is the likely cause?

medium

A. Using > operator on partition key is allowed

B. Missing partition key equality condition in key-condition-expression

C. Incorrect data type for expression attribute value

D. Missing sort key in attribute definitions

Solution

1. Step 1: Recall query key condition requirements

   DynamoDB query requires partition key equality condition in key-condition-expression.

2. Step 2: Identify error cause

   Query uses only 'OrderID > :oid' without partition key equality, causing error.

3. Final Answer:

   Missing partition key equality condition in key-condition-expression -> Option B

4. Quick Check:

   Partition key equality is mandatory in query [OK]

Hint: Query must have partition key equality condition [OK]

Common Mistakes:

• Using range operators on partition key
• Omitting partition key in query condition
• Confusing query with scan operation

5. You want to model a one-to-many relationship where each CustomerID can have multiple Invoices sorted by InvoiceDate. Which DynamoDB table design best uses the sort key to achieve this?

hard

A. Partition key: CustomerID, Sort key: InvoiceDate

B. Partition key: InvoiceDate, Sort key: CustomerID

C. Partition key: CustomerID only, no sort key

D. Partition key: InvoiceID, Sort key: InvoiceDate

Solution

1. Step 1: Understand one-to-many modeling in DynamoDB

   Use partition key for the 'one' side and sort key to order the 'many' items.

2. Step 2: Apply to CustomerID and Invoices

   CustomerID as partition key groups invoices; InvoiceDate as sort key orders them.

3. Final Answer:

   Partition key: CustomerID, Sort key: InvoiceDate -> Option A

4. Quick Check:

   Sort key models one-to-many order [OK]

Hint: Partition key = one side, sort key = many side order [OK]

Common Mistakes:

• Swapping partition and sort keys
• Not using sort key for ordering
• Using only partition key losing order info