Bird
Raised Fist0

Time-Series Databases - Metrics, Events, and IoT Patterns

HLDmediumInterview freq: medium
Choose your preparation mode4 modes available
ArchitectureTraceChallenge
🎯
Time-Series Databases - Metrics, Events, and IoT Patterns
45 min · senior
senior

You are tasked with designing a time-series database system to handle metrics, events, and IoT telemetry data for a startup with 50 million daily active users generating high-frequency data points. The system must support real-time ingestion, efficient range queries, and aggregation with P99 query latency under 200ms. Data must be encrypted at rest and in transit. The system should support offline data delivery for intermittent IoT devices and provide strong durability guarantees.

Constraints
  • End-to-end encryption (TLS in transit, AES-256 at rest) is mandatory
  • P99 query latency must be under 200ms for typical range queries
  • Support offline data delivery and sync for IoT devices with intermittent connectivity
  • System must handle ingestion rates of at least 1 million writes per second
  • Data retention policies require efficient cold storage tiering after 30 days
📋
Guided Steps
1/6
Step 1
Data Ingestion Architecture
How would you design the ingestion pipeline to handle 1M writes/sec with encryption and durability?
💡 Hint
Consider a distributed log system that supports partitioning and replication with TLS encryption.
Key Decision
Use Apache Kafka with TLS encryption for ingestion - Kafka partitions keyed by device_id ensure ordered writes and horizontal scalability; Kafka's replication guarantees durability.
Interviewer Expectation
Mid: Names Kafka for ingestion. Senior: Explains device_id partition key choice for ordering and how TLS secures data in transit.
⚠️Common Wrong Answer
Using HTTP REST endpoints directly to the database - leads to bottlenecks and no built-in replication or ordering guarantees at scale.
Step 2
Storage Engine Selection and Data Modeling
Which database technology and data model would you choose to store time-series data efficiently for fast range queries and aggregation?
💡 Hint
Look for a wide-column store with LSM-tree storage optimized for time-series writes and range scans.
Key Decision
Use Apache Cassandra with partition key as (device_id, metric_type) and clustering key as timestamp - LSM-tree writes scale linearly, and range queries on timestamp clustering enable efficient time-range scans.
Interviewer Expectation
Mid: Names Cassandra for storage. Senior: Explains composite partition and clustering keys for query efficiency and why relational DBs fail at this scale.
⚠️Common Wrong Answer
Using relational databases like MySQL - causes write amplification and poor range query performance at 1M writes/sec.
Step 3
Query API Design and Latency Optimization
How would you design the query API to support sub-200ms P99 latency for range queries and aggregations?
💡 Hint
Consider pre-aggregations, caching, and query routing to reduce query load and latency.
Key Decision
Expose a REST API backed by a query service that uses Apache Druid for real-time OLAP queries with pre-aggregated rollups and Redis caching for hot queries to meet latency SLAs.
Interviewer Expectation
Mid: Describes REST API with basic filtering. Senior: Details Druid's rollup segments, Redis TTL caching, and query routing to reduce latency.
⚠️Common Wrong Answer
Querying raw Cassandra data directly for every request - leads to high latency and poor P99 performance.
Step 4
Offline Data Delivery and Sync for IoT Devices
How would you support intermittent IoT devices that need offline data delivery and conflict-free sync?
💡 Hint
Look into message queues with replay capabilities and conflict resolution strategies like vector clocks or CRDTs.
Key Decision
Use MQTT protocol with AWS IoT Core for device connectivity supporting offline buffering and QoS levels; sync conflicts resolved via vector clocks stored in Cassandra.
Interviewer Expectation
Mid: Mentions MQTT for IoT connectivity. Senior: Explains QoS levels for offline delivery and vector clocks for conflict resolution.
⚠️Common Wrong Answer
Using HTTP polling for IoT devices - causes excessive battery drain and network overhead, collapsing under 50M devices.
Step 5
Data Encryption and Security
How do you ensure data is encrypted at rest and in transit while maintaining performance?
💡 Hint
Use TLS for transport and native encryption features of storage and messaging systems.
Key Decision
Enable TLS 1.3 for Kafka and API endpoints; use Cassandra's Transparent Data Encryption (TDE) with AES-256; keys managed via HashiCorp Vault for rotation.
Interviewer Expectation
Mid: Names TLS and encryption at rest. Senior: Details TLS 1.3 benefits, Cassandra TDE implementation, and secure key management.
⚠️Common Wrong Answer
Encrypting data only at the application layer - leads to complexity and potential data leakage in logs or backups.
Step 6
Cold Storage and Data Retention
How would you implement efficient cold storage tiering for data older than 30 days?
💡 Hint
Consider object storage with lifecycle policies and query federation for archived data.
Key Decision
Archive data older than 30 days to Amazon S3 with lifecycle policies; use Presto/Trino to federate queries across Cassandra and S3 for seamless access.
Interviewer Expectation
Mid: Mentions S3 for cold storage. Senior: Explains lifecycle policies, cost trade-offs, and query federation for unified analytics.
⚠️Common Wrong Answer
Keeping all data in Cassandra indefinitely - leads to high storage costs and degraded query performance.
🔌
API Design Challenge
Sketch 3 core API endpoints for this time-series system: data ingestion, querying, and device sync.
Hints
  • How does the client send high-frequency time-series data efficiently?
  • What parameters and filters should the query API support for range and aggregation?
  • How does the system handle offline IoT device data sync and conflict resolution?
