Design an Analytics Pipeline

This case study simulates a realistic FAANG system design interview for architecting a high-throughput, real-time analytics pipeline similar to Google Analytics, Mixpanel, or internal logging engines. It details event collector optimization, streaming message brokers, real-time stream enrichment, and columnar storage aggregation performance.

1. Present the Interview Question

2. Clarifying Questions

The candidate establishes the scale boundaries and query targets:

• Candidate: What is the expected scale of ingestion? How many events are processed per second?
  Interviewer: The system handles an average of 100,000 events per second, with peak volumes reaching 300,000 events per second.
• Candidate: What is the target data latency budget (time from client click to appearing on the dashboard)?
  Interviewer: Data must be queryable on dashboards within 10 seconds of generation.
• Candidate: What kinds of queries are dashboard users executing?
  Interviewer: Mostly aggregation queries, such as counting active users, grouping events by country/browser type, and calculating conversion percentages over time intervals.
• Candidate: Do we support event historical retention constraints?
  Interviewer: Yes, we must retain logs and data for 1 year.

3. Functional Requirements

• Event Ingestion: Ingest events (clicks, pageviews, transactions) via a client-facing API collector.
• Real-time Enrichment: Clean payloads, validate schemas, and enrich logs with geo-location data based on IP.
• Ad-hoc SQL Analytics: Expose a query API supporting aggregation (COUNT, SUM, GROUP BY) over historical data.
• Dashboard Visualization Support: Fast query responses for frontend graph renderers.

4. Non-Functional Requirements

• Low Latency Ingestion: Ingestion to storage propagation latency must be < 10 seconds.
• High Availability: Collector API must remain available even under downstream blockages.
• No Data Loss: Guarantee event durability; message streams must survive server crashes.
• Low Query Latency: Dashboard SQL aggregation queries must return in under 2 seconds.

5. Capacity Estimation

1. Throughput (Events Volume)

• Average: 100,000 events/sec.
• Daily Event Count: 100,000 * 86,400s = 8.64 Billion events/day.
• Peak Ingress: 300,000 events/sec.

2. Ingest Storage & Compression

• Average size of parsed event metadata: ~200 bytes.
• Daily raw storage volume: 8.64B * 200 bytes = ~1.72 TB of raw data/day.
• Because analytics systems use columnar databases, data is heavily compressed (typically 4x compression ratio).
• Compressed Daily Storage: 1.72 TB / 4 = ~430 GB/day.
• Yearly Data Retention Storage: 430 GB * 365 = ~157 Terabytes (TB)/year.

3. Network Bandwidth (Ingress)

Average Ingestion Bandwidth: 100,000 * 200 bytes = 20 MB/s (160 Mbps).
Peak Ingestion Bandwidth: 300,000 * 200 bytes = 60 MB/s (480 Mbps).

6. Identify Core Components

• Collector Service (HTTP API): Lightweight API gateway terminating HTTP connections and forwarding events to the buffer.
• Streaming Buffer (Apache Kafka): Acts as a durable queue to absorb peak traffic spikes.
• Stream Processor (Apache Flink): Consumes events, validates schemas, and enriches columns in real-time.
• Enrichment Service: Provides MaxMind Geo-IP mappings.
• Columnar Analytical Database (ClickHouse): Stores events on disk, optimized for fast aggregations.
• Query API Service: Validates and executes dashboard queries against ClickHouse.

7. High-Level Architecture

The High-Level design decouples API collection from the streaming processing and database analytics layers:

8. API Design

1. Event Ingestion API

POST /api/v1/collect

Request Payload:

Response Payload (202 Accepted):

9. Data Model: ClickHouse Schema

In ClickHouse, we design columns to match query aggregations, using a MergeTree storage engine ordered by time:

10. Database Selection

Candidate: I choose a Column-Oriented relational database like ClickHouse or Snowflake over row-oriented relational (PostgreSQL) or row-oriented NoSQL (Cassandra) stores.
Justification: In row-oriented databases, data is stored on disk as complete rows. An aggregation query like SELECT COUNT(*), country FROM events GROUP BY country forces the database to read the entire row (including user IDs and properties) into memory, saturating disk I/O. Columnar databases store values of the same column adjacent to each other on disk. To execute the query, ClickHouse only reads the country column from disk, speeding up queries by 100x and achieving high data compression.

11. Deep Dive: Stream Processing & Ingestion Buffering

1. Real-time Stream Processing (Apache Flink)

Flink consumes raw events from Kafka partitions in real-time:
- Validation: Discards malformed JSON payloads.
- Enrichment: Extracts client IP and resolves it to geo-location properties (country, city) using an in-memory MaxMind database.
- Sessionization: Tracks active user sessions using sliding event-time windows.

2. Ingestion Buffering (Kafka)

To handle peak ingestion traffic (300,000 QPS) without data loss, Kafka acts as our landing buffer.
- Kafka topics are divided into 32 partitions sharded using a round-robin partitioning key.
- This distributes the write load evenly across the broker nodes, preventing hotspots.

12. Request Lifecycle

