Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 30 ยท Projects

Project: an event and order store on Cassandra

Design a Cassandra event and order store from its queries: bucketed events, order history by user, and consistency choices, with Python stand-ins.

Time to put the Cassandra half of this tutorial together. In this project you design the storage layer of a small shop. It has to remember what customers do (page views, add-to-cart clicks, purchases) and what they buy (orders and their lines). That is a classic Cassandra workload: a very high write rate, data that is read mostly by "give me the latest items for this user", and a business that does not want to lose an order when one machine dies.

You will follow the method from Query-first data modeling: list the queries, design one table per query, bucket the data that grows without limit, and pick a consistency level for each operation. The real database cannot run on this site, so all CQL is read-only. The Python stand-ins mimic the table layout and the replica logic in memory. They show the design and the reasoning, not Cassandra's performance or its real storage engine.

Step 1: the requirements and the queries

A design starts from questions. Here is what the application must answer:

  1. Q1: show one order with all its lines (order confirmation page).
  2. Q2: show a customer's order history, newest first, 20 at a time.
  3. Q3: show a customer's latest activity events, newest first (the "recently viewed" strip).
  4. Q4: count how many orders a customer has placed.

And what it must write: an event for every click (thousands per second at peak), and an order with its lines when a customer checks out.

Write the queries next to the key you will supply for each. This is the table you fill in before writing any CQL:

Query Partition key Sort order inside the partition
Q1 order_id line_no
Q2 user_id placed newest first
Q3 user_id + day bucket ts newest first
Q4 user_id none (one counter row)
flowchart LR
    C["Checkout"] --> O1["orders_by_id<br/>partition: order_id"]
    C --> O2["orders_by_user<br/>partition: user_id"]
    C --> K["order_counts<br/>counter per user"]
    V["Click or view"] --> E["events_by_user_day<br/>partition: user_id + day"]

Checkout writes three tables, a click writes one. Every read goes to exactly one table.

Step 2: the schema

The CQL below is read-only. Read it against the table above.

SQL
CREATE KEYSPACE shop
  WITH replication = {'class': 'NetworkTopologyStrategy', 'dc1': 3};

CREATE TABLE shop.orders_by_id (
  order_id uuid,
  line_no  int,
  user_id  uuid static,
  placed   timestamp static,
  status   text static,
  sku      text,
  qty      int,
  price    decimal,
  PRIMARY KEY ((order_id), line_no)
);

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

CREATE TABLE shop.order_counts (
  user_id uuid PRIMARY KEY,
  orders  counter
);

