AWS DynamoDB Keys and Access Patterns

In a relational database you design tables around the data and work out queries later; the planner and indexes cope. DynamoDB flips that. It’s fast and predictable because almost every efficient read is a direct lookup on keys you chose in advance. If a query you need later doesn’t fit those keys, the fallback is a Scan over the whole table, which gets slower and more expensive as the table grows. So the design starts with a list of questions the application will ask.

How the keys actually work

Every item has a partition key, and optionally a sort key. Together they form the primary key and must be unique.

DynamoDB hashes the partition key to decide which physical partition stores the item. All items with the same partition key (an “item collection”) live together, ordered by sort key. That gives you exactly two efficient reads:

GetItem: one item, by its full primary key.
Query: items from one partition key value, optionally narrowed by a condition on the sort key (=, <, <=, >, >=, BETWEEN, begins_with), returned in sort-key order, forwards or backwards.

What you can’t do efficiently: query across partition key values, filter on a non-key attribute before reading (a FilterExpression is applied after items are read, so you pay for everything it throws away), or sort by anything other than the sort key. Those constraints are the design brief.

Start with the access pattern list

For a small shop, the application needs these reads. Write them down with a rough frequency, because frequency decides which ones get the best keys:

#	Access pattern	Frequency
A	Show a customer’s most recent orders	every account page view
B	Show a customer’s orders in a date range	occasional
C	Show one order with its line items	every order page view
D	Show the warehouse all orders with status `PENDING`	every few seconds
E	Get a customer’s profile	every page view

Now pick keys that make each one a GetItem or a Query.

A, B and E are all “things belonging to one customer”, so the customer is the partition key: PK = CUST#c42. The profile gets SK = PROFILE. Each order summary gets SK = ORDER#<date>#<orderId>. Because ISO dates sort lexically in time order, the sort key does the ordering for you: “most recent” is a reverse query, and “a date range” is BETWEEN.

C is “everything about one order”, so order items go under PK = ORDER#o1033, with SK = META for the header and SK = LINE#01, LINE#02 for the lines. One Query returns all of them.

Storing different entity types in one table this way is what people call single-table design. It isn’t mandatory. Its benefit is that related items sit in one partition and come back in one request, which matters when you’d otherwise make several round trips per page.

A model you can run

The Python below mimics the two rules that matter: items grouped by partition key, sorted by sort key, and a query that names exactly one partition. It isn’t DynamoDB, but the key strings and query shapes are the ones you’d use.

# Model of a DynamoDB table: items grouped by partition key, sorted by sort key.
# query() mirrors the rule that you must name one partition and may filter the sort key.
from collections import defaultdict

table = defaultdict(dict)
def put(item):
    table[item["PK"]][item["SK"]] = item

def query(pk, sk_begins=None, sk_between=None, newest_first=False):
    keys = sorted(table.get(pk, {}), reverse=newest_first)
    if sk_begins:
        keys = [k for k in keys if k.startswith(sk_begins)]
    if sk_between:
        lo, hi = sk_between
        keys = [k for k in keys if lo <= k <= hi]
    return [table[pk][k] for k in keys]

put({"PK": "CUST#c42", "SK": "PROFILE", "name": "Ana Ruiz"})
for oid, day, total in [("o1001", "2026-08-03", 59), ("o1017", "2026-09-14", 120), ("o1033", "2026-10-01", 18)]:
    put({"PK": "CUST#c42", "SK": f"ORDER#{day}#{oid}", "total": total})
    put({"PK": f"ORDER#{oid}", "SK": "META", "customer": "c42", "status": "SHIPPED" if day < "2026-10" else "PENDING"})
put({"PK": "ORDER#o1033", "SK": "LINE#01", "sku": "KB-104", "qty": 1})
put({"PK": "ORDER#o1033", "SK": "LINE#02", "sku": "MS-220", "qty": 2})

print("A. latest 2 orders for c42:",
      [i["SK"] for i in query("CUST#c42", sk_begins="ORDER#", newest_first=True)[:2]])
print("B. c42 orders in September:",
      [i["SK"] for i in query("CUST#c42", sk_between=("ORDER#2026-09-01", "ORDER#2026-09-30~"))])
