Cassandra data modelling, query-first, worked

What it is

Cassandra data modelling inverts the relational process. In a relational database you model the domain: entities, relationships, third normal form, and then you write whatever queries you need, because the query planner will find a way and you can add an index later. In Cassandra you model the queries: you enumerate every access pattern first, and then design a table per query, accepting that the same data is written several times.

The reason is structural rather than stylistic. Cassandra has no joins, no cross-partition aggregation, and no query planner that can rescue a poorly-matched schema. A query either maps onto a single partition's storage layout or it does not, and if it does not, no amount of tuning helps. The schema is the access plan.

The primary key has two parts and conflating them is the most common error:

PRIMARY KEY ((partition_key), clustering_col_1, clustering_col_2)
              ^^^^^^^^^^^^^   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
              WHERE the data  HOW it is sorted WITHIN the partition
              lives (a node)

The partition key is hashed to place the partition on nodes. Every query must supply it in full, because without it Cassandra does not know which node to ask. The clustering columns determine sort order inside the partition, which is what makes range queries and ORDER BY possible without a sort.

What this is confused with: denormalisation in Cassandra is not a performance optimisation you apply later, it is the design method. Writing the same logical row into four tables is correct and expected, not a compromise. Cassandra's writes are cheap (see LSM trees vs B-trees) and its reads are only fast when they touch one partition, so the trade is deliberate.

The problem it solves

The failure Cassandra prevents is the one relational systems permit: a query whose cost grows with the data. A relational join across two large tables works fine until it does not, and the transition is gradual and hard to predict. Cassandra makes the expensive query impossible to express rather than slow, which is a design choice worth naming, because it is why the modelling process feels restrictive.

Concretely, three things it refuses:

Queries without the partition key. SELECT * FROM users WHERE email = ? when email is not the partition key requires contacting every node. Cassandra makes you write ALLOW FILTERING to do it, which is a deliberate speed bump on a query that will time out in production.

Aggregations across partitions. SELECT count(*) FROM events reads the entire cluster. It works on a laptop with test data and times out on real data, which is the worst failure profile available.

Anything requiring a join. There is no mechanism, so the join happens at write time by writing to several tables, or at read time in your application by issuing several single-partition queries.

The problem it creates in exchange, and the one most of this page is about: you must know your queries before you design the schema, and adding an unanticipated query later means a new table and a backfill.

Mechanics

The process

Enumerate the access patterns. Every read the application performs, with its inputs and its expected result cardinality. This is the whole design.
One table per query pattern. Name tables after the query (bookings_by_customer_and_date) rather than the entity.
Choose the partition key from the query's fixed inputs, and check that it distributes evenly and bounds partition size.
Choose clustering columns from the query's ordering and range requirements.
Verify partition size, which is the step that gets skipped and is the one that causes production incidents.

Partition sizing is the constraint

A partition is stored contiguously and read as a unit. Cassandra's operational limits:

Metric	Warn	Fail	Why
Partition size	100 MB	1 GB+	Read latency, heap pressure, repair time
Cells per partition	100,000	2 billion (hard)	Read path allocates per cell
Rows per partition	~100,000 practical		Compaction and repair cost

Estimate before creating the table, with arithmetic rather than intuition:

Table: readings_by_sensor
Partition key: sensor_id
Rows per partition = readings per sensor over the table's lifetime

  1 reading / 10 seconds x 86,400 s/day = 8,640 rows/day
  Retained 2 years                       = 6.3 million rows per partition
  At ~120 bytes per row                  = 756 MB per partition

VERDICT: far too large. Add a time bucket to the partition key.

The fix is bucketing: put a time component into the partition key so partitions are bounded by construction.

CREATE TABLE readings_by_sensor (
    sensor_id   uuid,
    day         date,          -- the bucket
    reading_at  timestamp,
    value       double,
    PRIMARY KEY ((sensor_id, day), reading_at)
) WITH CLUSTERING ORDER BY (reading_at DESC);

-- Now: 8,640 rows x 120 bytes = ~1 MB per partition. Comfortable.

The cost of bucketing, which must be stated: a query spanning several buckets becomes several queries. Reading a week means seven partition reads, issued in parallel by the driver and merged by the application. That is the trade, and choosing the bucket granularity is choosing how often you pay it.

