DynamoDB Single Table Design — Interview Guide

One table, two access patterns, no join: Query PK = CUSTOMER#123 returns the customer's profile and every one of their orders in a single request (they share the same partition). Query PK = ORDER#001 fetches that order's own details directly. The keys are modeled around the *known* access patterns — not around normalized entities the way a relational schema would be.

Overloaded GSIs are what make one table serve many entity types cleanly. Instead of a dedicated index per entity, a single GSI1PK/GSI1SK pair holds different, purpose-built values per item type — for a CUSTOMER item, GSI1PK might be EMAIL#<email> (to look customers up by email); for an ORDER item, the same attribute name might hold STATUS#<status> (to list orders by status). The GSI attribute name is reused ("overloaded"), but each entity type puts a differently-shaped value in it — so one physical index quietly serves several unrelated access patterns instead of one GSI per query.

Adding a genuinely new access pattern later has three levels of cost, in order of preference. Cheapest: if an existing GSI's key already happens to sort/filter the new way you need, you're done — no schema change at all. Next: add a new GSI (GSI2PK/GSI2SK) — DynamoDB backfills it automatically from existing items, no downtime, but every existing item needs the new attribute populated, which for old items means either the application computed it defensively when the item was first written (the standard single-table-design discipline: populate GSI attributes for patterns you might need, not just ones you use today) or a one-time backfill script that scans and updates every item. Most expensive: the new pattern needs a key shape the existing attributes can't produce at all — that usually means a genuinely new item type or a denormalized copy, which is the scenario the whatIf above calls a "painful migration," and exactly why enumerating access patterns up front matters so much more here than in a relational schema.

Sparse GSIs turn "only some items have this attribute" into a free filtered index. A GSI only indexes items that actually have a value for the GSI's key attribute — an item missing that attribute simply doesn't appear in the index at all, rather than appearing with a null key. That's a deliberate feature, not an edge case to work around: give the GSI key attribute a value only on ORDER items where status = PENDING, and the GSI now contains only pending orders — no filter expression needed, no scanning past shipped/delivered orders to find them, because they were never written into the index in the first place. It's the standard single-table-design technique for "give me only the items matching some condition" queries, and it's a favorite follow-up precisely because it looks like a quirky side effect until you see it used deliberately. Because populating the key is what includes an item, removing it from a sparse index is just as deliberate — clear the GSI key attribute (e.g. when an order ships) and the item vanishes from the index on the very next write, no separate cleanup step required.

Keywords they're listening for: