Materialized View Concepts: Automatic Derived Tables and Primary-Key Requirements

Learning outcomes

AtlasMart already stores orders by tenant/day, but customer support needs a second access path by customer. An engineer proposes a Cassandra materialized view because it “automatically creates another table.” Before writing CQL, the team must answer a harder question: what exactly is automatic, which primary-key shapes are legal, and what operational contract is being accepted for an experimental feature?

Terms and mental model

A base table is the Cassandra table the application writes directly. A materialized view (MV) is an automatically maintained Cassandra table derived from one base table with a different primary-key layout; applications cannot mutate the view directly. In Cassandra 5.0 this feature is explicitly experimental. A derived table is a broader design idea: any table whose rows are produced from authoritative data for another read pattern. An explicit projection or query table is a derived table owned by application/stream/reconciliation logic rather than Cassandra's MV mechanism. Storage-Attached Indexing (SAI) is Cassandra 5.0's storage-integrated secondary indexing framework and is a different way to support non-primary-key predicates. Change Data Capture (CDC) exposes durable portions of commit-log segments for an external consumer; Cassandra does not turn those raw records into a projection for you.

A primary key determines Cassandra partition placement and clustering order. The partition key is hashed to a token; replicas store token ranges according to the keyspace replication strategy. A request-scoped coordinator routes work to replicas and waits for the chosen consistency level (CL). A view replica stores a materialized-view partition, whose partition key can hash to a different token than the corresponding base row. Batchlog is Cassandra's durable coordination mechanism for certain multi-mutation operations and is used internally by MV maintenance; it is not a relational transaction log. Hints, repair, and streaming are separate convergence mechanisms. An immutable SSTable (Sorted String Table) stores flushed table/view data on disk and is managed by compaction.

1. Start with feature status, not syntax

In Apache Cassandra 5.0, materialized views remain experimental, are disabled by default through materialized_views_enabled, and are not recommended for production use by the official configuration documentation. That status changes the architecture conversation: the learning goal is to understand the mechanism and its tradeoffs, not to normalize it as the default way to model every alternate query.

A view is internally a table derived from one base table. You write the base; Cassandra maintains corresponding view rows. The view has its own partition key and therefore its own token placement. A base row and its view row can live on different paired replicas. That is why “it is just another local index” is the wrong mental model.

2. Build an isolated MV-enabled Cassandra 5.0.9 cluster

The shared course image keeps Cassandra defaults. To make this chapter reproducible without mutating earlier labs, build a derived image that changes only two feature flags. The Dockerfile is intentionally tiny and pinned. On Windows, Docker Desktop with WSL/Git Bash or the PowerShell equivalent below is the supported learning path; do not treat this as native Windows production Cassandra.

mkdir -p chapter20-derived-imageprintf '%s\n' \  'FROM cassandra:5.0.9' \  'USER root' \  "RUN sed -ri 's/^[[:space:]#]*materialized_views_enabled:.*/materialized_views_enabled: true/' /etc/cassandra/cassandra.yaml \\\" \  " && sed -ri 's/^[[:space:]#]*cdc_enabled:.*/cdc_enabled: true/' /etc/cassandra/cassandra.yaml \\\" \  " && grep -q '^materialized_views_enabled: true' /etc/cassandra/cassandra.yaml \\\" \  " && grep -q '^cdc_enabled: true' /etc/cassandra/cassandra.yaml" \  'USER cassandra' \  > chapter20-derived-image/Dockerfiledocker build -t atlasmart-cassandra-derived:5.0.9 chapter20-derived-imagedocker run --rm atlasmart-cassandra-derived:5.0.9 sh -lc \  "grep -E '^(materialized_views_enabled|cdc_enabled|materialized_views_on_repair_enabled):' /etc/cassandra/cassandra.yaml || true"

