Query-First Data Modeling

List the access patterns first

Relational modeling starts with entities: you draw the nouns, normalize them, and trust the query planner to join them at read time. NoSQL modeling inverts this completely. In a system like DynamoDB or Cassandra there is no join and no flexible query planner, so if you model entities first and hope to query them later, you will find that the query you need is impossible or requires a full-table scan. The mindset shift is: list the access patterns first, then design keys and tables so each pattern is a single lookup.

Start by writing every read and write your feature performs, as concrete sentences: "list a user's conversations, most recent first," "load the last 50 messages in a thread," "get the unread count per conversation." Each of these must become one query against one partition. If any access pattern would require scanning or a scatter-gather, the model is wrong, not the database.

Composite keys co-locate the data

The core tool is the composite primary key: a partition key plus a sort key. The partition key decides which physical node the item lives on; everything with the same partition key is stored together, sorted by the sort key. To store a thread's messages, set partition key = THREAD#<id> and sort key = MSG#<timestamp>; "load the last 50 messages" is then a single Query on that partition, ScanIndexForward=false, Limit=50. No join, one partition, one round trip.

Modeling relationships is about embedding versus referencing. A one-to-many where the many are always read with the one, and are bounded, can be embedded: store the child items in the same partition as the parent (same partition key, distinct sort keys). If the many are large or unbounded, or read independently, reference them: give them their own partition and store just an id. A many-to-many is handled with an adjacency-list pattern or a global secondary index that lets you query the relationship from both directions.

Interview nuance: the tell of a weak NoSQL answer is designing a users table, a conversations table, and a messages table that mirror a relational schema, then discovering you cannot list a user's conversations without a scan. The strong answer often puts multiple entity types in one table (single-table design), keyed so each access pattern hits one partition.

Table

Single-table design: three item types in one table, keyed so every access pattern on the list is one contiguous Query slice against one partition, never a scan.

partition key	sort key	item type	access pattern it serves
USER#7	CONV#2026-07-23T09:14	conversation pointer	list user 7's conversations, most recent first: one Query on USER#7, descending
USER#7	CONV#2026-07-21T11:02	conversation pointer	same slice, next row
THREAD#123	MSG#2026-07-23T09:14	message	load the last 50 messages: Query THREAD#123, ScanIndexForward=false, Limit=50
THREAD#123	MSG#2026-07-23T09:12	message	same partition, already sorted by the timestamp in the sort key
THREAD#123	MEMBER#USER#7	participant	bounded and always read with the thread, so it is embedded in the same partition

Hot partitions and secondary indexes

The failure mode you must actively design against is the hot partition. Because the partition key routes to a physical node with a throughput ceiling (DynamoDB caps a single partition around 3,000 read and 1,000 write units per second), a key that concentrates traffic becomes a bottleneck no matter how much total capacity you provision. A celebrity user's thread, or a partition key of status=ACTIVE that every write touches, will throttle. Spread heat with a high-cardinality partition key and, for known-heavy keys, write sharding: append a suffix (THREAD#123#<0..9>) to fan one logical partition across ten physical ones, then scatter-read the ten on the way out.

Secondary indexes buy additional access patterns without a second table. A global secondary index (GSI) has its own partition and sort key over the same items, so you can query by a different attribute. GSIs are eventually consistent and cost extra write capacity (every base write replicates to the index), so add them per access pattern, not by default. A local secondary index (LSI) shares the partition key but offers an alternate sort key, and can be strongly consistent.

Recap: enumerate access patterns first, turn each into a single-partition lookup using composite partition and sort keys, choose embedding versus referencing by how the related data is read, design the partition key to avoid hot partitions, and add secondary indexes only to serve a named additional access pattern.