Redesign a Normalized Relational Model into Cassandra Query Tables and Explain the Tradeoffs

Learning outcomes

AtlasMart’s starting relational model has customers, products, orders, order_items and inventory with foreign keys and joins. The goal is not to translate those tables one-for-one. The goal is to preserve business invariants while deriving Cassandra tables from the application’s bounded reads, then document every duplication and operational consequence.

1. Start with the relational model, then refuse to copy it literally

Foreign-key relationships express integrity in the relational system. Cassandra has no foreign-key enforcement or cascading operations. If that integrity is business-critical, decide whether the relational system remains authoritative, whether the application enforces it, or whether the workflow can tolerate asynchronous projections. Do not silently discard the invariant during migration.

2. Derive query tables from the user journeys

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.orders_by_customer_bucket (customer_id text,order_month date,ordered_at timestamp,order_id text,status text,total_cents bigint,item_count int,source_version bigint,PRIMARY KEY ((customer_id,order_month),ordered_at,order_id)) WITH CLUSTERING ORDER BY (ordered_at DESC,order_id ASC) AND compaction={'class':'UnifiedCompactionStrategy'};CREATE TABLE atlasmart_model.order_details_by_order (order_id text,line_no int,product_id text,product_name text,quantity int,unit_price_cents int,status text,source_version bigint,PRIMARY KEY ((order_id),line_no)) WITH 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'};

3. Reproducible migration slice

# 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.

The lab migrates one order and one product—not because that volume is representative, but because the transformation is deterministic and inspectable. In a real migration, backfill throughput, partition distribution, retry behavior, source change capture and cutover windows must be measured separately.

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,100);INSERT INTO atlasmart_model.products_by_category (category,price_cents,product_id,name,active,source_version) VALUES ('laptops',129900,'p-501','AtlasBook 14',true,100);INSERT INTO atlasmart_model.orders_by_customer_bucket (customer_id,order_month,ordered_at,order_id,status,total_cents,item_count,source_version) VALUES ('c-17','2026-09-01','2026-09-07T10:00:00Z','o-9001','PAID',129900,1,55);INSERT INTO atlasmart_model.order_details_by_order (order_id,line_no,product_id,product_name,quantity,unit_price_cents,status,source_version) VALUES ('o-9001',1,'p-501','AtlasBook 14',1,129900,'PAID',55);SELECT * FROM atlasmart_model.products_by_id WHERE product_id='p-501';SELECT product_id,name,price_cents FROM atlasmart_model.products_by_category WHERE category='laptops' LIMIT 20;SELECT ordered_at,order_id,status,total_cents FROM atlasmart_model.orders_by_customer_bucket WHERE customer_id='c-17' AND order_month='2026-09-01' LIMIT 20;SELECT line_no,product_id,product_name,quantity,unit_price_cents FROM atlasmart_model.order_details_by_order WHERE order_id='o-9001';

4. Document the costs that moved

5. Migration and rollback sequence

1. Freeze the query inventory and acceptance criteria.
2. Create new Cassandra query tables additively.
3. Backfill a bounded slice with source versions and checksums/counts.
4. Run deterministic reconciliation against the authority.
5. Shadow-read Cassandra and compare semantic results, not only row counts.
6. Enable dual writes or event materialization with backlog/drift metrics.
7. Shift a controlled traffic percentage; observe p50/p95/p99, errors, retries and partition hotspots.
8. Retain the old read path and source data through the rollback window.
9. Only after acceptance, retire old projections/contracts deliberately.

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.

Chapter 07 now zooms in on the primary-key machinery used throughout these designs: composite partition keys, clustering columns, ordering and the boundary between a well-bounded partition and a hotspot.

Summary and next bridge

A successful Cassandra redesign does not preserve normalized tables; it preserves business behavior through workload-specific query tables. The gains are bounded, direct reads and predictable distribution. The costs are duplication, write fan-out, projection reconciliation, migration complexity and new operational capacity concerns. Chapter 07 makes the primary-key choices behind those tradeoffs explicit.

# Remove only Chapter 06 data; preserve the reusable course cluster.docker exec atlasmart-cass-1 cqlsh -e "DROP KEYSPACE IF EXISTS atlasmart_model;"# Optional full course-lab reset (destructive only to the dedicated disposable lab):# docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3# docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-data# docker network rm atlasmart-cassandra

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.