When Explicit Denormalized Tables Give Better Ownership and Repairability

Learning outcomes

AtlasMart decides that “automatic” is not itself a correctness requirement. The customer-order timeline needs predictable ordering, independent backfill, explicit repair, and an auditable reconciliation process. Those needs point toward a query table owned by the application rather than an experimental materialized view.

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. Explicit ownership removes MV key restrictions—but adds responsibilities

The base table remains authoritative. The projection can choose any primary key needed by the read, so AtlasMart creates a monthly customer bucket ordered by created_at. That exact shape was impossible as one MV because it would need both customer_id and created_at as additional non-base-primary-key columns. The application now owns writing/backfilling/reconciling the projection, but it gains direct control over those operations.

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

CONSISTENCY LOCAL_QUORUM;INSERT INTO atlasmart_derived.orders_by_customer_projection(tenant_id,customer_id,order_month,created_at,order_id,status,total,shipping_city,source_updated_at,projection_request_id)VALUES ('tenant-a','cust-42','2026-09-01','2026-09-08T08:00:00Z',20000000-0000-0000-0000-000000000001,'DELIVERED',129.90,'Baku','2026-09-08T09:20:00Z',30000000-0000-0000-0000-000000000001);INSERT INTO atlasmart_derived.orders_by_customer_projection(tenant_id,customer_id,order_month,created_at,order_id,status,total,shipping_city,source_updated_at,projection_request_id)VALUES ('tenant-a','cust-42','2026-09-01','2026-09-08T08:10:00Z',20000000-0000-0000-0000-000000000002,'PACKING',59.00,'Baku','2026-09-08T08:10:00Z',30000000-0000-0000-0000-000000000002);SELECT created_at,order_id,status,totalFROM atlasmart_derived.orders_by_customer_projectionWHERE tenant_id='tenant-a' AND customer_id='cust-42' AND order_month='2026-09-01'LIMIT 20;

The projection can now use natural time clustering without bending the base schema. The source_updated_at and projection_request_id fields are not magic transactional metadata; they make duplicate/reconciliation decisions explainable.

2. Simulate the failure materialized views try to hide from the application

Update the base but intentionally skip the corresponding projection write. This is a controlled application dual-write failure. The value of explicit ownership is not that failures disappear; it is that the contract defines how to detect and repair them.

UPDATE atlasmart_derived.orders_by_daySET status='RETURN_REQUESTED', updated_at='2026-09-08T10:00:00Z'WHERE tenant_id='tenant-a' AND order_day='2026-09-08'  AND order_id=20000000-0000-0000-0000-000000000002;SELECT order_id,status,updated_atFROM atlasmart_derived.orders_by_dayWHERE tenant_id='tenant-a' AND order_day='2026-09-08'  AND order_id=20000000-0000-0000-0000-000000000002;SELECT order_id,status,source_updated_atFROM atlasmart_derived.orders_by_customer_projectionWHERE tenant_id='tenant-a' AND customer_id='cust-42' AND order_month='2026-09-01';

The mismatch is expected. Do not “fix” it by blindly retrying every write forever. The reconciler should compare authoritative source version/timestamp with projection metadata and perform a deterministic upsert.

INSERT INTO atlasmart_derived.orders_by_customer_projection(tenant_id,customer_id,order_month,created_at,order_id,status,total,shipping_city,source_updated_at,projection_request_id)VALUES ('tenant-a','cust-42','2026-09-01','2026-09-08T08:10:00Z',20000000-0000-0000-0000-000000000002,'RETURN_REQUESTED',59.00,'Baku','2026-09-08T10:00:00Z',30000000-0000-0000-0000-000000000003);SELECT order_id,status,source_updated_at,projection_request_idFROM atlasmart_derived.orders_by_customer_projectionWHERE tenant_id='tenant-a' AND customer_id='cust-42' AND order_month='2026-09-01';

3. Ownership and repairability comparison

docker exec atlasmart-derived-cass-1 nodetool tablestats atlasmart_derived.orders_by_customer_projection# Controlled lab repair of the explicit table only.docker exec atlasmart-derived-cass-1 nodetool repair --full atlasmart_derived orders_by_customer_projection# Verify the query path again after repair/reconciliation.docker exec atlasmart-derived-cass-1 cqlsh -e "CONSISTENCY LOCAL_QUORUM; SELECT created_at,order_id,status,source_updated_at FROM atlasmart_derived.orders_by_customer_projection WHERE tenant_id='tenant-a' AND customer_id='cust-42' AND order_month='2026-09-01';"

4. Do not replace ownership with giant logged batches

A tiny logged batch can be appropriate when a business invariant truly requires atomic visibility of a few Cassandra mutations and the cost is measured, but Chapter 17 showed why cross-partition logged batches are coordination mechanisms—not bulk throughput optimizers. A customer projection that can tolerate bounded asynchronous lag is usually better served by idempotent writes plus reconciliation than by forcing every request through a large cross-partition batch.

// Pseudocode-quality skeleton: prepare once, bind deterministic keys, mark idempotent.PreparedStatement upsertProjection = session.prepare(  "INSERT INTO atlasmart_derived.orders_by_customer_projection " +  "(tenant_id,customer_id,order_month,created_at,order_id,status,total,shipping_city,source_updated_at,projection_request_id) " +  "VALUES (?,?,?,?,?,?,?,?,?,?)");BoundStatement stmt = upsertProjection.bind(/* deterministic values */)    .setConsistencyLevel(DefaultConsistencyLevel.LOCAL_QUORUM)    .setIdempotent(true);session.execute(stmt);// Persist/checkpoint the request/event separately so a reconciler can prove completion.

5. Verification checklist

• The projection key is derived from the customer-timeline query, not from MV restrictions.
• A deliberately missed projection update is detected by comparing source/projection state.
• The correction is a deterministic idempotent upsert.
• The explicit projection can be repaired/backfilled independently.
• The design documents lag, retry, reconciliation, and owner/on-call responsibilities.

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 4 adds CDC and stream/application workflows: explicit event/checkpoint ownership is more work, but it supports transformations, replay, side effects, and independent consumers that a materialized view cannot express.

Summary and next bridge

Explicit projections turn hidden derived-state ownership into visible application responsibility. They are not automatically safer, but they are easier to model, backfill, repair, and reconcile deliberately. Lesson 4 compares that model with Cassandra CDC, external streams, and direct application dual writes.

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