DynamoDB Single-Table Design: Access-Pattern-First Data Modeling

The first time you model Amazon DynamoDB like the relational database you already know, it works — for about a sprint. You create a Users table, an Orders table, an OrderItems table, one per entity, exactly as you would in PostgreSQL. Then the product manager asks for “all pending orders for a customer, newest first,” and you discover DynamoDB has no JOIN, no GROUP BY, and that the only way to answer your new question is a Scan that reads every item in the table and throws most of them away. The bill climbs, the p99 latency grows a shelf, and someone says the words every architect dreads: “maybe DynamoDB was the wrong choice.”

DynamoDB was not the wrong choice. The relational modeling method was. In a SQL database you normalize first — split data into tidy, non-redundant tables — and figure out the queries later, because the query planner will join whatever you ask for at read time. DynamoDB has no query planner and no joins; it trades that flexibility for guaranteed single-digit-millisecond latency at any scale. So you invert the process: you list the access patterns first, then design the schema to serve them — often collapsing every entity into one table whose partition and sort keys are generic, overloaded strings. This is single-table design, and it is the single highest-leverage skill in DynamoDB.

This article teaches that method end to end. You will learn the mental-model swap, then every technique that makes one table serve a dozen access patterns: overloaded keys, item collections, composite sort keys for hierarchies, GSI overloading, inverted and sparse indexes, the adjacency-list pattern for graphs, write sharding to defuse a hot partition, denormalization and stream-maintained aggregations, and transactions. You will model a complete e-commerce/SaaS workload into one table plus two GSIs — with the exact item shapes and every query — build it in a copy-pasteable aws CLI + Terraform lab, and, just as importantly, learn the cases where single-table design is the wrong answer and a boring multi-table or analytics design wins.

What problem this solves

The pain is not that DynamoDB is slow — it is that DynamoDB is fast only along the axes you designed for, and unforgiving everywhere else. Model it wrong and every symptom below shows up in production, usually under load, usually on a launch day.

Who hits this: every team that treats DynamoDB as “PostgreSQL without the ops.” The teams that succeed treat it as a purpose-built key-value/document store whose superpower — predictable performance at unlimited scale — is unlocked only by designing the keys around the questions the application actually asks. Get the keys right and DynamoDB never has a bad day; get them wrong and no amount of capacity will save you, because the ceiling is per partition, not per table.

Learning objectives

By the end of this article you will be able to:

