Data modeling in Cassandra: design for your queries

If you come to Apache Cassandra from relational databases, your instincts will lead you astray. There are no joins, very limited ad-hoc filtering and no normalization to lean on. Instead, Cassandra asks you to start from your queries and design one table for each of them.

How Cassandra stores data

Each table has a primary key made of two parts:

• the partition key, which is hashed to decide which nodes store the data, and
• optional clustering columns, which sort rows within a partition.

CREATE TABLE sensor_readings (
  sensor_id   uuid,
  day         date,
  reading_ts  timestamp,
  temperature double,
  PRIMARY KEY ((sensor_id, day), reading_ts)
) WITH CLUSTERING ORDER BY (reading_ts DESC);

Here (sensor_id, day) is the composite partition key and reading_ts is the clustering column. All readings for one sensor on one day live together on the same replicas, sorted newest first.

Rule 1: every query should hit one partition

Efficient reads specify the full partition key:

SELECT reading_ts, temperature
FROM sensor_readings
WHERE sensor_id = 5f1c0b8e-2b6a-4c5d-9d3e-2a1f4b6c7d8e
  AND day = '2026-07-28'
LIMIT 100;

A query that can't identify its partition must ask many nodes. Cassandra rejects such queries unless you add ALLOW FILTERING — treat that keyword as a red flag in production code.

Rule 2: one table per query

Suppose the app needs two views of user activity: "a user's recent actions" and "everyone who acted on an item". In SQL that's one table and two indexes. In Cassandra it's two tables:

CREATE TABLE activity_by_user (
  user_id uuid,
  ts      timeuuid,
  item_id uuid,
  action  text,
  PRIMARY KEY (user_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

CREATE TABLE activity_by_item (
  item_id uuid,
  ts      timeuuid,
  user_id uuid,
  action  text,
  PRIMARY KEY (item_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

The application writes each event to both. Denormalization is the normal case in Cassandra — disk is cheap, cross-partition reads are not. A logged BATCH can keep the two writes together when that matters.

Rule 3: keep partitions bounded

A partition key like sensor_id alone would grow forever as readings accumulate. Very large partitions slow down reads, repairs and compaction. Add a time bucket (day, week or month, depending on volume) to the partition key, as in the first example, so each partition stays a manageable size. A common guideline is to keep partitions well under about 100 MB and a few hundred thousand rows.

Rule 4: spread load evenly

A partition key with few distinct values — country, status — concentrates traffic on a few nodes ("hot partitions"). Choose keys with high cardinality and even access, or add a bucket to spread a hot key across several partitions.

Tunable consistency

Each read and write chooses a consistency level. With a replication factor of 3, using QUORUM for both reads and writes means a read always overlaps with the latest successful write (2 + 2 > 3). In multi-datacenter clusters, LOCAL_QUORUM keeps that guarantee within a data center without cross-DC latency.

A modeling checklist

1. List the application's queries first.
2. For each query, design a table whose partition key is in the WHERE clause.
3. Use clustering columns for the sort order the query needs.
4. Bucket partitions that grow over time.
5. Accept duplication; write to all the tables that need the data.