One Table per Query Pattern: Denormalization as an Explicit Write-Time Cost

Learning outcomes

AtlasMart can now state its read patterns, but a product update affects more than one table: product details, category browsing and inventory-facing projections may each need a materialized row. Cassandra’s write path makes repeated writes cheap relative to distributed joins, but denormalization transfers complexity from read time to write/reconciliation time. This lesson makes that transfer explicit.

1. A query table is a materialized read contract

The phrase “one table per query” is shorthand for a deeper rule: a Cassandra table should serve a coherent family of requests that share the same partition address and clustering order. Two filters that require incompatible partition keys or orderings often need different tables. Conversely, a single table can serve several bounded variants if they use the same access path.

For AtlasMart, products_by_id answers direct lookup, products_by_category answers category/price traversal, and inventory_by_product answers store availability for one product. Reusing one generic table would force scans or client joins; creating these projections creates write fan-out instead.

CREATE TABLE atlasmart_model.products_by_id (  product_id text PRIMARY KEY, name text, category text, price_cents int, active boolean, source_version bigint) WITH compaction = {'class':'UnifiedCompactionStrategy'};CREATE TABLE atlasmart_model.products_by_category (  category text, price_cents int, product_id text, name text, active boolean, source_version bigint,  PRIMARY KEY ((category), price_cents, product_id)) WITH CLUSTERING ORDER BY (price_cents ASC, product_id ASC)  AND compaction = {'class':'UnifiedCompactionStrategy'};CREATE TABLE atlasmart_model.inventory_by_product (  product_id text, store_id text, available int, updated_at timestamp, source_version bigint,  PRIMARY KEY ((product_id), store_id)) WITH compaction = {'class':'UnifiedCompactionStrategy'};

2. Count fan-out at two different layers

A single business mutation—“product p-501 price changed to 119900, source version 8”—may require two or three logical projection writes. With RF=3, each logical write is then replicated to natural replicas according to the keyspace and consistency protocol. Do not multiply those numbers together and call the result “database transactions”; they are distinct costs and failure surfaces.

3. Reproducible fan-out and drift lab

# Reuse the course cluster if it exists; otherwise create only these disposable resources.docker network inspect atlasmart-cassandra >/dev/null 2>&1 || docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-data# Start node 1 only if it does not already exist.docker inspect atlasmart-cass-1 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait until node 1 answers before starting peers:docker exec atlasmart-cass-1 nodetool statusdocker inspect atlasmart-cass-2 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker inspect atlasmart-cass-3 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9# Wait until all three nodes report UN before creating RF=3 data.docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 cqlsh -e "SELECT release_version,cluster_name,data_center,rack FROM system.local;"docker exec atlasmart-cass-1 cqlsh -e "CREATE KEYSPACE IF NOT EXISTS atlasmart_model WITH replication = {'class':'NetworkTopologyStrategy','dc1':3};"docker exec atlasmart-cass-1 cqlsh -e "DESCRIBE KEYSPACE atlasmart_model"

On Windows, run these Docker commands from WSL/Git Bash or adapt the existence checks to PowerShell. If three Cassandra JVMs are too heavy for the machine, a single cassandra:5.0.9 node with RF=1 is sufficient for the chapter's query-shape exercises, but it is not equivalent evidence for RF=3/QUORUM behavior.

CONSISTENCY QUORUM;INSERT INTO atlasmart_model.products_by_id (product_id,name,category,price_cents,active,source_version) VALUES ('p-501','AtlasBook 14','laptops',129900,true,7);INSERT INTO atlasmart_model.products_by_category (category,price_cents,product_id,name,active,source_version) VALUES ('laptops',129900,'p-501','AtlasBook 14',true,7);SELECT product_id,name,price_cents,source_version FROM atlasmart_model.products_by_id WHERE product_id='p-501';SELECT product_id,name,price_cents,source_version FROM atlasmart_model.products_by_category WHERE category='laptops' AND price_cents=129900 AND product_id='p-501';

Now inject a realistic partial dual-write failure: update only the ID projection to source version 8. This is safe because the fixture is disposable and the reset is deterministic.

UPDATE atlasmart_model.products_by_id SET price_cents=119900,source_version=8 WHERE product_id='p-501';SELECT product_id,price_cents,source_version FROM atlasmart_model.products_by_id WHERE product_id='p-501';SELECT product_id,price_cents,source_version FROM atlasmart_model.products_by_category WHERE category='laptops' AND price_cents=129900 AND product_id='p-501';

INSERT INTO atlasmart_model.products_by_category (category,price_cents,product_id,name,active,source_version) VALUES ('laptops',119900,'p-501','AtlasBook 14',true,8);DELETE FROM atlasmart_model.products_by_category WHERE category='laptops' AND price_cents=129900 AND product_id='p-501';SELECT product_id,price_cents,source_version FROM atlasmart_model.products_by_category WHERE category='laptops' AND price_cents=119900 AND product_id='p-501';

4. Wrong approach: uncontrolled dual writes

Writing table A and then table B from an HTTP handler without an idempotency key, source version, retry classification or reconciler makes “denormalization” synonymous with silent inconsistency. Cassandra does not provide cross-table foreign-key integrity that repairs this for you. Logged batches are not a generic replacement for an application consistency design; batch semantics are taught later and have partition/topology costs.

A safer pattern identifies one authoritative business change, assigns a stable event/version, makes each projection update idempotent, records success/failure, retries only when semantics allow it and periodically reconciles projections against the authority. The exact implementation may be synchronous application code, an outbox/event pipeline or a streaming processor; ownership and observability are mandatory either way.

5. When a new table is justified

Production judgment

A Cassandra table is an operational commitment, not just a schema object. Before approving the read shape, record expected partition cardinality and byte growth, retention/TTL behavior, read and write rates, tail-latency objectives, RF/CL, failure domains, write fan-out, retry/idempotency rules, reconciliation ownership, compaction and tombstone consequences, repair/backup requirements, observability signals, and the migration/rollback path. The same denormalization that removes a read-time join can multiply writes, storage, repair traffic and opportunities for projection drift.

Do not derive universal size or latency thresholds from this local lab. Production decisions require measurements with the real key distribution, payloads, concurrency, disk/network/JVM behavior and failure modes. SAI/vector features are intentionally not used to rescue a poor primary access model here; those mechanisms have their own later chapters and costs.

Lesson 3 uses the same idea to eliminate read-time joins and broad cross-partition scans by precomputing the shape the application actually renders.

Summary and next bridge

Denormalization is an exchange: predictable single-table reads in return for write fan-out, storage duplication and projection-consistency work. A source-version field and deterministic reconciliation make that exchange observable. Next, AtlasMart redesigns a join-heavy order view so the user-facing read is precomputed rather than reconstructed across arbitrary partitions.

Authoritative references

• Apache Cassandra downloads — release baseline; 5.0.9 is the current GA 5.0 patch at chapter generation time.
• Data modeling introduction — official query-driven modeling, partition-minimization and denormalization guidance.
• RDBMS design versus Cassandra design — official join, referential-integrity and denormalization contrasts.
• CQL data definition — primary-key, partition-key and clustering-order syntax used by the chapter tables.
• CQL data manipulation — SELECT restrictions and single-table query semantics.
• Unified Compaction Strategy — current Cassandra guidance for the explicit lab compaction baseline.