QUERY-DRIVEN DESIGN - Model for Your Queries

What is a Query?

Query = Question you ask the database

Think of database like a librarian:
• You ask: "Find me books by Shakespeare"
• Librarian searches and returns books
• That question = Query!

Database examples:
• "Show me user with ID 123"
• "Get all orders from yesterday"
• "Find posts by user Alice"

In code (CQL):
SELECT * FROM users WHERE user_id = 123;

Every time you fetch data = Query!

Data Modeling

Data Modeling = How you organize information

Real-world analogy - Organizing clothes:

Option A: By type
• All shirts together
• All pants together
• All shoes together

Option B: By occasion
• Work outfits (shirt+pants+shoes)
• Gym outfits
• Casual outfits

In database:
Option A = Traditional (normalized)
Option B = Query-driven!

Data modeling = your organization strategy!

Partition Key

Partition Key = Address where data lives

Perfect analogy - Apartment building:

Cassandra cluster = City with many buildings
• Each building = 1 server/node
• Building address = Partition key
• All roommates = Data with same key

Example:
Partition key: user_id
user_id = 123 → Building A (Node 1)
user_id = 456 → Building B (Node 2)
user_id = 789 → Building C (Node 3)

Critical rule:
All data for same partition key
lives on SAME node! ✓

This enables O(1) lookups!

Clustering Key

Clustering Key = Sorting ORDER within partition

Continuing apartment analogy:
• Building address = Partition key
• Apartment numbers = Clustering key
• Numbers sorted: 101, 102, 103...

Example table:
PRIMARY KEY (user_id, timestamp)
• user_id = Partition key (which building)
• timestamp = Clustering key (which apartment)

Data for user_id=123:
• Event at 09:00 (apartment 101)
• Event at 09:15 (apartment 102)
• Event at 09:30 (apartment 103)
Pre-sorted by time! ⚡

Clustering key = automatic sorting!

Normalization vs Denormalization

Two opposite philosophies!

📚 Normalization (Traditional SQL):
• Store each fact ONCE
• Join tables when querying
• No duplication allowed
• Saves storage ✓
• Complex queries ✗

Example:
Table 1: Users (id, name)
Table 2: Orders (id, user_id, total)
Query: JOIN to get user + orders

📦 Denormalization (Cassandra):
• Store data MULTIPLE times
• Each table self-contained
• Duplicates encouraged! ✓
• Uses more storage ✗
• Simple, fast queries ✓

Example:
Table: user_orders (has BOTH user AND order data)
Query: Single table, no JOIN!

Cassandra philosophy:
"Disk is cheap, JOINs are expensive!"

Performance & Complexity

Understanding Big O notation (simplified!)

O(1) - Constant time (BEST!):
• 1 row or 1 billion rows
• Takes SAME time always!
• Example: Hash table lookup
• Cassandra goal: ~1-5ms ✓

O(n) - Linear time (OK):
• More data = more time
• 2x data = 2x time
• Example: Scanning a list

O(n²) - Quadratic time (BAD!):
• 2x data = 4x time
• 10x data = 100x time!
• Example: Nested loops

Cassandra optimization:
Design for O(1) reads!
Know partition key → Direct lookup
Predictable, fast, always! 🚀

Distributed Systems

Distributed = Data spread across many computers

Analogy: Library system
• 1 library (single server) - Simple!
• 100 libraries (distributed) - Complex!

Single server (easy):
• All data in one place
• Can JOIN tables easily
• But: Limited capacity ✗

Distributed (Cassandra):
• Data across 100s of nodes
• Each node has subset
• Can't JOIN across nodes! ✗
• But: Unlimited capacity! ✓

The fundamental trade-off:
Scalability vs Query flexibility
Cassandra chooses scalability!

Replication

Replication = Making copies for safety

Analogy: Important documents
• Original at home
• Copy in safe deposit box
• Scan on cloud backup
• 3 copies! (RF=3)

In Cassandra:
Replication Factor (RF) = 3
Every piece of data → 3 nodes

Example:
user_id=123 stored on:
• Node A (primary)
• Node B (replica 1)
• Node C (replica 2)

Benefits:
• Node fails? 2 backups! ✓
• Read from any copy
• High availability!

Schema

Schema = Blueprint for your data

Analogy: House blueprint
• Defines room layout
• Specifies room sizes
• Must follow plan!

Database schema:
• Defines table structure
• Specifies column types
• Must match plan!

Example schema:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  name TEXT,
  email TEXT
);

Schema = contract for data structure!

Formal Definition

Query-Driven Design =
A data modeling methodology where table structure is determined by query patterns rather than entity relationships.

