DynamoDB Modeling skill for Claude Code

A DynamoDB design is done when every access pattern in the inventory maps to exactly one GetItem, Query, or transaction against a named key or index, no pattern needs a Scan or a client-side join, and no single partition key value receives more than its share of traffic. Model from queries backwards; the entity diagram is an input, not the design.

Workflow

- [ ] 1. Write the access-pattern inventory (with rates and consistency needs)
- [ ] 2. Decide single-table vs one-table-per-service-entity
- [ ] 3. Design base-table PK/SK and item collections
- [ ] 4. Add GSIs only for patterns the base table cannot serve
- [ ] 5. Define write paths: conditions, transactions, idempotency
- [ ] 6. Check hot partitions, item size, and fan-out
- [ ] 7. Prove it: run every pattern against DynamoDB Local or a dev table

1. Access-pattern inventory

Do not write a key until this table exists. Ask the user for missing rates; guess order-of-magnitude rather than skip the column.

#	Pattern	Type	Key inputs	Sort/filter	Rate (peak/s)	Consistency
1	Get user by id	read	userId	-	800	eventual
2	List a user’s orders, newest first	read	userId	createdAt desc, page 20	300	eventual
3	Get order with its line items	read	orderId	-	200	strong
4	List open orders for a warehouse	read	warehouseId, status=OPEN	createdAt	20	eventual
5	Place order (order + items + stock decrement)	write	-	-	50	atomic

Use the worksheet in references/access-pattern-worksheet.md when the domain has more than about eight patterns or several teams own entities.

2. Single-table or not

Situation	Choice
Patterns fetch several entity types together (order + items, user + settings)	Single table; co-locate them in one item collection
Entities are independent, accessed separately, owned by different teams	One table per entity; simpler IAM, metrics, backups
Need per-entity capacity mode, PITR, or stream consumers	Separate tables
Ad-hoc analytics or unknown future queries dominate	Not DynamoDB; use Postgres or export to S3/Athena

Default: single table per service when entities are read together; otherwise do not force it. Single-table is a tool for avoiding joins, not a goal.

3. Base-table keys

• Use generic attribute names PK and SK with typed prefixes so one table holds many entity types: USER#123, ORDER#2026-10-19T08:15:00Z#9f2c.
• The partition key picks the item collection; the sort key orders and ranges within it. Query supports =, <, <=, >, >=, BETWEEN, and begins_with on the sort key only.
• Put the time component in the SK as an ISO-8601 string or zero-padded number so lexical order equals chronological order. Use ScanIndexForward=false for newest-first.
• Store an entity’s own attributes plainly (userId, email) as well as in keys; keys are for access, attributes are for the application. Never parse keys in application logic.
• Add an entityType attribute to every item for filtering, exports, and migrations.

Example layout for the inventory above:

Entity	PK	SK	Notes
User	USER#<userId>	PROFILE	pattern 1 via GetItem
Order (user view)	USER#<userId>	ORDER#<createdAt>#<orderId>	pattern 2 via Query, begins_with ORDER#
Order (canonical)	ORDER#<orderId>	META	pattern 3
Line item	ORDER#<orderId>	ITEM#<lineNo>	pattern 3: one Query returns META + items
Stock	SKU#<sku>	STOCK	decremented in pattern 5 transaction

Duplicating the order summary under the user is intentional denormalization. Keep the duplicate small (summary fields only) and update both in one transaction or via Streams.

4. GSIs

• Add a GSI only for a pattern the base table cannot answer. Each GSI duplicates every projected item write and costs write capacity on every change to its key attributes.
• GSI reads are eventually consistent only. If a pattern needs strong consistency, it must use the base table.
• Project KEYS_ONLY or INCLUDE the fields the pattern renders; ALL doubles storage and write cost for wide items.
• Sparse index: an item appears in a GSI only if it has the GSI key attributes. Set GSI1PK = WAREHOUSE#<id>#OPEN only while an order is open and REMOVE it on close; the index then holds just the open set (pattern 4).
• Overloaded GSI (GSI1PK/GSI1SK reused by several entity types) keeps the index count low; default limit is 20 GSIs per table.
• Since November 2025, GSIs accept multi-attribute keys (up to four attributes each for the partition and sort key), which removes the need for concatenated synthetic keys such as country#state#city on new indexes. Sort-key attributes are matched left to right, like a composite index. Prefer this for new GSIs when the SDK version in use supports it; keep synthetic keys on the base table, where the PK/SK are still single attributes.
• LSIs must be created with the table, share the partition’s 10 GB item-collection limit, and allow strong reads. Use only when you need an alternate sort order with strong consistency and the collection will stay small.

5. Write paths

Uniqueness and invariants come from condition expressions, not from reads before writes.