print("C. order o1033 with its lines:", [i["SK"] for i in query("ORDER#o1033")])

python

Output from python3 keys.py:

A. latest 2 orders for c42: ['ORDER#2026-10-01#o1033', 'ORDER#2026-09-14#o1017']
B. c42 orders in September: ['ORDER#2026-09-14#o1017']
C. order o1033 with its lines: ['LINE#01', 'LINE#02', 'META']

Text

Two details are worth noticing. In B, the upper bound ends in ~ because sort keys compare as strings and ORDER#2026-09-30#o... is greater than ORDER#2026-09-30; ~ sorts after digits, letters and # in ASCII, so it catches every order on the 30th. And in A, the begins_with("ORDER#") condition keeps the PROFILE item out of the results; in real DynamoDB that’s a key condition, so the profile isn’t even read.

The real calls for A would be a Query with KeyConditionExpression: "PK = :pk AND begins_with(SK, :o)", ScanIndexForward: false and Limit: 2.

Pattern D needs a second set of keys

“All pending orders” crosses customers, so no Query on the base table answers it. This is what a global secondary index is for: DynamoDB maintains a copy of selected items keyed differently. Give the order META items attributes GSI1PK = STATUS#PENDING and GSI1SK = 2026-10-01T09:14:00Z#o1033, and a Query on the index with GSI1PK = STATUS#PENDING returns pending orders oldest first.

Things interviewers expect you to know about GSIs:

They’re updated asynchronously, so reads from a GSI are eventually consistent only. An order created a moment ago might not appear yet.
Only items that have the index’s key attributes appear in it (a “sparse” index). When an order ships, remove GSI1PK or change it, and it drops out of the pending list. That’s a cheap way to index “only the interesting items”.
They have their own throughput. A GSI that can’t keep up with writes can throttle writes to the base table.
Local secondary indexes share the base table’s partition key with a different sort key, must be created with the table, and limit each item collection to 10 GB. GSIs are the usual choice.

Hot partitions and the status key trap

Pattern D’s key has a problem. Every pending order shares one partition key value, STATUS#PENDING. A single partition supports up to 3,000 read units and 1,000 write units per second. During a sale, thousands of order writes per second all land on one GSI partition, and that partition throttles, which back-pressures writes to the table. Adaptive capacity helps with uneven load but can’t make one key exceed a partition’s limits.

The fix is write sharding: spread the key across a fixed number of values, such as STATUS#PENDING#0 to STATUS#PENDING#9, picking the shard from a hash of the order ID. The warehouse reads by running ten small queries in parallel and merging. It’s more work on read, but reads here are cheap and the write load is now spread across ten partitions.

The general rule: good partition keys have many distinct values with fairly even traffic. User IDs, order IDs and device IDs are good. Dates, status values, country codes and booleans are usually bad on their own.

Interview practice: adding a query the keys didn’t plan for

Six months after launch, product wants “show all orders containing SKU KB-104 in the last 30 days” for a recall. The table uses the design above. A developer proposes a Scan with a FilterExpression on sku. What do you say?

Answer

A Scan reads every item in the table and only then applies the filter, so you pay read capacity for the whole table and it gets slower as data grows. For a one-off recall report it might be acceptable if run off-peak with a parallel scan and a rate limit, or better, against an export to S3 queried with Athena so production capacity isn’t touched.

If it’s going to be a regular feature, it’s a new access pattern and deserves keys. The line items already carry sku, so I’d copy the order date onto each line item and add a GSI with GSI2PK = SKU#KB-104 and GSI2SK = <order date>#<orderId>, backfilling existing line items by updating them with the new attributes. Then the query is a Query on one SKU with a date range on the sort key. I’d check whether one popular SKU could become a hot key, and shard it if so. The broader point I’d make is that DynamoDB designs evolve by adding access patterns deliberately, usually via new indexes, rather than by falling back to scans.

Keep going

AWS interview questions
AWS MCQs
AWS DynamoDB query vs scan
AWS DynamoDB conditional writes and concurrency
NoSQL design for DynamoDB (AWS docs)