Sort key purpose and usage in DynamoDB - Mini Project: Build & Apply

Understanding Sort Key Purpose and Usage in DynamoDB

📖 Scenario: You are building a simple online bookstore database using DynamoDB. You want to organize books by their genre and then by their publication year within each genre.

🎯 Goal: Create a DynamoDB table with a partition key and a sort key to store books by genre and publication year. Then, add items to the table and query books by genre sorted by year.

📋 What You'll Learn

Create a DynamoDB table named Books with Genre as the partition key and Year as the sort key.

Add at least three book items with the same genre but different years.

Write a query to retrieve all books of a specific genre sorted by year.

💡 Why This Matters

🌍 Real World

Organizing data in DynamoDB using partition and sort keys helps efficiently store and retrieve related items, such as books by genre and year.

💼 Career

Understanding partition and sort keys is essential for designing scalable and performant NoSQL databases in cloud applications.

1

Create DynamoDB table with partition key and sort key

Create a DynamoDB table named Books with Genre as the partition key (string) and Year as the sort key (number).

DynamoDB

# Your code here to define the Books table with Genre as partition key and Year as sort key

Hint

Use aws dynamodb create-table command with --key-schema specifying HASH for partition key and RANGE for sort key.

2

Add book items with same genre but different years

Add three book items to the Books table with Genre set to Fiction and Year values 2010, 2015, and 2020. Include an attribute Title with any book names.

DynamoDB

aws dynamodb create-table \
    --table-name Books \
    --attribute-definitions AttributeName=Genre,AttributeType=S AttributeName=Year,AttributeType=N \
    --key-schema AttributeName=Genre,KeyType=HASH AttributeName=Year,KeyType=RANGE \
    --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5
# Your code here to add three Fiction books with years 2010, 2015, and 2020

Hint

Use aws dynamodb put-item command to add each book with the specified Genre and Year.

3

Query books by genre sorted by year

Write a DynamoDB query command to get all books where Genre is Fiction. The results should be sorted by Year in ascending order (default).

DynamoDB

aws dynamodb create-table \
    --table-name Books \
    --attribute-definitions AttributeName=Genre,AttributeType=S AttributeName=Year,AttributeType=N \
    --key-schema AttributeName=Genre,KeyType=HASH AttributeName=Year,KeyType=RANGE \
    --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2010"}, "Title": {"S": "Book One"}}'
aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2015"}, "Title": {"S": "Book Two"}}'
aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2020"}, "Title": {"S": "Book Three"}}'
# Your code here to query all Fiction books sorted by Year

Hint

Use aws dynamodb query with --key-condition-expression to filter by Genre. Results are sorted by Year automatically.

4

Explain the purpose of the sort key in this table

Add a comment line explaining that the Year sort key allows storing multiple books in the same Genre partition and sorting them by publication year.

DynamoDB

aws dynamodb create-table \
    --table-name Books \
    --attribute-definitions AttributeName=Genre,AttributeType=S AttributeName=Year,AttributeType=N \
    --key-schema AttributeName=Genre,KeyType=HASH AttributeName=Year,KeyType=RANGE \
    --provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2010"}, "Title": {"S": "Book One"}}'
aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2015"}, "Title": {"S": "Book Two"}}'
aws dynamodb put-item --table-name Books --item '{"Genre": {"S": "Fiction"}, "Year": {"N": "2020"}, "Title": {"S": "Book Three"}}'

aws dynamodb query \
    --table-name Books \
    --key-condition-expression "Genre = :g" \
    --expression-attribute-values '{":g":{"S":"Fiction"}}'
# Your comment here explaining the sort key purpose

Hint

Write a comment starting with # explaining that the sort key helps organize items with the same partition key and sorts them.

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