Section 3: Tables & Schema Design

Cassandra Tables

Master table creation, primary keys, partitioning, clustering, and schema design! Learn from Netflix, Spotify, and Uber's production patterns with animations and live examples.

📖 The Story: The Filing Cabinet Analogy

Imagine you're organizing 100 million customer records for a global company. How would you structure your filing system?

❌ The BAD Way: Traditional SQL Thinking

Approach: Single giant filing cabinet (one table), search through everything

-- SQL approach
SELECT * FROM customers
WHERE customer_id = 'C12345';

-- Behind the scenes:
-- Scans through 100 MILLION rows
-- Uses index (but still slow at scale)
-- Query time: 500ms - 2 seconds
                                    

Problems:

Slow lookups: Must search through millions of records
Single point of failure: One cabinet = one server
Can't scale: Can't add more cabinets easily
Bottleneck: Everyone waits in line for ONE cabinet

✅ The BRILLIANT Way: Cassandra Thinking

Approach: Multiple filing cabinets (distributed), smart organization by customer ID

-- Cassandra approach
CREATE TABLE customers (
    customer_id TEXT,  -- Partition Key (MAGIC!)
    name TEXT,
    email TEXT,
    PRIMARY KEY (customer_id)
);

-- Behind the scenes:
-- Hash customer_id → Know EXACT cabinet
-- Go directly to that cabinet
-- Query time: 2-10ms (200x FASTER!)
                                    

Why It's BRILLIANT:

Lightning fast: O(1) lookup - know exact location!
Distributed: Data spread across multiple servers
Scalable: Add more cabinets (nodes) anytime
No bottleneck: Parallel access to all cabinets

🎯 The Key Difference: Partition Key

SQL Table: "Here's all the data. Good luck finding what you need!"

Cassandra Table: "Tell me the customer_id, and I'll take you DIRECTLY to the right cabinet!"

This is why Netflix can serve 200M+ users with millisecond response times!
Tables + Partition Keys = Distributed Magic! ✨

📊 What is a Table in Cassandra?

The fundamental data structure that looks like SQL but behaves completely differently!

Simple Definition

Cassandra Table: A structured collection of rows and columns where data is automatically distributed across multiple nodes using a partition key.

Key Components:

Rows: Individual records (like SQL rows)
Columns: Data fields (like SQL columns)
Primary Key: Unique identifier (but WAY more powerful than SQL)
Partition Key: Determines physical data location
Clustering Key: Determines sort order within partition

Table Structure Visualization

Key Insight

Tables in Cassandra look like SQL, but behave VERY differently:

✅ Same: Rows, columns, data types
✅ Same: CREATE TABLE, INSERT, SELECT syntax
⚠️ DIFFERENT: No JOINs (denormalize instead)
⚠️ DIFFERENT: No WHERE on non-key columns (without indexes)
⚠️ DIFFERENT: Primary key determines data distribution
⚠️ DIFFERENT: Query patterns drive table design (not normalization!)

⚖️ SQL vs Cassandra Tables: Critical Differences

Understanding these differences is ESSENTIAL for effective Cassandra use!

Aspect	SQL (PostgreSQL/MySQL)	Cassandra
Primary Key	Uniqueness only	Uniqueness + Distribution + Sorting
JOINs	✅ Supported (normalized design)	❌ Not supported (denormalize!)
WHERE Clause	Any column (with indexes)	Only on primary key columns
Data Distribution	Single server (or sharded manually)	Automatic across all nodes
Sorting	ORDER BY any column (slow)	Pre-sorted by clustering key (fast!)
Transactions	ACID across tables	Atomic within partition only
Design Philosophy	Normalize, then query	Know queries, then design tables

The Golden Rule of Cassandra Tables

Query First, Schema Second!

In SQL: Design normalized schema, then write any queries you want

In Cassandra: Know your queries FIRST, then design tables to serve those queries efficiently

Example: User profile access patterns

Query 1: Get user by user_id → Table: users_by_id (partition: user_id)
Query 2: Get user by email → Table: users_by_email (partition: email)
Query 3: Get users by country → Table: users_by_country (partition: country)