In simple terms:
Design tables based on HOW you'll query them, not WHAT entities they represent.

Process:
1. List all queries needed (complete list!)
2. Design one table per query pattern
3. Optimize each table for its query
4. Accept data duplication as strategy
5. Prioritize read speed over storage

Core principle:
"One table per query pattern" 🎯

Core Principle

"One table per query pattern"

Need 3 different queries?
Create 3 different tables!

Real example:
Entity: User
Queries needed:
1. Get user by ID
2. Get user by email
3. Get users by country

Traditional SQL:
1 table: users
Add indexes on email, country

Cassandra:
3 tables:
• users_by_id
• users_by_email
• users_by_country

Same data, 3 tables!
Each optimized for its query ✓

Duplication is not a bug, it's the strategy!

Ultimate Goal

Achieve O(1) read performance for all queries

What's O(1)?
Constant time - always the same speed!

Examples:
• 1 row in table: 2ms
• 1 million rows: 2ms
• 1 billion rows: 2ms
• 1 trillion rows: 2ms ✓

How to achieve:
1. Know partition key in query
2. Direct node lookup (hash function)
3. No table scanning needed
4. Consistent performance!

Compare to O(n):
• 1 million rows: 100ms
• 1 billion rows: 100,000ms! 💥
• Unacceptable!

Query-driven design =
Predictable, fast, always! 🚀

The Distributed Systems Problem

Cassandra is distributed - data spreads across multiple servers. This creates fundamental limitations:

❌ NO JOINS: Can't join tables across different servers (too slow!)
❌ NO FULL TABLE SCANS: Scanning billions of rows across cluster = disaster
❌ NO ARBITRARY QUERIES: Must know partition key to find data

✅ Solution: Design each table for specific query pattern!
If you need 5 ways to query data, create 5 tables. Each optimized for fast, direct lookup.

One Table Per Query

Pattern: Duplicate data for different access patterns

Query 1: users_by_id (PK: user_id)
Query 2: users_by_email (PK: email)
Query 3: users_by_country (PK: country, CK: user_id)

Same user data, 3 different tables!

Time-Series Pattern

Pattern: Partition by user/entity, cluster by time

Table: user_activity
PK: user_id
CK: timestamp DESC

Fast queries: "Get recent activity for user"

Bucketing Pattern

Pattern: Split large partitions into buckets

Table: events_by_day
PK: (user_id, day_bucket)
CK: event_time

Prevents hot partitions!

1. List Queries First

Before any design, write down ALL queries your app needs. Be specific and complete!

2. Know Partition Key

Every query MUST include partition key in WHERE clause. No exceptions!

3. Embrace Duplication

Disk is cheap, reads are precious. Duplicate data freely for query optimization!

❌ Why JOINs Don't Work in Distributed Systems

The fundamental problem: Data lives on different servers!

Example JOIN query in SQL:
SELECT users.name, orders.total
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.country = 'USA'
In single server (works!):
1. Scan users table (in RAM/disk)
2. Scan orders table (in RAM/disk)
3. Match rows together (fast - same machine)
4. Return results
Total time: Maybe 50ms ✓

In distributed Cassandra (DISASTER!):
1. users table scattered across 100 nodes
2. orders table also scattered across 100 nodes
3. To JOIN, must:
→ Scan ALL 100 nodes for users ❌ (billions of rows!)
→ Scan ALL 100 nodes for orders ❌ (billions of rows!)
→ Transfer MASSIVE data across network ❌
→ Match rows on coordinator node ❌
4. Total time: Maybe 30 SECONDS or timeout! 💥

Why it's so slow:
• Network transfer: Moving GBs of data between nodes (10-100x slower than disk)
• Full scans: Can't use indexes across nodes
• Coordinator bottleneck: One node processing billions of rows
• Unpredictable: Performance depends on data volume

Solution: Query-Driven Design!
Instead of JOIN, create denormalized table:
CREATE TABLE user_orders_by_country (
  country TEXT,
  user_id UUID,
  user_name TEXT,
  order_id UUID,
  order_total DECIMAL,
  PRIMARY KEY (country, user_id)
);
Query becomes:
SELECT * FROM user_orders_by_country
WHERE country = 'USA';
Result: 2ms! All data on ONE node! ⚡

Key insight:
Can't JOIN? Don't need to! Store joined data together from the start!

❌ Why Full Table Scans Don't Work

The scaling problem: More data = exponentially slower!

Example "bad" query:
SELECT * FROM users WHERE age > 25;
Why this is terrible in Cassandra:

Scenario: 1 billion users across 100 nodes
• Each node: 10 million rows
• No partition key specified! (age is just a column)
• Must scan EVERY node!

