Bird
Raised Fist0

Wide-Column Stores - Cassandra Partitioning and Data Modeling

HLDmediumInterview freq: common
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🏗️
Wide-Column Stores - Cassandra Partitioning and Data Modeling
Designing scalable partitioning and data modeling strategies for Cassandra at 2B DAU scale
mediumDatabases - NoSQLFreq: common
Prerequisites:NoSQL DatabasesConsistent HashingDistributed Systems BasicsData Modeling in Wide-Column Stores
⚙️
Functional Requirements
  • Store and retrieve user-generated messages with low latency
  • Support efficient range queries on time-series data (e.g., messages sorted by timestamp)
  • Allow scalable writes at peak load without hotspots
  • Support TTL-based data expiration for message retention
  • Enable querying messages by user and conversation
  • Provide eventual consistency with tunable consistency levels
📊
Non-Functional Requirements
  • Support 2 billion daily active users (DAU)
  • Handle 40 messages per user per day on average
  • Achieve 99.99% availability SLA
  • Ensure 100ms P99 read and write latency
  • Data retention of 30 days with automatic expiry
❓
Clarifying Questions to Ask
  • What is the expected message size and variability?
  • Are queries mostly by user, conversation, or global?
  • What is the acceptable consistency model (e.g., eventual, quorum)?
  • What is the data retention policy and TTL requirements?
  • Are there any requirements for secondary indexes or materialized views?
  • What is the expected read-to-write ratio?
Out of Scope
✗ Full-text search or complex secondary indexing
✗ Cross-datacenter replication and disaster recovery
✗ Real-time analytics or OLAP workloads
✗ Strong consistency or distributed transactions
📌
Assumptions
  • 2 billion DAU
  • 40 messages per user per day
  • Average message size 100 bytes
  • Retention period 30 days
⚡
QPS Calculation
write qps2B × 40 / 86400 ≈ 925,926 writes/sec
peak write qpsAssuming 2× peak factor → 1,851,852 writes/sec
read qpsAssuming 1.5 reads per write → 1.39M reads/sec
💾
Storage Estimate
daily storage gb(2B × 40 × 100 bytes) / (1024³) ≈ 7.45 TB/day
retention storage gb7.45 TB × 30 days ≈ 223.5 TB
with replication223.5 TB × 3 replicas = 670.5 TB total
🖥️
Server Estimate
cassandra nodesAssuming 500 GB usable per node → 670.5 TB / 0.5 TB ≈ 1341 nodes
coordinator nodesAssuming 10k QPS per coordinator → 1.85M / 10k ≈ 185 coordinators
🏗️
Architecture Diagram
Architecture Diagram
          +----------------------+
          |     Client API       |
          +----------+-----------+
                     |
          +----------v-----------+
          |  Load Balancer /     |
          |  Coordinator Nodes   |
          +----------+-----------+
                     |
          +----------v-----------+
          |   Cassandra Cluster  |
          |  (Partitioned by PK) |
          +----------+-----------+
                     |
          +----------v-----------+
          |  Storage on SSDs     |
          +----------------------+
🧩
Components
Client API
Receives client requests and routes them to coordinator nodes
Why thisStateless API layer enables horizontal scaling and request validation using REST or gRPC
Why notDirect client-to-Cassandra bypasses load balancing and complicates client logic
Load Balancer / Coordinator Nodes
Distributes requests to Cassandra nodes and manages consistency levels
Why thisCassandra's native coordinator nodes handle query routing and tunable consistency
Why notUsing external proxies like Envoy adds latency and complexity
Cassandra Cluster
Stores data partitioned by partition key with clustering columns for sorting
Why thisCassandra's wide-column model supports high write throughput and tunable consistency
Why notHBase requires HDFS and has higher operational complexity at this scale
Partition Key Design
Determines data distribution and hotspot avoidance
Why thisComposite partition keys with user_id and time bucket prevent hotspots and enable efficient queries
Why notSingle user_id partition key causes uneven load and large partitions
Clustering Columns
Sort data within partitions by timestamp or message_id for efficient range scans
Why thisEnables time-ordered queries and TTL expiration within partitions
Why notSecondary indexes cause performance degradation at high write QPS
Storage on SSDs
Persist data with low latency and high IOPS
Why thisSSDs provide fast random reads/writes needed for Cassandra's LSM-tree storage
Why notHDDs have higher latency and lower throughput, causing P99 latency spikes
🔄
Data Flow
1
Client API → Load Balancer / Coordinator Nodes
Receive write/read request and forward with consistency level
Ensures request routing and load distribution
2
Coordinator Nodes → Cassandra Cluster
Determine partition key and route request to responsible nodes
Uses consistent hashing to locate partition replicas
3
Cassandra Cluster → Storage on SSDs
Write/read data to/from SSTables and memtables
LSM-tree structure optimizes write throughput
4
Storage on SSDs → Cassandra Cluster
Return read results or write acknowledgments
Ensures durability and consistency per configured level
5
Cassandra Cluster → Coordinator Nodes
Aggregate responses from replicas and return to client
Handles quorum reads/writes and retries
6
Coordinator Nodes → Client API
Send final response to client
Completes request lifecycle