1. Event Generation: The User SDK captures a button click and sends an HTTP POST request to the API Collector.
2. Ingestion: The Collector receives the request, validates the payload structure, and writes the event to Kafka. It returns an immediate HTTP 202 status (Latency: ~5ms).
3. Enrichment: Flink consumes the event, enriches it with country and device metadata, and writes the enriched record to an output buffer.
4. Batch Loading: Flink groups events in memory and performs bulk inserts of 10,000 rows at a time into ClickHouse every 5 seconds.
5. Dashboard Query: The dashboard requests the count of clicks per country. The Query API executes the SQL query against ClickHouse, which reads only the relevant columns and returns the aggregated values (Latency: ~80ms).

13. Scaling Strategy

• Horizontal Ingestion Scaling: Collector pods run statelessly behind a Network Load Balancer (NLB), scaling automatically based on CPU usage.
• ClickHouse Sharding: Shard ClickHouse across multiple nodes using hash(user_id) to distribute writes. We configure a Distributed Table layer. When the Query API targets the distributed table, ClickHouse fans out the query to all shards and merges results automatically.

14. Bottleneck Analysis: ClickHouse Segment Overload

Candidate: ClickHouse is optimized for batch writes, not single-row inserts. To prevent part overload:
1. We do not insert rows directly from the Collector API.
2. Flink Consumer Buffering: Flink buffers events in memory and writes to ClickHouse in bulk. We configure a threshold: write when the buffer reaches 10,000 rows, or every 5 seconds (whichever comes first).
3. Kafka Batching: This reduces insertion frequency to only 10 writes per second per shard, allowing ClickHouse to write segment files cleanly.

15. Trade-off Discussion: Flink vs. ClickHouse Aggregation

Interviewer: Why use Flink to process the stream instead of writing raw logs directly to ClickHouse and doing all calculations using SQL?
Candidate:
- *ClickHouse Processing (ELT model):* Simple architecture since we bypass Flink. However, enriching records (like Geo-IP MaxMind checks) using SQL joins during ingestion increases database CPU usage. If we need to join external user metadata, it creates high query overhead.
- *Flink Processing (ETL model):* Decouples enrichment from database engines. Flink performs validation and Geo-IP lookups in memory before writing to the database. This keeps ClickHouse CPU dedicated to dashboard query execution.
*Decision:* We choose the ETL model using Flink to preserve ClickHouse query performance, keeping dashboard response times below 2 seconds.

16. Failure Scenarios

Handling system crashes:

• ClickHouse Node Failure: If a ClickHouse node crashes, Flink cannot write its batch buffer.
  Mitigation: Flink uses Checkpointing. If writes fail, Flink stops committing Kafka offsets. Once the database recovers, Flink resumes consumption and retries the batch, guaranteeing at-least-once delivery with zero data loss.
• Kafka Broker Crash: If a Kafka node crashes, events in transit must survive.
  Mitigation: Configure replication factor = 3 for Kafka partitions, and set acks=all on the API Collector. This ensures writes are acknowledged only after replicating to multiple broker disks.

17. Security Design

• PII Scrubbing: Configure Flink workers to hash or remove sensitive user details (like raw email addresses or IP addresses) before writing to ClickHouse, ensuring compliance with privacy regulations (GDPR/CCPA).

18. Monitoring & Observability

• Consumer Lag: The number of unread events in Kafka. A spike in lag indicates Flink or ClickHouse processing bottlenecks.
• Query Latency (p99): Alert if SQL dashboard aggregation times exceed 2 seconds.

19. Cost Optimization

ClickHouse storage is expensive. To optimize costs, we implement a Retention Policy:
- Keep the last 90 days of detailed event logs in ClickHouse for ad-hoc dashboard queries.
- Move data older than 90 days to a daily batch job that compiles pre-aggregated statistics (e.g. daily click counts by country) and saves them to a summary table.
- The raw events are deleted from ClickHouse and archived in compressed formats on S3, reducing SSD storage costs by up to 80%.

20. Production Improvements: Lambda Architecture

To support both real-time dashboard updates and complex historical queries, we implement a Lambda Architecture:
1. Speed Layer: Flink processes events in real-time, streaming updates to ClickHouse to support real-time dashboards (last 24 hours of data).
2. Batch Layer: Clickstream logs are archived in S3. A daily Spark job runs complex aggregations, updates historical tables, and corrects any inconsistencies.

21. Interviewer Follow-up Questions

Candidate: Flink manages out-of-order events using Watermarks and Allowed Lateness:
1. We use Event-Time processing (the timestamp when the event occurred on the device) rather than Processing-Time (when the server received it).
2. Flink generates watermarks that track the maximum event time processed minus a delay threshold (e.g. 5 minutes).
3. Events arriving within the watermark window are processed correctly.
4. If an event arrives later than the watermark window (e.g., uploaded 5 hours late), Flink routes it to a side output, which updates ClickHouse historical records asynchronously.

22. Final Architecture

The complete analytics pipeline architecture featuring Flink streaming, batch archives, and columnar storage:

23. Summary

We designed an end-to-end real-time analytics pipeline to ingest 100,000 events/sec. By decoupling the layers using a Kafka buffer and Flink stream processors, we kept ingestion reliable. Columnar storage (ClickHouse) indexes events in batches of 10,000 rows to avoid disk bottleneck overhead, supporting ad-hoc dashboard SQL queries in under 2 seconds.

24. Cheat Sheet

25. Candidate Interview Evaluation