What happens:
1. Coordinator sends query to all 100 nodes
2. Each node scans its 10M rows
3. Each node filters by age > 25
4. Each node returns ~7M matching rows
5. Coordinator receives 700M rows total!
6. Coordinator must merge/sort results

Performance disaster:
• Time: 10-60 seconds (or timeout!)
• Network: Transferring GBs of data
• Memory: Coordinator might run out (OOM crash!)
• Cluster load: All nodes working simultaneously
• Other queries: Blocked/slow (resources exhausted)

Query-Driven Solution:
If you need to query by age, create a table for it!
CREATE TABLE users_by_age_range (
  age_bucket TEXT, -- '20-29', '30-39', etc
  user_id UUID,
  age INT,
  name TEXT,
  PRIMARY KEY (age_bucket, user_id)
);
Query becomes:
SELECT * FROM users_by_age_range
WHERE age_bucket IN ('20-29', '30-39', '40-49', ...);
Result: 5-10ms! Direct partition lookups! ⚡

Rule of thumb:
If your query doesn't have partition key in WHERE clause, it's probably wrong for Cassandra!

✅ The Query-Driven Solution Summary

Because of these limitations, we MUST design differently:

What Cassandra CAN'T do:
• ❌ JOINs across tables/nodes
• ❌ Full table scans
• ❌ Queries without partition key
• ❌ Ad-hoc queries (unpredictable patterns)
• ❌ Complex WHERE clauses (multiple conditions)

What Cassandra CAN do amazingly well:
• ✅ Direct partition key lookups (O(1) - instant!)
• ✅ Range queries within partition (using clustering keys)
• ✅ Massive scale (petabytes, trillions of rows)
• ✅ Predictable performance (always fast)
• ✅ High availability (99.99%+ uptime)

The Query-Driven Design strategy:
1. Accept limitations: No JOINs, no full scans
2. Know your queries: List every query pattern needed
3. Design per query: Create table for each pattern
4. Denormalize freely: Duplicate data for performance
5. Optimize reads: Fast queries > storage efficiency

The payoff:
• Every query: 1-5ms (predictable!)
• Linear scalability: 10 nodes or 1000 nodes, same performance
• High throughput: Millions of queries/second
• Always available: No downtime

Bottom line:
"We can't query like SQL, so we design like Cassandra - for the queries we know we'll run!"

One Table Per Query Pattern

The fundamental pattern!

Rule: Each unique query = new table

Example - User entity:
Query 1: users_by_id (PK: user_id)
Query 2: users_by_email (PK: email)
Query 3: users_by_country (PK: country, CK: user_id)

When to use: Always! Core principle.

Benefits:
• Optimized for specific access
• O(1) performance
• No compromises!

Time-Series Pattern

For time-based data

Structure:
• PK: Entity ID (user, sensor, etc)
• CK: Timestamp DESC

