Section 2: Data Modeling Fundamentals

Cassandra Modeling Patterns

Master ALL fundamental data modeling patterns! Learn when to use each pattern with real-world scenarios, detailed explanations, animated diagrams, and production examples from Netflix, Spotify, and more.

📖 The Story: Organizing Your Recipe Book

Imagine you're organizing a massive recipe collection for a cooking website. You have thousands of recipes, and different users want to find them in completely different ways. How do you organize your recipe book so everyone can find what they need quickly?

📚 Scenario 1: Browse by Category

User wants: "Show me all dessert recipes"

How you organize: Put all dessert recipes together in one section, so you can flip to "Desserts" and see everything at once.

Cassandra pattern: Wide Rows (category = partition key, all recipes in that category together)

PRIMARY KEY (category, recipe_name)

// All "Desserts" in one partition

🔍 Scenario 2: Find Specific Recipe

User wants: "Show me the chocolate cake recipe by ID: recipe_12345"

How you organize: Index by unique recipe ID for instant lookup, like a dictionary.

Cassandra pattern: Skinny Rows (recipe_id = partition key, direct lookup)

PRIMARY KEY (recipe_id)

// Direct O(1) lookup

⏰ Scenario 3: Recently Added

User wants: "Show me recipes added in the last week, newest first"

How you organize: Sort by date added, with newest recipes at the top of each section.

Cassandra pattern: Time-Series (partition key + timestamp DESC)

PRIMARY KEY (category, created_at)

WITH CLUSTERING ORDER BY (created_at DESC);

// Time-sorted, newest first!

💡 The Key Insight

Just like organizing a recipe book DIFFERENTLY for DIFFERENT ways people want to access recipes, Cassandra uses DIFFERENT DATA MODELING PATTERNS based on HOW you'll query the data!

Query-Driven Design: Your queries determine your data model. NOT the other way around!

🎯 The 6 Essential Cassandra Patterns

Every Cassandra data model falls into one (or more) of these 6 fundamental patterns. Master these, and you can model ANY application!

Pattern Quick Reference

Pattern	When to Use	Example Use Case	Company
1️⃣ Time-Series	Query by time range, chronological data	Viewing history, sensor readings, logs	Netflix, IoT
2️⃣ Entity Lookup	Get single record by unique ID	User profiles, product details, orders	Spotify, Amazon
3️⃣ Activity Stream	User's recent actions/events	Timeline, notifications, user feed	Twitter, Facebook
4️⃣ Leaderboard	Top N ranked by score/value	Game rankings, trending posts	Gaming, Reddit
5️⃣ Denormalization	Avoid JOINs, co-locate related data	Posts with user info, orders with products	Facebook, E-commerce
6️⃣ Bucketing	Prevent huge partitions, split by time/hash	Daily logs, hourly metrics	Uber, Monitoring

Time-Series Pattern

Store and query data chronologically. Perfect for events, logs, measurements, and any data where time is the primary access dimension. Data is sorted by timestamp, enabling efficient time-range queries.

🎬 Real Scenario: Netflix Viewing History

The Challenge:

Netflix needs to track what each user watches, when they watched it, how long they watched, and which episode/movie. 200 million users generate billions of viewing events. Users want to see their "Continue Watching" list and browse their viewing history.

The Queries Users Make:

"Show me what I watched in the last 7 days" 📅
"What did I watch yesterday?" 🔍
"Give me my most recent 10 viewing sessions" ⏰
"Continue watching from where I left off" ▶️

💡 Key Insight:

Every query needs BOTH:

user_id → Which user's history?
time range → When did they watch?