• Invert your modeling reflex — produce an access-pattern list and an entity chart before you write a single CreateTable call, the way a senior DynamoDB modeler works.
• Overload keys — pack many entity types into generic PK/SK attributes and read them back unambiguously with key prefixes.
• Build item collections and composite sort keys to fetch parents-with-children and query hierarchies (ORG#a#DEPT#b) with begins_with.
• Design GSIs that pull their weight — GSI overloading (one index, many patterns), the inverted index for many-to-many, and sparse indexes for cheap filtered views.
• Model graphs and many-to-many with the adjacency-list pattern, and keep derived data consistent with DynamoDB Streams and TransactWriteItems.
• Defuse hot partitions with write sharding and scatter-gather reads, and know the per-partition throughput ceiling that forces it.
• Model a full e-commerce/SaaS workload into one table + two GSIs, and translate every access pattern into a concrete query call.
• Recognize when NOT to use single-table design and pick multi-table, OLAP, or Athena-on-S3 instead.

Prerequisites & where this fits

You should be comfortable with DynamoDB’s primitives — tables, primary keys, GetItem/Query/PutItem, and on-demand vs provisioned capacity. If those are fuzzy, read DynamoDB Tables, Keys and Capacity Hands-On first; this article assumes them and builds the modeling layer on top. Where a design choice interacts with throttling, DynamoDB Throttling and Hot-Partition Troubleshooting goes deeper on the operational side.

Where it fits in the bigger picture: single-table design is the data tier decision underneath a serverless web application and most event-driven architectures on EventBridge, SQS and Lambda. It is a DVA-C02 (Developer Associate) core competency and appears on SAA-C03 wherever the exam asks you to choose or defend a NoSQL data model. It is not a replacement for understanding when a relational engine is the right tool — a boundary drawn in RDS, DynamoDB and Aurora Compared.

Core concepts

Before the techniques, pin down the vocabulary. Every single-table trick is just a creative use of these primitives.

The primary key and the partition

Every item has a primary key. It is either a simple key (partition key only) or a composite key (partition key + sort key). The partition key (PK) — also called the hash key — is run through an internal hash function to pick which physical partition stores the item. The sort key (SK) — the range key — orders items within a partition. That ordering is the mechanism behind nearly everything: ranges, hierarchies, “newest first,” parent-plus-children.

The three ways to read, and why Scan is the enemy

The whole game is arranging your data so that every user-facing question is a GetItem or a Query. A FilterExpression is not a rescue: it runs after the read and after the bill, so filtering a 10-million-item table down to 3 results still reads (and charges for) 10 million items. If you ever need Scan + filter for a real feature, that is the signal you are missing an access pattern — add an index, not a filter.

Global vs local secondary indexes

An index is a second copy of your items, automatically maintained by DynamoDB, keyed differently so you can query along another axis. Two kinds, and the differences decide your model.

The line that trips people up: the 10 GB per-item-collection limit only exists if the table has an LSI. Without any LSI, an item collection can grow past 10 GB freely. That is the biggest reason experienced modelers avoid LSIs and reach for GSIs — a GSI adds no such ceiling and can be added later, whereas an LSI is a permanent decision made before you have real data. Use an LSI only when you genuinely need strong consistency on the alternate sort axis.

Projection: what the index actually stores

A GSI that returns “customer name + order total for status = PENDING” should INCLUDE exactly name, total — not ALL. Over-projecting is the quiet cost leak of single-table design: every write to the base table is also a write to every GSI whose keys changed, and ALL means copying the whole item each time.

Access-pattern-first: the modeling method

Here is the method that separates a table that scales from one that fights you. It is deliberately mechanical.

The five steps

Steps 1 and 2 are the whole ballgame. If you cannot list your access patterns, you are not ready to model — and if a new pattern appears after launch that you did not list, you add a new index or item shape, you do not reshape the table.

The access-pattern list (fill this in first)

This is the artifact you produce before any CreateTable. A real one for the worked example later:

The entity chart (derive keys from the list)

Once the patterns are fixed, you draw the entity chart — every entity type, its PK/SK template, and its GSI keys. This is the single most valuable document in a DynamoDB project; keep it in the repo and update it with every new pattern.

Notice what just happened: five entity types, one table, generic PK/SK strings whose values carry the type via a prefix. That prefixing is the next technique.

Primary key patterns: overloading, collections and hierarchies

Key overloading

Key overloading means the PK and SK attributes are generic (literally named PK and SK), and different entity types live in the same table distinguished by the prefix of the value. CUST#123 is a customer partition; ORDER#o789 is an order partition; PROD#sku-9 is a product. Because you always Query by exact PK, there is never ambiguity — you know the type from the key you asked for. The payoff: one table, one set of IAM policies, one backup, and the ability to fetch different entity types that share a partition in a single call.

Item collections: fetch the parent and children in one query

An item collection is every item sharing a partition key. Model an order’s header and its line items with the same PK — PK=ORDER#o789, SK=META for the header and SK=ITEM#<sku> for each line — and a single Query PK=ORDER#o789 returns the header plus every line item, ordered, in one round trip. No join, no second call, one charge. This is how you replicate a SQL “order with its items” without a join.

Because META sorts before ITEM# sorts before PAYMENT# sorts before SHIP#, a single Query with no SK condition returns them in a stable, useful order. Want just the lines? SK begins_with ITEM#. Want header + lines but not shipping? SK between META and ITEM$ (the $ byte sorts just after #). The sort key is your sub-query language.

Composite sort keys for hierarchies

A composite sort key packs a hierarchy into one string so you can query at any level with begins_with. Model an org chart as SK = ORG#123#DEPT#456#EMP#789 and one key serves the whole tree.

The rule: order the components most-significant first (org before dept before emp), because begins_with only anchors at the start. You cannot ask “all employees named Smith across all departments” from this key — that is a different access pattern needing a different index. That constraint is the whole discipline: hierarchies you listed are one Query; ones you did not are a redesign.

Vertical partitioning vs one fat item

There are two ways to store an entity with many attributes: one fat item (all attributes on a single META item) or vertical partitioning (split into several items in the same collection). The trade-off is real.

Split when parts have different lifecycles (an order’s mutable status vs its immutable ITEM# lines) or when the whole would blow past 400 KB. Keep it one item when everything is read and written together — fewer items, cheaper reads.

Secondary index patterns: overloading, inverted and sparse

Primary keys serve the patterns you can express as “by partition key.” Everything else rides a GSI. Three techniques make a small number of GSIs serve a large number of patterns — which matters because you get 20, and each one costs write throughput.

GSI overloading

Just as you overload the base PK/SK, you overload the GSI keys: name them GSI1PK/GSI1SK, and let different entity types write different values into them so one index answers many patterns. In the worked example, GSI1PK=STATUS#PENDING, GSI1SK=<ts> serves “orders by status” and “orders by status within a date range” — and if you also set GSI1PK=CATEGORY#books, GSI1SK=<price> on product items, the same GSI1 serves “products in a category by price.” One index, four patterns.

The cost: an item only appears in GSI1 if it has both GSI1PK and GSI1SK. That is not a bug — it is the sparse-index mechanism, below.

The inverted index

An inverted index is a GSI whose partition key is the base table’s sort key and whose sort key is the base partition key — you swap them. It lets you traverse a relationship from the other direction. If your base items are edges PK=USER#u1, SK=GROUP#g9 (u1 is in g9), the base table answers “which groups is u1 in?” (Query PK=USER#u1). Invert it — GSI PK=SK, GSI SK=PK — and the same items answer “which users are in g9?” (Query GSI PK=GROUP#g9). One set of items, both directions of a many-to-many.

Sparse indexes

A GSI only projects an item if that item has both of the GSI’s key attributes. Omit the attribute and the item simply is not in the index. A sparse index exploits this deliberately: write the index key attribute only on the subset you want to query, and the GSI becomes a tiny, cheap, pre-filtered view.

The canonical use: “list open orders.” Instead of a FilterExpression status <> FULFILLED that reads every order, add GSI2PK = "OPEN" to an order only while it is unfulfilled, and remove that attribute when it is fulfilled. GSI2 now contains only open orders — Query GSI2 PK=OPEN returns exactly them, reading only what it returns.

Sparse indexes are the antidote to the FilterExpression cost trap. Any time you catch yourself filtering a large result down to a small, well-defined subset, ask: can I make that subset a sparse index instead?

Advanced patterns: adjacency lists, many-to-many and graphs

Relational modelers reach for a join table for many-to-many. DynamoDB’s equivalent is the adjacency-list pattern, and with an inverted GSI it traverses graphs in both directions.

The adjacency-list pattern

Model both nodes and edges as items in the same table. A node item is PK=<node>, SK=<node> (or SK=META); an edge item is PK=<nodeA>, SK=<nodeB> carrying edge attributes. The base table answers “all edges out of A” (Query PK=A); the inverted GSI answers “all edges into B” (Query GSI1PK=B). This models users-in-groups, actors-in-films, parts-in-assemblies, followers, permissions — any many-to-many or graph.

Many-to-many decision table

The last row matters: DynamoDB does adjacency lists and one-or-two-hop traversals beautifully, but arbitrary deep-graph queries (“shortest path,” “friends-of-friends-of-friends”) belong in a graph database like Amazon Neptune, not in a pile of scatter-gather Query calls.

Aggregations, denormalization and streams

SQL computes COUNT, SUM and JOIN at read time. DynamoDB has none of those, so you pre-compute and denormalize at write time and keep the derived data consistent with either transactions or streams.

Denormalization strategies

Denormalization means storing the same fact in more than one place — the customer’s name on both the profile and every order. You accept that duplication in exchange for join-free reads. The risk is drift (the name changes), which you handle by choosing: snapshot values that should be frozen (the name at order time) or maintaining live copies via streams.

DynamoDB Streams: keep derived items in sync

DynamoDB Streams emit an ordered, 24-hour log of every item change; a Lambda consumes it and updates derived items — the classic way to maintain aggregations and fan-out copies without slowing the write path.

The pattern for a live orders-per-customer count: the stream Lambda sees an order INSERT, does UpdateItem PK=CUST#id, SK=PROFILE ADD orderCount 1. For revenue, NEW_AND_OLD_IMAGES lets it add the difference when an order total changes. Because streams are asynchronous, the count is eventually consistent — correct within a second, which is right for dashboards and wrong for “do not oversell the last unit” (that needs a transaction).

Transactions: multi-item, all-or-nothing

TransactWriteItems applies up to 100 items / 4 MB as a single all-or-nothing operation with optional per-item ConditionCheck — the tool for invariants that must hold across items now.

“Place an order” is the textbook transaction: create the order header, create each line item, and ConditionCheck + decrement product inventory (ConditionExpression stock >= qty) — all or nothing, so you never charge a customer for an out-of-stock item or leave a dangling order. Contrast with the orders-per-customer count, which is fine to maintain eventually via streams because a dashboard being one second stale harms no one.

Write sharding: defusing a hot partition

Throughput is delivered per partition — ~3,000 RCU and ~1,000 WCU each — so a single partition key that attracts disproportionate traffic throttles even when the table has spare capacity. Adaptive capacity isolates and boosts hot partitions automatically, but it cannot exceed the per-partition ceiling for a single key. When one key is genuinely hotter than one partition can serve, you shard the write across N synthetic partitions.

The technique

Append a shard suffix to the PK: PK=EVENT#e456#<0..N>. Writes hash across N partitions; reads scatter-gather — issue N Query calls (one per shard) in parallel and merge. You trade read complexity and cost for write headroom.

Sharding is a targeted tool, not a default — it complicates every read of that entity. Reach for it only when a specific key is provably hotter than one partition can serve; the throttling and hot-partition playbook covers how to confirm that with Contributor Insights before you reshape anything.

The worked example: an e-commerce/SaaS model end to end

Now assemble every technique into one model. The workload is a small commerce/SaaS backend; the access-pattern list and entity chart from earlier drive it. One base table, commerce-main, plus two GSIs.

The item shapes (the entity chart, concretely)

Note the PRICE#0000019.99 zero-padding: sort keys are strings, so numbers must be left-padded to sort correctly. Note too that the order is stored twice — once in the customer’s collection (CUST#c1) for “a customer’s orders,” and once as its own collection (ORDER#o9) for “order with items.” That duplication is deliberate denormalization, kept consistent with a transaction at write time.

Every access pattern → its exact query

Twelve access patterns, one table, two GSIs, zero Scans. GSI1 alone serves patterns 4, 5, 8 and 9 — that is GSI overloading paying rent. Pattern 6 rides the sparse GSI2. Pattern 11 shows the sparse mechanic live: setting the status to fulfilled removes GSI2PK, so the order silently leaves the “open orders” index. Pattern 12 reads a counter the stream keeps current.

Why two GSIs and not five

Each GSI you add is written on every base write whose indexed keys change, and counts against your 20-GSI limit and your bill. This model needs exactly two: GSI1 (overloaded — status, category, tier axes) and GSI2 (sparse — open orders). If a thirteenth pattern arrives — say “orders by shipping carrier” — you add a value to GSI1 (GSI1PK=CARRIER#fedex) or a new sparse GSI, without touching a single existing item. That is the elasticity single-table design buys you.

Architecture at a glance

The diagram traces the model left to right: the application’s access-pattern list on the left drives everything; each pattern resolves to a single Query/Get against the base table (overloaded PK/SK, item collections), or against GSI1 (overloaded and inverted — the by-status and reverse-lookup axis) or the sparse GSI2 (only open orders). On the right, DynamoDB Streams feed a Lambda that maintains denormalized rollups and counters, while TransactWriteItems guarantees multi-item invariants like “order created and inventory decremented.” The numbered badges mark the six load-bearing ideas — patterns-first, overloaded keys, item collections, GSI overloading/inversion, the sparse index, and stream-maintained aggregates.

Real-world scenario

LedgerLeaf, a fictional but very typical B2B SaaS invoicing startup, launched on DynamoDB with a comfortable relational instinct: five tables — Accounts, Users, Invoices, LineItems, Payments — one per entity, each keyed by its own id. It shipped fast and ran fine through beta at a few hundred accounts.

The trouble began with the “Invoices” dashboard. The product needed “all overdue invoices for an account, newest first, with the customer name and total.” In the five-table model that was: Scan the Invoices table with a FilterExpression on status = OVERDUE and accountId = X, then for each result GetItem the account for its name, then for each invoice Query the LineItems table for the total. At 40,000 invoices the dashboard took 9 seconds, the Scan read every invoice on every load, and the DynamoDB bill for that one screen crossed $700/month — almost all of it capacity burned reading rows the filter immediately discarded.

The team brought in a single-table redesign. First, the access-pattern list — eleven patterns, written down for the first time. Then one table, ledger-main, with overloaded keys: PK=ACCOUNT#a1, SK=INVOICE#<ts>#<id> put every invoice in its account’s item collection, so “an account’s invoices, newest first” became one Query with ScanIndexForward=false. The customer name was denormalized onto each invoice item (snapshotted at issue time — exactly the value the invoice should show). The overdue dashboard moved to a sparse GSI: an overdue partition key written onto an invoice only while it was past due and removed on payment, so Query GSI2 PK=OVERDUE#a1 returned precisely the overdue set, reading only what it displayed. Invoice totals were pre-aggregated onto the header and kept current by a streams Lambda as line items changed.

The results were the kind that end a debate. The dashboard dropped from 9 seconds to under 120 ms. The monthly cost for that access pattern fell from ~$700 to about $18 — because the sparse GSI reads dozens of items, not tens of thousands. No Scan remained in any user-facing path. The migration itself was the hard part: a one-time backfill job re-shaped 1.4 million legacy items into the new key structure, run through a throttled BatchWriteItem pipeline over a weekend, with the app dual-reading (old table, then new) behind a feature flag until the new model was verified. The lesson the team wrote in their runbook: list the access patterns before the first CreateTable, not after the first incident.

Advantages and disadvantages

When each matters: the advantages dominate for known, high-volume OLTP access patterns — the invoice dashboard, the order history, the user’s feed. The disadvantages dominate when patterns are unknown, ad-hoc, or analytical — which is the entire next section.

Hands-on lab

You will build the commerce-main table with GSI1, load the entity items, run each access pattern as a real query, then add a new pattern via a sparse GSI2 on a live table — with both aws CLI and Terraform. Everything here fits the DynamoDB always-free tier (25 GB, 25 RCU/WCU or on-demand equivalents at this volume ≈ $0).

⚠️ Costs: on-demand at a few dozen requests is fractions of a cent. Adding a GSI on a large table does consume write capacity to backfill — trivial here, not on a 10 M-item table. Always run teardown (Step 8).

Step 1 — Create the base table with GSI1 (aws CLI)

aws dynamodb create-table \
  --table-name commerce-main \
  --attribute-definitions \
      AttributeName=PK,AttributeType=S \
      AttributeName=SK,AttributeType=S \
      AttributeName=GSI1PK,AttributeType=S \
      AttributeName=GSI1SK,AttributeType=S \
  --key-schema \
      AttributeName=PK,KeyType=HASH \
      AttributeName=SK,KeyType=RANGE \
  --billing-mode PAY_PER_REQUEST \
  --global-secondary-indexes \
      '[{"IndexName":"GSI1",
         "KeySchema":[{"AttributeName":"GSI1PK","KeyType":"HASH"},
                      {"AttributeName":"GSI1SK","KeyType":"RANGE"}],
         "Projection":{"ProjectionType":"ALL"}}]' \
  --region us-east-1

Only key and index attributes are declared — every other attribute is schemaless. Wait for ACTIVE:

aws dynamodb wait table-exists --table-name commerce-main --region us-east-1

Step 2 — Load the entity items

# Customer profile
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"CUST#c1"},"SK":{"S":"PROFILE"},
  "entity":{"S":"Customer"},"name":{"S":"Asha Rao"},
  "email":{"S":"asha@example.com"},"orderCount":{"N":"0"},
  "GSI1PK":{"S":"TIER#gold"},"GSI1SK":{"S":"2026-01-02"}}'