CREATE TABLE shop.events_by_user_day (
  user_id uuid,
  day     date,
  ts      timeuuid,
  kind    text,
  detail  text,
  PRIMARY KEY ((user_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC)
  AND default_time_to_live = 7776000;

Several choices are worth explaining.

Orders are small and bounded, so no bucket. An order has a handful of lines, so order_id alone is a perfectly good partition key. The static columns hold facts that belong to the whole order, stored once instead of once per line.

Order history is unbounded, but slowly. A customer places maybe a few hundred orders in their lifetime. That is far below the point where a partition becomes a problem, so user_id alone is enough for Q2. Newest first comes from CLUSTERING ORDER BY (placed DESC, order_id ASC); the order_id makes two orders at the same millisecond distinct rows.

Events are unbounded and fast, so they get a bucket. A busy customer or a bot could generate millions of events. If the partition key were only user_id, that partition would grow forever, which you learned to avoid in Time-series and bucketing patterns. Adding the day to the partition key caps each partition at one day of one user's clicks. The table also has a default TTL of 90 days (7,776,000 seconds), so old events expire by themselves.

Counting is a counter. SELECT count(*) over a partition would read it all. A counter table keeps the number up to date. Counters have their own caveats (they are not safe to retry), covered in Lightweight transactions, batches and counters.

The queries are then plain and cheap:

SQL
-- Q1
SELECT * FROM shop.orders_by_id WHERE order_id = ?;

-- Q2 (first page, then page with the driver's paging state)
SELECT placed, order_id, total, status FROM shop.orders_by_user
 WHERE user_id = ? LIMIT 20;

-- Q3 (today's bucket; the application walks back day by day)
SELECT ts, kind, detail FROM shop.events_by_user_day
 WHERE user_id = ? AND day = ? LIMIT 20;

-- Q4
SELECT orders FROM shop.order_counts WHERE user_id = ?;

Stand-in 1: bucketed events and walking back

"Latest 20 events" is the interesting query, because the answer may span several day buckets. The application asks today's bucket first; if it holds fewer than 20 rows, it asks yesterday's, and so on, until it has enough or reaches the retention limit. The stand-in below stores events in a dictionary keyed by (user_id, day), exactly like the partition key, and implements that walk.

Python Events in day buckets, latest N across buckets

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.

Look at the three runs. Asking for one event stops after reading the first non-empty day (the walk back passes over empty days at no cost beyond a lookup). Asking for 20, more than ada has, walks the whole retention window and returns everything she has. That last number is the price of bucketing: a "latest N" query for an inactive user can touch many empty partitions. Real applications limit the walk (for example 30 days) or keep a small "last seen" summary table for inactive users.

Notice also what the model cannot do: it cannot answer "all purchases in the whole shop today". That is a different query, and it would need its own table partitioned by something like (day, kind), with care to avoid a single hot partition for one busy day.

Step 3: writing an order

Checkout touches three tables. Each is an upsert, so repeating the whole sequence is harmless for the first two; the counter is the exception, because incrementing twice counts twice. The usual approach is to make the order itself the source of truth and treat the counter as a convenience that can be rebuilt by a background job.

SQL
INSERT INTO shop.orders_by_id (order_id, line_no, user_id, placed, status, sku, qty, price)
VALUES (?, 1, ?, toTimestamp(now()), 'placed', 'book-1', 2, 19.99);

INSERT INTO shop.orders_by_user (user_id, placed, order_id, total, status)
VALUES (?, ?, ?, 39.98, 'placed');

UPDATE shop.order_counts SET orders = orders + 1 WHERE user_id = ?;

An unlogged batch (or parallel async writes) can send the first two to the cluster together. Do not wrap tables with different partition keys in a logged batch for speed: it adds work, and the guarantee it gives (all or eventually all) is about atomicity of the group, not about performance.

Step 4: choosing consistency per operation

Not every operation needs the same guarantee. With a replication factor of 3 in one data center, the rule you met in Replication and tunable consistency is that reads and writes overlap when R + W > RF.

Operation Write level Read level Reasoning
Place an order LOCAL_QUORUM n/a An order must survive one node failure.
Read order confirmation n/a LOCAL_QUORUM Together with the write: 2 + 2 > 3, so the customer sees their order.
Order history page LOCAL_QUORUM LOCAL_QUORUM A missing order looks like data loss.
Click events ONE ONE A lost click is acceptable, speed matters most.
Order counter LOCAL_QUORUM ONE A slightly stale count is acceptable.

The next stand-in builds three replicas and shows how those levels behave. It simulates acknowledgements and versions only: real Cassandra also uses hinted handoff and read repair, which are not modeled here.

Python Replicas, quorum writes and a stale ONE read

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.

Step 2 shows why QUORUM is the sweet spot: with one node down, a write at QUORUM still succeeds, while the same write at ALL is reported as failed. In step 3 the returned node holds an old version, so ONE can hand back a stale placed, but a quorum read always includes at least one replica that saw the latest write and picks the newest version. In step 4 nothing at QUORUM can be served, while ONE still answers (with whatever C has). That is the availability versus consistency trade you choose per operation. One honest simplification: in real Cassandra a failed ALL write may still have reached the live replicas, and the stand-in does the same. That is why the reads in step 3 return paid2, the value of the "failed" ALL write, which A and B accepted anyway; node C only has placed.

Step 5: what to test and watch

Before you call a model like this done, check the following.

  • Partition size. Estimate rows per partition times bytes per row for your busiest user. If a day of events for one bot is millions of rows, shrink the bucket to an hour.
  • Hot partitions. The user_id and day key spreads load across users, but a single celebrity customer is still one partition for a day.
  • Write amplification. Checkout writes three tables, so the cluster must handle three times the order rate.
  • Backfills. When the product team asks for "orders by status", add a new table and fill it from the old data. Do not reach for ALLOW FILTERING.
  • Operations. TTL-heavy tables like events_by_user_day are good candidates for a time-window compaction strategy (The read path, bloom filters and compaction).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the events table use (user_id, day) as the partition key instead of only user_id?

    A partition per user grows forever. Adding the day bucket bounds each partition to one day of one user's events.
  2. 2. With RF=3, which pair of levels guarantees that a read sees the latest acknowledged write?

    Quorum is 2 of 3. With W=2 and R=2, W+R=4 is greater than RF=3, so every read set overlaps the write set.
  3. 3. The order history query is 'newest orders first for one customer'. Which design delivers that without sorting at read time?

    Rows inside a partition are stored in clustering order, so descending order on placed makes the newest orders come first.
  4. 4. Which statement about the order counter is correct?

    Counter increments are not idempotent. Keep the real data in the order tables and treat the counter as a rebuildable summary.

Summary

  • Start from the queries and write the partition key and sort order for each before any CQL.
  • One table per query: orders by id, orders by user, events by user and day, and a counter.
  • Bucket unbounded data (events) by a time unit, and let a TTL expire old rows.
  • A "latest N" query across buckets walks back bucket by bucket, so limit the walk.
  • Pick a consistency level per operation: LOCAL_QUORUM for orders, ONE for clicks.
  • Writing several tables means more writes and partial failures: use idempotent upserts and keep one source of truth.

Next: Project: a knowledge graph.