New-Item -ItemType Directory -Force chapter20-derived-image | Out-Null@'FROM cassandra:5.0.9USER rootRUN sed -ri 's/^[[:space:]#]*materialized_views_enabled:.*/materialized_views_enabled: true/' /etc/cassandra/cassandra.yaml \ && sed -ri 's/^[[:space:]#]*cdc_enabled:.*/cdc_enabled: true/' /etc/cassandra/cassandra.yaml \ && grep -q '^materialized_views_enabled: true' /etc/cassandra/cassandra.yaml \ && grep -q '^cdc_enabled: true' /etc/cassandra/cassandra.yamlUSER cassandra'@ | Set-Content -Encoding ascii chapter20-derived-image/Dockerfiledocker build -t atlasmart-cassandra-derived:5.0.9 chapter20-derived-imagedocker run --rm atlasmart-cassandra-derived:5.0.9 sh -lc "grep -E '^(materialized_views_enabled|cdc_enabled):' /etc/cassandra/cassandra.yaml"

docker network inspect atlasmart-derived-net >/dev/null 2>&1 || docker network create atlasmart-derived-netdocker volume create atlasmart-derived-cass-1-datadocker volume create atlasmart-derived-cass-2-datadocker volume create atlasmart-derived-cass-3-datadocker inspect atlasmart-derived-cass-1 >/dev/null 2>&1 || docker run -d --name atlasmart-derived-cass-1 --hostname atlasmart-derived-cass-1 --network atlasmart-derived-net -e CASSANDRA_CLUSTER_NAME=atlasmart-derived-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -v atlasmart-derived-cass-1-data:/var/lib/cassandra atlasmart-cassandra-derived:5.0.9# Wait for node 1 to become UN before starting peers.docker exec atlasmart-derived-cass-1 nodetool statusdocker inspect atlasmart-derived-cass-2 >/dev/null 2>&1 || docker run -d --name atlasmart-derived-cass-2 --hostname atlasmart-derived-cass-2 --network atlasmart-derived-net -e CASSANDRA_CLUSTER_NAME=atlasmart-derived-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-derived-cass-1 -v atlasmart-derived-cass-2-data:/var/lib/cassandra atlasmart-cassandra-derived:5.0.9docker inspect atlasmart-derived-cass-3 >/dev/null 2>&1 || docker run -d --name atlasmart-derived-cass-3 --hostname atlasmart-derived-cass-3 --network atlasmart-derived-net -e CASSANDRA_CLUSTER_NAME=atlasmart-derived-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-derived-cass-1 -v atlasmart-derived-cass-3-data:/var/lib/cassandra atlasmart-cassandra-derived:5.0.9# Continue only when all nodes are UN in dc1.docker exec atlasmart-derived-cass-1 nodetool versiondocker exec atlasmart-derived-cass-1 java -versiondocker exec atlasmart-derived-cass-1 nodetool statusdocker exec atlasmart-derived-cass-1 sh -lc "grep -E '^(materialized_views_enabled|cdc_enabled|materialized_views_on_repair_enabled):' /etc/cassandra/cassandra.yaml || true"

3. Derive the view key from the base key

CREATE KEYSPACE IF NOT EXISTS atlasmart_derivedWITH replication = {'class':'NetworkTopologyStrategy','dc1':3};CREATE TABLE IF NOT EXISTS atlasmart_derived.orders_by_day (    tenant_id text,    order_day date,    order_id uuid,    customer_id text,    created_at timestamp,    status text,    total decimal,    shipping_city text,    note text,    updated_at timestamp,    PRIMARY KEY ((tenant_id,order_day),order_id)) WITH compaction = {'class':'UnifiedCompactionStrategy'}  AND cdc = false;CONSISTENCY LOCAL_QUORUM;

CONSISTENCY LOCAL_QUORUM;INSERT INTO atlasmart_derived.orders_by_day (tenant_id,order_day,order_id,customer_id,created_at,status,total,shipping_city,note,updated_at) VALUES ('tenant-a','2026-09-08',20000000-0000-0000-0000-000000000001,'cust-42','2026-09-08T08:00:00Z','PAID',129.90,'Baku','gift','2026-09-08T08:00:00Z');INSERT INTO atlasmart_derived.orders_by_day (tenant_id,order_day,order_id,customer_id,created_at,status,total,shipping_city,note,updated_at) VALUES ('tenant-a','2026-09-08',20000000-0000-0000-0000-000000000002,'cust-42','2026-09-08T08:10:00Z','PACKING',59.00,'Baku','fragile','2026-09-08T08:10:00Z');INSERT INTO atlasmart_derived.orders_by_day (tenant_id,order_day,order_id,customer_id,created_at,status,total,shipping_city,note,updated_at) VALUES ('tenant-a','2026-09-08',20000000-0000-0000-0000-000000000003,'cust-99','2026-09-08T08:20:00Z','PAID',210.00,'Ganja','priority','2026-09-08T08:20:00Z');INSERT INTO atlasmart_derived.orders_by_day (tenant_id,order_day,order_id,customer_id,created_at,status,total,shipping_city,note,updated_at) VALUES ('tenant-a','2026-09-07',20000000-0000-0000-0000-000000000004,'cust-42','2026-09-07T18:00:00Z','SHIPPED',90.50,'Baku','','2026-09-07T18:00:00Z');SELECT tenant_id,order_day,order_id,customer_id,status,totalFROM atlasmart_derived.orders_by_dayWHERE tenant_id='tenant-a' AND order_day='2026-09-08';

The base primary key is ((tenant_id, order_day), order_id). A legal materialized-view primary key must contain all base primary-key columns—tenant_id, order_day, and order_id—and may add only one base non-primary-key column. AtlasMart adds customer_id, producing ((tenant_id, customer_id, order_day), order_id). Every column used in the view primary key must be restricted as non-null in the view's WHERE clause.

CREATE MATERIALIZED VIEW atlasmart_derived.orders_by_customer_mv ASSELECT tenant_id,order_day,order_id,customer_id,created_at,status,total,shipping_cityFROM atlasmart_derived.orders_by_dayWHERE tenant_id IS NOT NULL  AND order_day IS NOT NULL  AND order_id IS NOT NULL  AND customer_id IS NOT NULLPRIMARY KEY ((tenant_id,customer_id,order_day),order_id)WITH compaction = {'class':'UnifiedCompactionStrategy'};SELECT order_id,created_at,status,totalFROM atlasmart_derived.orders_by_customer_mvWHERE tenant_id='tenant-a' AND customer_id='cust-42' AND order_day='2026-09-08';

docker exec atlasmart-derived-cass-1 cqlsh -e "DESCRIBE KEYSPACE atlasmart_derived"docker exec atlasmart-derived-cass-1 nodetool viewbuildstatus atlasmart_derived orders_by_customer_mvdocker exec atlasmart-derived-cass-1 nodetool getconcurrentviewbuilders# Implementation-detail tables: useful for this pinned lab, not an application API.docker exec atlasmart-derived-cass-1 cqlsh -e "SELECT * FROM system.built_views;" || truedocker exec atlasmart-derived-cass-1 cqlsh -e "SELECT * FROM system.view_builds_in_progress;" || true

If the base data existed before the view, Cassandra performs an initial build. nodetool viewbuildstatus is the supported operational evidence for that build. The exact output/timing is dataset- and node-dependent; do not claim the view is ready just because schema agreement completed.

4. Deliberately invalid keys reveal the design boundary

-- customer_id and created_at are both non-primary-key columns in the base.-- A materialized-view primary key may add only one such column.CREATE MATERIALIZED VIEW atlasmart_derived.orders_by_customer_time_bad ASSELECT * FROM atlasmart_derived.orders_by_dayWHERE tenant_id IS NOT NULL  AND order_day IS NOT NULL  AND order_id IS NOT NULL  AND customer_id IS NOT NULL  AND created_at IS NOT NULLPRIMARY KEY ((tenant_id,customer_id,order_day),created_at,order_id);

This rejection is not an inconvenience to bypass. It tells AtlasMart that a customer timeline ordered by created_at is better represented as an explicit query table whose primary key is designed directly for that read. A second boundary: static columns cannot be included in materialized views, and the view's select/WHERE syntax is intentionally constrained—no arbitrary aggregates, ordering clause, limit, or ALLOW FILTERING.

5. Verification and reset

• materialized_views_enabled: true exists only in the isolated Chapter 20 image.
• All three lab nodes are UN in dc1.
• The view key contains all base primary-key columns plus only customer_id.
• nodetool viewbuildstatus is recorded before view-read claims.
• The invalid multi-extra-column design fails and is replaced by an explicit-table design decision rather than a workaround.

Production judgment

Do not choose a derived-data mechanism because it removes code from one layer. Evaluate who owns correctness, replay, backfill, deletion semantics, schema evolution, failure recovery, repair, observability, and rollback. For Cassandra materialized views, start with the documented fact that the feature is experimental and disabled by default. If an organization still evaluates it, test base/view key shape, update/delete mix, view build duration, write amplification, p95/p99 base-write latency, view-query latency/freshness, replica failures, streaming/repair behavior, disk headroom, compaction/tombstones, and upgrade behavior under the exact Cassandra patch. For explicit query tables or CDC/application projections, budget application/stream complexity, idempotency, checkpointing, retries, poison-event handling, backfill, reconciliation lag, and ownership. For SAI, measure selectivity/fanout, index build/disk cost, query limits, and failure behavior.

Replication factor and consistency level protect Cassandra replica operations but do not turn a derived-data pipeline into a global transaction. Security and tenant boundaries must be part of the query/table/index design; a local secondary access path is not authorization. Driver retry and speculative execution require idempotency analysis. Managed Cassandra services may disable, alter, or omit materialized views/CDC and may expose different repair/backup controls, so verify the actual service contract rather than assuming Apache Cassandra behavior. Keep migration reversible: dual-read comparison, backfill/reconciliation, explicit cutover criteria, and the ability to drop or stop the derived mechanism without losing the authoritative base data. Lesson 2 now follows a base write through view maintenance and a replica failure, separating base-write success from derived-view freshness and recovery.

Summary and next bridge

A Cassandra materialized view is an automatically maintained derived table with strict primary-key rules—not a general indexed view. In 5.0 it remains experimental and disabled by default. Lesson 2 makes the more important operational question observable: what happens to base/view state under propagation, failure, and repair?

Authoritative references

Materialized-view behavior is version-sensitive and the feature remains experimental in Cassandra 5.0. Re-check these sources before promoting any example beyond a disposable lab.

• Apache Cassandra downloads / current 5.0 patch
• Cassandra 5.0 materialized-view CQL rules and limitations
• cassandra.yaml: materialized views, repair replay, CDC flags
• nodetool command index including viewbuildstatus
• Cassandra repair guidance
• Cassandra Change Data Capture
• SAI index reference
• Cassandra data-modeling introduction