# Order header in the customer's collection (also the sparse OPEN flag)
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"CUST#c1"},"SK":{"S":"ORDER#2026-07-14#o9"},
  "entity":{"S":"Order"},"total":{"N":"59.98"},"customerName":{"S":"Asha Rao"},
  "GSI1PK":{"S":"STATUS#PENDING"},"GSI1SK":{"S":"2026-07-14T09:30Z"},
  "GSI2PK":{"S":"OPEN"}}'

# Order self-collection: header + two line items
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"ORDER#o9"},"SK":{"S":"META"},"status":{"S":"PENDING"},"total":{"N":"59.98"}}'
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"ORDER#o9"},"SK":{"S":"ITEM#sku-101"},"qty":{"N":"2"},"unitPrice":{"N":"19.99"},"name":{"S":"Clean Code"}}'
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"ORDER#o9"},"SK":{"S":"ITEM#sku-205"},"qty":{"N":"1"},"unitPrice":{"N":"20.00"},"name":{"S":"DDIA"}}'

# Product with a category axis on GSI1
aws dynamodb put-item --table-name commerce-main --item '{
  "PK":{"S":"PROD#sku-101"},"SK":{"S":"META"},"title":{"S":"Clean Code"},
  "stock":{"N":"7"},"price":{"N":"19.99"},
  "GSI1PK":{"S":"CATEGORY#books"},"GSI1SK":{"S":"PRICE#0000019.99"}}'