Practice

(1/5)
1. A blob storage system must handle millions of small object writes per second with low latency and guarantee durability. Which component is primarily responsible for decoupling the high write throughput from the slower disk persistence layer?
easy
A. Use a single master node to serialize all writes
B. Use a relational database to store all object metadata synchronously
C. Use a CDN edge cache to absorb write bursts
D. Use a distributed write-ahead log (e.g., Kafka) to buffer writes before persistence

Solution

  1. Step 1: Identify the role of the distributed log

    The distributed write-ahead log buffers incoming writes asynchronously, decoupling high write throughput from slower disk persistence.

  2. Step 2: Eliminate other options

    Relational DBs synchronously block writes, CDN caches are read-optimized not write buffers, and a single master node creates a bottleneck.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Distributed log buffers writes asynchronously for durability and throughput [check]
Hint: Distributed log buffers writes before disk persistence [check]
Common Mistakes:
2. In a chat system request flow, at Step 4 the Delivery Worker checks the recipient's online status cache. What happens if the cache entry is expired at this step?
easy
A. Delivery Worker falls back to persistent message queue for offline delivery
B. Delivery Worker immediately marks message as delivered
C. Delivery Worker drops the message assuming recipient is offline
D. Delivery Worker retries checking cache indefinitely

Solution

  1. Step 1: Understand Delivery Worker role at Step 4

    It checks recipient online status cache to decide delivery method.

  2. Step 2: Behavior on expired cache entry

    Expired cache means unknown status; system falls back to persistent queue to ensure message is not lost.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Expired cache triggers fallback to persistent queue [check]
Hint: Expired cache triggers fallback, not message drop or infinite retry [check]
Common Mistakes:
3. A critical database cluster must failover automatically without data loss or downtime during a primary node crash. Which component is responsible for orchestrating this failover in a distributed SQL database system?
easy
A. Consensus protocol (e.g., Raft) managing leader election and log replication
B. Load balancer redirecting traffic based on health checks
C. Client-side retry logic to reconnect to a new primary node
D. Backup scheduler triggering periodic snapshots for recovery

Solution

  1. Step 1: Identify failover orchestration component

    The consensus protocol like Raft manages leader election and ensures log replication consistency, enabling automatic failover without data loss.

  2. Step 2: Eliminate other options

    Client-side retries (A) handle reconnection but don't orchestrate failover; load balancers (B) redirect traffic but don't manage state; backups (D) aid recovery but not live failover.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Consensus protocol controls leader election and failover [check]
Hint: Consensus protocols manage failover, not clients or load balancers
Common Mistakes:
4. In a system where Step 4 is the Delivery Worker checking message delivery status, what happens if the Delivery Worker finds the recipient's presence TTL expired?
easy
A. The Delivery Worker queues the message for offline delivery and updates status to 'sent but not delivered'
B. The Delivery Worker immediately marks the message as delivered without retry
C. The Delivery Worker deletes the message assuming recipient is unreachable
D. The Delivery Worker retries sending indefinitely until TTL is refreshed

Solution

  1. Step 1: Trace Delivery Worker decision at Step 4 -- Bob's presence TTL

    If TTL expired, recipient is offline; message can't be delivered immediately.

  2. Step 2: Understand offline delivery handling

    Delivery Worker queues message for later delivery and marks status as 'sent but not delivered' to reflect state.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Expired TTL -> queue message, status 'sent but not delivered' [check]
Hint: Expired TTL means offline, queue message for later delivery [check]
Common Mistakes:
5. For a global DNS service aiming to minimize latency for users worldwide, which architecture is more appropriate and why?
medium
A. A single centralized authoritative DNS server with a large cache to serve all queries
B. A recursive DNS server that forwards all queries to root servers directly without caching
C. A distributed network of authoritative DNS servers geographically located near users with DNS Anycast routing
D. A peer-to-peer DNS system where clients query each other for domain resolution

Solution

  1. Step 1: Analyze latency requirements and architecture options

    A single centralized server creates latency for distant users and is a single point of failure. Peer-to-peer systems lack reliability and consistency guarantees. Recursive servers forwarding to root servers without caching cause excessive load and latency.

  2. Step 2: Identify benefits of distributed authoritative servers with Anycast

    Geographically distributed authoritative servers with Anycast routing direct users to the nearest server, minimizing latency and improving availability.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Distributed authoritative servers + Anycast reduce latency globally [check]
Hint: Anycast + distributed authoritative servers minimize global latency [check]
Common Mistakes:
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard