Apache Cassandra
Query-First Data Modeling & Denormalization
Design Cassandra schemas by starting from query patterns rather than normalized entity relationships.
In a relational database you model the entities and write queries afterwards. In Cassandra you list the queries first and build a table for each one.
It's like keeping several different indexes of the same library books — one sorted by author, one by genre, one by publish date — instead of one master catalog you'd have to cross-reference every time.
Key Concepts
1
This is not a stylistic preference. It follows from what the engine can do.
2
Why the usual order does not work. Cassandra has no joins, and a WHERE clause can only filter
efficiently on the primary key. So a table that does not already match your query cannot be rescued by one.
WHERE
3
A normalized schema assumes you can assemble data at read time. Cassandra cannot. The assembling has to
have happened at write time.
4
So the same fact gets written several times. An order that must be findable by customer, by product and
by date becomes three tables:
5
orders_by_customer PRIMARY KEY ((customer_id), order_date, order_id)
orders_by_product PRIMARY KEY ((product_id), order_date, order_id)
orders_by_day PRIMARY KEY ((order_day), order_id)6
Same order, written three times, each copy keyed so that one query is a single-partition read.
7
To a relational eye this is a bug. Here it is the design — you are trading disk and write work, both cheap
and scalable, for read latency, which is neither.
8
What you take on. Cassandra has no foreign keys and no cascading updates. If the customer's name is
copied into four tables and it changes, your code updates four tables.
9
Nothing checks that you did. A missed table is silently wrong data that surfaces months later, so the
write path for a denormalized entity belongs in one place in the codebase, not spread across the callers.
10
The method, in order.
1. Write down each application workflow and the questions it asks.
2. Turn each question into a query: what do you know, what do you want back?
3. Give each query its own table, keyed so the query is one partition.
4. Check every partition for size and skew — will one grow forever, will one key take all the traffic?11
Step 4 is where designs fail. A table that answers the query perfectly is still wrong if one partition
holds ten million rows.
12
Say the number of tables out loud in an interview. Candidates often hesitate to propose four tables for
one entity, expecting it to sound wasteful. Proposing it, and naming the write-path cost you accept in
exchange, is the answer they are listening for.
13
What the interviewer is probing.1. "How does modelling differ from relational design?" Probing: the inversion. Stalls: "It
is similar." Moves up: you start from the queries and build a table per access pattern, accepting
duplication — the opposite of normalise first and query later.
14
2. "Why is duplication acceptable here?" Probing: the rationale. Stalls: "Storage is cheap."
Moves up: there are no joins, so the only way to answer a query efficiently is to have a table
shaped for it; writes are cheap and done once.
15
3. "How do you keep duplicated copies consistent?" Probing: the responsibility. Stalls: "The
database does it." Moves up: the application writes all copies, often in a batch — Cassandra will
not do it for you.
16
4. "What happens if a new query arrives later?" Probing: the cost. Stalls: "Add an index."
Moves up: a new table and a backfill, which is why gathering access patterns up front matters more
than in a relational design.