Interview Preparation

System Design

Design complete systems from scratch - URL Shortener, Instagram, Uber, and more with Cassandra!

🏗️ Cassandra System Design Interviews

System design questions test your ability to architect complete solutions from scratch!

What Interviewers Evaluate:

📊 Requirements: Can you ask the right questions?
📈 Scale Estimation: Calculate storage, throughput, QPS
🎨 Schema Design: Query-driven modeling
🏗️ Architecture: Components, data flow, APIs
⚖️ Trade-offs: Why Cassandra vs alternatives?
🚀 Scaling: Handle growth, hotspots

System Design Interview Structure (45-60 min)

⏰ 5 min: Clarify requirements & constraints

⏰ 5 min: Estimate scale (users, storage, QPS)

⏰ 10 min: High-level architecture & API design

⏰ 15 min: Database schema & data modeling

⏰ 10 min: Deep dive (caching, sharding, consistency)

⏰ 5 min: Bottlenecks & scaling strategies

📋 System Design Framework

Requirements Gathering (5 minutes)

Essential Questions to Ask

Functional Requirements:

What features are needed? (Core vs nice-to-have)
What queries will be most common?
Read-heavy or write-heavy?
Real-time requirements?

Non-Functional Requirements:

📊 Scale: How many users? DAU/MAU?
⚡ Performance: Latency expectations? (p50, p99)
🌍 Availability: 99.9%? 99.99%? Multi-region?
⚖️ Consistency: Strong vs eventual?
💾 Durability: Data retention period?

Scale Estimation (5 minutes)

Back-of-Envelope Calculations

Example: URL Shortener

// Assumptions
Users: 100M DAU
Ratio: 100:1 read:write

// Write QPS
URLs shortened/day: 10M
Write QPS: 10M / 86400 = ~115 writes/sec
Peak: 115 × 3 = ~350 writes/sec

// Read QPS
Reads/day: 10M × 100 = 1B
Read QPS: 1B / 86400 = ~11,500 reads/sec
Peak: 11,500 × 3 = ~35,000 reads/sec

// Storage (5 years)
URLs total: 10M × 365 × 5 = 18B URLs
Per URL: ~500 bytes (URL + metadata)
Total: 18B × 500 = 9TB raw data
With RF=3: 9TB × 3 = 27TB

🔗 Design: URL Shortener (like bit.ly)

Requirements & Scale

System Requirements

Functional:

✅ Shorten URL (generate short code)
✅ Redirect short URL → original URL
✅ Optional custom alias
✅ Analytics (click count, geography)
✅ Expiration/TTL

Scale:

📊 100M DAU, 10M new URLs/day
⚡ Read:Write = 100:1
🎯 Write QPS: ~115 (peak ~350)
🎯 Read QPS: ~11,500 (peak ~35,000)
💾 Storage: 27TB (5 years, RF=3)

Schema Design

-- Primary table: Redirect lookups (READ-HEAVY)
CREATE TABLE urls (
    short_code text PRIMARY KEY,    ← 7 chars: abc1234
    original_url text,
    user_id uuid,
    created_at timestamp,
    expires_at timestamp,
    PRIMARY KEY (short_code)
);