Step 3 — Run access patterns 1–3 (base table)

# Pattern 1: customer profile
aws dynamodb get-item --table-name commerce-main \
  --key '{"PK":{"S":"CUST#c1"},"SK":{"S":"PROFILE"}}'

# Pattern 2: a customer's orders, newest first
aws dynamodb query --table-name commerce-main \
  --key-condition-expression "PK = :pk AND begins_with(SK, :o)" \
  --expression-attribute-values '{":pk":{"S":"CUST#c1"},":o":{"S":"ORDER#"}}' \
  --no-scan-index-forward

# Pattern 3: an order with all its line items (one query, the item collection)
aws dynamodb query --table-name commerce-main \
  --key-condition-expression "PK = :pk" \
  --expression-attribute-values '{":pk":{"S":"ORDER#o9"}}'

Expected: pattern 3 returns three items — META, ITEM#sku-101, ITEM#sku-205 — in sort order, in one call. That is the item collection replacing a join.

Step 4 — Run patterns 4, 5, 8 (overloaded GSI1)

# Pattern 4: all PENDING orders (across all customers)
aws dynamodb query --table-name commerce-main --index-name GSI1 \
  --key-condition-expression "GSI1PK = :s" \
  --expression-attribute-values '{":s":{"S":"STATUS#PENDING"}}'

