Avoid Joins and Cross-Partition Scans by Precomputing Read Shapes

Learning outcomes

AtlasMart’s relational order page joins orders, order items, products and customers. Copying those normalized tables into Cassandra preserves the nouns but not the access pattern. Cassandra does not execute joins or subqueries, and a coordinator that must touch many unrelated partitions turns a simple UI request into variable fan-out. The remedy is to precompute the read shape that the page actually needs.

1. A join is evidence that the Cassandra read shape is missing

Suppose the order-history screen needs order ID, time, status, total and a short customer-facing summary. A normalized design might read orders, join customers, join order_items, and join products. Cassandra has no server-side join engine. Issuing those reads from application code creates network round trips and failure handling proportional to the number of referenced rows.

Instead, materialize exactly what the history page needs into orders_by_customer_bucket. For an order-detail screen, materialize a separate order_details_by_order partition whose rows already include the display fields required for each line item.

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, shipping_city text,  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, ordered_at timestamp, status text, customer_id text,  product_id text, product_name text, quantity int, unit_price_cents int,  PRIMARY KEY ((order_id),line_no)) WITH compaction={'class':'UnifiedCompactionStrategy'};

2. “One partition” is a routing property, not a row-count guarantee

A query such as WHERE order_id='o-9001' targets one partition even if that partition contains many line-item rows. That is different from scanning many partitions. But a single partition can still become too large, so the model must bound line counts and payload size. Query-first modeling minimizes partition fan-out; it does not excuse unlimited partition growth.

Similarly, asking “all PAID orders everywhere” is not local just because the result is small. Without an index or a dedicated query table, Cassandra cannot know which partitions contain matching rows without broader work. For a recurring operational query, create a dedicated bounded projection such as orders_by_status_day.

CREATE TABLE atlasmart_model.orders_by_status_day (  status text, order_day date, ordered_at timestamp, order_id text, customer_id text, total_cents bigint,  PRIMARY KEY ((status,order_day),ordered_at,order_id)) WITH CLUSTERING ORDER BY (ordered_at DESC,order_id ASC)  AND compaction={'class':'UnifiedCompactionStrategy'};

3. Reproducible direct-read versus filtering experiment

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

CREATE TABLE IF NOT EXISTS atlasmart_model.orders_source_fixture (  order_id text PRIMARY KEY, customer_id text, ordered_at timestamp, status text, total_cents bigint) WITH compaction={'class':'UnifiedCompactionStrategy'};INSERT INTO atlasmart_model.orders_source_fixture (order_id,customer_id,ordered_at,status,total_cents) VALUES ('o-9001','c-17','2026-09-07T10:00:00Z','PAID',129900);INSERT INTO atlasmart_model.orders_source_fixture (order_id,customer_id,ordered_at,status,total_cents) VALUES ('o-9002','c-17','2026-09-07T11:00:00Z','PACKING',4599);INSERT INTO atlasmart_model.orders_by_status_day (status,order_day,ordered_at,order_id,customer_id,total_cents) VALUES ('PAID','2026-09-07','2026-09-07T10:00:00Z','o-9001','c-17',129900);TRACING ON;SELECT order_id,customer_id,total_cents FROM atlasmart_model.orders_source_fixture WHERE status='PAID' ALLOW FILTERING;TRACING OFF;TRACING ON;SELECT ordered_at,order_id,customer_id,total_cents FROM atlasmart_model.orders_by_status_day WHERE status='PAID' AND order_day='2026-09-07' LIMIT 50;TRACING OFF;

The fixture is intentionally tiny so ALLOW FILTERING is safe for instruction. Capture both traces and compare coordinators/replica reads and timing in your environment. Do not extrapolate the tiny scan to production. The direct table makes the partition address part of the API contract, so cost scales with that bounded partition rather than with unrelated order partitions.

4. Wrong approach: “we can join in the client” as the default

Client-side joins are possible, but using them routinely reintroduces data-dependent network fan-out, partial failures, retry amplification and tail-latency variability. They also make cache and consistency behavior harder to reason about. The official Cassandra modeling guidance treats client joins as an exceptional fallback when a query cannot be integrated into an appropriate table.

Do not blindly duplicate entire source entities either. Materialize the fields required by the read contract, plus provenance/version fields needed to reconcile them. Large rarely-read blobs can have a different ownership path.

5. Query cost becomes testable

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 4 addresses the other half of precomputation: if the same fact appears in several read shapes, an application or streaming workflow must own projection consistency and reconciliation.

Summary and next bridge

Precomputed read shapes replace joins and broad filtering with partition-addressable tables designed for the request the user actually makes. This improves predictability by moving work to write/materialization time. The next lesson deliberately breaks one duplicated projection, detects it with source versions and repairs it with a replayable workflow.

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.