Example:
CREATE TABLE user_activity (
  user_id UUID,
  event_time TIMESTAMP,
  event_type TEXT,
  PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY
  (event_time DESC);

Perfect for: Activity feeds, logs, metrics, sensor data

Time Bucketing Pattern

Prevent hot partitions!

Problem: One partition too large
Solution: Split by time bucket

Example:
CREATE TABLE events_by_day (
  day_bucket TEXT, -- '2024-12-29'
  event_time TIMESTAMP,
  event_data TEXT,
  PRIMARY KEY (day_bucket, event_time)
);

Result: Evenly distributed load!
Use when: >100MB per partition

Write-Time Denormalization

Fan-out writes for instant reads

Pattern: Write to multiple tables when data changes

Example - Social media post:
1. User posts → Write to posts_by_user
2. Also write to timeline of 1000 followers
3. Also write to posts_by_hashtag

Trade-off:
• Writes: 1000+ operations
• Reads: 1 simple query ✓

When to use: Read-heavy workloads (90%+ reads)

Wide Row Pattern

Store related data together

Concept: Many clustering keys per partition

Example - Product catalog:
CREATE TABLE products_by_category (
  category TEXT,
  product_id UUID,
  name TEXT,
  price DECIMAL,
  PRIMARY KEY (category, product_id)
);

One partition = all products in category!

Limit: Keep partition <100MB

Materialized View Pattern

Let Cassandra handle duplication

Base table:
CREATE TABLE users_by_id (...);

Materialized view:
CREATE MATERIALIZED VIEW
users_by_email AS
  SELECT * FROM users_by_id
  WHERE email IS NOT NULL
  PRIMARY KEY (email, user_id);

Automatic sync! But limited flexibility.
Use sparingly - manual tables more control.

Querying Without Partition Key

NEVER DO THIS:
SELECT * FROM users
WHERE age > 25;

Why it's terrible:
• Full cluster scan!
• All 100 nodes queried
• 10-60 seconds or timeout
• May crash cluster!

Fix: Create users_by_age_range table!

Using ALLOW FILTERING

RED FLAG:
SELECT * FROM users
WHERE country = 'USA'
ALLOW FILTERING;

What it means:
"I know this is slow, do it anyway!"

Reality:
• Scans entire table
• Filters in memory
• Extremely slow

Fix: Redesign table with proper keys!

Ignoring Partition Size

DANGER:
Partition grows to 10GB!

Problems:
• Slow reads (scanning GB)
• Slow writes (large memtable)
• Slow compaction
• Hot partition (one node overloaded)

Rule: Keep <100MB per partition

Fix: Use time bucketing!

Trying to Normalize Data

SQL Thinking:
"Store each fact once, JOIN when needed"

Problem in Cassandra:
• No JOINs across nodes!
• Must fetch from multiple tables
• Multiple round trips
• Slow and complex

Fix: Denormalize! Duplicate freely!

Designing Tables First

BACKWARDS!
1. Design perfect tables
2. Then try to query...
3. Queries don't work!
4. Must redesign everything 😫

CORRECT:
1. List queries FIRST
2. Design tables for queries
3. Queries work perfectly! ✓

Using Secondary Indexes

TEMPTING BUT BAD:
CREATE INDEX ON users(country);

Problems:
• Distributed index (queries all nodes)
• Slow with many matching rows
• Can cause timeouts

Fix: Create dedicated table with country as PK!

1. Start with Application Workflow

Before ANY design work:

1. Map complete user journey
2. List every screen/feature
3. Write exact query for each
4. Include frequency estimates

Example:
• Login page → Query: user by email (1K/sec)
• Profile page → Query: user by ID (10K/sec)
• Feed page → Query: timeline (50K/sec)

Frequency guides optimization priorities!

2. Choose Partition Keys Carefully

Critical decisions!

Goals:
• Even data distribution
• Predictable partition size
• Matches query patterns

Good partition keys:
✓ user_id (millions of users)
✓ sensor_id (thousands of sensors)
✓ (channel_id, day_bucket)

Bad partition keys:
✗ country (only ~200 values!)
✗ status (active/inactive)
✗ timestamp (unbounded growth)

3. Monitor Partition Sizes

Set alerts and limits!

Recommended limits:
• Warning: 50MB per partition
• Critical: 100MB per partition
• Max: Never exceed 1GB!

Check with:
nodetool cfstats tablename

If too large: Add bucketing!

4. Embrace Duplication

It's not a bug, it's a feature!

Duplicate when:
• Need different access patterns
• Optimizing for read speed
• Avoiding JOINs

Storage is cheap:
• 1TB disk: $20/month
• 5x duplication: $100/month
• Fast queries: Priceless! ✓

Don't optimize for storage, optimize for performance!

5. Plan for Updates

Denormalization = write complexity!

When user changes email:
1. Update users_by_id
2. Delete old users_by_email row
3. Insert new users_by_email row
4. Update denormalized email in ALL tables!

Use batch statements:
BEGIN BATCH
  UPDATE ...
  DELETE ...
  INSERT ...
APPLY BATCH;

Maintains consistency!

6. Test at Scale

Design looks good on paper...

Must test with:
• Realistic data volumes
• Actual query patterns
• Production-like load

Load testing tools:
• cassandra-stress
• nosqlbench
• Custom scripts

Discover issues before production!

7. Iterate Based on Metrics

Monitor everything!

Key metrics:
• Query latency (P50, P99, P999)
• Partition sizes
• Tombstone warnings
• Read/write ratios

Be ready to redesign:
• Usage patterns change
• New features added
• Scale increases

Design evolves with app!

8. Document Your Decisions

Future you will thank you!

Document for each table:
• What query it serves
• Why keys chosen
• Expected query volume
• Update patterns

Example:
-- timeline_by_user
-- Query: User's home feed
-- Volume: 50K QPS peak
-- Updated: On follow + on post
-- PK: user_id (even dist)
-- CK: post_time DESC (newest first)

9. Know When NOT to Use Cassandra

Cassandra isn't always the answer!

Bad fit when:
• Need complex JOINs
• Ad-hoc analytics queries
• Transactions across rows
• Small dataset (<100GB)
• Unpredictable access patterns

Consider instead:
• PostgreSQL (complex queries)
• MongoDB (flexible schema)
• Redis (small, fast data)