Resources

Data Modeling Guide

Master Cassandra data modeling - principles, patterns, anti-patterns, and real-world examples!

Home › Resources › Data Modeling Guide

🎯 Core Principle

Query-first, not entity-first

🔑 Key Design

Partition + Clustering keys

📊 Denormalization

Duplicate data for queries

⚡ Performance

One partition = one query

🎯 Core Modeling Principles

The Golden Rule

Design tables based on QUERIES, not entities!

Cassandra modeling is fundamentally different from relational databases. You model for how you READ, not how data is structured.

Cassandra vs Relational Modeling

❌ Relational (SQL)

  • 📐 Normalize data (3NF)
  • 🔗 JOIN tables at query time
  • 🎯 Design entities first
  • 📊 Query flexibility (any JOIN)
  • ⚠️ Slow for large datasets

✅ Cassandra (NoSQL)

  • 📦 Denormalize data
  • 🚫 NO JOINs (pre-joined)
  • 🔍 Design queries first
  • ⚡ Fast, fixed queries only
  • ✅ Scalable to petabytes

The Three Fundamental Rules

1️⃣ Spread Data Evenly Across Cluster

Goal: Avoid "hot partitions" where one node handles all traffic.
How: Choose partition keys with high cardinality (many unique values).
Example: user_id ✅ (millions) vs country ❌ (hundreds)

2️⃣ Minimize Partition Reads

Goal: One query = one partition lookup (or very few).
How: Include partition key in WHERE clause.
Example: WHERE user_id = '123' ✅ vs WHERE email = 'alice@...' ❌ (full scan)

3️⃣ Minimize Partition Size

Goal: Keep partitions < 100MB (ideally < 10MB).
How: Use composite partition keys to split large datasets.
Example: (user_id, date) instead of just user_id for time-series

Data Duplication is OK!

In Cassandra, disk is cheap, but queries are expensive. Duplicate data across multiple tables optimized for different queries. This is normal and expected!

📋 The Modeling Process

Step-by-Step Methodology

Step 1: Define Application Queries

List EVERY query your application needs. Be specific!

Example: Social Media App Q1: Get user profile by user_id Q2: Get user's posts (latest first) Q3: Get user's followers Q4: Get user's following Q5: Get posts by hashtag Q6: Get user's feed (posts from people they follow)

Step 2: Identify Access Patterns

For each query, determine:

  • What is known? (partition key)
  • What order? (clustering key)
  • How much data? (partition size)
  • How often? (read/write ratio)

Step 3: Design Tables for Queries

Create ONE table per query pattern:

-- Q1: Get user by ID CREATE TABLE users_by_id ( user_id uuid PRIMARY KEY, username text, email text ); -- Q2: Get user's posts (newest first) CREATE TABLE posts_by_user ( user_id uuid, post_time timestamp, post_id uuid, content text, PRIMARY KEY (user_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- Q5: Get posts by hashtag CREATE TABLE posts_by_hashtag ( hashtag text, post_time timestamp, post_id uuid, user_id uuid, PRIMARY KEY (hashtag, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC);

Step 4: Optimize & Validate

  • ✅ Each query touches one partition?
  • ✅ Partitions < 100MB?
  • ✅ Data distributed evenly?
  • ✅ No hot partitions?
  • ✅ Write patterns sustainable?

🔑 Primary Key Design

Primary Key Anatomy

PRIMARY KEY ((partition_key), clustering_column1, clustering_column2) └──────────┘ └────────────────────────────────┘ Determines Determines row order which node within partition

Types of Primary Keys

Simple Partition Key

PRIMARY KEY (user_id) -- Single column determines partition -- One row per partition CREATE TABLE users ( user_id uuid PRIMARY KEY, name text, email text );

Composite Partition Key

PRIMARY KEY ((user_id, date)) -- Multiple columns determine partition -- Splits data across more partitions CREATE TABLE user_activity ( user_id uuid, date text, -- '2024-01-15' activity_time timestamp, action text, PRIMARY KEY ((user_id, date), activity_time) ); -- Query: SELECT * FROM user_activity WHERE user_id = uuid() AND date = '2024-01-15';

Compound Primary Key (Partition + Clustering)

