Cassandra and DynamoDB Modelling: Design the Table From the Query

What you'll learn: By the end of this lesson you will be able to turn a list of access patterns into Cassandra tables and a DynamoDB table, choose partition keys and clustering or sort keys on purpose, explain why these stores duplicate data instead of joining it, use a global secondary index and know what it costs, recognise a hot or unbounded partition before it ships, and say when a relational database is still the better answer.

A hotel front desk has a card file. One drawer is sorted by guest name, another by room number, another by arrival date. Staff never search a drawer for a name it was not sorted by: they go to the drawer built for the question. When a guest checks in, a clerk writes the same fact on three cards, one per drawer. Adding a drawer later means writing cards for every guest already in the building.

Cassandra and DynamoDB work the same way. You decide the questions first, build one lookup structure per question, and write the data into each. The four NoSQL families are compared in NoSQL types, the broader choice between SQL and these stores is in SQL vs NoSQL: a decision framework, and the mechanics of the log-structured engine underneath Cassandra are in storage engines. This lesson is the dedicated treatment of modelling inside the two key-oriented stores.

Verified against the Apache Cassandra documentation (the version selector showed 5.0; the page also pointed to a 6.0 alpha) and the Amazon DynamoDB Developer Guide, all read on 2026-11-08. Both products change: check the page for the version and service behaviour you run.

The core idea

Query first, table second. The Cassandra documentation says "data modeling is query-driven" and that data is duplicated across tables in a process it calls denormalization. The DynamoDB guide says you should not start designing a schema until you know the questions it must answer, and it contrasts relational design (flexibility first, normalization important) with DynamoDB design, where you shape the schema so "the most common and important queries" are fast and cheap.

Three kinds of example run through the lesson, each labelled:

• Minimal example: one question, one Cassandra table, one DynamoDB key design.
• Realistic example: ShopStream's order history, three questions served from duplicated tables, with a global secondary index.
• Failure example: a partition that grows without bound (store event history) and a partition key that concentrates traffic.

The trade: reads become simple because each targets a known partition, writes become more work because every copy must be kept in step, and an unplanned question may need a new table and a backfill.

Partitions, clustering and sort keys

Both products spread data over machines by hashing a partition key, and both keep related rows or items together and sorted inside a partition.

Cassandra's rules for SELECT are strict and worth memorising: only an equality is allowed on a partition key, the partition key must be given before clustering columns in WHERE, and the restriction on clustering columns must select a contiguous set of rows. ORDER BY can only follow the clustering order declared on the table (or its reverse). DynamoDB's Query likewise needs the partition key as an equality condition and returns results sorted by the sort key, ascending unless ScanIndexForward is false.

Minimal example: one question, one table

The question: "show a shopper's most recent orders, newest first." In Cassandra the table is built for exactly that question:

CREATE TABLE orders_by_shopper (
    shopper_id  bigint,
    placed_at   timestamp,
    order_id    uuid,
    status      text,
    total_cents int,
    PRIMARY KEY ((shopper_id), placed_at, order_id)
) WITH CLUSTERING ORDER BY (placed_at DESC, order_id ASC);

SELECT order_id, placed_at, status, total_cents
FROM orders_by_shopper
WHERE shopper_id = 77
LIMIT 20;

The partition key is shopper_id, so one shopper's orders form one partition. placed_at is a clustering column, declared DESC, so the newest rows come first and LIMIT 20 stops early. order_id is in the key because, as the documentation stresses, the primary key must "define a unique data element": two orders at the same instant must not overwrite each other. The CQL types used (bigint, timestamp, uuid, text, int) are native types on the CQL types page.

In DynamoDB the same question becomes a key design, not a schema. A table with a string partition key pk and a string sort key sk holds an item per order with pk = "SHOPPER#77" and sk = "ORDER#2026-11-08T09:15:00Z#1001". ISO 8601 timestamps in UTC sort correctly as text, and the guide notes that string sort keys are compared by UTF-8 bytes. The query (illustrative request shape, following the form in the developer guide):

{
  "TableName": "Orders",
  "KeyConditionExpression": "pk = :p AND begins_with(sk, :prefix)",
  "ExpressionAttributeValues": {
    ":p": { "S": "SHOPPER#77" },
    ":prefix": { "S": "ORDER#" }
  },
  "ScanIndexForward": false
}