📊
Scoring Rubric
▼ Show
Must Have - 60%
  • Use Kafka with device_id partition key for ingestion scalability and ordering
  • Choose Cassandra with time-series data model for efficient writes and range queries
  • Design query API with Druid and Redis caching to meet latency SLAs
  • Support offline IoT sync with MQTT and conflict resolution via vector clocks
  • Implement TLS and AES-256 encryption with secure key management
Good to Have - 30%
  • Cold storage tiering using S3 and query federation with Presto/Trino
  • Explain trade-offs of partition key design and data modeling for query patterns
  • Describe QoS levels in MQTT for offline delivery guarantees
Bonus - 10%
  • Discuss multi-region replication and disaster recovery strategies
Anti-patterns to Avoid
✅
Model Answer
▼ Reveal

This design uses Apache Kafka for scalable, ordered ingestion with TLS encryption, Apache Cassandra for efficient time-series storage with composite keys, and Apache Druid with Redis caching for low-latency queries. MQTT with AWS IoT Core supports offline IoT device sync with vector clocks for conflict resolution. Data is encrypted in transit with TLS 1.3 and at rest with Cassandra TDE (AES-256), with keys managed by HashiCorp Vault. Cold data is archived to Amazon S3 with lifecycle policies and queried via Presto/Trino federation.

Key Decisions
  • Use Kafka with device_id partition key to ensure ordered, scalable ingestion at 1M writes/sec
  • Model time-series data in Cassandra with composite partition and clustering keys for efficient writes and range queries
  • Use Druid with pre-aggregated rollups and Redis caching to meet sub-200ms P99 query latency
  • Support offline IoT device sync with MQTT protocol and AWS IoT Core, resolving conflicts via vector clocks
  • Encrypt data in transit with TLS 1.3 and at rest with Cassandra Transparent Data Encryption (AES-256)
  • Manage encryption keys securely using HashiCorp Vault with rotation policies
  • Implement cold storage tiering by archiving data older than 30 days to Amazon S3 with lifecycle policies
  • Use Presto/Trino to federate queries across Cassandra and S3 for seamless analytics
  • Apache Kafka with TLS for scalable, ordered, and durable ingestion at 1M writes/sec
  • Apache Cassandra with composite partition and clustering keys for efficient time-series storage and range queries
  • Apache Druid for real-time OLAP queries with pre-aggregations and Redis caching to meet sub-200ms P99 latency
  • MQTT protocol with AWS IoT Core for offline-capable device connectivity and QoS guarantees
  • Vector clocks stored in Cassandra for conflict-free offline data sync in IoT devices
  • TLS 1.3 for transport encryption and Cassandra Transparent Data Encryption (AES-256) for data at rest
  • HashiCorp Vault for secure encryption key management and rotation
  • Amazon S3 with lifecycle policies for cold storage tiering and Presto/Trino for query federation

Practice

(1/5)
1. In a bulkhead-isolated microservice architecture, at step 4 in the request trace, the Bulkhead Manager checks the thread pool status before dispatching a request. What happens if the thread pool is fully occupied?
easy
A. The request is queued indefinitely until a thread becomes available
B. The request is forwarded to a fallback service without delay
C. The Bulkhead Manager increases the thread pool size dynamically to handle the request
D. The Bulkhead Manager rejects the request immediately to prevent resource exhaustion