# Pattern 5: PENDING orders in a date window
aws dynamodb query --table-name commerce-main --index-name GSI1 \
  --key-condition-expression "GSI1PK = :s AND GSI1SK BETWEEN :a AND :b" \
  --expression-attribute-values '{":s":{"S":"STATUS#PENDING"},
     ":a":{"S":"2026-07-01"},":b":{"S":"2026-07-31"}}'

# Pattern 8: products in a category, cheapest first
aws dynamodb query --table-name commerce-main --index-name GSI1 \
  --key-condition-expression "GSI1PK = :c AND begins_with(GSI1SK, :p)" \
  --expression-attribute-values '{":c":{"S":"CATEGORY#books"},":p":{"S":"PRICE#"}}'

One index, three different questions — GSI overloading in action.

Step 5 — Add a new pattern on a live table: the sparse GSI2

Add “list only open orders” without touching existing data — only items carrying GSI2PK join the index:

aws dynamodb update-table --table-name commerce-main \
  --attribute-definitions AttributeName=GSI2PK,AttributeType=S \
  --global-secondary-index-updates \
   '[{"Create":{"IndexName":"GSI2",
       "KeySchema":[{"AttributeName":"GSI2PK","KeyType":"HASH"}],
       "Projection":{"ProjectionType":"ALL"}}}]'

