Cassandra: A New Query Is a New Table, Not a New Index Hope

The on-call page was coordinator CPU, not a missing dashboard. An analyst had been given a CQL user “for a one-off.” The query was SELECT * FROM events WHERE day = '2026-08-24' ALLOW FILTERING. day was not the partition key. The cluster did a scatter-gather. Timeouts started on the read path the app actually uses.

We revoked the role and rebuilt the report from the warehouse. The serving table was fine. The model was honest: this table answered events_by_user and nothing else. The lie was that Cassandra would answer a new question because it looks like SQL.

I design Cassandra backwards from the query. If product needs a second query, that is a second table, written twice from the same application event, not an index I hope the coordinator can paper over.

Query first, then keys

Write the CQL you will run in prod. Then pick keys so that CQL is a single-partition (or token-aware few-partition) read.

Partition key — which node owns the data. Every query you care about must specify it. Equality, not a range, unless you really meant a token range job.

Clustering key — sort order inside the partition. Ranges, LIMIT, and “latest N” live here. They do not replace the partition key.

-- Serves: get a user's events for a day, newest first.
CREATE TABLE serving.events_by_user_day (
    user_id    uuid,
    day        date,
    event_ts   timestamp,
    event_id   uuid,
    event_type text,
    payload    text,
    PRIMARY KEY ((user_id, day), event_ts, event_id)
) WITH CLUSTERING ORDER BY (event_ts DESC, event_id ASC);

-- New query: events by type for a day. New table.
CREATE TABLE serving.events_by_type_day (
    event_type text,
    day        date,
    event_ts   timestamp,
    event_id   uuid,
    user_id    uuid,
    payload    text,
    PRIMARY KEY ((event_type, day), event_ts, event_id)
) WITH CLUSTERING ORDER BY (event_ts DESC, event_id ASC);

Same writes, two tables. That feels wasteful until you price a coordinator-wide filter against a 40-node ring. Disk is cheaper than a serving outage.

flowchart TD
  q["Write the query"] --> pk["Partition key from equality"]
  pk --> ck["Clustering for order and range"]
  ck --> t["One table per query"]
  q2["New query"] --> t2["New table, same write path"]
  hope["ALLOW FILTERING"] --> scan["Scatter-gather"]
  scan --> timeout["Coordinator timeout"]

The second table is the feature. ALLOW FILTERING is the incident.

Not a warehouse

I do not run finance rollups on Cassandra. No joins, no ad-hoc group-by, no “add a materialized view later” as a substitute for a warehouse model. Views have their own operational cost and still do not make this a columnar analyzer.

Facts and slowly changing dimensions live where the star schema post says they live. Cassandra holds the keyed read the app or the feature service needs in single-digit milliseconds.

Extract without a full scan

Warehouse loads should not SELECT * across every partition. Options that have not paged me:

• The application writes the same event to Kafka (or the warehouse landing zone) as it writes to Cassandra.
• A change-data path if you operate one and accept the lag and the operational weight.
• A bounded extract that walks known partition keys you already have in silver (user ids for the day), not a token-range surprise on the serving cluster at noon.

from cassandra.cluster import Cluster
from cassandra.query import SimpleStatement

SELECT = SimpleStatement(
    """
    SELECT event_id, event_ts, event_type, payload
    FROM serving.events_by_user_day
    WHERE user_id = %s AND day = %s
    """
)

def extract_user_day(session, user_id, day):
    return session.execute(SELECT, (user_id, day))

That is an extract. A token-range Spark job against the serving DC at 14:00 is a load test you did not schedule.

Bound the partition when you write the CREATE

A partition that is “all events for a user forever” will be fast for a week and hostile in year two. I put time in the partition key when the write rate is unbounded.

-- Bucket if a user can emit more than you want
-- in one partition (aim well under 100 MB).
PRIMARY KEY ((user_id, day), event_ts, event_id)

If product asks “all history for a user,” that is either many partition reads you fan out in the app (known days) or a warehouse query. It is not ALLOW FILTERING on user_id alone when user_id is only half the key.

Compaction and repair time track partition size. Wide partitions are how a “small” cluster misses its p99 SLO.

Dual-write from the application

I prefer the service that already has the event to write Cassandra and emit to Kafka (or the landing bucket) in the same handler, with an outbox if you need the write and the emit to agree.

app event
  ├─ INSERT serving.events_by_user_day
  ├─ INSERT serving.events_by_type_day   -- second query
  └─ produce topic events.raw            -- warehouse / search

Two Cassandra tables plus a log is cheaper than one table and a BI user on the ring. See Kafka vs Pulsar if you are choosing the log, and the Cassandra hub.

FAQ

When is a secondary index acceptable? Low-cardinality, not on the write-hot path, and you measured coordinator load. “So finance can filter” is not that case.

Scylla instead of Cassandra? Same query-first model. Different ops. The ALLOW FILTERING incident still happens if you let CQL look like Snowflake.

Can Spark read SSTables offline? Yes, as a bulk job in a quiet window, throttled, against a copy or a dedicated DC — not as the 14:00 refresh of a dashboard.

What about Lightweight Transactions? They are for rare compare-and-set, not for warehouse uniqueness. Unique grain is a warehouse test.

TTL on serving rows? Fine for features that may disappear. Not for orders finance will ask about in two years. Those land in silver.

Pitfalls

Secondary index on a high-cardinality column “so BI can filter.” Coordinator scatter. You wanted a table.

Partitions that grow without a day (or bucket) in the key. Hot partitions, long repairs, p99 death.

Materialized views as a free second query. They are another write path with failure modes. Budget them like a table.

Sparking the whole ring for a backfill during business hours. Use the write-time log or a quiet window and throttle.

Teaching analysts CQL and ALLOW FILTERING. Teach them the warehouse.

What this means for your pipelines

Cassandra is fast when the query was designed with the table. It is a liability when someone treats CQL like Snowflake. I add a table when I add a query, I keep partitions bounded, and I extract along keys I already know.

The coordinator timeout was not a capacity miss. It was a report that never should have run there. Put the new question in a new table or in the warehouse. Do not hope an index will invent a partition key you did not write.