Solution: Partition by user_id (group all user's data together), cluster by timestamp DESC (sort newest first)!

Schema Design

CREATE TABLE viewing_history (
  -- Partition Key (which user?):
  user_id UUID,

  -- Clustering Column (when? sort by time):
  watched_at TIMESTAMP,

  -- Regular columns (what did they watch?):
  show_id UUID,
  show_title TEXT,
  episode_id UUID,
  season INT,
  episode INT,
  duration_seconds INT,
  progress_seconds INT,

  PRIMARY KEY (user_id, watched_at)
) WITH CLUSTERING ORDER BY (watched_at DESC);

// DESC = newest first (perfect for "Continue Watching"!)

Why This Schema Works:

user_id = Partition Key: All of Alice's viewing history lives in ONE partition on ONE node
watched_at = Clustering Column: Rows automatically sorted by time within partition
DESC Order: Most recent views appear first (no need to reverse!)
Efficient Queries: "Last 7 days" = read partition + time filter

Common Query Patterns

⏰

Get Most Recent

Get user's last 10 viewing sessions (Continue Watching feature)

SELECT * 

FROM viewing_history

WHERE user_id = ?

LIMIT 10;

// Returns 10 newest

// Already sorted DESC!

// Latency: ~2ms

📅

Last 7 Days

Time range filter for recent viewing history

SELECT * 

FROM viewing_history

WHERE user_id = ?

AND watched_at >= ?

LIMIT 100;

// Time range query

// Latency: ~5ms

📆

Date Range

Get viewing between two specific dates

SELECT * 

FROM viewing_history

WHERE user_id = ?

AND watched_at >= ?

AND watched_at <= ?;

// Between dates

// Latency: ~3-8ms

Performance Characteristics

Read Performance:

Recent 10 rows: 2-5ms
Last 7 days (~50-100 rows): 5-10ms
Full year (~1000 rows): 20-50ms
Cached reads: < 1ms

Write Performance:

Insert new viewing: 1-2ms
Append-only (no updates)
No read-before-write needed
Linear scalability

Netflix at Scale:

200M+ users worldwide 🌍
6 billion+ viewing events stored 📊
100K+ queries per second handled ⚡
p99 latency: < 10ms 🚀
TTL: Auto-delete views older than 2 years ♻️

✅ PERFECT FOR

📺 Viewing history (Netflix, YouTube)
📊 Sensor readings (IoT, temperature)
💰 Transaction history (banking)
📝 Application logs (system events)
📱 User activity streams
📈 Time-series metrics

❌ DON'T USE FOR

Only need latest value (use static)
Aggregate across all users (can't SUM all)
Query by non-time field (need index)
Update old records frequently
Random time access patterns

Entity Lookup Pattern

Direct access to individual entities by unique ID. The simplest and fastest pattern - perfect for "get user by ID", "get product by SKU", or any single-record lookup. O(1) hash-based access.

🎵 Real Scenario: Spotify User Profiles

The Challenge:

Spotify has 300 million+ users. When someone opens the app, Spotify needs to load their profile INSTANTLY: username, email, subscription type, playlists count, etc. No time ranges, no sorting - just direct lookup by user_id.

The Query:

"Give me ALL information for user_id = abc-123"

Key Insight:

You ALWAYS know the user_id. You never query by "all users with premium subscription" or "users who joined last month". It's always direct: user_id → profile data.

📋 Schema Design

CREATE TABLE user_profiles (
  -- Partition Key ONLY (no clustering!):
  user_id UUID PRIMARY KEY,

  -- All user data:
  username TEXT,
  email TEXT,
  full_name TEXT,
  subscription_type TEXT,
  country TEXT,
  created_at TIMESTAMP,
  last_login TIMESTAMP,
  playlist_count INT,
  followers_count INT
);

// Simple! Just partition key = direct lookup

Why This Works:

user_id ONLY: No clustering column needed
One row per partition: Skinny rows pattern
Hash distribution: Users spread evenly across cluster
O(1) lookup: Hash user_id → find node → read row

Simple Query Pattern

-- Get user profile:

SELECT * FROM user_profiles

WHERE user_id = ?;

// That's it! Super simple.

// Latency: 1-2ms

// No sorting, no filtering

// Just direct hash lookup

⚡ Performance Profile

Read Latency: 1-2ms (O(1) hash lookup)
Write Latency: 1-2ms (single row insert/update)
Scalability: Perfect - users evenly distributed
Caching: Extremely effective (stable data)

Spotify Scale:
• 300M+ users
• 1-2ms average lookup time
• 500K+ reads/second
• 99.99% cache hit rate

✅ PERFECT FOR

User profiles
Product details
Order lookup
Document by ID
Session data
Any single-record access

❌ DON'T USE FOR

Range queries (use time-series)
Sorting by value (use leaderboard)
Multiple related items (use wide rows)
Time-based access

Activity Stream Pattern

Track a user's stream of activities/actions over time. Combines user identification (partition key) with chronological ordering (clustering). Perfect for timelines, feeds, and notification systems.

🐦 Real Scenario: Twitter User Timeline

The Challenge:

Twitter users post tweets throughout the day. When you visit someone's profile, you see their tweets in reverse chronological order (newest first). Twitter needs to show "Alice's last 50 tweets" instantly.

The Queries:

"Show Alice's timeline" (her tweets, newest first)
"Get Alice's last 20 tweets"
"Scroll through Bob's tweet history"

Key Pattern:

Partition by user_id (whose tweets?), cluster by tweet_time DESC (newest first). Similar to time-series but focused on USER activity!

📋 Schema Design

CREATE TABLE user_timeline (
  user_id UUID,
  tweet_id TIMEUUID,
  tweet_text TEXT,
  created_at TIMESTAMP,
  likes_count INT,
  retweets_count INT,
  PRIMARY KEY (user_id, tweet_id)
) WITH CLUSTERING ORDER BY (tweet_id DESC);

// TIMEUUID automatically sorts by time!

Why TIMEUUID?

Combines unique ID + timestamp in one column
Automatically sorted chronologically
DESC gives newest tweets first
Guaranteed uniqueness even with concurrent writes

✅ PERFECT FOR

Social media posts/tweets
User notifications
Comment threads
Chat messages per user
Activity logs per user

Twitter Stats

500M+ tweets/day
Billions of timeline rows
< 10ms timeline queries
Real-time updates

Leaderboard Pattern

Maintain rankings sorted by score or value. Cluster by score DESC to get top performers. Perfect for game leaderboards, trending content, and any "top N" query.

🎮 Real Scenario: Game High Scores

The Challenge:

Epic Games needs to show "Top 100 players globally" for Fortnite. Players have scores that change. Need to query "Who are the current top 100?" efficiently.

The Solution:

Cluster by score DESC so highest scores appear first. Query with LIMIT 100 to get top players instantly!

📋 Schema Design

CREATE TABLE game_leaderboard (
  game_id UUID,
  score INT,
  player_id UUID,
  player_name TEXT,
  achieved_at TIMESTAMP,
  PRIMARY KEY (game_id, score, player_id)
) WITH CLUSTERING ORDER BY (score DESC, player_id ASC);

// Score DESC = highest scores first!
// player_id ASC = tiebreaker for same scores

Get Top 100

SELECT * FROM game_leaderboard

WHERE game_id = ?

LIMIT 100;

// Instant top 100!

// Already sorted by score DESC

✅ PERFECT FOR

Game leaderboards
Top sellers/products
Most viewed videos
Trending posts
Highest rated items

⚠️ LIMITATION

Updates are expensive (rewrite row)
Best for stable/infrequent updates
Not for real-time score changes
Consider bucketing for active games

Denormalization Pattern

Duplicate data to avoid JOINs. Store related information together for single-query access. Trade storage space for query speed - a fundamental Cassandra principle!

👥 Real Scenario: Facebook Posts

The Challenge:

When showing a post, Facebook needs: post content AND user info (name, avatar). In SQL, you'd JOIN posts + users tables. But Cassandra has NO JOINS!

The Solution:

Denormalize! Store user name and avatar DIRECTLY in the posts table. Now one query gets everything!

❌ SQL Way (2 tables)

-- posts table

post_id, user_id, content

-- users table

user_id, name, avatar

// Need JOIN to get both!

✅ Cassandra Way

CREATE TABLE posts (

  post_id UUID PRIMARY KEY,

  user_id UUID,

  user_name TEXT,

  user_avatar TEXT,

  content TEXT

);

// Everything in one query!

⚠️ Trade-offs to Consider

PROS:

Single query gets all data (fast!)
No network overhead from JOINs
Predictable performance

CONS:

Data duplication (uses more storage)
Update complexity (change user name → update all posts)
Potential stale data if not updated everywhere

Cassandra Philosophy: Storage is cheap, queries are expensive. Duplicate data to make queries fast!

✅ DENORMALIZE WHEN

Data changes rarely (user names)
Read-heavy workload
Query speed critical
Can tolerate eventual consistency

❌ DON'T DENORMALIZE

Data changes frequently
Strong consistency required
Storage is severely limited
Update complexity unmanageable

Bucketing Pattern

Prevent massive partitions by splitting data into time or hash buckets. Essential when a single partition would grow too large (> 100MB or millions of rows).

🚗 Real Scenario: Uber Trip Logs

The Problem:

If you partition by city, NYC might have millions of trips per day! That's a HUGE partition (bad for performance).

The Solution:

Add a bucket to partition key: (city, date). Now NYC trips split by day. Each day = separate partition = manageable size!

❌ Without Bucketing

PRIMARY KEY (city, trip_id)

// NYC partition: MILLIONS of rows!

// Slow reads, huge memory usage

✅ With Daily Bucketing

PRIMARY KEY ((city, date), trip_id)

// NYC split by day!

// Each day = manageable partition

🎯 When to Add Bucketing

Add buckets if:

Partition would exceed 100MB
Millions of rows per partition
Partition grows unbounded over time
Hot partition causing performance issues

Common Bucketing Strategies:

Time-based: Daily, hourly, monthly buckets
Hash-based: Modulo operation (user_id % 10)
Range-based: Geographic regions, categories

🌳 Pattern Selection Decision Tree

📋 Step-by-Step Pattern Selection Guide

Start here → Answer these questions:

Q1: Do you query by time range? ("Last 7 days", "This month")

→ YES: Use TIME-SERIES pattern (Pattern 1)

Q2: Do you look up single entity by ID? ("Get user 123")

→ YES: Use ENTITY LOOKUP pattern (Pattern 2)

Q3: Do you need a user's activity stream? ("My timeline", "My posts")

→ YES: Use ACTIVITY STREAM pattern (Pattern 3)

Q4: Do you need top N by score? ("Top 100 players")

→ YES: Use LEADERBOARD pattern (Pattern 4)

Q5: Do you need related data together (avoid JOINs)?

→ YES: Use DENORMALIZATION pattern (Pattern 5)

Q6: Will a partition grow too large (millions of rows)?

→ YES: Add BUCKETING pattern (Pattern 6)

💡 Pro Tip: You can COMBINE patterns!

Example: Time-Series + Bucketing for massive time-series data

🌍 Real Production Examples

🎬

Netflix

Pattern: Time-Series

Use Case: Viewing history

200M+ users
6B+ viewing events
< 10ms p99 latency
Time-based queries

🎵

Spotify

Pattern: Entity Lookup

Use Case: User profiles

300M+ users
1-2ms lookups
O(1) hash access
Direct by user_id

🐦

Twitter

Pattern: Activity Stream

Use Case: User timelines

500M+ tweets/day
Billions of rows
Real-time feeds
Per-user streams

🎮

Epic Games

Pattern: Leaderboard

Use Case: Game rankings

300M+ players
Score-based sorting
Top 100 queries
Real-time updates

👥

Facebook

Pattern: Denormalization

Use Case: Posts with user info

Billions of posts
No JOINs needed
Single query access
User data embedded

🚗

Uber

Pattern: Bucketing

Use Case: Trip logs by day

Millions of trips/day
Daily buckets
Manageable partitions
Time-based splits

✅ Best Practices

DO These Things

Design for your queries first
Denormalize data freely
Use bucketing for large partitions
Combine patterns when needed
Think in partitions
Test with production data volumes

DON'T Do These

Think like SQL (no JOINs!)
Try to normalize everything
Allow unbounded partition growth
Query without partition key
Use secondary indexes as primary access
Ignore partition size limits

🎯 The Golden Rules

Know your queries! Design your data model around how you'll query it
One query, one table. Create separate tables for different query patterns
Duplicate is okay! Storage is cheap, queries are expensive
Keep partitions manageable. < 100MB, use bucketing if needed
Always specify partition key. Never query without it

🎓 You've Mastered Cassandra Modeling Patterns!

You now know the 6 fundamental patterns that power the world's largest applications. From Netflix's viewing history to Twitter's timelines, these patterns are used everywhere!

🚀 What You Can Build Now:

Time-series systems (Netflix-scale viewing history)
User profile systems (Spotify-scale lookups)
Social media feeds (Twitter-scale timelines)
Gaming leaderboards (Epic Games rankings)
Denormalized applications (Facebook posts)
Massive-scale logging (Uber trip bucketing)

Remember: Design for your queries, think in partitions, and don't be afraid to denormalize. You're now ready to model ANY Cassandra application! 🎉

Responsive Ad