aws dynamodb wait table-exists --table-name commerce-main
# Pattern 6: only open orders — the PENDING order carries GSI2PK=OPEN, the products don't
aws dynamodb query --table-name commerce-main --index-name GSI2 \
  --key-condition-expression "GSI2PK = :o" \
  --expression-attribute-values '{":o":{"S":"OPEN"}}'

Step 6 — Watch the sparse mechanic: fulfill an order

# Mark fulfilled: change status AND remove the sparse key so it leaves GSI2
aws dynamodb update-item --table-name commerce-main \
  --key '{"PK":{"S":"CUST#c1"},"SK":{"S":"ORDER#2026-07-14#o9"}}' \
  --update-expression "SET GSI1PK = :s REMOVE GSI2PK" \
  --expression-attribute-values '{":s":{"S":"STATUS#FULFILLED"}}'

# Re-run pattern 6 — the order is gone from the open-orders index
aws dynamodb query --table-name commerce-main --index-name GSI2 \
  --key-condition-expression "GSI2PK = :o" \
  --expression-attribute-values '{":o":{"S":"OPEN"}}'

Expected: the second query returns zero items — REMOVE GSI2PK dropped the order out of the sparse index without deleting anything.

Step 7 — The same table as Terraform

resource "aws_dynamodb_table" "commerce_main" {
  name         = "commerce-main"
  billing_mode = "PAY_PER_REQUEST"
  hash_key     = "PK"
  range_key    = "SK"

  attribute { name = "PK"     type = "S" }
  attribute { name = "SK"     type = "S" }
  attribute { name = "GSI1PK" type = "S" }
  attribute { name = "GSI1SK" type = "S" }
  attribute { name = "GSI2PK" type = "S" }

  global_secondary_index {
    name            = "GSI1"
    hash_key        = "GSI1PK"
    range_key       = "GSI1SK"
    projection_type = "ALL"
  }

  global_secondary_index {
    name            = "GSI2"          # sparse: only items with GSI2PK appear
    hash_key        = "GSI2PK"
    projection_type = "ALL"
  }

  stream_enabled   = true
  stream_view_type = "NEW_AND_OLD_IMAGES"   # for the aggregation Lambda

  point_in_time_recovery { enabled = true }
  server_side_encryption { enabled = true }

  ttl { attribute_name = "expiresAt", enabled = true }
}

Note the whole table is declared with just its key attributes plus the two GSIs — you never declare name, total, or stock, because DynamoDB is schemaless outside the keys. stream_view_type = NEW_AND_OLD_IMAGES wires up the delta-based aggregation path.

Step 8 — Teardown (do this)

aws dynamodb delete-table --table-name commerce-main --region us-east-1
# or, if you used Terraform:
terraform destroy -auto-approve

Common mistakes & troubleshooting

The single-table playbook. Each row: the symptom, the modeling root cause, the exact way to confirm it, and the fix.

Error / exception reference

The three nastiest real failures

The Scan that hid in “just a filter.” A developer adds “search invoices by status” with Scan + FilterExpression because it works in dev with 200 items. In production with 20 million items it reads (and bills) all 20 million on every search, throttles the table, and pages the on-call. The tell is a ConsumedReadCapacity graph wildly larger than the rows returned. There is no capacity fix — only a modeling fix: a sparse or overloaded GSI so the query reads what it returns. Ban Scan from user-facing paths in code review.

