Design a real-time analytics dashboard

The read pattern is 'filter by a few dimensions, group by time and one or two dimensions, aggregate' over billions of rows, at thousands of queries per second. Row stores like Postgres cannot do this at scale; a warehouse like BigQuery or Snowflake gives seconds of latency and minutes of freshness. Real-time OLAP engines exist for exactly this: they ingest from Kafka directly, store data in columnar segments with indexes, and answer in tens of milliseconds. Apache Pinot was built at LinkedIn for user-facing analytics (every member's 'who viewed my profile'), with high QPS, star-tree pre-aggregation indexes and upserts by primary key, which suits order status changes. ClickHouse is excellent for internal analytics with rich SQL and very high scan speed, but high-QPS point-ish queries need care, and updates are done with ReplacingMergeTree or similar. Druid is similar to Pinot. For 100k partner dashboards, Pinot's partition-aware routing (partition on restaurant_id, so a partner query touches one partition) is the deciding factor; for an internal-only dashboard, ClickHouse is often simpler. Say which you pick and why, rather than listing all three.

A rider's phone in a basement sends its events 4 minutes late; Kafka consumers lag during a deploy. If we bucket events by the time we process them (processing time), a deploy creates a fake dip and then a fake spike. So windows use event time: the timestamp when the thing happened. The stream processor needs to know when a window is 'complete enough'. A watermark is its estimate that no events older than T are still coming, typically max event time seen minus an allowed lateness (say 30 s). Windows fire early results every few seconds for freshness and a final result when the watermark passes; events later than the allowed lateness are either sent to a side output and merged by an upsert, or simply corrected by the nightly batch. Clock skew on phones is real, so the collector also records server receive time and clamps absurd client times. For idempotence, rollups are written as upserts keyed by (minute, dimensions) with the full current value, not increments, so a replay after a Flink restart rewrites the same numbers instead of double-counting.

Querying raw events for every widget refresh wastes work: 'orders per minute in Bengaluru today' scans millions of rows 20k times per second. Pre-aggregate in two places. Flink writes minute rollups per (city, zone, restaurant) with additive measures: counts, sums, and sum of delivery minutes plus count (so averages can be recomputed at any level; never store an average and then average it). Pinot's star-tree index adds pre-aggregated combinations for common group-bys. Non-additive metrics need sketches. Distinct users cannot be summed across minutes or cities, so store a HyperLogLog sketch per row (~1-2% error with a few KB) and merge sketches at query time. Percentiles such as p90 delivery time use t-digest or KLL sketches, which merge correctly. Tell the interviewer which metrics are exact (orders, GMV) and which are approximate (unique users, p90), and that the finance number of record comes from the batch warehouse, reconciled daily within 0.5%.

Partner dashboards are many small queries, each scoped to one restaurant group. Three things keep them cheap. First, physical layout matches the access path: partition and sort by restaurant_id so a query reads a tiny slice. Second, tenant isolation is enforced in the query service, never in the client: the API takes structured queries (metric, filters, group by), not raw SQL, and always injects the tenant filter from the auth token; this also prevents a partner from guessing another restaurant's id. Third, fewer queries: dashboards subscribe over SSE, and the server computes each widget once per interval and fans the result out to all viewers of the same widget, instead of every browser polling. Cache live-range results for 2-5 s and closed past ranges (yesterday) for hours. Add per-tenant query rate limits and a query cost limit (max time range, max group-by cardinality) so one heavy drill-down cannot slow everyone. Internal ad-hoc users go to a separate replica group so their large scans do not hurt partner latency.

Kappa (one streaming path, replay from Kafka to fix bugs) keeps one codebase. Lambda adds a batch path that recomputes exact numbers. Here a mostly-Kappa design plus a nightly batch reconciliation for finance-grade metrics gives the best of both without maintaining two full pipelines.

Polling every 5 s from 100k clients is simple but multiplies queries. SSE lets the server compute once and fan out. WebSockets are unnecessary because dashboards rarely send data back.

Rollups are fast and cheap but answer only pre-planned questions. Raw facts allow ad-hoc drill-downs but cost more per query. Keep both: rollups for dashboards, raw facts (90 days) for drill-down.

On match night one city dominates. Rollups are already small; the risk is a single Kafka partition if keyed by city. Key by order_id or restaurant_id, and aggregate by city in Flink with two-stage (local then global) aggregation.

20k queries/s. Widget-level result sharing over SSE, short caches, and more Pinot broker and server replicas; pre-warm before known events like IPL matches.

Real-time ingestion creates many small segments; schedule merges and rollups of older segments (minute → hour after 7 days) to keep query fan-out low.

Flink restores from its last checkpoint (every ~30 s) and reprocesses from the matching Kafka offsets. Rollups are upserts of full values, so reprocessing rewrites the same rows; dashboards show a short freshness gap, not wrong numbers.

Segments have replicas (2-3); brokers route around the lost server. Real-time consumption resumes from Kafka offsets on the replacement.

Schema registry checks at the collector reject or quarantine invalid events, so a broken app release cannot poison dashboards; a metric on rejected events alerts the owning team.