Solution

  1. Step 1: Trace Bulkhead Manager behavior at Step 4

    When the thread pool is full, queuing indefinitely (A) risks resource exhaustion. Dynamic resizing (D) breaks isolation guarantees. Forwarding to fallback (C) is a separate mechanism.

  2. Step 2: Understand bulkhead failure containment

    Rejecting requests immediately (B) prevents overload and contains failure blast radius.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Bulkhead rejects requests when resource limits reached [check]
Hint: Reject requests when bulkhead resources are exhausted [check]
Common Mistakes:
2. A search ranking system must store and serve feature vectors for millions of users with low latency. Which component is best suited to provide fast, consistent feature retrieval at scale?
easy
A. A relational database with complex joins to compute features on demand
B. A distributed in-memory feature store with precomputed feature vectors
C. A batch processing system that recomputes features daily and stores them in object storage
D. A key-value store without versioning or consistency guarantees

Solution

  1. Step 1: Identify feature retrieval requirements

    Low latency and consistency for millions of users require fast, precomputed feature access.

  2. Step 2: Evaluate component suitability

    A relational DB with joins (A) is too slow for real-time; batch recomputation (C) is stale; key-value store without consistency (D) risks stale or inconsistent features. Distributed in-memory feature store (B) provides fast, consistent retrieval.

  3. Final Answer:

    Option B -> Option B

  4. Quick Check:

    In-memory feature store -> low latency, consistent features [check]
Hint: Precompute features in-memory for fast, consistent retrieval [check]
Common Mistakes:
3. If the recursive DNS server's cache is completely disabled, what is the most immediate failure mode experienced by users during domain resolution?
medium
A. Users' browsers fallback to using IP addresses directly, bypassing DNS
B. Users receive stale IP addresses causing connection failures
C. The recursive server returns IP addresses from its local hosts file instead of querying DNS servers
D. The recursive server blocks user queries until authoritative servers respond, increasing latency and potential timeouts

Solution

  1. Step 1: Understand recursive server caching role

    The recursive DNS server cache reduces query latency by storing recent results. Without caching, every query requires a full resolution chain traversal.

  2. Step 2: Trace failure impact

    Without cache, recursive server must synchronously query authoritative servers for every request, causing increased latency and possible timeouts, blocking user queries.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    No cache -> synchronous queries -> high latency and blocking [check]
Hint: No cache means blocking queries until authoritative response [check]
Common Mistakes:
4. At 10x scale, which component in a saga-based event-driven system is most likely to fail first due to stateful bottlenecks, and why?
hard
A. The saga coordinator, because it maintains in-memory state per saga and cannot shard easily
B. The message broker, because partition count limits throughput and ordering guarantees
C. The database, because increased writes cause lock contention on outbox tables
D. The event relay service, because network bandwidth saturates before CPU

Solution

  1. Step 1: Identify stateful bottleneck

    The saga coordinator holds per-saga state in memory, limiting horizontal scaling and sharding.

  2. Step 2: Compare other components

    Message brokers scale with partitions; DB scales with sharding; relay is stateless and network-bound.

  3. Final Answer:

    Option A -> Option A

  4. Quick Check:

    Saga coordinator's in-memory state limits scaling first [check]
Hint: Stateful saga coordinator limits scaling before stateless components [check]
Common Mistakes:
5. At 10x scale, which component in a real-time chat system is most likely to fail first due to latency-throughput trade-offs, and why?
hard
A. The database, because it cannot handle the increased write volume
B. The message broker, because partition count cannot be increased beyond a fixed limit
C. The load balancer, because it cannot distribute traffic evenly at high scale
D. The chat servers, because WebSocket connections are stateful and cannot be resharded easily

Solution

  1. Step 1: Identify stateful bottlenecks

    Chat servers maintain WebSocket connections statefully, limiting horizontal scaling.

  2. Step 2: Compare other components

    Databases (A) can be sharded; brokers (C) can add partitions; load balancers (D) scale horizontally; chat servers (B) fail first due to sticky state and connection limits.

  3. Final Answer:

    Option D -> Option D

  4. Quick Check:

    Stateful WebSocket servers limit scaling more than other components [check]
Hint: Stateful WebSocket servers block scaling first at 10x load [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