AWS DynamoDB Single-Table Data Modeling Review Prompt
Review a DynamoDB schema against its real access patterns — partition/sort key choices, GSIs, item collections, and single-table design — before it ships and becomes expensive to change.
- Target user
- Backend, platform, and serverless engineers designing or refactoring DynamoDB tables
- Difficulty
- Advanced
- Tools
- Claude, ChatGPT, Cursor
The prompt
You are a senior cloud engineer who designs DynamoDB schemas from access patterns, not from entity diagrams. You know that in DynamoDB the queries come first and the table shape is derived from them, that a hot partition or a missing GSI is far cheaper to fix on paper than in production, and that single-table design is a means, not a goal. I will provide: - The list of access patterns the application must serve (read and write), with expected frequency: [ACCESS_PATTERNS] - The current or proposed table design (PK/SK attributes, GSIs, LSIs, attribute names): [TABLE_DESIGN] - Entity relationships and cardinality (one-to-many, many-to-many): [ENTITY_MODEL] - Scale and constraints (item sizes, RPS, read/write ratio, hot keys, on-demand vs. provisioned): [SCALE_CONSTRAINTS] Do the following, numbered: 1. Enumerate every access pattern as a concrete query: for each, state which key (base table or a named index) serves it, the exact KeyConditionExpression shape, and whether it is a GetItem, Query, or (a red flag) a Scan. Any pattern that can only be served by a Scan or by client-side filtering is a design gap — name it explicitly. 2. Audit the partition key for cardinality and heat: confirm the PK has enough distinct values to spread load, and flag any key (a status field, a tenant with a dominant customer, a date) that will concentrate traffic on one partition and risk throttling. Suggest write-sharding or a composite key where heat is real. 3. Audit the sort key design: verify that the SK enables the range, begins_with, and sorted queries the access patterns need, and that composite SK values (e.g. `ORG#123#USER#456`) are ordered so the prefixes the application queries on actually sort together. 4. Review item collections and single-table layout: confirm related entities that are fetched together share a partition key so one Query returns the whole collection, and flag entities crammed into one table that are never queried together and would be cleaner separated. 5. Validate every GSI against a real access pattern: each GSI must serve at least one pattern that the base table cannot. Flag unused or speculative GSIs (they cost writes and storage), check projection type (KEYS_ONLY / INCLUDE / ALL) against what each query actually reads, and watch for GSIs that inherit a hot partition key. 6. Check GSI write amplification and consistency: remember GSIs are eventually consistent and each base-table write fans out to every matching index. Estimate the write multiplier and flag designs where a single logical write triggers many index updates. 7. Review LSI usage if present: confirm LSIs are actually needed (strong consistency on an alternate sort key), and flag the 10 GB-per-partition item-collection limit and the fact that LSIs must be created at table creation time. 8. Assess large items and overflow: flag items approaching the 400 KB limit, recommend offloading large blobs to S3 with a pointer, and identify attributes that should be split or compressed. 9. Plan capacity and cost: recommend on-demand vs. provisioned based on traffic shape, estimate RCU/WCU for the hottest patterns, and note where adaptive capacity will and will not save a genuinely skewed key. 10. Recommend a safe migration path if the design is changing: DynamoDB has no ALTER for keys, so adding a new key or restructuring requires a new table or new GSI plus a backfill — outline the dual-write or backfill strategy and how to validate parity before cutover. Output as: (a) an access-pattern-to-query mapping table marking each as GetItem/Query/Scan and the index used, (b) a list of design risks ranked by severity (hot partition, Scan-only pattern, unused GSI, oversized item), (c) concrete schema changes with the reasoning, and (d) a migration/rollout plan if keys change. Recommend the model state its assumptions about traffic distribution rather than guessing. Apply least-privilege to any IAM policy you suggest for the table or its streams, scoping actions and resource ARNs to exactly the table and indexes in use, and present every schema or capacity change as a reviewed proposal to apply against a non-production table first, never an automatic mutation of a live table.
Why this prompt works
DynamoDB punishes the instinct most engineers bring from relational databases: model the entities first and figure out the queries later. In DynamoDB the access patterns are the design. This prompt forces that discipline by requiring every pattern to be written out as a concrete query and mapped to a specific key or index, with any Scan or client-side filter called out as a defect. That single step surfaces the most common production failure — a perfectly reasonable-looking table that simply cannot answer a question the application needs to ask cheaply.
The review then targets the two failure modes that actually page people at 3 a.m.: hot partitions and runaway GSIs. A partition key with low cardinality or a dominant tenant concentrates traffic until one partition throttles while the table sits far under its provisioned ceiling, and adaptive capacity only partly rescues a genuinely skewed key. Meanwhile every GSI fans out writes and adds eventually-consistent surprises, so the prompt insists each index justify itself against a real pattern and flags the speculative ones that quietly inflate the bill. Naming these explicitly steers the model to the expensive mistakes rather than cosmetic ones.
Finally, the prompt respects the property that makes DynamoDB modeling high-stakes: the key schema is immutable. You cannot ALTER a partition or sort key — restructuring means a new table or index plus a backfill and a careful cutover. By requiring a migration plan when keys change, by scoping any suggested IAM policy to least privilege, and by treating schema and capacity changes as reviewed proposals tested against non-production first, the prompt keeps a human in control of decisions that are genuinely hard to reverse once data is live.