Bucket = hour:  360 rows/partition. Tiny. A 1-day query = 24 partition reads.
Bucket = day:   8,640 rows. Good. A 1-week query = 7 reads.
Bucket = month: 260k rows, ~31 MB. Acceptable. A 1-day query reads a 31 MB partition.

Size the bucket so the most common query reads one or a few partitions, and check that the resulting partition stays under about 100 MB. Those two constraints usually pin it within a factor of two.

Worked schema: a booking system

Access patterns, enumerated first:

Q1. Get a booking by its ID
Q2. List a customer's bookings, most recent first
Q3. List a property's bookings for a date range
Q4. List bookings by status for an admin dashboard (bounded by property)
Q5. Find a booking by confirmation code (customer support)

Five queries, five tables:

-- Q1: point lookup by ID.
CREATE TABLE bookings_by_id (
    booking_id      uuid PRIMARY KEY,
    customer_id     uuid,
    property_id     uuid,
    check_in        date,
    check_out       date,
    status          text,
    confirmation    text,
    total_cents     bigint
);

-- Q2: a customer's bookings, newest first.
CREATE TABLE bookings_by_customer (
    customer_id     uuid,
    created_at      timestamp,
    booking_id      uuid,
    property_id     uuid,
    check_in        date,
    status          text,
    total_cents     bigint,
    PRIMARY KEY ((customer_id), created_at, booking_id)
) WITH CLUSTERING ORDER BY (created_at DESC);
-- Partition size: a heavy customer might have 500 bookings. ~100 KB. Fine.

-- Q3: a property's bookings in a date range. Bucketed by month.
CREATE TABLE bookings_by_property_month (
    property_id     uuid,
    month           text,           -- '2026-08'
    check_in        date,
    booking_id      uuid,
    customer_id     uuid,
    status          text,
    PRIMARY KEY ((property_id, month), check_in, booking_id)
);
-- Partition size: a property has at most ~31 bookings a month. Tiny.