Yes, you need 3 tables! Each optimized for its query pattern!

➕ CREATE TABLE Syntax & Examples

Master the complete syntax with real-world examples.

Basic Syntax

CREATE TABLE [IF NOT EXISTS] keyspace_name.table_name (
    column_name data_type [PRIMARY KEY],
    column_name data_type,
    ...,
    PRIMARY KEY ((partition_key[, ...]), [clustering_key[, ...]])
) [WITH table_options];
                        

Example 1: Simple User Table

-- Most basic table: one partition key
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    email TEXT,
    created_at TIMESTAMP
);

-- Insert data
INSERT INTO users (user_id, name, email, created_at)
VALUES (uuid(), 'Alice Johnson', 'alice@example.com', toTimestamp(now()));

-- Query (FAST - O(1) lookup)
SELECT * FROM users WHERE user_id = 123e4567-e89b-12d3-a456-426614174000;
                        

Behind the Scenes

What happens when you query by user_id:

Hash user_id using Murmur3 → Get token (e.g., -3847293847293)
Look up which node owns that token
Send query directly to that node
Node retrieves data from local disk
Return result (total time: 2-10ms)

Result: Lightning-fast O(1) lookup!

Example 2: Time-Series Data (With Clustering)

-- User activity log: partition by user, sort by timestamp
CREATE TABLE user_activity (
    user_id UUID,
    activity_time TIMESTAMP,
    activity_type TEXT,
    details TEXT,
    PRIMARY KEY (user_id, activity_time)
) WITH CLUSTERING ORDER BY (activity_time DESC);

-- Query: Get recent activity for user (FAST!)
SELECT * FROM user_activity
WHERE user_id = 123e4567-e89b-12d3-a456-426614174000
LIMIT 10;
-- Returns 10 most recent activities (pre-sorted!)
                        

Example 3: Composite Partition Key

