Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 30 ยท Cassandra Data Model

Query-first data modeling

Learn query-first modeling for Cassandra: list your queries, build one table per query, duplicate data on purpose, and keep the copies in step.

In a relational database you model the data first: find the entities, normalize them, and let any query be answered later with joins. In Cassandra you do the reverse. You write down the queries your application must answer, and then you design one table for each query. The data gets copied into as many tables as there are queries. That feels wrong if you grew up on normalization, and it is exactly what makes Cassandra fast and scalable.

This lesson walks through the method with a small blog and a small order system. You will see why denormalization (storing the same fact in several places) is normal here, what it costs on the write side, and how to keep the copies consistent. The real database cannot run on this site, so the CQL is read-only, and two Python stand-ins model several query tables so you can watch one write fan out into many.

Why not normalize?

Normalization removes duplication so that a fact lives in one place. It relies on joins to reassemble the data when you read it. Cassandra has no joins, and it could not do them efficiently across machines: the two tables' rows would live on different nodes. So Cassandra pushes the work to write time. You pay a little more when you write (several copies) and in return every read is a single fast lookup in one partition.

Storage is cheap, and distributed joins and big scans are not. Duplicating data is therefore the intended design, not a mistake.

The method in four steps

  1. List the queries. Be specific: "show the latest 20 posts by an author", "show all posts with a tag", "get one post by id".
  2. For each query, choose the partition key. It is the thing you will supply with =: the author, the tag, the post id.
  3. Choose clustering columns to give the sort order and slicing the query needs: usually a time column, then an id to make rows unique.
  4. Include every column the query returns. If the page needs the title and a summary, store them in that table so no second lookup is needed.

The result is a set of tables named after their query, such as posts_by_author. The _by_ naming convention makes it obvious which question each table answers.

flowchart LR
    W["Application<br/>publishes a post"] --> T1["posts_by_id<br/>partition: post_id"]
    W --> T2["posts_by_author<br/>partition: author<br/>clustering: published DESC"]
    W --> T3["posts_by_tag<br/>partition: tag<br/>clustering: published DESC"]
    Q1["Get one post"] --> T1
    Q2["Latest posts by author"] --> T2
    Q3["Latest posts for a tag"] --> T3

One write fans out to one table per query; each read goes to exactly one table.

Example: a blog

Three queries, three tables. The CQL below is read-only.

SQL
CREATE TABLE posts_by_id (
  post_id   uuid PRIMARY KEY,
  author    text,
  title     text,
  body      text,
  tags      set<text>,
  published timestamp
);

CREATE TABLE posts_by_author (
  author    text,
  published timestamp,
  post_id   uuid,
  title     text,
  PRIMARY KEY ((author), published, post_id)
) WITH CLUSTERING ORDER BY (published DESC, post_id ASC);

CREATE TABLE posts_by_tag (
  tag       text,
  published timestamp,
  post_id   uuid,
  author    text,
  title     text,
  PRIMARY KEY ((tag), published, post_id)
) WITH CLUSTERING ORDER BY (published DESC, post_id ASC);

Notice the differences. posts_by_id holds the whole post, including the body. The listing tables hold only what a list page shows (title, author) and the post_id to open the full post. The tag table has one row per tag per post, so a post tagged cassandra and nosql appears in two partitions. The clustering column post_id makes each row unique, because two posts can share a publish time.

The author listing is a query like:

SQL
SELECT published, post_id, title FROM posts_by_author WHERE author = 'ada' LIMIT 20;

It touches one partition and reads 20 rows already sorted newest first. No joins, no scans.

Stand-in 1: one write, many tables

The Python code below keeps three dictionaries as the three tables, with the same keys as the CQL. publish_post writes to all of them. Reads touch one table. A last query asks for an author's posts on a tag, a combination for which no table exists, to show what happens when a question was not planned for.

Python One post, three query tables

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Three posts became eleven stored rows (3 in the id table, 3 in the author table, 5 in the tag table). Each of the three planned queries is a single lookup. The unplanned question works here only because the dataset is tiny: on a real cluster it would mean reading a whole tag partition and filtering in your application, or building another table. When a new query appears, the usual answer is add a new table and fill it (a backfill job copies the existing data).

Keeping the copies in step

The price of duplication is that every change must reach every copy. If an author edits a title, you must update posts_by_id, the author row and every tag row. To do that you need to know which rows exist, which means reading the post first (to learn its published time and tags), or storing enough in the application state to rebuild the keys.

Cassandra offers batches to group such writes so they succeed or fail together, and you will learn their limits in Lightweight transactions, batches and counters. In practice many teams write the copies in parallel and make each write idempotent, which means that repeating it gives the same result. Because INSERT is an upsert, a retry of the whole sequence simply rewrites the same rows with the same values.

Cassandra's materialized views and secondary indexes can maintain some copies for you, but they come with trade-offs covered in Secondary indexes, SAI and materialized views. Understand manual denormalization first.

Example: orders

An online shop needs two queries: "show one order with its lines" and "show the order history of a user, newest first". Two tables answer them.

SQL
CREATE TABLE orders_by_id (
  order_id uuid,
  line_no  int,
  user_id  uuid static,
  placed   timestamp static,
  sku      text,
  qty      int,
  PRIMARY KEY ((order_id), line_no)
);

CREATE TABLE orders_by_user (
  user_id  uuid,
  placed   timestamp,
  order_id uuid,
  total    decimal,
  PRIMARY KEY ((user_id), placed, order_id)
) WITH CLUSTERING ORDER BY (placed DESC, order_id ASC);

The static columns hold values that are the same for every row of the partition (the order's owner and time), stored once. The history table stores just the summary needed for a list.

Stand-in 2: writes that partly fail

Because one logical action writes several tables, one of the writes can fail while the others succeed. The stand-in below makes the second table fail on the first attempt and shows the inconsistency, then repeats the whole write. Because each table write is an upsert, the retry repairs the gap without creating duplicates. The failure here is simulated by the program; it is not a measurement of how often it happens in real clusters.

Python Partial failure and an idempotent retry

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

After the failed attempt the order exists in one table but not in the user's history, so the user would not see it in the list. Retrying fixed it, and retrying again changed nothing. That is why idempotent writes matter for denormalized designs.

Common mistakes

  • Designing tables around entities (a users table, an orders table) and then wanting to filter by other columns. Start from queries instead.
  • A table per query, but partition keys that are not in the query. If the WHERE cannot supply the partition key, the table is useless for that query.
  • Low-cardinality partition keys such as status or country, which make a few giant partitions.
  • Forgetting the write cost. Ten query tables mean ten writes per event; count them.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the first step of query-first modeling?

    You start with the queries, then create one table per query with the partition key and sort order each query needs.
  2. 2. Why is storing the same data in several tables acceptable in Cassandra?

    Duplication moves the work to write time so that every read is a single partition lookup.
  3. 3. A post has three tags and you keep a posts_by_tag table. How many rows does that table hold for this post?

    The tag is the partition key, so the post appears once in each tag's partition: three rows.
  4. 4. Why should writes to the query tables be idempotent?

    When one of several writes fails, the application retries. Idempotent upserts make the retry safe.

Summary

  • Cassandra modeling starts from queries: one table per query, named after what it answers.
  • Each table has a partition key you can supply with = and clustering columns that give the needed order.
  • Duplicating data is normal; the cost moves to write time, so keep all copies in step.
  • Idempotent upserts make retries after partial failures safe.
  • When a new query appears, add a table and backfill it instead of filtering or scanning.

Next: Data types, collections and UDTs.