A single Query returns at most 1 MB of data and signals more through LastEvaluatedKey, so a feed page asks for one page at a time and carries that key forward. The SHOPPER# and ORDER# prefixes are a naming convention, not a feature: they let several kinds of item share one table without colliding.

Figure 1. The query-first design loop. How to read this: start at the top with questions, not entities. The loop through the partition check is where most real designs change.

Denormalization on purpose

The Cassandra documentation says there are no joins, so the data one query needs must sit in one table, and "data duplication and a high write throughput are used to achieve a high read performance." Its own hotel example has one table per question (hotels by point of interest, available rooms by hotel and date, and so on), with tables named for the primary entity and the search key (hotels_by_poi). The physical-design page adds that the reservation tables are denormalized and kept up to date manually.

Two practical consequences:

1. Every write fans out. One business event, such as an order placed, becomes one write per table that holds it. The application owns that fan-out.
2. Writes must be repeatable. CQL INSERT and UPDATE do not check whether the row exists: "the row is created if none existed before, and updated otherwise." A retry of the same write to the same primary key lands on the same row, which makes the fan-out safe to retry when the key is deterministic (idempotent operations). A BATCH can group the statements, and the documentation says a logged batch makes "all operations in a batch eventually complete or none will," but it also says operations are isolated only within a single partition, batches are "not a full analogue for SQL transactions", and there is a performance penalty when a batch spans several partitions.

DynamoDB's guide describes the same idea as keeping related data together and using sort order and secondary indexes to answer different questions, and its relational-modelling page lists three patterns: single-table design, multi-table design and aggregate design (embed data that is always read together, such as an order and its lines).

Realistic example: ShopStream order history

This is the realistic example. It is an educational scenario on the course's running example, ShopStream, and every figure comes from the shared illustrative assumptions (500,000 orders per day, 5,000 stores, about 58 checkouts per second at peak) or is labelled illustrative. The Stage 3 data model lesson concluded that ShopStream does not need a wide-column store at its size and keeps orders in PostgreSQL. Treat what follows as the exercise of what the design would be if order history had to move to a key-oriented store, for example to isolate a very large read-heavy history from the transactional database.

The access patterns, written first:

Figure 2. One order, three copies, each shaped for one question. How to read this: the checkout service writes the same order into three structures. Each read goes to exactly one structure and one partition, so none needs a join.

The Cassandra design for A1 is the table above. A3 gets a composite partition key so one store's day is one partition:

CREATE TABLE orders_by_store_day (
    store_id    bigint,
    day         date,
    placed_at   timestamp,
    order_id    uuid,
    status      text,
    total_cents int,
    PRIMARY KEY ((store_id, day), placed_at, order_id)
) WITH CLUSTERING ORDER BY (placed_at DESC, order_id ASC);

SELECT order_id, placed_at, status, total_cents
FROM orders_by_store_day
WHERE store_id = 17 AND day = '2026-11-08';

Size check, the way the documentation asks for it. The data-model analysis section gives a general guideline of fewer than 100,000 values per partition and under 100 MB of disk space per partition, and notes that exact needs vary. An average store sees 500,000 / 5,000 = 100 orders a day (illustrative), so one (store, day) partition holds about 100 order rows, comfortably inside the guideline even for a store with far more than the average. A shopper's partition grows with their lifetime orders, which is bounded by human behaviour. Both partitions are bounded; that is the check that matters. A2 is order_lines with PRIMARY KEY ((order_id), product_id): a handful of rows per partition.

The DynamoDB version puts A1 and A2 in one table and A3 in a global secondary index (GSI):

A Query on pk = ORDER#1001 returns the header and every line in one request. The summary item carries gsi1pk and gsi1sk, so a Query against the index with gsi1pk = STORE#17#2026-11-08 answers A3. The index treats the items that lack those attributes as absent: the guide states that "a global secondary index only tracks data items where its key attributes actually exist", so header and line items are never copied into it. The attribute names pk, sk and gsi1pk do not appear on the DynamoDB reserved-word list, but several natural names do (status, total, store, date, name, item, order): in expressions those need an expression attribute name placeholder.

