Cassandra Query-First Modeling
Query-first modeling is the core methodology for designing Cassandra tables. Instead of starting with entities and relationships (the relational approach), you start with the questions your application needs to answer. Each query gets its own table, shaped exactly to return the answer efficiently in a single partition read.
The Restaurant Menu Analogy
A restaurant does not cook every possible dish and then wait to see what customers order. It designs a fixed menu based on what people order most. The kitchen pre-prepares ingredients for those specific dishes. Cassandra data modeling works the same way — you design tables for the specific queries your application will run, not for every possible question someone might someday ask.
The Three-Step Process
Step 1: List your application queries
Step 2: For each query, design a table whose partition key
equals the query's filter and whose clustering columns
provide the required sort order
Step 3: Write to all necessary tables on each data mutation
Step 1: Identify Your Queries
Before writing any CREATE TABLE statement, write down every data-access pattern your application requires. Be specific — include which columns you filter on, what order results need, and whether you need a limited range or all matching rows.
E-commerce application queries: Q1: Get all orders for a specific customer, newest first Q2: Get all orders with status = 'pending', ordered by date Q3: Get a single order by order_id Q4: Get all products in a specific category, sorted by price Q5: Get a single product by product_id
Step 2: Design One Table Per Query
Q1: Orders for a customer, newest first
-- Filter: customer_id. Sort: order_date DESC. CREATE TABLE orders_by_customer ( customer_id UUID, order_date TIMESTAMP, order_id UUID, status TEXT, total DECIMAL, PRIMARY KEY (customer_id, order_date, order_id) ) WITH CLUSTERING ORDER BY (order_date DESC, order_id ASC);
Q2: Pending orders ordered by date
-- Filter: status. Sort: order_date ASC. CREATE TABLE orders_by_status ( status TEXT, order_date TIMESTAMP, order_id UUID, customer_id UUID, total DECIMAL, PRIMARY KEY (status, order_date, order_id) ) WITH CLUSTERING ORDER BY (order_date ASC, order_id ASC);
Q3: Single order by order_id
-- Direct lookup: order_id is the partition key. CREATE TABLE orders_by_id ( order_id UUID PRIMARY KEY, customer_id UUID, order_date TIMESTAMP, status TEXT, total DECIMAL );
Q4: Products in a category, sorted by price
-- Filter: category. Sort: price ASC. CREATE TABLE products_by_category ( category TEXT, price DECIMAL, product_id UUID, name TEXT, PRIMARY KEY (category, price, product_id) ) WITH CLUSTERING ORDER BY (price ASC, product_id ASC);
Q5: Single product by product_id
CREATE TABLE products_by_id ( product_id UUID PRIMARY KEY, name TEXT, category TEXT, price DECIMAL, in_stock BOOLEAN );
Step 3: Write to All Tables
When an order is created, the application writes to every table that holds order data. A LOGGED BATCH keeps these writes consistent.
BEGIN BATCH
INSERT INTO orders_by_customer
(customer_id, order_date, order_id, status, total)
VALUES ([uuid], now(), [order-uuid], 'pending', 149.99);
INSERT INTO orders_by_status
(status, order_date, order_id, customer_id, total)
VALUES ('pending', now(), [order-uuid], [uuid], 149.99);
INSERT INTO orders_by_id
(order_id, customer_id, order_date, status, total)
VALUES ([order-uuid], [uuid], now(), 'pending', 149.99);
APPLY BATCH;
Query-First vs Entity-First Comparison
Entity-First (Relational) Query-First (Cassandra) ────────────────────────────────────────────────────────────── Start with entities (Order, User) Start with queries Normalize to remove redundancy Denormalize for each query JOIN at query time Pre-join data at write time One table per entity One table per query pattern Schema is query-agnostic Schema is query-specific Flexible queries but slow at scale Fast queries with fixed patterns
Handling Status Updates with Query-First Tables
When an order status changes from 'pending' to 'shipped', you must update every table that stores the status. This also means deleting the old partition key value in the status-based table and inserting a new one.
-- Order status changes from 'pending' to 'shipped':
BEGIN BATCH
-- Remove old status entry:
DELETE FROM orders_by_status
WHERE status = 'pending'
AND order_date = [original_date]
AND order_id = [order-uuid];
-- Insert new status entry:
INSERT INTO orders_by_status
(status, order_date, order_id, customer_id, total)
VALUES ('shipped', [original_date], [order-uuid], [uuid], 149.99);
-- Update the lookup table:
UPDATE orders_by_id SET status = 'shipped'
WHERE order_id = [order-uuid];
-- Update customer orders:
UPDATE orders_by_customer SET status = 'shipped'
WHERE customer_id = [uuid]
AND order_date = [original_date]
AND order_id = [order-uuid];
APPLY BATCH;
Common Query-First Modeling Mistakes
Mistake Correct Approach
──────────────────────────────────────────────────────────────
Designing tables before knowing Always list queries first;
the queries let queries drive the schema
Using the same table for multiple Create one table per distinct
access patterns query pattern
Skipping a table for "rare" queries If the app needs it, model it;
ALLOW FILTERING is not the answer
Choosing low-cardinality PK Pick high-cardinality partition
(e.g., boolean, status alone) keys; bucket if needed
Summary
Query-first modeling is the fundamental methodology for Cassandra schema design. Start with a complete list of application queries. Design one table per query, using the query's filter column as the partition key and its sort order as clustering columns. Write to all relevant tables on each data mutation using LOGGED BATCH. This approach produces tables that answer every query with a fast, single-partition read and scales to any volume of data.