The migration nobody scoped. Single-table’s dirty secret is that changing the key structure of live data means rewriting every item. Teams routinely under-estimate this: a backfill of millions of items must be paced against write capacity, made idempotent (it will be re-run), and usually paired with dual-reads behind a feature flag so the app serves the old model until the new one is verified. Model the access patterns up front precisely so you migrate rarely.

Silent aggregate drift. A stream-maintained orderCount slowly diverges from reality because the Lambda wasn’t idempotent and reprocessed some records on a retry, or its IteratorAge climbed and it fell behind. Nobody notices until a customer reports a wrong number. Guard it: idempotent handlers keyed on the event, NEW_AND_OLD_IMAGES deltas rather than blind increments, an alarm on stream IteratorAge, and a periodic reconcile job that recomputes the truth.

Best practices

• List access patterns before the first CreateTable. The access-pattern list and entity chart are deliverables, not afterthoughts — commit them to the repo and update them with every new pattern.
• Default to one table with generic PK/SK, but do it because the patterns share access, not as dogma. Multi-table is fine when entities are genuinely unrelated.
• Prefix every key value with its entity type (CUST#, ORDER#) and carry a redundant entity attribute for stream consumers and exports.
• Overload GSIs before adding new ones. One well-designed overloaded GSI beats three single-purpose ones — fewer writes, lower cost, under the 20-GSI limit.
• Reach for a sparse index whenever you would otherwise FilterExpression a large set down to a small, well-defined subset.
• Project GSIs narrowly — INCLUDE exactly what the pattern reads; ALL only when the pattern truly needs the whole item. Every projected attribute is a write cost.
• Prefer GSIs over LSIs. LSIs are permanent (creation-time only) and impose the 10 GB collection cap; GSIs are flexible and cap-free.
• Denormalize deliberately, and decide snapshot vs live for each copied field — freeze historical facts, stream-maintain live ones.
• Use transactions for cross-item invariants, streams for eventual aggregates. Do not pay 2× WCU for a dashboard counter, and do not risk overselling on an eventual count.
• Shard only proven-hot keys and cache the scatter-gather reads; over-sharding multiplies read cost for no benefit.
• Enable PITR, on-demand backups and TTL from day one — they are cheap insurance and a schemaless item makes TTL trivial (expiresAt epoch).

Security notes

DynamoDB’s security model rewards the same access-pattern thinking — you can lock IAM down to item and attribute granularity, which single-table design makes especially powerful because one policy governs one table.

The multi-tenant win is worth dwelling on: with dynamodb:LeadingKeys, an IAM policy can restrict a caller to only items whose partition key begins with their tenant id — so a single overloaded table safely serves thousands of tenants, each fenced to their own partitions, with no application-side filtering to get wrong.

Cost & sizing

DynamoDB bills for throughput (reads/writes) and storage, and single-table design bends both — usually down, because you stop over-reading. The numbers below are us-east-1, mid-2026 (WRU/RRU reflect the ~50% price cut from November 2024).

Worked example. The commerce workload at 5M eventually-consistent reads (≤ 4 KB) + 1M writes/month, on-demand: 5M × $0.125/M + 1M × $0.625/M ≈ $1.25/month in throughput, plus a few cents of storage under the 25 GB free tier — roughly ₹105/month. The same workload modeled as five tables with a Scan-and-filter dashboard read the whole invoice table on every dashboard load, which is exactly how LedgerLeaf’s bill reached $700; the single-table redesign returned it to single dollars. Right-sizing rule: start on-demand (zero idle cost, absorbs spikes, no throttling risk at low scale), and only move a proven steady, high-volume table to provisioned + auto-scaling once you have real traffic curves.

Interview & exam questions

Q1. Why do you model DynamoDB access-patterns-first instead of normalizing? Because DynamoDB has no joins and no ad-hoc query planner — it guarantees performance only along the key axes you designed. Normalizing then querying leaves you with patterns no key serves, forcing Scan. You list patterns first so every one maps to a single Query/Get. (DVA-C02, SAA-C03)

Q2. What is key overloading and why do it? Storing multiple entity types in generic PK/SK attributes, distinguished by value prefixes (CUST#, ORDER#). It collapses many entities into one table so related items share partitions, one transaction can span types, and there is one IAM/backup surface. (DVA-C02)

Q3. Explain the difference between a GSI and an LSI, and when you’d pick each. A GSI has any PK/SK, its own capacity, is eventually consistent, and can be added/removed anytime (20 max). An LSI shares the base PK and the table’s capacity, allows strong consistency, must be created with the table (5 max), and imposes a 10 GB item-collection limit. Prefer GSIs; use an LSI only when you need strong consistency on an alternate sort key. (DVA-C02, SAA-C03)

Q4. What is a sparse index and what problem does it solve? A GSI only projects items that have both its key attributes; write the key onto just a subset and the index holds only that subset. It replaces an expensive FilterExpression (which reads and bills the whole set) with a cheap query that reads only the matching items — e.g. “open orders.” (DVA-C02)

Q5. How do you model a many-to-many relationship? The adjacency-list pattern: store node items and edge items in one table (PK=A, SK=B for the edge). The base table gives one direction (Query PK=A); an inverted GSI (swap PK/SK) gives the other (Query GSI1PK=B). (DVA-C02)

Q6. A single partition key is throttling despite spare table capacity. Why, and what do you do? Throughput is per partition (~1,000 WCU/3,000 RCU); one hot key can’t exceed one partition even with adaptive capacity. Confirm with Contributor Insights, then write-shard the key (#0..#N) and scatter-gather reads, or cache hot reads. (SAA-C03, DVA-C02)

Q7. How do you keep an aggregate like orders-per-customer without a Scan? Maintain a counter attribute and update it on write — via a TransactWriteItems when it must be exact, or asynchronously via a DynamoDB Streams Lambda (ADD on insert, deltas from NEW_AND_OLD_IMAGES) when eventual consistency is acceptable. (DVA-C02)

Q8. When is single-table design the wrong choice? When access patterns are unknown or heavily ad-hoc/analytical, for small/simple apps where the modeling overhead isn’t worth it, or for reporting/aggregation workloads — route those to S3 + Athena or Redshift, or use multiple simpler tables. (SAA-C03)

Q9. What does a FilterExpression cost you, and why prefer an index? It is applied after the read, so it reads and bills every item the key condition matched before discarding non-matches — no capacity saving. A GSI/sparse index makes the query read only the items you return. (DVA-C02)

Q10. How do item collections replace joins? Items sharing a partition key form a collection returned by one Query. Storing an order header and its line items under the same PK returns both in a single call, ordered by sort key — the join happens at write-time modeling, not read-time. (DVA-C02)

Q11. What are the limits on a DynamoDB transaction? TransactWriteItems/TransactGetItems handle up to 100 items and 4 MB, cost 2× the normal capacity, are serializable, and fail atomically with TransactionCanceledException (with CancellationReasons) on a condition failure or write conflict. (DVA-C02)

Q12. Why prefix and zero-pad values in sort keys? Sort keys are strings compared lexicographically. Prefixes (ORDER#) group and order entity types within a collection; zero-padding numbers (PRICE#0000019.99) makes them sort numerically so range and begins_with queries behave. (DVA-C02)

Quick check

1. You need “all orders with status = SHIPPED, newest first.” Which index technique, and what are its keys?
2. Your table has an LSI and a write just failed with a size error. What limit did you hit, and what is the fix?
3. Why does adding GSI2PK to only some items create a useful index, and what is that called?
4. Give the base-table and inverted-GSI queries for “which users are in group g9?” in an adjacency-list model.
5. When should orders-per-customer be maintained by a transaction rather than a stream?

Answers

1. A sparse or overloaded GSI: GSI1PK=STATUS#SHIPPED, GSI1SK=<timestamp>, queried with ScanIndexForward=false. If SHIPPED is a transient state you filter to often, a sparse GSI keyed only while shipped is even cheaper.
2. The 10 GB item-collection limit, which exists because the table has an LSI. Fix: split the collection across more partition keys, or rebuild the table without the LSI (using a GSI instead).
3. Because a GSI only projects items that have both its key attributes — writing GSI2PK to just the subset you want makes the index contain only that subset. This is a sparse index.
4. Base: Query PK=USER#u1, SK begins_with GROUP# for u1’s groups. Inverted GSI (PK=SK, SK=PK): Query GSI1PK=GROUP#g9 for g9’s members.
5. When the count is part of a correctness invariant that must hold immediately (e.g. enforcing a hard cap of N orders atomically). For a dashboard number, an eventually-consistent stream-maintained counter is cheaper and sufficient.

Glossary

Next steps

• Ground the primitives underneath this model with DynamoDB Tables, Keys and Capacity Hands-On.
• Take the operational side further with DynamoDB Throttling and Hot-Partition Troubleshooting — the confirm-then-shard workflow in depth.
• See the table in its natural habitat in AWS Serverless Web Application Architecture.
• Wire stream-maintained aggregates into a larger system with AWS Event-Driven Architecture: EventBridge, SQS and Lambda.
• Know the boundary where you should reach for a relational engine instead in AWS Databases: RDS, DynamoDB and Aurora Compared.