Figure 3. A partial fan-out and its repair. How to read this: the timeout leaves the three copies disagreeing. Because the key is deterministic, repeating the write converges them. A design with random keys per attempt would create a second row instead.

What a global secondary index costs

A GSI is the DynamoDB answer to "I need another query", and the guide is precise about the price. It has its own key schema (key values need not be unique), always projects the base table's keys, and cannot fetch attributes from the base table, so the projection (KEYS_ONLY, INCLUDE, ALL) is a design decision. It is updated asynchronously: changes propagate "within a fraction of a second, under normal conditions", and applications must handle results that are not up to date. Each write touching an indexed attribute consumes write capacity on the index, and insufficient index capacity can throttle writes to the base table.

So A3 (the store dashboard) may lag behind A1 by a moment. For a dashboard that is usually acceptable, and it is a business decision, not a technical one.

Failure examples: when a partition goes wrong

Unbounded partitions

The failure example is an events table keyed by store alone:

CREATE TABLE store_order_events_bad (
    store_id    bigint,
    occurred_at timestamp,
    order_id    uuid,
    event_type  text,
    PRIMARY KEY ((store_id), occurred_at, order_id)
);

It answers "what happened in store 17?" and nothing bounds the partition: a store's history only grows. An average store takes 100 orders a day (500,000 / 5,000, illustrative). Say each order produces 5 events (placed, accepted, packed, picked up, delivered; an illustrative teaching assumption): that is 500 rows a day per store, so one partition reaches the documentation's 100,000-value guideline in 100,000 / 500 = 200 days. A busier store gets there sooner. The fix is the move used for orders_by_store_day: put a bucket in the partition key, PRIMARY KEY ((store_id, day), occurred_at, order_id). The price is that a question spanning several days needs one query per day. The same bucketing applies to courier location history, which the NoSQL types lesson models, and purpose-built stores for it are in time-series databases.

Concentrated traffic

DynamoDB's guide documents the other failure. Every partition is designed to deliver at most 3,000 read units and 1,000 write units per second, and a key design that does not distribute requests creates hot partitions that cause throttling. Its comparison table rates a user id with many users as good and a status code with a few values as bad, and it rates the creation date rounded to a day as bad: all of a day's new items go to one partition key value. When one key must absorb heavy writes, the guide's remedy is write sharding: append a suffix, random (2014-07-09.1 to .200) or calculated from something you query on (a hash of the order id). The cost is on the read side: to read a whole day you query every suffix and merge the results.

ShopStream's 58 checkouts per second at peak (illustrative) would not by itself approach 1,000 write units per second for small items, so a day key would not be the first problem there. The lesson is the check: compute the per-partition rate for your own item sizes, using the guide's definition of a write unit as one write per second for an item up to 1 KB.

Single-table or several tables

The DynamoDB guide gives balanced advice. The NoSQL-design page says to keep "as few tables as possible", with exceptions for high-volume time-series data and very different access patterns. The relational-modelling page says single-table design suits entities with high access correlation and similar operational characteristics, multi-table design suits independent operational requirements, and its own worked example is multi-table. Use a shared table with prefixed keys for items read together (an order and its lines); use separate tables when teams, scaling or retention differ. Single-table design is a technique for access patterns, not a rule of the product.

Trade-offs

In practice: serving a store dashboard from a key-oriented store

This is an educational scenario on ShopStream, and every number is illustrative.

This is a realistic scenario, not a case history.

The situation

Store owners open a dashboard that lists today's orders and refreshes it often. The queries hit the PostgreSQL replicas that serve other work. The team wants to move order history reads to a key-oriented store so store dashboards cannot compete with checkout.

Starting architecture

PostgreSQL stays the source of truth. A projection fed from order events populates the by-store and by-shopper structures. The checkout path is unchanged.

Options considered

The decision

All three questions are known, bounded and read-heavy, which suits B or C. If the team already uses a managed cloud, C avoids running a cluster. If not, A comes first and the key-oriented store waits until the replica measurably limits checkout.

Rollout and verification

Shadow-write for a period, compare a day of orders per store with PostgreSQL, then move the dashboard reads. Verify rows per (store, day) against the guideline and the delay from order to dashboard.

The 2 a.m. failure