# boto3 - create user only if email is unclaimed, atomically
client.transact_write_items(TransactItems=[
    {"Put": {"TableName": T, "Item": {"PK": {"S": f"USER#{uid}"}, "SK": {"S": "PROFILE"}, "email": {"S": email}},
             "ConditionExpression": "attribute_not_exists(PK)"}},
    {"Put": {"TableName": T, "Item": {"PK": {"S": f"EMAIL#{email.lower()}"}, "SK": {"S": "EMAIL"}, "userId": {"S": uid}},
             "ConditionExpression": "attribute_not_exists(PK)"}},
], ClientRequestToken=request_id)   # idempotent for 10 minutes

# Optimistic locking on a version attribute
table.update_item(
    Key={"PK": f"ORDER#{oid}", "SK": "META"},
    UpdateExpression="SET #s = :new, version = version + :one",
    ConditionExpression="version = :expected",
    ExpressionAttributeNames={"#s": "status"},
    ExpressionAttributeValues={":new": "SHIPPED", ":one": 1, ":expected": v},
)

Need	Tool
Atomic counter	UpdateItem with ADD (not idempotent on retry; pair with a request-id guard item if double counts matter)
All-or-nothing across items/tables	TransactWriteItems (up to 100 actions, 4 MB, same Region; costs 2x WCU)
Bulk load, independent items	BatchWriteItem (25 items per call; retry UnprocessedItems with backoff)
Uniqueness on a non-key attribute	Guard item keyed on that value, written in the same transaction
React to changes (projections, search sync, audit)	DynamoDB Streams to Lambda; consumers must be idempotent
Expire sessions, idempotency keys	TTL attribute (epoch seconds); deletion is lazy, typically within days - filter expired items in reads

Handle ConditionalCheckFailedException as a domain outcome (409, “already exists”), and TransactionCanceledException by reading CancellationReasons to see which item failed.

6. Hot partitions, size, fan-out

• A single partition serves about 3,000 RCU and 1,000 WCU per second. Adaptive capacity rebalances, but one hot key value still caps out. Watch CloudWatch Contributor Insights for the most-accessed keys.
• Write sharding for high-write keys (a global counter, a popular tenant’s event feed): append a suffix #0..N-1 chosen by hash or random, and query all N shards in parallel on read. Pick N from peak write rate / 1,000 with headroom.
• Items are capped at 400 KB including attribute names. Move blobs to S3 and store the key. Long attribute names cost on every item; keep them short but readable.
• Query returns at most 1 MB per page before filters apply. FilterExpression reduces bytes returned, not capacity consumed - if a filter discards most items, the key design is wrong.
• Unbounded item collections (all events for a tenant forever) grow hot and slow; bucket by time in the PK (TENANT#42#2026-10) when a collection could exceed a few GB or sustained high write rates.

Capacity mode

Default to on-demand for new or spiky workloads; on-demand prices dropped substantially in late 2024 and it can be capped with maximum throughput settings. Switch to provisioned with auto scaling only when traffic is steady and the cost model shows savings.

Gotchas

• Scan in a request path is a design bug. Accept it only for offline jobs, with Segment/TotalSegments parallelism and a rate limit.
• Reserved words (status, name, date, count) must go through ExpressionAttributeNames.
• Numbers are sent as strings in the low-level API and come back as Decimal in boto3’s resource layer; JSON-encoding them needs a custom encoder.
• Changing a key schema means a new table or new GSI plus backfill. GSI backfill on a large table takes hours and consumes write capacity; plan it as a migration.
• Pagination: always loop on LastEvaluatedKey; never assume one call returns everything. Expose an opaque cursor (base64 of the key), not page numbers.
• Strongly consistent reads cost 2x and are unavailable on GSIs and global table replicas in other Regions.
• Global tables are last-writer-wins by default for concurrent writes in different Regions; design writes to be Region-affine if conflicts matter.

Verification

1. Run DynamoDB Local (docker run -p 8000:8000 amazon/dynamodb-local) or a dev table.
2. Load a seed fixture covering each entity type, including an edge case per pattern (empty collection, max page, duplicate email).
3. Execute every inventory row as code and assert it uses only GetItem/Query/transaction calls - grep the data layer for scan( and fail review on hits.
4. Check ConsumedCapacity (ReturnConsumedCapacity="TOTAL") per pattern and multiply by peak rate to estimate cost and per-key load.
5. If a pattern needs a filter discarding over half the read items, revisit step 3 or 4.

Output: design note

## Table: orders-service (on-demand, PITR on, TTL attr `expiresAt`)
| # | Pattern | Operation | Key condition | Index |
|---|---|---|---|---|
| 2 | User's orders newest first | Query | PK=USER#id AND begins_with(SK,'ORDER#'), ScanIndexForward=false | base |
| 4 | Open orders per warehouse | Query | GSI1PK=WAREHOUSE#id#OPEN | GSI1 (sparse, INCLUDE total,status) |
**Write invariants:** email uniqueness via EMAIL# guard item; order + stock in one transaction.
**Hot-key risk:** none above 200 WCU/key at peak; counters sharded x8.
**Deferred:** full-text search on products -> Streams to OpenSearch.