intermediate~2h

Cassandra — CQL, Secondary Indexes & Materialized Views

CQL reads like SQL, which is exactly what makes its restrictions surprising the first time you hit one. This chapter covers what CQL will and won't let you query, and the two ways to add a second query pattern to an existing table — one of them a trap.

Learning objectives

Write basic CQL that respects Cassandra's partition-key query restrictions.
Explain why a secondary index query without a partition key has to fan out to every node.
Explain what a materialized view automates, and why most production teams still prefer denormalized tables.

Basic CQL genuinely does look like SQL:

SELECT order_id, total, status FROM orders_by_user
WHERE user_id = ? AND order_date > '2026-01-01'
ORDER BY order_date DESC
LIMIT 20;

INSERT INTO orders_by_user (user_id, order_date, order_id, status, total)
VALUES (?, ?, ?, 'PENDING', ?);

UPDATE orders_by_user SET status = 'SHIPPED' WHERE user_id = ? AND order_date = ? AND order_id = ?;

DELETE FROM orders_by_user WHERE user_id = ? AND order_date = ? AND order_id = ?;

What's quietly missing, all for the same underlying reason — Cassandra has no query planner that can efficiently combine data living on different nodes:

No JOIN. Chapter 01's whole query-first, duplicate-per-query-pattern approach exists specifically because this isn't available.
No arbitrary WHERE clause. A SELECT must restrict by the full partition key (an equality match) before Cassandra will run it efficiently — Chapter 01 §2 covers why.
Clustering-column filters must stay in key order. You can range-filter order_date because it's the first clustering column; you can't filter a later clustering column without also restricting the ones before it, for the same reason you can't use a multi-column SQL index efficiently while skipping its leftmost column.
No subqueries.
Aggregate functions exist (COUNT, SUM, AVG) but scan whatever partitions they're allowed to touch. A cluster-wide aggregate without a partition-key restriction is a full-cluster scan and is discouraged at any real scale for exactly that reason.

💻 Code example

SELECT order_id, total, status FROM orders_by_user
WHERE user_id = ? AND order_date > '2026-01-01'
ORDER BY order_date DESC
LIMIT 20;

◆ The problem

A developer used to SQL adds CREATE INDEX ON orders_by_user (status);, expecting it to behave like a familiar B-tree index — a fast lookup from anywhere in the table, by any indexed value. It doesn't behave that way, and the reason is structural, not a missing optimization.

A Cassandra secondary index is built and stored locally, per node — each node only indexes the rows it physically holds. There is no global index telling the coordinator which node(s) hold rows matching status = 'PENDING'. So a query that filters only by the indexed column, with no partition key, has to be sent to every node in the cluster; each node scans its own local index and returns whatever it finds; the coordinator merges the results. This is a scatter-gather query, and it gets more expensive as the cluster grows — the opposite of what adding nodes is supposed to buy you. It's especially bad on a low-cardinality column like status, where each node ends up returning a large fraction of its own data.

A secondary index used alongside a partition-key restriction — narrow to one known partition first, then filter further by the indexed column — is fine; the fan-out problem only bites when the index is the sole thing narrowing the query.

▲ Common mistake

Adding a secondary index as a quick fix to make an ad hoc, no-partition-key query "just work," then being surprised it doesn't scale in production the way a SQL index would. This is one of the most common real interview questions and real production incidents in Cassandra. The fix is almost always Chapter 01's actual answer: build a dedicated table for that query pattern (orders_by_status) instead of indexing around the problem.

CREATE MATERIALIZED VIEW orders_by_status AS
    SELECT user_id, order_date, order_id, status, total
    FROM orders_by_user
    WHERE status IS NOT NULL AND user_id IS NOT NULL
      AND order_date IS NOT NULL AND order_id IS NOT NULL
    PRIMARY KEY (status, user_id, order_date, order_id);

A materialized view is Cassandra's own version of Chapter 01's "one table per query pattern" idea, but automated: you keep writing only to the base table, orders_by_user, and Cassandra itself propagates every write to orders_by_status behind the scenes, keeping the second table's partition key (status) in sync without your application code ever writing to it directly.

💻 Code example

CREATE MATERIALIZED VIEW orders_by_status AS
    SELECT user_id, order_date, order_id, status, total
    FROM orders_by_user
    WHERE status IS NOT NULL AND user_id IS NOT NULL
      AND order_date IS NOT NULL AND order_id IS NOT NULL
    PRIMARY KEY (status, user_id, order_date, order_id);

▲ Common mistake

Treating materialized views as a strictly-better, maintenance-free replacement for Chapter 01's manual denormalized tables. Despite the convenience, materialized views have a genuinely rockier operational track record than the rest of Cassandra — the Apache Cassandra project itself has long flagged them as needing caution in production, with real, documented cases of a view silently drifting out of sync with its base table around node failure, repair, or streaming events.

The structural reason is worth knowing: a base-table write's consistency is something you control directly with the levels from Chapter 02. The propagation from base table to materialized view happens as an internal step you don't get to set a consistency level for — you can't ask a materialized-view read for the same explicit guarantee you can ask a base-table read for.

Given that, the recommendation here for a second query pattern is Chapter 01's manual approach: the application writes to both tables itself. It's more code, and it inherits the same eventual-consistency-between-copies trade-off any two independently-updated tables would have — but it's the well-understood, battle-tested version of the same idea. Materialized views are worth knowing exist and worth understanding what they promise; they're worth defaulting away from for anything that matters in production.

Want a visual for this concept?

Generate a diagram tailored to “Cassandra — CQL, Secondary Indexes & Materialized Views” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to Spring Boot with Cassandra — Spring Data Cassandra →← Back to all Cassandra chapters