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
45 min · senior
senior

You are designing the backend data storage for a messaging platform with 50 million daily active users (DAU). The system must support real-time message delivery and offline message sync with P99 latency under 200ms. Messages are encrypted at rest and in transit. Users can reconnect from multiple devices and expect consistent message ordering and efficient cursor-based sync. The system must handle 925K messages per second on average, with spikes up to 2M msg/sec. Data modeling and partitioning in Cassandra must ensure linear write scalability, efficient range queries for message sync, and avoid hotspots.

Constraints
  • End-to-end encryption required for all messages at rest and in transit
  • Offline message delivery with cursor-based sync on reconnect
  • P99 read and write latency under 200ms at peak load
  • Support 50M DAU generating ~925K messages/sec average
  • Data model must avoid partition hotspots and support efficient range scans
📋
Guided Steps
1/5
Step 1
Choosing the Partition Key
What partition key would you choose in Cassandra for storing messages to ensure scalability and ordered retrieval?
💡 Hint
Consider grouping messages by conversation to enable efficient range scans and avoid cross-partition queries.
Key Decision
Use conversation_id as the Cassandra partition key to ensure all messages in a conversation reside on the same node, enabling O(1) range scans for cursor-based sync and linear write scalability with LSM-tree storage.
Interviewer Expectation
Mid-level: Names conversation_id as partition key. Senior: Explains why sender_id or timestamp alone cause cross-partition queries or hotspots, and how conversation_id ensures message ordering and locality.
⚠️Common Wrong Answer
Using sender_id as partition key causes messages to scatter across nodes, breaking ordered retrieval and increasing read complexity.
Step 2
Clustering Columns and Data Modeling
How would you design clustering columns in Cassandra to support efficient message ordering and pagination?
💡 Hint
Think about sorting messages by timestamp or sequence number within a partition to enable range queries for sync.
Key Decision
Use a clustering column of message_timestamp (or a monotonically increasing sequence) in descending order to enable efficient range scans for recent messages and cursor-based pagination.
Interviewer Expectation
Mid-level: Mentions timestamp clustering. Senior: Explains descending order clustering for efficient retrieval of latest messages first and how it supports cursor-based sync without full partition scans.
⚠️Common Wrong Answer
Not using clustering columns or using ascending order causes inefficient full partition scans and higher latency on sync queries.
Step 3
Handling Hot Partitions and Data Skew
How would you mitigate hotspots caused by very active conversations or popular group chats in Cassandra?
💡 Hint
Consider adding a bucketing or sharding mechanism within the partition key to distribute load evenly.
Key Decision
Add a bucketing suffix (e.g., hash of message_timestamp modulo N) to the partition key to split large partitions into smaller ones, preventing hotspots while preserving range query ability within buckets.
Interviewer Expectation
Mid-level: Suggests sharding without details. Senior: Explains trade-offs of bucketing, how it prevents large partitions exceeding Cassandra’s recommended 100MB size, and how to merge results client-side for sync.
⚠️Common Wrong Answer
Ignoring hotspots leads to large partitions causing GC pauses and increased read/write latency.
Step 4
Encryption and Data Security
How do you ensure end-to-end encryption for messages stored in Cassandra while maintaining queryability for sync?
💡 Hint
Think about encrypting message payloads but leaving partition and clustering keys in plaintext for query routing.
Key Decision
Encrypt message payloads client-side before writing to Cassandra; store partition_key (conversation_id) and clustering columns (timestamp) in plaintext to enable efficient queries without decrypting metadata.
Interviewer Expectation
Mid-level: Mentions encryption at rest. Senior: Explains client-side encryption of payloads, key management, and why partition keys must remain unencrypted for Cassandra routing.
⚠️Common Wrong Answer
Encrypting entire rows including partition keys breaks Cassandra’s ability to route queries, causing full table scans and high latency.
Step 5
API Design for Message Sync and Delivery
Design 3 core API endpoints for sending messages, syncing missed messages, and acknowledging delivery. What are their inputs, outputs, and key considerations?
💡 Hint
Consider how the client sends a message with encryption, how it requests messages after a cursor, and how it confirms receipt to update delivery status.
Key Decision
Endpoints: (1) POST /messages/send with encrypted payload and conversation_id; (2) GET /messages/sync with conversation_id and last_received_timestamp for range query; (3) POST /messages/ack with message_ids to update delivery status asynchronously.
Interviewer Expectation
Mid-level: Names endpoints and basic inputs. Senior: Details encryption handling, cursor-based sync using clustering keys, and asynchronous ack processing to avoid blocking writes.
⚠️Common Wrong Answer
Using HTTP polling for sync causes massive load spikes and latency issues at 50M DAU.
🔌
API Design Challenge
Sketch 3 core API endpoints for the messaging backend focusing on message send, message sync, and message acknowledgement.
Hints
  • How does the client send a message and ensure it is durably stored?
  • How does the client efficiently sync missed messages after reconnect?
  • How does the recipient acknowledge message receipt to update delivery status?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Correct choice of conversation_id as partition key with reasoning
  • Use of clustering columns for ordered message retrieval
  • Handling hotspots with bucketing or sharding
  • Client-side encryption of payloads with plaintext partition keys
  • API design covering send, sync, and ack endpoints