-- Sensor data: partition by (sensor_id, date) for time-based queries
CREATE TABLE sensor_readings (
    sensor_id TEXT,
    reading_date DATE,
    reading_time TIMESTAMP,
    temperature DECIMAL,
    humidity DECIMAL,
    PRIMARY KEY ((sensor_id, reading_date), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC);

-- Query: Get all readings for sensor on specific date
SELECT * FROM sensor_readings
WHERE sensor_id = 'SENSOR_001'
  AND reading_date = '2024-01-15';
-- FAST: Single partition, pre-sorted by time!
                        

Expert: Why Composite Partition Keys?

Scenario: 1000 sensors, each producing 100,000 readings/day

Bad Design (single partition key):

PRIMARY KEY (sensor_id, reading_time)
-- Result: 100k rows per sensor per day in ONE partition
-- Size: ~1GB per partition (TOO BIG!)
-- Performance: Slow reads/writes
                            

Good Design (composite partition key):

PRIMARY KEY ((sensor_id, reading_date), reading_time)
-- Result: 100k rows per sensor per DAY = separate partitions per day
-- Size: ~3MB per partition (PERFECT!)
-- Performance: FAST reads/writes
                            

Rule of thumb: Keep partitions under 100MB (ideally under 10MB)

🔑 Primary Key: The Most Important Concept

Understanding primary keys is THE key to mastering Cassandra!

CRITICAL: Primary Key ≠ SQL Primary Key

In SQL: Primary key = uniqueness constraint (that's it)

In Cassandra: Primary key = uniqueness + data distribution + sorting + query optimization

Getting the primary key wrong = poor performance + unfixable design!

Primary Key Anatomy

Primary Key Formats

-- Format 1: Single partition key (no clustering)
PRIMARY KEY (user_id)
-- Partition: user_id | Clustering: none

-- Format 2: Partition + single clustering
PRIMARY KEY (user_id, created_at)
-- Partition: user_id | Clustering: created_at

-- Format 3: Partition + multiple clustering
PRIMARY KEY (user_id, year, month, day)
-- Partition: user_id | Clustering: year, month, day

-- Format 4: Composite partition + clustering
PRIMARY KEY ((user_id, app_id), timestamp)
-- Partition: (user_id, app_id) | Clustering: timestamp

-- Format 5: Composite partition + multiple clustering
PRIMARY KEY ((sensor_id, date), hour, minute, second)
-- Partition: (sensor_id, date) | Clustering: hour, minute, second
                        

Quick Decision Guide

Use single partition key when:

You always query by one unique identifier (user_id, order_id)
Data per partition is small (< 10MB)

Add clustering key when:

You need sorting within partition (time-series data)
You need range queries (between dates)
Multiple rows per partition

Use composite partition key when:

Single partition would be too large (> 100MB)
Natural grouping exists (sensor + date, user + month)
Need to distribute load across more partitions

🎯 Partition Key: The Secret to Speed

The most important concept in Cassandra - understanding this unlocks everything!

🏢 Real-World Analogy: The Library System

❌ SQL Way (Traditional Library):

Imagine a library with 1 million books all in ONE giant room, arranged randomly. When someone asks for "Harry Potter", the librarian must:

Check the index card catalog (like SQL index)
Walk to shelf 47,293 (could be anywhere)
Find the book
Takes 30-60 seconds

✅ Cassandra Way (Smart Library System):

Now imagine a library with 100 rooms (nodes), each room has books organized by first letter:

Room 1: Books starting with A-C
Room 2: Books starting with D-F
Room 42: Books starting with H (Harry Potter!)
etc...

When someone asks for "Harry Potter":

Calculate: H = Room 42 (instant math!)
Go directly to Room 42
Grab the book from organized shelf
Takes 2-3 seconds!

🚀 This is exactly how partition keys work!

How Partition Key Works Internally

Why This is BRILLIANT for Performance

Time Complexity:

SQL Full Table Scan: O(n) - Must check ALL rows (slow!)
SQL with Index: O(log n) - Binary search tree (better)
Cassandra Partition Key: O(1) - Direct lookup (FASTEST!)

Real Numbers (1 Billion rows):

SQL Full Scan: ~5-10 seconds
SQL Indexed: ~100-500ms
Cassandra Partition: ~2-10ms (500x faster!)

📐 Clustering Key: FREE Sorting Magic

Learn how Cassandra gives you sorted data without any performance cost!

📱 Real Example: Instagram Activity Feed

Imagine you're building Instagram. When users open the app, they want to see their recent activity - posts they liked, comments they made, photos they uploaded.

📋 Requirements:

Show activities for ONE user (not all users mixed together)
Show NEWEST activities first (like "2 minutes ago", "5 minutes ago")
Load FAST (users won't wait 5 seconds!)
User might have THOUSANDS of activities over time

Step 1️⃣: Create the Table (With Detailed Explanation)

-- Let's build the user_activity table step by step!

CREATE TABLE user_activity (
    
    -- Column 1: Who did this activity?
    user_id UUID,
    -- This will be our PARTITION KEY
    -- All activities for one user stay together
    
    -- Column 2: When did they do it?
    activity_time TIMESTAMP,
    -- This will be our CLUSTERING KEY
    -- Cassandra will automatically SORT by this!
    
    -- Column 3: What did they do?
    activity_type TEXT,
    -- Examples: "liked_post", "commented", "uploaded_photo"
    
    -- Column 4: Extra details
    details TEXT,
    -- JSON string with more info
    
    -- THE MAGIC LINE: Define the PRIMARY KEY
    PRIMARY KEY (user_id, activity_time)
    -- ^              ^
    -- |              |
    -- Partition      Clustering (sort by this)
    
) WITH CLUSTERING ORDER BY (activity_time DESC);
  -- DESC = Descending = Newest First!
  -- (Like Instagram showing newest posts at top)
                        

✨ What Just Happened?

user_id is the PARTITION KEY: All activities for user "Alice" go to ONE location (one node)
activity_time is the CLUSTERING KEY: Within Alice's partition, activities are automatically sorted by time
DESC means newest first: Most recent activity appears at the top (like Instagram feed)

Step 2️⃣: Insert Some Real Data

-- Let's add activities for Alice (user_id = aaaa-1111...)

-- Activity 1: Alice liked a post (10 minutes ago)
INSERT INTO user_activity (user_id, activity_time, activity_type, details)
VALUES (
    aaaa-1111-2222-3333-4444,
    '2024-01-15 14:20:00',
    'liked_post',
    '{"post_id": "photo_12345", "owner": "Bob"}'
);

-- Activity 2: Alice commented (5 minutes ago)
INSERT INTO user_activity (user_id, activity_time, activity_type, details)
VALUES (
    aaaa-1111-2222-3333-4444,
    '2024-01-15 14:25:00',
    'commented',
    '{"comment": "Great photo!", "post_id": "photo_67890"}'
);

-- Activity 3: Alice uploaded a photo (2 minutes ago)
INSERT INTO user_activity (user_id, activity_time, activity_type, details)
VALUES (
    aaaa-1111-2222-3333-4444,
    '2024-01-15 14:28:00',
    'uploaded_photo',
    '{"photo_id": "photo_99999", "caption": "Sunset"}'
);

-- Activity 4: Alice liked another post (1 minute ago)
INSERT INTO user_activity (user_id, activity_time, activity_type, details)
VALUES (
    aaaa-1111-2222-3333-4444,
    '2024-01-15 14:29:00',
    'liked_post',
    '{"post_id": "photo_11111", "owner": "Charlie"}'
);
                        

Step 3️⃣: How Data is Stored (Visual)

📦 Inside the Partition for Alice (user_id: aaaa-1111...):

                                    NEWEST (14:29:00) - liked_post - "photo_11111" ⬅️ Shows FIRST
                                
                                    (14:28:00) - uploaded_photo - "Sunset photo"
                                
                                    (14:25:00) - commented - "Great photo!"
                                
                                    OLDEST (14:20:00) - liked_post - "photo_12345"

✅ Cassandra already sorted them! No extra work needed!

Step 4️⃣: Query the Data (The Easy Part!)

-- Get Alice's recent activity (what Instagram does when you open the app)

SELECT * FROM user_activity
WHERE user_id = aaaa-1111-2222-3333-4444
LIMIT 10;

-- What Cassandra does behind the scenes:
-- 1. Hash user_id → Find exact node (e.g., Node 5)
-- 2. Go to Node 5, find Alice's partition
-- 3. Read first 10 rows (ALREADY SORTED!)
-- 4. Return result
-- Total time: 2-5 milliseconds! ⚡
                        

🎉 Result (What Alice sees):

Time	Activity	Details
1 min ago (14:29)	liked_post	Charlie's photo
2 min ago (14:28)	uploaded_photo	Sunset
5 min ago (14:25)	commented	"Great photo!"
10 min ago (14:20)	liked_post	Bob's photo

⚡ Delivered in 2-5 milliseconds because data was PRE-SORTED!

🆚 Comparison: WITH vs WITHOUT Clustering Key

❌

WITHOUT Clustering Key

PRIMARY KEY (user_id)
-- Only partition key!
                                

What happens:

❌ Data stored in RANDOM order
❌ Query must sort 1000+ rows
❌ Takes 100-500ms (SLOW!)
❌ High CPU usage

✅

WITH Clustering Key

PRIMARY KEY (user_id, activity_time)
-- Partition + clustering!
                                

What happens:

✅ Data stored PRE-SORTED
✅ Query reads first 10 rows
✅ Takes 2-5ms (FAST!)
✅ Minimal CPU usage

🎓 Key Lessons for Beginners

Partition Key = WHERE you must search: You MUST include "WHERE user_id = ..." in every query
Clustering Key = FREE sorting: Cassandra sorts data when you INSERT it, so queries are instant
DESC = Newest first: Like social media feeds - you want recent stuff at the top!
LIMIT = Performance saver: Only read what you need (e.g., "show me 10 activities")
Think in partitions: Each user has their own "bucket" of activities that's kept together

This is why Instagram can show feeds for 2 BILLION users in milliseconds! 🚀

🖥️ Interactive Live Console: Create, Insert, Query!

Practice the complete workflow - from table creation to querying data! See exactly what happens at each step.

Complete CQL Workflow Simulator

Enter Command (CREATE TABLE, INSERT, or SELECT):

🚀 Interactive CQL Console Ready!

This console simulates a real Cassandra database!

What you can do:
• ➕ CREATE TABLE - Make a new table
• 📝 INSERT INTO - Add data
• 🔍 SELECT - Query your data
• 📊 See exactly what happens at each step!

Try the examples above or write your own!

💡 How to Use This Console

Load an Example: Click "Simple Table", "Time Series", or "Full Workflow" to see pre-written examples
Run Multiple Commands: You can write multiple commands (separated by semicolons) and run them all at once!
See Detailed Feedback: Watch how Cassandra processes each command step-by-step
Experiment: Try creating your own tables, inserting data, and querying!

Example Complete Workflow:

1️⃣ CREATE TABLE products (id TEXT PRIMARY KEY, name TEXT, price DECIMAL);
2️⃣ INSERT INTO products VALUES ('P001', 'Laptop', 999.99);
3️⃣ SELECT * FROM products WHERE id = 'P001';

🎨 Cassandra Data Types: Complete Guide

Understanding data types helps you choose the right type for your columns!

📝 Text & String Types

Type	Description	Example	When to Use
TEXT	UTF-8 string, any length	'Hello World'	Names, emails, descriptions
VARCHAR	Same as TEXT (alias)	'alice@email.com'	Use TEXT instead (more common)
ASCII	ASCII characters only	'USA'	Country codes, tags (rarely used)

🔢 Number Types

Type	Range	Example	When to Use
INT	-2³¹ to 2³¹-1	42, -100	Age, count, quantity
BIGINT	-2⁶³ to 2⁶³-1	9223372036854775807	Timestamps, large IDs
DECIMAL	Variable precision	19.99, 1234.5678	Prices, financial data
FLOAT	32-bit IEEE 754	3.14, -0.001	Scientific calculations
DOUBLE	64-bit IEEE 754	3.141592653589793	High precision math

⏰ Date & Time Types

Type	Format	Example	When to Use
TIMESTAMP	Date + Time (millisecond)	'2024-01-15 14:30:00'	Created/updated times
DATE	Date only (no time)	'2024-01-15'	Birthdays, partition keys
TIME	Time only (nanosecond)	'14:30:00'	Schedule times, duration

🆔 Unique Identifier Types

Type	Description	Example	When to Use
UUID	Universal Unique ID (random)	123e4567-e89b-12d3...	User IDs, primary keys
TIMEUUID	UUID with timestamp (sortable)	50554d6e-29bb-11e5...	Time-ordered IDs

Choosing the Right Data Type

Common Patterns:

Primary Keys: Use UUID (random distribution) or TEXT (human-readable)
Timestamps: Use TIMESTAMP for clustering keys (automatic sorting)
Money: Use DECIMAL (exact precision, no rounding errors)
Counts: Use INT or BIGINT (whole numbers)
Text: Use TEXT (most flexible, UTF-8 support)

Example Table with Good Type Choices:

CREATE TABLE orders (
    order_id UUID,           -- Random distribution
    user_id UUID,            -- Reference to users table
    order_date DATE,         -- For partitioning by day
    created_at TIMESTAMP,    -- Exact time for sorting
    total_amount DECIMAL,    -- Exact money (no rounding)
    item_count INT,          -- Whole number
    status TEXT,             -- 'pending', 'shipped', 'delivered'
    PRIMARY KEY ((user_id, order_date), created_at)
);
                                

🏢 Real-World Table Design Patterns

Learn from production systems handling billions of requests!

🎬

Netflix: User Viewing History

Challenge: 200M+ users, track what they watched, when, and how much

CREATE TABLE viewing_history (
    user_id UUID,
    watched_at TIMESTAMP,
    content_id TEXT,
    watch_duration INT,
    device_type TEXT,
    PRIMARY KEY (user_id, watched_at)
) WITH CLUSTERING ORDER BY 
  (watched_at DESC);
                                

Why This Works:

Partition: user_id - all history for one user together
Clustering: watched_at DESC - newest shows first
Query: "Show me last 20 things I watched" = instant!

Result: 200M users × avg 100 views/user = 20B rows, query time: 2-5ms

🚗

Uber: Driver Locations

Challenge: Track 5M+ drivers, update location every 4 seconds, query by city

CREATE TABLE driver_locations (
    city TEXT,
    geohash TEXT,
    driver_id UUID,
    updated_at TIMESTAMP,
    latitude DOUBLE,
    longitude DOUBLE,
    status TEXT,
    PRIMARY KEY ((city, geohash), 
                  updated_at, driver_id)
) WITH CLUSTERING ORDER BY 
  (updated_at DESC);
                                

Why This Works:

Composite Partition: (city, geohash) - group by area
Clustering: updated_at - latest location first
Smart: Geohash divides city into small grids

Result: Find all nearby drivers in 100m radius in <3ms

🎵

Spotify: User Playlists

Challenge: 500M+ users, billions of playlists, show songs in order

CREATE TABLE playlist_songs (
    playlist_id UUID,
    position INT,
    song_id UUID,
    added_at TIMESTAMP,
    added_by UUID,
    PRIMARY KEY (playlist_id, position)
) WITH CLUSTERING ORDER BY 
  (position ASC);
                                

Why This Works:

Partition: playlist_id - all songs together
Clustering: position ASC - song 1, 2, 3...
Perfect: Songs appear in exact playlist order!

Result: Load 1000-song playlist in 5-10ms (pre-sorted!)

🎯 Common Pattern: One Query = One Table

Notice how each company creates tables optimized for SPECIFIC queries:

Example: User Profile System

Query 1: "Get user by ID"

CREATE TABLE users_by_id (
    user_id UUID PRIMARY KEY,
    name TEXT, email TEXT
);
                                    

Query 2: "Get user by email" (login)

CREATE TABLE users_by_email (
    email TEXT PRIMARY KEY,
    user_id UUID, name TEXT
);
                                    

Query 3: "Get users by country" (analytics)

CREATE TABLE users_by_country (
    country TEXT,
    user_id UUID,
    name TEXT, email TEXT,
    PRIMARY KEY (country, user_id)
);
                                    

Yes, you need 3 tables! Each optimized for its access pattern!

⭐ Best Practices: Design Like a Pro

Production-proven strategies from teams managing billions of rows!

✅

DO's

Know your queries first! Design tables for specific queries
Keep partitions small: Under 100MB (ideally <10MB)
Use UUID for IDs: Random distribution across nodes
Denormalize data: Duplicate data to avoid JOINs
Use clustering for sorting: Free pre-sorted data!
Add timestamps: Track when data was created/updated
Test with realistic data: Millions of rows, not 10

❌

DON'Ts

Don't think like SQL: No JOINs, no complex WHERE
Don't use auto-increment IDs: Sequential IDs = hot spots
Don't make huge partitions: >100MB = performance death
Don't use ALLOW FILTERING: Full table scan = slow
Don't normalize: Cassandra isn't built for JOINs
Don't query without partition key: Scans all nodes
Don't forget about time: Add timestamps to all tables

🎓

Pro Tips

Use buckets for time data: Partition by (user_id, date)
Write queries before tables: Query-driven design
Monitor partition sizes: Use nodetool tablestats
Add TTL for expiring data: Auto-delete old rows
Use composite keys wisely: Prevent partition hotspots
Test partition key distribution: Ensure even spread
Document your queries: Each table serves specific queries

Common Mistakes That Kill Performance

Mistake #1: Using Sequential IDs

-- ❌ BAD: Sequential IDs create hot spots
CREATE TABLE users (
    user_id INT PRIMARY KEY,  -- 1, 2, 3, 4...
    name TEXT
);
-- Problem: All recent users go to same node!

-- ✅ GOOD: Random UUIDs distribute evenly
CREATE TABLE users (
    user_id UUID PRIMARY KEY,  -- Random distribution
    name TEXT
);
                                

Mistake #2: Huge Partitions

-- ❌ BAD: One user's entire history in one partition
PRIMARY KEY (user_id, timestamp)
-- Problem: Active user = 1GB partition = SLOW!

-- ✅ GOOD: Bucket by time (daily/monthly)
PRIMARY KEY ((user_id, date), timestamp)
-- Result: Max ~10MB per day = FAST!
                                

Mistake #3: Query Without Partition Key

-- ❌ BAD: Queries all nodes!
SELECT * FROM users 
WHERE name = 'Alice' ALLOW FILTERING;
-- Scans EVERY node, takes 5-10 seconds!

-- ✅ GOOD: Create dedicated table for this query
CREATE TABLE users_by_name (
    name TEXT,
    user_id UUID,
    PRIMARY KEY (name, user_id)
);
SELECT * FROM users_by_name 
WHERE name = 'Alice';
-- Direct partition lookup, takes 2-5ms!
                                

💼 Interview Questions & Expert Answers

Master these questions to ace your Cassandra interviews!

1 Explain the difference between partition key and clustering key with a real example. ▼

Answer:

Partition Key: Determines WHICH node stores the data

Clustering Key: Determines HOW data is sorted WITHIN that partition

Real Example: Instagram User Activity

CREATE TABLE user_activity (
    user_id UUID,          -- Partition Key
    activity_time TIMESTAMP, -- Clustering Key
    activity_type TEXT,
    PRIMARY KEY (user_id, activity_time)
);
                                        

What happens:

user_id (Partition): All activities for user "Alice" go to Node 5
activity_time (Clustering): Within Node 5, Alice's activities are sorted by time
Result: Query "get recent activity for Alice" → Go to Node 5, read first 10 rows (already sorted!)

Analogy:

Think of a library system:

Partition Key = Building (which library building has your book?)
Clustering Key = Shelf Organization (within that building, books sorted alphabetically)

2 Why can't you query by a non-primary key column in Cassandra? ▼

Answer:

Because Cassandra doesn't know which node has that data!

Example:

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

-- ✅ This works (partition key)
SELECT * FROM users WHERE user_id = 123...;
-- Cassandra: Hash user_id → Node 7 → Read data

-- ❌ This fails (not in primary key)
SELECT * FROM users WHERE name = 'Alice';
-- Cassandra: "I don't know which nodes have name='Alice'!"
-- Would need to scan ALL 100 nodes = SLOW!
                                        

SQL vs Cassandra:

SQL: All data on one server → Can search any column (with indexes)
Cassandra: Data spread across 100+ nodes → Must know which node first!

Solution:

Create a separate table optimized for that query:

CREATE TABLE users_by_name (
    name TEXT PRIMARY KEY,
    user_id UUID,
    email TEXT
);
-- Now name is partition key!
SELECT * FROM users_by_name WHERE name = 'Alice';
-- Works perfectly!
                                        

3 When should you use a composite partition key? Give a concrete example. ▼

Answer:

Use composite partition key when a single partition key would create partitions that are TOO BIG (>100MB).

Example: IoT Sensor Data

Scenario: 1000 sensors, each producing 100,000 readings per day

-- ❌ BAD: Single partition key
CREATE TABLE sensor_data (
    sensor_id TEXT,
    reading_time TIMESTAMP,
    temperature DECIMAL,
    PRIMARY KEY (sensor_id, reading_time)
);

-- Problem:
-- One sensor = 100k readings/day × 30 days = 3M rows
-- = ~1GB partition (TOO BIG!)
-- Read/write performance degrades badly
                                        

-- ✅ GOOD: Composite partition key
CREATE TABLE sensor_data (
    sensor_id TEXT,
    reading_date DATE,
    reading_time TIMESTAMP,
    temperature DECIMAL,
    PRIMARY KEY ((sensor_id, reading_date), 
                  reading_time)
);

-- Solution:
-- One sensor per DAY = 100k rows = ~3MB partition
-- PERFECT size! Fast reads/writes!
                                        

Rule of Thumb:

Partition size should be: 10MB - 100MB max
If one partition would exceed this, add time-based bucketing (date, month, hour)
Netflix partitions by (user_id, date) for viewing history
Uber partitions by (city, geohash) for driver locations

4 How would you design a table for Twitter's timeline feature? ▼

Answer:

Requirements Analysis:

Show tweets for ONE user's timeline
Newest tweets first
Fast loading (users expect <100ms)
User might have 10,000+ tweets in timeline

Design:

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

Why This Works:

Partition Key (user_id): All timeline tweets for one user stored together
Clustering Key (tweet_time DESC): Newest tweets appear first (automatic sorting!)
Denormalized: Store author_name with tweet (no JOIN needed)

Query Pattern:

-- Get user's timeline (newest 50 tweets)
SELECT * FROM user_timeline
WHERE user_id = alice_id
LIMIT 50;

-- Result: 2-5ms (O(1) partition lookup + pre-sorted)
                                        

Important Note:

When someone tweets, Twitter writes that tweet to ALL their followers' timelines:

Write amplification: 1 tweet × 1000 followers = 1000 writes
Trade-off: Slow writes, FAST reads
Perfect for Twitter: Read-heavy (millions read, few write)

5 What's wrong with using ALLOW FILTERING? When is it acceptable? ▼

Answer:

What ALLOW FILTERING Does:

Forces Cassandra to scan ALL partitions and filter results in memory

Example:

CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    category TEXT,
    price DECIMAL,
    in_stock BOOLEAN
);

-- ❌ TERRIBLE performance
SELECT * FROM products
WHERE category = 'electronics'
  AND price < 100
  AND in_stock = true
ALLOW FILTERING;

-- What happens:
-- 1. Cassandra reads ALL 10 million products from ALL nodes
-- 2. Filters in memory (category, price, in_stock)
-- 3. Returns matching products
-- Time: 5-30 SECONDS (cluster-wide scan!)
                                        

Why It's Bad:

Full cluster scan: Reads data from ALL nodes
Memory intensive: Must load and filter millions of rows
Slow: Takes seconds instead of milliseconds
Not scalable: Gets worse as data grows

Correct Solution:

-- Create table optimized for this query!
CREATE TABLE products_by_category (
    category TEXT,
    price DECIMAL,
    product_id UUID,
    in_stock BOOLEAN,
    PRIMARY KEY (category, price, product_id)
) WITH CLUSTERING ORDER BY (price ASC);

-- Now this query is FAST!
SELECT * FROM products_by_category
WHERE category = 'electronics'
  AND price < 100
  AND in_stock = true
ALLOW FILTERING;  -- Now only filters in_stock within partition

-- Time: 2-10ms (single partition scan)
                                        

When ALLOW FILTERING is Acceptable:

✅ Analytics/reporting queries: Run occasionally, not user-facing
✅ Small datasets: Testing with <1000 rows
✅ Within single partition: Already filtered by partition key
❌ NEVER in production user-facing queries!

Key Lesson:

If you need ALLOW FILTERING, your table design is wrong! Create a new table optimized for that query.

🎓 Chapter Summary: Tables Mastery

Congratulations! You now understand Cassandra tables!

Key Concepts Mastered:

Tables: Look like SQL but behave for distribution
Primary Key: Uniqueness + distribution + sorting
Partition Key: Determines which node (O(1) lookup)
Clustering Key: Sorts data within partition
Design Philosophy: Know queries first, then design tables

The Golden Rules:

Query First, Schema Second - Design tables for your queries
One Query = One Table - Denormalize for performance
Keep Partitions Small - Under 100MB (ideally under 10MB)
Use Clustering for Sorting - Pre-sorted data = fast queries
No JOINs! - Embed or create multiple tables

🚀 You're ready to design production-quality tables!

Responsive Ad