PRIMARY KEY (user_id, post_time) └──────┘ └─────────┘ partition clustering CREATE TABLE posts_by_user ( user_id uuid, ← Partition key post_time timestamp, ← Clustering column post_id uuid, content text, PRIMARY KEY (user_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- Multiple rows per partition, sorted by post_time

Partition Key Selection Guide

Scenario Good Partition Key Bad Partition Key
User data user_id ✅ country ❌
Time-series (sensor_id, date) ✅ sensor_id ❌
Products product_id ✅ category ❌
Messages thread_id ✅ user_id ❌

✅ Common Design Patterns

Pattern 1: One-to-Many Relationship

User has many posts

CREATE TABLE posts_by_user ( user_id uuid, ← Partition: Groups all user's posts post_time timestamp, ← Clustering: Orders by time post_id uuid, content text, likes int, PRIMARY KEY (user_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- Query: Get user's 10 most recent posts SELECT * FROM posts_by_user WHERE user_id = 'user-123' LIMIT 10;

Pattern 2: Time-Series Data

Sensor readings over time

CREATE TABLE sensor_readings ( sensor_id text, date text, ← Bucket by date reading_time timestamp, ← Order within day temperature double, humidity double, PRIMARY KEY ((sensor_id, date), reading_time) ) WITH CLUSTERING ORDER BY (reading_time DESC); -- Query: Get today's readings for sensor SELECT * FROM sensor_readings WHERE sensor_id = 'temp-001' AND date = '2024-01-15'; -- ✅ Prevents unbounded partition growth!

Pattern 3: Multiple Access Patterns (Duplication)

Blog posts by author AND by category

-- Access Pattern 1: Posts by author CREATE TABLE posts_by_author ( author_id uuid, post_time timestamp, post_id uuid, title text, content text, category text, PRIMARY KEY (author_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- Access Pattern 2: Posts by category (DUPLICATE DATA!) CREATE TABLE posts_by_category ( category text, post_time timestamp, post_id uuid, author_id uuid, title text, content text, PRIMARY KEY (category, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- ✅ Duplicate content in both tables - this is normal!

Pattern 4: Bucketing Large Partitions

Prevent unlimited partition growth

-- ❌ BAD: Unbounded partition PRIMARY KEY (user_id, activity_time) -- Problem: User with 10 years of activity = huge partition! -- ✅ GOOD: Bucketed by month CREATE TABLE user_activity ( user_id uuid, month text, ← '2024-01' bucket activity_time timestamp, action text, PRIMARY KEY ((user_id, month), activity_time) ); -- ✅ Now each month is a separate partition!

Pattern 5: Wide Rows (Skinny vs Fat Partitions)

-- Skinny: Few columns, many rows CREATE TABLE messages ( thread_id uuid, message_time timestamp, sender text, content text, PRIMARY KEY (thread_id, message_time) ); -- Good for: Chat history, time-series -- Wide/Fat: Many columns per row CREATE TABLE user_profile ( user_id uuid PRIMARY KEY, name text, email text, age int, -- ... 50 more columns ... last_login timestamp ); -- Good for: Configuration, profiles

❌ Anti-Patterns to Avoid

Anti-Pattern 1: Using Secondary Indexes for Everything

-- ❌ BAD CREATE TABLE users ( user_id uuid PRIMARY KEY, email text, username text ); CREATE INDEX ON users (email); CREATE INDEX ON users (username); -- Why bad: Secondary indexes query ALL nodes! -- Use only for low-cardinality columns (status, type) -- ✅ GOOD: Create dedicated tables CREATE TABLE users_by_email ( email text PRIMARY KEY, user_id uuid, username text );

Anti-Pattern 2: Unbounded Partition Growth

-- ❌ BAD: Partition grows forever CREATE TABLE events ( user_id uuid, event_time timestamp, event_type text, PRIMARY KEY (user_id, event_time) ); -- Problem: After 10 years, partition > 1GB! -- ✅ GOOD: Bucket by time period CREATE TABLE events ( user_id uuid, year_month text, ← '2024-01' event_time timestamp, event_type text, PRIMARY KEY ((user_id, year_month), event_time) );

Anti-Pattern 3: Low-Cardinality Partition Keys

-- ❌ BAD: Only ~200 countries CREATE TABLE users ( country text, user_id uuid, name text, PRIMARY KEY (country, user_id) ); -- Problem: USA partition = millions of users on one node! -- ✅ GOOD: High cardinality CREATE TABLE users ( user_id uuid PRIMARY KEY, country text, name text );

Anti-Pattern 4: Treating Cassandra Like SQL

-- ❌ BAD: Trying to JOIN -- There are NO JOINs in Cassandra! -- ❌ BAD: Querying without partition key SELECT * FROM users WHERE age > 21; -- Error: Requires ALLOW FILTERING (full table scan!) -- ✅ GOOD: Always include partition key SELECT * FROM users WHERE user_id = uuid();

Anti-Pattern 5: Large Partitions

Rule: Partitions should be < 100MB (ideally < 10MB)

Signs of large partitions:

  • Slow queries on specific keys
  • Timeouts on certain partitions
  • Uneven disk usage across nodes
  • GC pressure on specific nodes

Solution: Add bucketing column to partition key!

💼 Real-World Examples

Example 1: E-Commerce Order System

-- Query 1: Get user's orders (newest first) CREATE TABLE orders_by_user ( user_id uuid, order_time timestamp, order_id uuid, total_amount decimal, status text, PRIMARY KEY (user_id, order_time) ) WITH CLUSTERING ORDER BY (order_time DESC); -- Query 2: Get order details by order_id CREATE TABLE orders_by_id ( order_id uuid PRIMARY KEY, user_id uuid, order_time timestamp, total_amount decimal, status text, items list> ); -- Query 3: Get pending orders (for fulfillment) CREATE TABLE orders_by_status ( status text, order_time timestamp, order_id uuid, user_id uuid, PRIMARY KEY (status, order_time) ) WITH CLUSTERING ORDER BY (order_time ASC);

Example 2: Social Media Feed

-- Query 1: Get user's timeline (posts from people they follow) CREATE TABLE user_timeline ( user_id uuid, ← Viewer's ID post_time timestamp, post_id uuid, author_id uuid, content text, PRIMARY KEY (user_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC); -- Write pattern: When Alice posts, write to ALL followers' timelines! -- This is "fan-out on write" - trades write cost for read speed -- Query 2: Get user's own posts CREATE TABLE posts_by_author ( author_id uuid, post_time timestamp, post_id uuid, content text, likes int, PRIMARY KEY (author_id, post_time) ) WITH CLUSTERING ORDER BY (post_time DESC);

Example 3: IoT Sensor Data

-- Time-series data with bucketing CREATE TABLE sensor_data ( sensor_id text, date text, ← '2024-01-15' (daily buckets) hour int, ← 0-23 (hourly sub-buckets) reading_time timestamp, temperature double, humidity double, battery_level int, PRIMARY KEY ((sensor_id, date, hour), reading_time) ) WITH CLUSTERING ORDER BY (reading_time DESC); -- Query: Get last hour of data SELECT * FROM sensor_data WHERE sensor_id = 'temp-sensor-001' AND date = '2024-01-15' AND hour = 14; -- ✅ Each hour = small partition (< 10MB)

🏆 Best Practices Summary

✅ DO These

  • Model queries first
  • Denormalize data
  • Duplicate tables per query
  • Use high-cardinality partition keys
  • Keep partitions < 100MB
  • Bucket time-series data
  • Include partition key in WHERE
  • Use clustering for sort order
  • Test with production data volume
  • Monitor partition sizes

❌ DON'T Do These

  • Try to normalize like SQL
  • Use JOINs (they don't exist!)
  • Use ALLOW FILTERING in production
  • Create unbounded partitions
  • Use low-cardinality partition keys
  • Query without partition key
  • Overuse secondary indexes
  • Ignore partition size warnings
  • Model entities before queries
  • Expect flexible querying

The Modeling Mindset

Think in queries, not entities.

Embrace data duplication. Disk is cheap, queries are expensive.

Design for scalability. How will this work with 100TB of data?

One query = one partition lookup. This is the golden rule.

📚 Recommended Reading Order

  1. Start: Understand your queries (application requirements)
  2. Learn: Partition key selection (data distribution)
  3. Practice: One-to-many patterns (most common)
  4. Master: Time-series bucketing (prevents growth)
  5. Advanced: Multi-table duplication (query optimization)
Previous
Nodetool
Advertisement

Responsive Ad