Review a Use Case and Choose Between Base Table, SAI, Materialized View, or Explicit Projection

Learning outcomes

AtlasMart now has four plausible mechanisms for alternate reads: a correctly modeled base/query table, SAI, a materialized view, and an explicit projection fed by application/CDC/stream logic. The final lesson replaces feature preference with a repeatable decision process.

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 from the query and invariant

“Fewer components” is not automatically lower risk. A materialized view reduces application code but moves correctness/recovery behavior into an experimental Cassandra subsystem. A CDC pipeline adds components but can make lag, replay, ownership, and transformations explicit. The right choice minimizes total system risk for the business invariant.

2. Put SAI beside the view/projection—not underneath them

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"

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;CREATE MATERIALIZED VIEW IF NOT EXISTS 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'};CREATE TABLE IF NOT EXISTS atlasmart_derived.orders_by_customer_projection (    tenant_id text,    customer_id text,    order_month date,    created_at timestamp,    order_id uuid,    status text,    total decimal,    shipping_city text,    source_updated_at timestamp,    projection_request_id uuid,    PRIMARY KEY ((tenant_id,customer_id,order_month),created_at,order_id)) WITH CLUSTERING ORDER BY (created_at DESC,order_id ASC)  AND compaction = {'class':'UnifiedCompactionStrategy'};

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';

CREATE INDEX IF NOT EXISTS orders_status_saiON atlasmart_derived.orders_by_day (status) USING 'sai';CREATE INDEX IF NOT EXISTS orders_city_saiON atlasmart_derived.orders_by_day (shipping_city) USING 'sai';CONSISTENCY LOCAL_QUORUM;TRACING ON;SELECT order_id,customer_id,status,totalFROM atlasmart_derived.orders_by_dayWHERE tenant_id='tenant-a' AND order_day='2026-09-08'  AND status='PAID';TRACING OFF;SELECT order_id,customer_id,status,totalFROM atlasmart_derived.orders_by_dayWHERE tenant_id='tenant-a' AND order_day='2026-09-08'  AND status='PAID' AND shipping_city='Baku';

This SAI query keeps the strong base partition restriction and adds secondary predicates. It is not equivalent to the customer timeline: the view/projection changes routing by customer, whereas SAI filters within/distributed across indexed data. Measure selectivity, indexed SSTables, p95/p99 query latency, disk/write cost, and failure behavior before adopting it.

docker exec atlasmart-derived-cass-1 nodetool tablestats atlasmart_derived.orders_by_daydocker exec atlasmart-derived-cass-1 nodetool tablestats atlasmart_derived.orders_by_customer_mvdocker exec atlasmart-derived-cass-1 nodetool tablestats atlasmart_derived.orders_by_customer_projectiondocker exec atlasmart-derived-cass-1 nodetool viewbuildstatus atlasmart_derived orders_by_customer_mvdocker exec atlasmart-derived-cass-1 cqlsh -e "SELECT keyspace_name,index_name,column_name,indexed_sstable_count,is_building,is_queryable,per_column_disk_size,per_table_disk_size FROM system_views.indexes WHERE keyspace_name='atlasmart_derived';" || true

3. Operational ownership matrix

For every candidate, assign an owner/on-call, dashboards, failure drills, backfill runbook, repair/reconciliation policy, SLO/SLA, capacity envelope, upgrade test, and rollback. If nobody owns these, the architecture is incomplete even if the CQL is valid.

4. Acceptance tests before production adoption

Mechanism under test: [base/query table | SAI | MV | CDC/app projection]Cassandra/container patch: 5.0.9 / cassandra:5.0.9 derivativeTopology: dc/rack/node count, RF, CLDataset: rows, bytes, partition distribution, TTL/delete rateQuery: predicates, expected cardinality, page/result sizeWrite load: concurrency, payload, update/delete mixDerived-state build/backfill duration and disk headroomp50 / p95 / p99 base-write latency before/after featurep50 / p95 / p99 derived-read latencyFreshness/lag definition and measured distributionReplica/node/DC failure outcomesRepair/recovery/rebuild/replay time and verificationSchema change + rolling-upgrade testSecurity/tenant authorization pathDriver retry/idempotency/speculation assumptionsCost and operational owner/on-callRollback: dual-read comparison, cutover condition, removal plan

5. Final Chapter 20 decision drill and cleanup

For AtlasMart's customer order history, choose the explicit projection because time ordering and reconciliation/backfill ownership matter. For bounded status/city filters on an already-correct partition key, evaluate SAI. For fan-out events to fraud/warehouse/notifications, choose CDC/stream/application projection. Keep the materialized view only as an experimental comparison unless the organization has explicitly accepted its current Apache Cassandra status and demonstrated recovery/upgrade behavior under representative load.

docker rm -f atlasmart-derived-cass-1 atlasmart-derived-cass-2 atlasmart-derived-cass-3 2>/dev/null || truedocker volume rm atlasmart-derived-cass-1-data atlasmart-derived-cass-2-data atlasmart-derived-cass-3-data 2>/dev/null || truedocker network rm atlasmart-derived-net 2>/dev/null || truedocker image rm atlasmart-cassandra-derived:5.0.9 2>/dev/null || truerm -rf chapter20-derived-image

Removing the isolated image/network/volumes returns the host to the earlier course baseline. No shared atlasmart-course data is touched.

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. Chapter 21 moves from server-side data structures to the application transport layer: native protocol sessions, topology discovery, prepared routing metadata, paging, load balancing, retries, and speculative execution determine how clients actually reach these tables and indexes.

Summary and next bridge

Derived-table design is an ownership decision, not a feature checklist. Base/query tables remain the first choice for known access paths; SAI provides measured secondary filtering; materialized views are an experimental automatic alternative with significant operational caveats; CDC/application projections provide explicit replay/transformation ownership. Chapter 21 now shows how a maintained driver discovers and routes requests to Cassandra correctly.

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