-- Q4: by status, scoped to a property so the partition is bounded.
CREATE TABLE bookings_by_property_status (
    property_id     uuid,
    status          text,
    created_at      timestamp,
    booking_id      uuid,
    customer_id     uuid,
    PRIMARY KEY ((property_id, status), created_at, booking_id)
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Q5: lookup by confirmation code.
CREATE TABLE bookings_by_confirmation (
    confirmation    text PRIMARY KEY,
    booking_id      uuid,
    customer_id     uuid,
    property_id     uuid
);

One booking is written to five tables. The write is a BATCH scoped to one logical entity:

BEGIN BATCH
  INSERT INTO bookings_by_id (...) VALUES (...);
  INSERT INTO bookings_by_customer (...) VALUES (...);
  INSERT INTO bookings_by_property_month (...) VALUES (...);
  INSERT INTO bookings_by_property_status (...) VALUES (...);
  INSERT INTO bookings_by_confirmation (...) VALUES (...);
APPLY BATCH;

A logged batch here is correct, and this is the one legitimate use of BATCH in Cassandra: atomicity across denormalised copies of the same logical write. The batch log guarantees all statements eventually apply, which is what stops the copies diverging. It is not isolated (readers can see partial results) and it is not a performance feature.

Using BATCH to group unrelated writes to improve throughput is the anti-pattern: the coordinator must fan out to every partition involved, so a large multi-partition batch makes one node do work that should have been spread across the cluster, and throughput gets worse rather than better.

Updating a clustering column means delete and insert

status appears as a clustering-key component in bookings_by_property_status. Changing a booking's status is not an update there; it is a delete of the old row and an insert of the new one:

BEGIN BATCH
  DELETE FROM bookings_by_property_status
   WHERE property_id = ? AND status = 'PENDING' AND created_at = ? AND booking_id = ?;
  INSERT INTO bookings_by_property_status (property_id, status, created_at, booking_id, customer_id)
       VALUES (?, 'CONFIRMED', ?, ?, ?);
APPLY BATCH;

That delete writes a tombstone, and a status column that changes often generates tombstones steadily. This is why putting a mutable value in a key is a decision to examine: it works, and it costs you tombstones proportional to the mutation rate. See Cassandra tombstones.

Secondary indexes, and why not

CREATE INDEX ON bookings_by_id (status);        -- almost always wrong

A native secondary index is stored locally on each node, indexing only that node's data. A query on it with no partition key must therefore contact every node, gather partial results, and merge them: a scatter-gather whose latency is the slowest node's and whose cost grows with cluster size. It is the opposite of what Cassandra is good at.

Secondary indexes are defensible only when the query also supplies the partition key, narrowing to one node, and even then a purpose-built table is usually better.

SASI indexes are deprecated. Storage-Attached Indexes (SAI), added in Cassandra 5.0, are a genuine improvement: better performance, support for numeric ranges, and lower write overhead, and they are still local per node, so the scatter-gather property remains. SAI makes secondary indexing viable for low-cardinality filtering within a known partition set; it does not make it a substitute for query-first modelling.

Materialized views exist and remain marked experimental with known consistency issues (they can diverge from the base table under certain failure sequences, and there is no automatic repair). The standard advice, which I follow, is to write the extra table from the application instead. That is more code and it is code whose failure modes you can see.

A worked example: a partition that grew for eleven months

A logistics platform tracking package scan events. The original table:

CREATE TABLE scans_by_route (
    route_id    uuid,
    scanned_at  timestamp,
    package_id  uuid,
    facility    text,
    status      text,
    PRIMARY KEY ((route_id), scanned_at, package_id)
) WITH CLUSTERING ORDER BY (scanned_at DESC);

The design intent was reasonable: the main query was "show the scan history for a route," which this serves in one partition read.

What nobody computed. A route is a long-lived entity, some of them years old, and a busy route sees roughly 40,000 scans a day.

40,000 scans/day x 365 days x ~180 bytes = 2.6 GB per partition per year

Symptoms after eleven months in production:

p99 read on scans_by_route:      40ms -> 4,200ms  (degraded gradually)
largest partition:               3.1 GB
nodes with heap pressure:        4 of 15
repair time:                     11 hours -> 3 days (and often failed)
compaction on the largest
  partition:                     could not complete within the timeout

Why each of those happens is worth spelling out, because they are all the same root cause:

Reads slowed because a partition read materialises rows in the coordinator's heap. A query for the last 50 scans reads only 50 rows, and Cassandra must still seek within a 3 GB partition and traverse its index, and any query without a tight clustering bound reads far more.
Heap pressure because the read path allocates per cell, and a wide partition read can allocate hundreds of megabytes in one request.
Repair took days because repair operates at partition granularity: comparing a 3 GB partition between replicas means streaming and Merkle-tree comparison over the whole thing, and a failure restarts it.
Compaction could not finish because merging SSTables containing that partition meant rewriting gigabytes for one key.

The gradual degradation is what made it hard: nothing alerted, because no single day was much worse than the one before.

The redesign. They enumerated the actual queries, which nobody had written down:

Q1. Scan history for a route, most recent first, usually last 100  (90% of traffic)
Q2. Scan history for a route for a specific day                    (8%)
Q3. All scans at a facility for a day                              (2%, ops)

Q1 asks for recent scans, which the original schema served by reading a huge partition sorted by time. Bucketing by day matched the access pattern exactly:

CREATE TABLE scans_by_route_day (
    route_id    uuid,
    day         date,
    scanned_at  timestamp,
    package_id  uuid,
    facility    text,
    status      text,
    PRIMARY KEY ((route_id, day), scanned_at, package_id)
) WITH CLUSTERING ORDER BY (scanned_at DESC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_unit': 'DAYS',
                    'compaction_window_size': 1}
  AND default_time_to_live = 15552000;    -- 180 days

CREATE TABLE scans_by_facility_day (
    facility    text,
    day         date,
    scanned_at  timestamp,
    package_id  uuid,
    route_id    uuid,
    status      text,
    PRIMARY KEY ((facility, day), scanned_at, package_id)
) WITH CLUSTERING ORDER BY (scanned_at DESC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy', ...}
  AND default_time_to_live = 15552000;

Q1 now reads today's partition first and walks back a day at a time only if it needs more than that day holds, which for a busy route it never does. Q3 got its own table instead of an ALLOW FILTERING query that had been quietly timing out.

Measured after migration:

                              before        after
p99 read (Q1)                 4,200ms       11ms
largest partition             3.1 GB        7.2 MB
repair time (full cluster)    3 days        4 hours
heap pressure incidents/mo    ~6            0
disk used                     28 TB         9 TB    (TTL now actually reclaims)
rows written per scan         1             2       (the denormalisation cost)

Two things about this deserve emphasis.

The disk drop from 28 TB to 9 TB was not from the schema change alone. The original table had no TTL, and adding one under size-tiered compaction would have reclaimed space by merging huge partitions. Combining the bucketed schema with TWCS meant expiry became dropping whole files (see compaction strategies), which is why the reclamation actually happened rather than being permanently backlogged.

The write cost doubled, from one row per scan to two, and that is the correct trade stated plainly: Cassandra writes are cheap and wide-partition reads are not. At 40,000 scans a day per route the extra write is invisible; the 4.2-second read was not.

The lesson: partition size is not a tuning parameter, it is a schema property, fixed at design time and only changeable by writing a new table and backfilling. The five minutes of arithmetic at design time (rows/day x retention x row size) would have prevented eleven months of gradual degradation and a migration.

Production evidence

DataStax's data modelling methodology (Chebotko diagrams, and the "query-first" process) is the published formalisation of this approach: enumerate queries, derive tables, verify partition size. It is taught this way because teams arriving from relational backgrounds reliably design entity tables first and discover the problem in production.

Cassandra's own nodetool tablehistograms reports partition size percentiles, and the compaction_large_partition_warning_threshold_mb setting (default 100 MB) exists because wide partitions are the most common production failure. A warning threshold in the default configuration is a strong signal about failure frequency.

Apple, Netflix and Discord have all published on running Cassandra at scale, and Discord's account of their message storage is the clearest public worked example of bucketing: messages partitioned by (channel_id, bucket) where the bucket is a fixed time window, chosen precisely to bound partition size for channels with wildly different message rates.

Materialized views were marked experimental in Cassandra 3.11 and have stayed that way, with the project's own guidance recommending application-managed denormalised tables instead. A feature that ships and is then officially discouraged is worth knowing about specifically so you do not reach for it.

Storage-Attached Indexes (SAI) landed in Cassandra 5.0 (CEP-7), developed from DataStax's work, explicitly to make secondary indexing usable where previous implementations were not. The proposal's own framing is that it improves the local index, which is the honest scope.

The debate

Is query-first modelling too rigid? The objection is real: you must know your queries in advance, and a new access pattern means a new table and a backfill, which in a relational database would have been an index. For a product still discovering its shape, that is genuine friction.

My position: the rigidity is the feature, and it is also a reason not to choose Cassandra. If your access patterns are unknown or changing weekly, Cassandra is the wrong database and you should use PostgreSQL until the patterns stabilise. Cassandra earns its place when you have a known, stable, high-volume access pattern and need linear scalability and multi-region writes. Choosing it for flexibility you then have to work around is the common mistake.

How much denormalisation is too much? Each table is another write and another thing to keep consistent. Five tables per entity is normal; fifteen suggests you are modelling a query surface that wants a different database or a search index. A useful check: if a table exists to serve a query issued a few times a day by an internal tool, that query probably belongs in an analytics store fed by CDC rather than in the operational schema.

Bucket granularity. The trade is partition size against the number of partitions a query touches. Size the bucket so the dominant query reads one partition and the result stays under about 100 MB. If those two constraints conflict, the access pattern and the data volume are mismatched and something else has to change: shorter retention, a narrower row, or a different data store for the long tail.

Secondary indexes: is SAI enough to change the advice? Partly. SAI makes filtering within a known partition set genuinely usable, which removes some of the need for narrow single-purpose tables. It is still a local index, so a query without a partition key is still scatter-gather, and the core advice holds: model for the queries that matter, use SAI for the low-frequency filtered ones. Treating SAI as a general-purpose index is how you rediscover why the advice existed.

Should you use BATCH? Only for atomicity across denormalised copies of the same logical write, which is exactly the booking example. A multi-partition batch of unrelated writes makes the coordinator fan out to every partition, concentrating work on one node that should have been spread across the cluster. BATCH is a consistency tool that costs throughput, and using it for throughput gets you the opposite.

Follow-up Q&A

"How do you decide the partition key?"

From the query's fixed inputs first: whatever the query always supplies is a candidate, because a query without the full partition key cannot be served. Then check three things. Distribution: does it spread evenly, or does one value take a disproportionate share (a partition key of country on a service that is 70 percent one country is a hot partition). Bounded growth: compute rows per partition over the table's lifetime, and if it grows without bound, add a bucket. Query locality: does the dominant query read one partition, or does bucketing force it to read twenty.

"How do you know a partition is too big?"

Compute it at design time: rows per day times retention days times row size. Over about 100 MB is a warning and over 1 GB is a problem. In production, nodetool tablehistograms gives partition size percentiles and Cassandra logs a warning above compaction_large_partition_warning_threshold_mb. The reason to compute rather than observe is that the degradation is gradual, so nothing alerts until it is bad, and by then the fix is a new table and a backfill.

"Why is a secondary index usually wrong?"

It is a local index: each node indexes only its own data. A query on it without a partition key contacts every node, gathers partial results and merges them, so latency is the slowest node's and cost grows with cluster size. That is scatter-gather, which is what Cassandra's whole design avoids. SAI in 5.0 makes the local index much better and does not change the scatter-gather property.

"What is the legitimate use of BATCH?"

Atomicity across the denormalised copies of one logical write: the same booking going into five tables. The batch log guarantees all statements eventually apply, which is what keeps the copies from diverging. It is not isolated, so readers can see partial results, and it is not a throughput optimisation. Batching unrelated writes across many partitions concentrates fan-out on one coordinator and reduces throughput.

"A new query arrives that the schema does not serve. What do you do?"

Create a table for it and backfill, which is the honest answer and the cost of the model. The steps: design the table for the query, dual-write from the application so new data lands in both, backfill historical data from the source table with a paged scan, verify counts, then switch reads over. The mistake to avoid is serving it with ALLOW FILTERING as a stopgap, because that works on staging data and times out in production, and it will be forgotten until it does.

"Why not use materialized views?"

They remain marked experimental, with known cases where a view diverges from its base table under certain failure sequences and no automatic repair to reconcile them. The project's own guidance recommends application-managed tables. That is more code, and it is code whose failure modes are visible to you rather than hidden in the database.

Common misconceptions

"Denormalisation is a performance optimisation." In Cassandra it is the design method. Writing the same logical row to five tables is the intended shape, not a compromise you make under pressure.

"You can add an index later like in Postgres." A native secondary index is local per node, so a query on it without the partition key is scatter-gather. The Cassandra equivalent of "add an index" is "add a table and backfill."

"ALLOW FILTERING is a way to run the query." It is a way to run a query whose cost is unbounded. It works in development where the data is small and times out in production, which is the worst possible failure profile because it passes review.

"BATCH improves write throughput." Only when all statements target the same partition. A multi-partition batch makes one coordinator fan out to every involved node, which is more work concentrated in one place.

"Partition size is an operational concern." It is a schema property fixed at design time. The only fix is a new table and a backfill, which is why the arithmetic belongs in the design review.

Interview delivery note

Say this verbatim: "I model the queries, not the entities: one table per access pattern, and the first thing I compute is rows per partition over the table's lifetime, because partition size is a schema property you cannot tune later. Over 100 MB and I add a time bucket to the partition key." Method plus the specific check plus the threshold, which is a complete answer.

The senior-versus-staff separator is partition growth over the table's lifetime rather than at launch. A senior engineer picks a sensible partition key and gets the immediate distribution right. A staff engineer computes rows/day x retention x row size, notices that a route accumulating 40,000 scans a day reaches 2.6 GB in a year, and adds the bucket before the table exists. The failure is gradual and never alerts, so it is only caught at design time.

The second signal is knowing that a mutable value in a clustering key means every change is a delete plus an insert, generating tombstones proportional to the mutation rate. That connects the schema decision to the operational consequence, which is the thing query-first modelling is really about.

The Staff & Team Lead Interview Reference