Composite sort key pattern in DynamoDB - Mini Project: Build & Apply

Using Composite Sort Key Pattern in DynamoDB

📖 Scenario: You are building a simple DynamoDB table to store information about books in a library. Each book has a unique BookID and belongs to a category. You want to organize the data so that you can query all books in a category sorted by their publication year and title.

🎯 Goal: Create a DynamoDB table with a composite sort key pattern that combines PublicationYear and Title to allow sorting books within each category by year and then by title.

📋 What You'll Learn

Create a DynamoDB table named Library with Category as the partition key and SortKey as the sort key.

The SortKey should combine PublicationYear and Title separated by a hash (#).

Insert three books with exact data provided.

Write a query to get all books in the Science category sorted by the composite sort key.

💡 Why This Matters

🌍 Real World

Composite sort keys are used in DynamoDB to organize related data items so they can be efficiently queried and sorted within a partition, such as sorting books by year and title within a category.

💼 Career

Understanding composite sort keys is essential for designing scalable and efficient NoSQL databases, a key skill for backend developers and database engineers working with DynamoDB.

1

Create the DynamoDB table and insert books

Create a DynamoDB table named Library with Category as the partition key and SortKey as the sort key. Insert these three books as items with attributes: Category, SortKey, BookID, Author. Use the exact values: {Category: 'Science', SortKey: '2020#Physics Fundamentals', BookID: 'B001', Author: 'John Doe'}, {Category: 'Science', SortKey: '2018#Chemistry Basics', BookID: 'B002', Author: 'Jane Smith'}, {Category: 'Literature', SortKey: '2019#Modern Poetry', BookID: 'B003', Author: 'Emily Clark'}.

DynamoDB

# Create the Library table with Category as partition key and SortKey as sort key
# Insert the three books as items

Hint

Remember to define Category as the partition key and SortKey as the sort key. Combine PublicationYear and Title with a hash (#) for the sort key.

2

Define a helper function to create the composite sort key

Create a helper function called create_sort_key that takes publication_year and title as parameters and returns a string combining them with a hash (#) in between, like "2020#Physics Fundamentals".

DynamoDB

def create_sort_key(publication_year, title):
    # Your code here

Hint

Use an f-string to combine publication_year and title with a hash (#) in between.

3

Use the helper function to insert a new book item

Use the create_sort_key function to create the SortKey for a new book with Category 'Science', PublicationYear 2021, Title 'Astronomy Basics', BookID 'B004', and Author 'Alice Johnson'. Insert this item into the Library table.

DynamoDB

new_sort_key = create_sort_key(2021, 'Astronomy Basics')
# Your code here to insert the new item

Hint

Use the helper function to create the sort key string, then add the new item to the Library dictionary with the correct keys and values.

4

Query books in the Science category sorted by composite sort key

Write a query to get all books in the Science category sorted by the SortKey. Use a for loop to iterate over the Library items, filter by Category equal to 'Science', and collect the items sorted by SortKey in ascending order.

DynamoDB

science_books = [item for item in Library.values() if item['Category'] == 'Science']
science_books_sorted = sorted(science_books, key=lambda x: x['SortKey'])
# Your code here to complete the query

Hint

Filter the items by Category and then sort them by SortKey using sorted() with a lambda function.

Practice

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

easy

A. To create a unique partition key for each item

B. To increase the read capacity of the table

C. To encrypt data stored in the table

D. To combine multiple attributes into one key for better sorting and grouping

Solution

1. Step 1: Understand composite sort key concept

   A composite sort key combines multiple values into one string to organize data efficiently.

2. Step 2: Identify its main use

   This helps group related items and sort them within the same partition key.

3. Final Answer:

   To combine multiple attributes into one key for better sorting and grouping -> Option D

4. Quick Check:

   Composite sort key = combine attributes [OK]

Hint: Composite sort keys combine values to sort and group data [OK]

Common Mistakes:

• Confusing composite sort key with partition key
• Thinking it encrypts data
• Assuming it increases capacity

2. Which of the following is a correct example of a composite sort key format?

easy

A. 2023-04-01_Customer123_Order

B. Customer123|Order|2023-04-01

C. Order:2023-04-01:Customer123

D. Order#2023-04-01#Customer123

Solution

1. Step 1: Identify proper delimiter usage

   Composite sort keys use clear delimiters like colons to separate parts for easy parsing.

2. Step 2: Check each option's delimiter consistency

   Order:2023-04-01:Customer123 uses colons consistently, making it a clear composite key format.

3. Final Answer:

   Order:2023-04-01:Customer123 -> Option C

4. Quick Check:

   Clear delimiters = Order:2023-04-01:Customer123 [OK]

Hint: Use consistent delimiters like ':' in composite keys [OK]

Common Mistakes:

• Using inconsistent or unclear delimiters
• Mixing delimiters without pattern
• Not separating parts clearly

3. Given a DynamoDB table with partition key UserID and composite sort key OrderDate#OrderID, what will be the result of querying for UserID = 'U123' and sort key begins with '2023-05-'?

medium

A. All orders by user U123 placed in May 2023

B. All orders by user U123 regardless of date

C. Orders from all users placed in May 2023

D. No results because sort key format is invalid

Solution

1. Step 1: Understand the composite sort key structure

   The sort key combines OrderDate and OrderID separated by '#', so prefix '2023-05-' matches May 2023 orders.

2. Step 2: Query with partition key and sort key prefix

   Querying UserID='U123' and sort key begins with '2023-05-' returns all U123's orders in May 2023.

3. Final Answer:

   All orders by user U123 placed in May 2023 -> Option A

4. Quick Check:

   Partition + sort key prefix = filtered orders [OK]

Hint: Use sort key prefix to filter by date in composite keys [OK]

Common Mistakes:

• Ignoring partition key in query
• Assuming all users' data is returned
• Misunderstanding sort key prefix filtering

4. You wrote a composite sort key as OrderDate-OrderID but your queries using begins_with('2023-06') return no results. What is the likely problem?

medium

A. The sort key should use a consistent delimiter like ':' or '#' instead of '-'

B. The query must use contains instead of begins_with

C. The delimiter '-' is missing in the query filter

D. The partition key is incorrect

Solution

1. Step 1: Check delimiter consistency in composite keys

   Using '-' as delimiter can cause confusion if date format also contains '-'.

2. Step 2: Understand query prefix matching

   Because date and delimiter both use '-', the prefix '2023-06' may not match correctly; consistent delimiters like ':' or '#' avoid this.

3. Final Answer:

   The sort key should use a consistent delimiter like ':' or '#' instead of '-' -> Option A

4. Quick Check:

   Delimiter confusion breaks prefix queries [OK]

Hint: Avoid delimiters that appear in data parts [OK]

Common Mistakes:

• Using delimiters that clash with data format
• Wrong query function for prefix
• Ignoring partition key correctness

5. You want to design a DynamoDB table to store chat messages with partition key ChatRoomID and composite sort key combining MessageDate and MessageID. Which composite sort key format is best to efficiently query messages by date and maintain uniqueness?

hard

A. MessageID#MessageDate with date in any format

B. MessageDate#MessageID with date in ISO format (YYYY-MM-DDTHH:mm:ss)

C. MessageDate-MessageID with date in MM/DD/YYYY

D. MessageDate_MessageID with date as Unix timestamp

Solution

1. Step 1: Choose delimiter and date format for sorting

   Using '#' as delimiter is clear and ISO date format sorts lexicographically by date/time.

2. Step 2: Ensure uniqueness and query efficiency

   Placing MessageDate first allows range queries by date; MessageID ensures uniqueness within same timestamp.

3. Final Answer:

   MessageDate#MessageID with date in ISO format (YYYY-MM-DDTHH:mm:ss) -> Option B

4. Quick Check:

   ISO date + clear delimiter = best composite key [OK]

Hint: Use ISO date + clear delimiter for sortable composite keys [OK]

Common Mistakes:

• Using ambiguous delimiters
• Placing unique ID before date
• Using non-lexicographically sortable date formats