Good to Have - 30%
  • Explanation of trade-offs in bucketing
  • Details on asynchronous ack processing
  • Discussion of P99 latency constraints
Bonus - 10%
  • Innovative approaches to key management
  • Advanced query optimization techniques
Anti-patterns to Avoid
✅
Model Answer
▼ Reveal

This design uses Cassandra with conversation_id as the partition key to co-locate messages per conversation, enabling efficient range scans and linear scalability. Clustering columns use message_timestamp in descending order to support fast retrieval of recent messages and cursor-based pagination. Hot partitions are mitigated by bucketing partitions using a hash of message_timestamp modulo N, preventing oversized partitions and hotspots. Messages are encrypted client-side to ensure end-to-end security, while partition and clustering keys remain plaintext for efficient query routing. The API includes endpoints for sending encrypted messages, syncing missed messages using cursor-based range queries, and asynchronously acknowledging message delivery to maintain low latency.

Key Decisions
  • Use conversation_id as Cassandra partition key to ensure message locality and ordered retrieval
  • Use message_timestamp as clustering column in descending order for efficient recent message queries
  • Implement bucketing by hashing message_timestamp modulo N to split large partitions and avoid hotspots
  • Encrypt message payloads client-side; keep partition and clustering keys plaintext for query routing
  • Design APIs to support encrypted message send, cursor-based sync, and asynchronous ack updates
  • conversation_id as Cassandra partition key ensures all messages in a conversation are co-located for efficient range scans
  • Clustering column on message_timestamp in descending order enables fast retrieval of recent messages and cursor-based pagination
  • Bucketing large partitions by hashing timestamp modulo N prevents hotspots and oversized partitions in Cassandra
  • Client-side encryption of message payloads preserves end-to-end security while keeping partition keys queryable
  • Use of asynchronous message acknowledgement updates to avoid write amplification and maintain low write latency
  • API design with POST /messages/send, GET /messages/sync, and POST /messages/ack supports efficient real-time and offline delivery
  • Rejecting sender_id or timestamp-only partition keys due to cross-partition queries and lack of message ordering guarantees
  • Avoiding full partition scans by leveraging clustering columns and partition keys for O(1) range queries
  • Rejecting full encryption of partition keys to maintain Cassandra’s routing and query efficiency
  • Rejecting HTTP polling for sync due to massive reconnect load at 50M DAU causing system collapse

Practice

(1/5)
1. A system must handle 1 million events per second, ensuring durability and ordered processing per aggregate. Which component best fits this requirement?
easy
A. Use a Redis pub/sub channel for event distribution
B. Use Kafka with partitioning by aggregate ID
C. Use a relational database with triggers for event handling
D. Use RabbitMQ with round-robin queue distribution

Solution

  1. Step 1: Identify Kafka's role in event sourcing

    Kafka supports high throughput with partitioning by aggregate ID, ensuring ordered event processing per aggregate.

  2. Step 2: Evaluate other options

    Redis pub/sub lacks durability guarantees; relational DB triggers don't scale to 1M events/sec; RabbitMQ's round-robin breaks ordering per aggregate.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Kafka partitions by aggregate ID -> ordered, durable event stream [check]
