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.

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.