How do you design data models in Cassandra (query-driven design)?
Master query-driven Cassandra data modeling: design one table per query, pick scalable partition keys, and embrace duplication for fast single-partition reads.
Expected Interview Answer
In Cassandra you design data models query-first: you list the exact read queries the application needs, then create one table per query so each read is served from a single partition, choosing partition and clustering keys to match that access pattern.
Unlike relational design, which starts from normalized entities and relationships, Cassandra modeling starts from application queries and works backward to tables. You pick a partition key that both distributes data evenly across nodes and groups the rows a query needs together, then add clustering columns to order rows within a partition. Because there are no JOINs, the same data is often duplicated across several tables — one per query — and writes are cheap enough to make this trade-off worthwhile.
- Every read hits a single partition, keeping latency low and predictable
- Even data distribution avoids hotspots across the cluster
- Scales horizontally without redesign
- Clustering columns give free in-partition ordering
- Write-optimized engine makes duplicating data affordable
AI Mentor Explanation
A relational modeler builds one giant scorebook and hopes to answer any question later. A Cassandra modeler behaves like a coach preparing separate laminated cards for each situation the team will actually face — one card for powerplay fields, one for death overs, one for spin. Each card (table) is built for a specific in-game question so the answer is instant. You accept carrying several cards with overlapping notes because reading the right card mid-over must be immediate.
Step-by-Step Explanation
Step 1
List the queries
Enumerate every read the application must perform before designing any table.
Step 2
One table per query
Create a dedicated table for each access pattern rather than reusing a normalized schema.
Step 3
Choose the partition key
Pick a key that distributes data evenly and groups the rows a query reads together.
Step 4
Add clustering columns
Order rows within each partition to match how the query needs results sorted.
Step 5
Duplicate as needed
Accept writing the same data to several tables so every read stays single-partition.
Step 6
Validate partition size
Ensure partitions stay bounded (roughly under 100MB / 100k rows) to avoid hotspots.
What Interviewer Expects
- Query-first rather than entity-first thinking
- Ability to justify one-table-per-query duplication
- Sound partition key selection for even distribution
- Understanding of clustering columns for ordering
- Awareness of partition size limits and hotspots
Common Mistakes
- Starting from normalized relational entities
- Choosing a low-cardinality partition key that creates hotspots
- Building unbounded partitions that grow forever
- Trying to reuse one table for many different queries
- Fearing data duplication instead of embracing it
Best Answer (HR Friendly)
“In Cassandra you design your database backward compared to a traditional one: you first write down every question the app will ask, then build a separate table for each question. This makes every lookup fast, even if it means storing some of the same data in more than one place.”
Code Example
-- Query: get a user's orders newest-first
CREATE TABLE orders_by_user (
user_id uuid,
order_id timeuuid,
total decimal,
PRIMARY KEY (user_id, order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);
-- Query: look up an order by its id (data duplicated on purpose)
CREATE TABLE orders_by_id (
order_id timeuuid,
user_id uuid,
total decimal,
PRIMARY KEY (order_id)
);Follow-up Questions
- How do you pick a good partition key?
- What problems does an unbounded partition cause?
- Why is data duplication acceptable in Cassandra?
- How do clustering columns control row ordering?
- How would you handle a query pattern you discover after launch?
MCQ Practice
1. Cassandra data modeling starts from what?
Cassandra uses query-driven design: you list the read queries first and build a table for each.
2. Why is the same data often duplicated across Cassandra tables?
Cassandra is write-optimized, so duplicating data across query-specific tables keeps every read on one partition.
Flash Cards
What drives Cassandra data modeling? — The application's queries — you design one table per access pattern (query-first design).
What makes a good partition key? — One that distributes data evenly across nodes and groups the rows a query reads together.
Why duplicate data across tables? — Writes are cheap; duplication keeps each read single-partition and fast, replacing JOINs.
What do clustering columns do? — They order rows within a partition to match how a query needs results sorted.
Continue Learning
Related Interview Questions
Why is denormalization encouraged in Cassandra?
medium
What is CQL and how does it differ from SQL?
easy
How do you size partitions for a time-series table in Cassandra so they never grow unbounded?
hard
Given a new query pattern, how do you choose between a secondary index, a materialised view and a second table?
hard