Hint: Kafka partitions preserve order per aggregate [check]
Common Mistakes:
2. For a system requiring both low latency and strong consistency in user profile updates, which architecture choice best balances these constraints?
medium
A. Use eventual consistency with asynchronous replication to maximize availability
B. Use a single primary database with synchronous replication for strong consistency
C. Use a distributed cache with write-behind to the database for low latency
D. Use client-side caching to reduce read latency without server coordination

Solution

  1. Step 1: Identify consistency and latency requirements

    Strong consistency requires synchronous replication; low latency demands minimal delay.

  2. Step 2: Evaluate architecture options

    Single primary DB with sync replication ensures consistency but may increase latency slightly; best trade-off.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Synchronous replication balances strong consistency and latency [check]
Hint: Strong consistency needs sync replication, not eventual or cache-only [check]
Common Mistakes:
3. If the distributed lock service is unavailable, what exactly breaks in the hotel booking system's reservation flow?
medium
A. Reservations proceed normally but double bookings increase due to lack of coordination
B. Reservation requests block indefinitely waiting for locks, causing user timeouts and failed bookings
C. Inventory service marks rooms as available incorrectly, causing UI to show wrong availability
D. Payment processing fails because it depends on the lock service for transaction ordering

Solution

  1. Step 1: Identify lock service role in reservation flow

    The lock service ensures exclusive access to room inventory during reservation to prevent double booking.

  2. Step 2: Trace failure impact

    Without the lock service, Inventory cannot reliably mark rooms as unavailable, leading to stale or incorrect availability states shown to users (Inventory service marks rooms as available incorrectly, causing UI to show wrong availability). Reservations may still proceed but with inconsistent inventory state. Blocking indefinitely (Reservation requests block indefinitely waiting for locks, causing user timeouts and failed bookings) is unlikely as fallback mechanisms exist. Payment processing (Payment processing fails because it depends on the lock service for transaction ordering) is independent of locking. Double bookings (Reservations proceed normally but double bookings increase due to lack of coordination) may increase but the immediate failure is incorrect inventory state.

  3. Final Answer:

    Option C -> Option C

  4. Quick Check:

    Lock service failure causes stale inventory availability state [check]
Hint: Lock service failure -> stale inventory state, not immediate booking block [check]
Common Mistakes:
4. If the Kafka cluster buffering RTMP packets crashes and loses the latest messages, what exactly breaks in the live streaming pipeline?
medium
A. The CDN serves stale HLS segments but continues streaming without interruption
B. The RTMP ingest server retries sending lost packets automatically with no impact
C. The transcoder stalls waiting for missing packets, causing segment gaps and viewer buffering
D. The player switches to a backup stream seamlessly without user noticing

Solution

  1. Step 1: Understand Kafka's role

    Kafka buffers RTMP packets to ensure ordered, durable delivery to the transcoder.

  2. Step 2: Trace failure impact

    If Kafka loses messages, the transcoder stalls (A) due to missing packets, causing segment gaps. CDN (B) serves only what it has, so stale segments appear. RTMP ingest server (C) does not retry lost packets automatically; loss is permanent. Player (D) cannot switch streams without explicit failover.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Kafka loss causes permanent packet loss; RTMP ingest lacks automatic retry [check]
Hint: Kafka loss means lost packets; RTMP ingest has no auto-retry [check]
Common Mistakes:
5. If the idempotency key store becomes unavailable, what is the most likely failure mode for the payment API when a client retries a payment request?
medium
A. The payment API processes each retry as a new payment, causing duplicate charges
B. The payment API queues requests until the store recovers, delaying all payments
C. The payment API rejects all requests with 503 Service Unavailable errors
D. The payment API returns cached responses from a fallback in-memory store

Solution

  1. Step 1: Understand idempotency store role in retries

    Without the store, the API cannot verify if a request is a retry, so it must delay processing to avoid duplicates.

  2. Step 2: Eliminate other options

    Rejecting all requests is possible but not typical; processing retries as new causes duplicates, which is avoided; fallback caches are uncommon and inconsistent.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    Unavailable idempotency store causes request queuing to prevent duplicates [check]
Hint: No idempotency store -> queue retries to avoid duplicates [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