-- User's URLs
CREATE TABLE urls_by_user (
    user_id uuid,
    created_at timestamp,
    short_code text,
    original_url text,
    PRIMARY KEY (user_id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Analytics: Click tracking
CREATE TABLE url_clicks (
    short_code text,
    click_date text,           ← Bucket: 'YYYY-MM-DD'
    click_time timestamp,
    ip_address text,
    country text,
    referrer text,
    PRIMARY KEY ((short_code, click_date), click_time)
) WITH CLUSTERING ORDER BY (click_time DESC)
  AND default_time_to_live = 2592000;  -- 30 days

-- Analytics: Aggregated stats
CREATE TABLE url_stats (
    short_code text,
    date text,                 ← 'YYYY-MM-DD'
    click_count counter,
    unique_visitors counter,
    PRIMARY KEY (short_code, date)
);

Key Design Decisions

Short Code Generation:

Base62 encoding (a-z, A-Z, 0-9) = 62^7 = 3.5 trillion URLs
Use distributed ID generator (Snowflake) → base62 encode
Or MD5(URL + timestamp) → take first 7 chars → check collision

Why This Schema:

🔍 urls: Partition by short_code (high cardinality, even distribution)
📊 url_clicks: Bucketed by date (bounded partitions)
📈 url_stats: Counters for aggregated metrics

Architecture & Flow

System Components

Write Flow (Shorten URL):

Client → API Gateway → Shortener Service
Generate short_code (Snowflake ID → Base62)
Write to Cassandra (urls + urls_by_user)
Cache in Redis (short_code → original_url)
Return short URL to client

Read Flow (Redirect):

Client → API Gateway → Redirect Service
Check Redis cache (99% hit rate)
If miss: Query Cassandra urls table
Cache result in Redis (TTL = 1 day)
Async: Write click event to Kafka
Return 301/302 redirect

Analytics Pipeline:

Kafka → Spark Streaming → Aggregate clicks
Write to url_clicks (raw data)
Update url_stats counters (aggregated)

Optimization & Scaling

⚡

Caching

Redis: Hot URLs (80/20 rule)
Hit Rate: 99%+
TTL: 1 day
Size: ~10M hot URLs × 500B = 5GB
Eviction: LRU

🔥

Hot Partitions

Problem: Viral URL (millions of reads)
Solution: CDN caching
CloudFlare: Cache redirects at edge
TTL: 1 hour
Benefit: Offload 95% traffic

📊

Cassandra Tuning

Consistency: QUORUM writes, ONE reads
RF: 3 per DC
Compaction: LCS (read-optimized)
Row Cache: Enabled for urls table
Nodes: 6 nodes (3 per DC)

📸 Design: Instagram Photo Sharing

Requirements & Scale

System Requirements

Functional:

✅ Upload photos
✅ Follow/unfollow users
✅ View timeline (feed)
✅ Like/comment on photos
✅ User profiles

Scale:

📊 500M users, 200M DAU
📸 100M photos uploaded/day
👥 Average 200 followers/user
⚡ Write QPS: ~1,200 photos/sec
⚡ Read QPS: ~100,000 timeline loads/sec

Schema Design

-- User timeline (fan-out on write)
CREATE TABLE user_timeline (
    user_id uuid,
    photo_id timeuuid,
    author_id uuid,
    author_username text,
    photo_url text,
    caption text,
    posted_at timestamp,
    PRIMARY KEY (user_id, photo_id)
) WITH CLUSTERING ORDER BY (photo_id DESC);

-- User's own photos
CREATE TABLE photos_by_user (
    user_id uuid,
    photo_id timeuuid,
    photo_url text,
    caption text,
    likes counter,
    posted_at timestamp,
    PRIMARY KEY (user_id, photo_id)
) WITH CLUSTERING ORDER BY (photo_id DESC);

-- Photo details
CREATE TABLE photos (
    photo_id timeuuid PRIMARY KEY,
    user_id uuid,
    username text,
    photo_url text,
    caption text,
    likes counter,
    created_at timestamp
);

-- Followers
CREATE TABLE followers (
    user_id uuid,
    follower_id uuid,
    follower_username text,
    followed_at timestamp,
    PRIMARY KEY (user_id, follower_id)
);

-- Following
CREATE TABLE following (
    user_id uuid,
    following_id uuid,
    following_username text,
    followed_at timestamp,
    PRIMARY KEY (user_id, following_id)
);

-- Likes
CREATE TABLE photo_likes (
    photo_id timeuuid,
    user_id uuid,
    username text,
    liked_at timestamp,
    PRIMARY KEY (photo_id, user_id)
);

Fan-Out Challenge

Problem: User with 10M followers posts photo → 10M writes!

Solution: Hybrid Approach

📊 Regular users (<10K followers): Fan-out on write
🌟 Celebrities (>10K followers): Fan-out on read
💾 Cache celebrity timelines: Redis sorted sets

Timeline Generation

Two Approaches

Approach 1: Fan-out on Write (Instagram's Actual Approach)

// When user posts photo
1. Upload photo to S3/CDN
2. Write to photos table
3. Get user's followers (from followers table)
4. For each follower:
     Write photo to their user_timeline
5. This happens async via Kafka

// When user views timeline
SELECT * FROM user_timeline 
WHERE user_id = 'current_user' 
LIMIT 50;

✅ Pros: Fast reads (pre-computed)
❌ Cons: Expensive writes for celebrities

Approach 2: Fan-out on Read (Twitter-style)

// When user views timeline
1. Get users they follow
2. For each following:
     Query photos_by_user for recent photos
3. Merge and sort all photos by timestamp
4. Cache result in Redis

✅ Pros: Simple writes
❌ Cons: Slow reads (multiple queries + merge)

🚗 Design: Uber (Ride Matching)

Requirements & Challenges

Core Features

Functional:

✅ Riders request rides
✅ Match rider with nearby driver
✅ Real-time driver location updates
✅ Ride history
✅ Real-time tracking

Challenges:

🌍 Geospatial: Find nearby drivers (within 5km)
⚡ Real-time: Location updates every 4 seconds
📊 Scale: 1M active drivers, 100M location updates/min
🎯 Latency: Match in <2 seconds

Schema Design (Cassandra + Redis)

-- Driver locations (WRITE-HEAVY, recent data only)
CREATE TABLE driver_locations (
    geohash text,              ← 6 chars: ~1.2km precision
    driver_id uuid,
    latitude decimal,
    longitude decimal,
    bearing int,
    speed decimal,
    status text,               -- 'available', 'busy'
    updated_at timestamp,
    PRIMARY KEY (geohash, driver_id)
) WITH default_time_to_live = 300;  -- 5 min TTL

-- Ride history
CREATE TABLE rides (
    ride_id timeuuid PRIMARY KEY,
    rider_id uuid,
    driver_id uuid,
    pickup_lat decimal,
    pickup_lng decimal,
    dropoff_lat decimal,
    dropoff_lng decimal,
    status text,
    created_at timestamp,
    completed_at timestamp,
    fare decimal
);

-- Rider's rides
CREATE TABLE rides_by_rider (
    rider_id uuid,
    ride_id timeuuid,
    driver_name text,
    pickup_location text,
    dropoff_location text,
    fare decimal,
    created_at timestamp,
    PRIMARY KEY (rider_id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

-- Driver's rides (same structure)
CREATE TABLE rides_by_driver (
    driver_id uuid,
    ride_id timeuuid,
    rider_name text,
    fare decimal,
    created_at timestamp,
    PRIMARY KEY (driver_id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC);

Geospatial Challenge

Problem: Cassandra doesn't have native geospatial queries!

Solution: Geohashing + Redis

🗺️ Geohash: Convert lat/lng → geohash (u4pruydqqvj)
📊 Precision: 6 chars = ~1.2km × 0.6km grid
⚡ Redis Sorted Sets: Store available drivers per geohash
🔍 Search: Query current + 8 neighboring geohashes

# Redis for real-time matching
GEOADD drivers:available -122.4194 37.7749 driver123

# Find nearby (5km radius)
GEORADIUS drivers:available -122.4194 37.7749 5 km

Architecture

Hybrid Architecture

Hot Path (Matching):

🔥 Redis Geo: Real-time driver locations (available drivers only)
⚡ Match in <2sec: GEORADIUS query
💾 TTL: Remove inactive drivers (60 sec)

Cold Path (History):

📊 Cassandra: All rides, ride history, analytics
💾 S3: Historical location data for ML/analytics

Location Updates Flow:

Driver app → Kafka (location events)
Stream processor → Update Redis (if available)
Batch job → Write to Cassandra (every 5 min)
Archived to S3 (daily)

🏆 Design: Real-Time Gaming Leaderboard

Requirements

Features

✅ Update player score in real-time
✅ Get top 100 global players
✅ Get player's rank
✅ Get players around user (±50 ranks)
⚡ Scale: 10M concurrent players, 100K score updates/sec

Hybrid Solution: Redis + Cassandra

-- Cassandra: Permanent storage
CREATE TABLE player_scores (
    player_id uuid PRIMARY KEY,
    username text,
    score bigint,
    rank int,
    last_updated timestamp
);

-- By score (for rank calculation)
CREATE TABLE players_by_score (
    game_id text,
    score bigint,
    player_id uuid,
    username text,
    PRIMARY KEY (game_id, score, player_id)
) WITH CLUSTERING ORDER BY (score DESC);

Redis for Real-Time

# Redis Sorted Set (THE solution for leaderboards)
ZADD leaderboard:global 9500 player123

# Top 100
ZREVRANGE leaderboard:global 0 99 WITHSCORES

# Player's rank (O(log N))
ZREVRANK leaderboard:global player123

# Players around user
rank = ZREVRANK leaderboard:global player123
ZREVRANGE leaderboard:global (rank-50) (rank+50) WITHSCORES

Why Redis for Leaderboard:

⚡ O(log N) operations: ZADD, ZRANK, ZRANGE
🔥 In-memory: Millisecond latency
📊 Built-in: Sorted sets perfect for this

Cassandra Role:

💾 Persistent storage (Redis crashes → recover from Cassandra)
📊 Historical data & analytics
🔄 Periodic sync (every 5 min)

📊 Design: Real-Time Analytics Dashboard

Use Case: Website Analytics (like Google Analytics)

Requirements

✅ Track page views, clicks, events
✅ Real-time dashboard (active users, popular pages)
✅ Historical reports (daily, weekly, monthly)
⚡ Scale: 1M websites, 10B events/day

Schema Design (Time-Series Optimized)

-- Raw events (write-heavy, with TTL)
CREATE TABLE page_views (
    website_id uuid,
    date text,                 ← Bucket: 'YYYY-MM-DD'
    timestamp timestamp,
    page_url text,
    user_id text,
    session_id text,
    referrer text,
    country text,
    device text,
    PRIMARY KEY ((website_id, date), timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC)
  AND default_time_to_live = 2592000   -- 30 days
  AND compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_size': 1,
    'compaction_window_unit': 'DAYS'
  };

-- Aggregated stats (pre-computed)
CREATE TABLE daily_stats (
    website_id uuid,
    date text,
    page_url text,
    views counter,
    unique_visitors counter,
    PRIMARY KEY ((website_id, date), page_url)
);

-- Real-time active users (1-hour window)
CREATE TABLE active_users (
    website_id uuid,
    minute text,               ← 'YYYY-MM-DD HH:MM'
    user_id text,
    last_seen timestamp,
    PRIMARY KEY ((website_id, minute), user_id)
) WITH default_time_to_live = 3600;  -- 1 hour

-- Top pages (hourly aggregation)
CREATE TABLE top_pages (
    website_id uuid,
    hour text,                 ← 'YYYY-MM-DD-HH'
    views bigint,
    page_url text,
    PRIMARY KEY ((website_id, hour), views, page_url)
) WITH CLUSTERING ORDER BY (views DESC);

Time-Series Best Practices

⏰ Time Bucketing: YYYY-MM-DD for bounded partitions
🗜️ TWCS Compaction: Perfect for time-series data
⏳ TTL: Auto-delete old data (30 days raw, keep aggregates longer)
📊 Pre-aggregate: Real-time Spark/Flink → update counters
💾 Multi-tier: Hot data (Cassandra) → Cold data (S3)

Lambda Architecture

Three Layers

1. Speed Layer (Real-time):

Events → Kafka → Flink/Spark Streaming
Update Cassandra counters every minute
Redis for current active users (sorted sets)

2. Batch Layer (Historical):

Hourly/Daily Spark jobs
Aggregate from page_views → daily_stats
Generate reports, insights

3. Serving Layer:

API combines real-time + batch views
Cache in Redis (dashboard queries)
Cassandra for raw data queries

💡 System Design Interview Tips

Keys to Success

❓ Ask Questions: Clarify requirements before designing
📊 Estimate Scale: Always do back-of-envelope calculations
🎯 Query Patterns: List all queries before schema design
⚖️ Trade-offs: Explain why Cassandra vs MySQL vs Redis
🏗️ Start Simple: Basic design first, then optimize
🔍 Deep Dive: Be ready to drill into any component
📈 Scaling: Discuss bottlenecks and solutions

✅

When to Use Cassandra

Write-heavy workloads
Time-series data
High availability needed
Linear scalability required
Simple query patterns
Eventual consistency OK

❌

When NOT Cassandra

Complex JOINs needed
Strong ACID required
Ad-hoc queries
Small dataset (<1TB)
Frequent schema changes
Relational integrity critical

🔄

Hybrid Solutions

Redis: Caching, real-time
PostgreSQL: Transactional data
Cassandra: Events, history
S3: Cold storage
Elasticsearch: Search

Common Patterns Summary

⏰ Time Bucketing: Essential for time-series (YYYY-MM-DD)
📦 Denormalization: One table per query pattern
🔥 Fan-out: On write (Instagram) vs on read (Twitter)
💾 Caching: Redis for hot data (80/20 rule)
📊 Counters: For aggregated metrics
🔄 TWCS: For time-series compaction
⚡ Hybrid: Combine Cassandra + Redis + PostgreSQL

🎯 You're Ready for System Design Interviews!

You now have complete end-to-end system design experience with Cassandra!

🏗️ Systems Designed:

🔗 URL Shortener (bit.ly) - Caching, analytics, short code generation
📸 Instagram - Fan-out on write, timeline generation, photo storage
🚗 Uber - Geospatial matching, real-time locations, hybrid Redis+Cassandra
🏆 Leaderboard - Real-time ranking with Redis sorted sets
📊 Analytics - Time-series data, TWCS, Lambda architecture

💡 Remember:

❓ Clarify First: Requirements, scale, constraints
📊 Estimate Scale: QPS, storage, bandwidth
🎯 Query Patterns: Design schema around queries
⚖️ Trade-offs: Why this database, not that one
🔄 Hybrid Solutions: Combine multiple databases
📈 Scaling Strategy: Bottlenecks and solutions

🏗️ Practice complete system designs - it's the ultimate interview test! 🚀

Responsive Ad