Building DynamoDB Query Patterns: An AWS CLI Tutorial
.jpeg)
Learn to design and test query patterns in DynamoDB — including composite keys, prefixes, and filters — all from your terminal. This tutorial will help you master how to read efficiently without scanning your entire table.
Step 1: Create a Table with a Composite Sort Key
In this step, you’ll build a table that supports multiple access patterns using a composite sort key.
This design uses UserID (partition key) and ActivityKey (sort key) to form a composite key, allowing you to query by user and filter on the action and timestamp.
aws dynamodb create-table \
--table-name ActivityTable \
--attribute-definitions \
AttributeName=UserID,AttributeType=S \
AttributeName=ActivityKey,AttributeType=S \
--key-schema \
AttributeName=UserID,KeyType=HASH \
AttributeName=ActivityKey,KeyType=RANGE \
--billing-mode PAY_PER_REQUEST
Output:
{
"TableDescription": {
"TableName": "ActivityTable",
"TableStatus": "CREATING",
"BillingModeSummary": { "BillingMode": "PAY_PER_REQUEST" },
"ItemCount": 0
}
}
Check status until it’s active:
aws dynamodb describe-table --table-name ActivityTable --query "Table.TableStatus"
Output:
"ACTIVE"
This table uses UserID as the partition key and a compound sort key, ActivityKey, which holds the action type and timestamp (for example, LOGIN#2025-10-16, PURCHASE#2025-10-16).
Step 2: Insert Sample Items with Key Prefixes
You’ll now insert activities for two users — each activity type has a prefix (LOGIN#, PURCHASE#) for grouping.
aws dynamodb put-item \
--table-name ActivityTable \
--item '{"UserID": {"S": "USER#1001"}, "ActivityKey": {"S": "LOGIN#2025-10-16T09:00"}, "Device": {"S": "Chrome"}}'
aws dynamodb put-item \
--table-name ActivityTable \
--item '{"UserID": {"S": "USER#1001"}, "ActivityKey": {"S": "PURCHASE#2025-10-16T09:15"}, "Amount": {"N": "39.95"}}'
aws dynamodb put-item \
--table-name ActivityTable \
--item '{"UserID": {"S": "USER#1002"}, "ActivityKey": {"S": "LOGIN#2025-10-16T10:00"}, "Device": {"S": "Firefox"}}'
Output:
{}
Each successful insert returns {} — indicating success.
Step 3: Query All Activity for a Single User
Retrieve every record for one user.
aws dynamodb query \
--table-name ActivityTable \
--key-condition-expression "UserID = :u" \
--expression-attribute-values '{":u":{"S":"USER#1001"}}'
Output:
{
"Items": [
{
"UserID": {"S": "USER#1001"},
"ActivityKey": {"S": "LOGIN#2025-10-16T09:00"},
"Device": {"S": "Chrome"}
},
{
"UserID": {"S": "USER#1001"},
"ActivityKey": {"S": "PURCHASE#2025-10-16T09:15"},
"Amount": {"N": "39.95"}
}
],
"Count": 2
}
You now see both the login and purchase activities for one user — all under a single partition.
Step 4: Use begins_with() to Filter by Activity Type
Now query only login events for a user using the begins_with() function.
aws dynamodb query \
--table-name ActivityTable \
--key-condition-expression "UserID = :u AND begins_with(ActivityKey, :a)" \
--expression-attribute-values '{":u":{"S":"USER#1001"}, ":a":{"S":"LOGIN#"}}'
Output:
{
"Items": [
{
"UserID": {"S": "USER#1001"},
"ActivityKey": {"S": "LOGIN#2025-10-16T09:00"},
"Device": {"S": "Chrome"}
}
],
"Count": 1
}
The prefix pattern LOGIN# neatly filters results within the user’s partition key.
Step 5: Query by Range Using between()
Retrieve all activity within a specific time window.
aws dynamodb query \
--table-name ActivityTable \
--key-condition-expression "UserID = :u AND ActivityKey BETWEEN :start AND :end" \
--expression-attribute-values '{":u":{"S":"USER#1001"}, ":start":{"S":"LOGIN#2025-10-16T08:00"}, ":end":{"S":"PURCHASE#2025-10-16T10:00"}}'
Output:
{
"Items": [
{
"UserID": {"S": "USER#1001"},
"ActivityKey": {"S": "LOGIN#2025-10-16T09:00"},
"Device": {"S": "Chrome"}
},
{
"UserID": {"S": "USER#1001"},
"ActivityKey": {"S": "PURCHASE#2025-10-16T09:15"},
"Amount": {"N": "39.95"}
}
],
"Count": 2
}
BETWEEN can compare sort keys lexicographically — making time or category-based filtering easy.
Note that DynamoDB compares sort keys lexicographically (by string order). Because the ActivityKey values share a consistent string format, the BETWEEN expression correctly retrieves all activities in the intended time window.
Step 6: Clean Up
Delete the table to avoid costs.
aws dynamodb delete-table --table-name ActivityTable
Output:
{
"TableDescription": {
"TableName": "ActivityTable",
"TableStatus": "DELETING"
}
}
The DELETING status confirms that DynamoDB has accepted the deletion request and is removing the table in the background.
Option 1 — Verify via list-tables
Wait 15–60 seconds and check:
aws dynamodb list-tables
Output after deletion:
{
"TableNames": []
}
If the list is empty (or the table name is missing), the table has been fully deleted.
Option 2 — Verify via describe-table
For explicit confirmation, try describing the same table again:
aws dynamodb describe-table --table-name ActivityTable
Output:
An error occurred (ResourceNotFoundException) when calling the DescribeTable operation: Cannot do operations on a non-existent table
This ResourceNotFoundException confirms the table is completely deleted.
DynamoDB does not return a “DELETED” status — once it’s gone, it’s simply absent.
Wrap-Up
You’ve built a real DynamoDB table with a composite sort key and practiced prefix and range queries.
These key design patterns — prefix-based keys and range filtering — are what make DynamoDB powerful for real-time data models.
Plan your access paths early, group data logically, and you’ll avoid costly scans down the road.
Pro Tip #1 — Use ISO 8601 Timestamps
Always format timestamps as ISO 8601 strings (for example, 2025-10-16T09:00:00Z).
This ensures they sort correctly in lexicographical order and keeps your queries predictable over time ranges.
Pro Tip #2 — Use Consistent Key Prefixes
Prefix your sort keys (e.g., LOGIN#, PURCHASE#, COMMENT#) consistently across your data model.
It makes debugging easier, filters cleaner, and enables begins_with() queries that scale elegantly as your dataset grows.
Aaron Rose is a software engineer and technology writer at tech-reader.blog and the author of Think Like a Genius.
Comments
Post a Comment