A store's dashboard misses recent orders. Check whether the projection consumer is lagging, whether the failed write in Figure 3 was retried and, in the DynamoDB variant, whether the GSI has enough write capacity. Replaying the missing events is safe because the keys are deterministic. The lasting fix is a reconciliation job that compares counts per store and day with the source.

Architect's notes

• Write the access list before anything else, with its rate and the staleness each tolerates. A design review that starts from tables has already skipped the step that matters.
• Choose the partition key for two things at once: what every query names, and an even spread. When they conflict, add a bucket and accept the fan-out read.
• State partition size and growth next to every table in the design document, with the arithmetic.
• Deterministic keys make retries safe. Use random identifiers only where a duplicate row is acceptable.
• A new question is a project, not a query. Price the backfill and the extra write cost when someone asks for a new view.
• Keep money and uniqueness elsewhere unless the store's own conditional-write or transaction features cover them; see the decision framework lesson.

Figure 4. When this modelling style earns its cost. How to read this: each diamond is a way the design can be wrong. Only a data set that passes all four belongs in a key-oriented store.

Common mistakes

• Modelling entities first, then discovering the queries do not fit the keys.
• A partition key with a few values (a status code, a rounded date) that sends the traffic to one place.
• An unbounded partition, such as an id alone for an append-only history.
• Forgetting the fan-out: updating one copy and leaving others stale.
• Random identifiers in retried writes, which turn a retry into a duplicate.
• Using filters as a substitute for keys. In DynamoDB a filter expression runs after the read, so Query consumes the same read capacity with or without it; in Cassandra ALLOW FILTERING runs a full scan.
• Ignoring the cost of a GSI on a write-heavy table.

Key takeaways

• Both stores are designed from the query: one table or key design per access pattern, with duplication on purpose.
• The partition key decides where data lives and how traffic spreads; the clustering or sort key decides the order and the range you can read.
• A query must name the whole partition key; anything else is a scan or a different table.
• Check partition size and growth for every design: Cassandra's guideline is under 100,000 values and 100 MB per partition, DynamoDB's partition limits are 3,000 read and 1,000 write units per second.
• Bucket an unbounded key (add a day) and shard a hot key (add a suffix), and accept the fan-out on reads.
• Deterministic primary keys make a repeated write converge, which is what makes denormalised fan-out repairable.
• A DynamoDB GSI is eventually consistent, has its own capacity and can throttle base-table writes.
• If the queries are unknown, the data is small, or a wrong answer costs money, start relational.

Practice exercises

Exercise 1 (30 min). Write the access list for a courier app (current assignment, a courier's deliveries today, deliveries for a store in a time window). For each, state the Cassandra partition key and clustering columns.

Exercise 2 (45 min). Translate your Exercise 1 design into a DynamoDB table with prefixed keys and one GSI. Show the Query request for each pattern and say which are eventually consistent.

Exercise 3 (45 min). For orders_by_store_day, compute the partition size if a store takes 20,000 orders in a day (illustrative) and each row is 200 bytes (illustrative). Compare it with the guideline and propose a bucket if needed.

Exercise 4 (1 hour). Design write sharding for a DynamoDB table keyed by day with 5,000 writes per second (illustrative). Choose the suffix range, and write the read path that returns one day.

Related reading

• NoSQL types: the four families and what each understands about your data.
• SQL vs NoSQL: a decision framework: when to pick these stores at all.
• Storage engines: LSM-trees under Cassandra.
• Sharding: how partitioning is done and what hot keys do.
• Quorum reads and writes: the consistency-level arithmetic behind Cassandra.
• CAP theorem and PACELC: consistency settings for Cassandra.
• ShopStream Stage 3: choosing the right data store: why ShopStream keeps orders relational.
• Idempotent and commutative operations: why deterministic keys make retries safe.

Official documentation

• Apache Cassandra documentation: data modeling introduction, logical data modeling, physical data modeling.
• Apache Cassandra CQL: data definition, data manipulation, types.
• Amazon DynamoDB Developer Guide: NoSQL design and first steps for modelling relational data.
• Amazon DynamoDB Developer Guide: partition key design, write sharding and sort keys.
• Amazon DynamoDB Developer Guide: global secondary indexes, key condition expressions.