Design a pre-aggregation architecture for low-latency experiment results

WHAT THIS TESTS: This tests your ability to bridge high-volume event ingestion with low-latency experiment analytics, specifically the OLAP-at-scale problem where billions of raw events must become millisecond-level metric queries. The interviewer wants to see that you understand pre-aggregation is not just about caching but about dimensional trade-offs, cardinality constraints, and the tension between query flexibility and compute cost.

A GOOD ANSWER COVERS: First, an ingestion tier: event streams land in Kafka or a similar log, then flow through a stream processor such as Flink or Spark Streaming to compute time-windowed rollups. Second, a storage tier that separates hot pre-aggregated data from warm raw data, using a real-time OLAP engine like Apache Pinot, Druid, or ClickHouse that supports both real-time ingestion and pre-aggregated indexes. Third, aggregation strategy: partial cubes or star schemas that pre-compute only the high-traffic dimension combinations such as experiment variant by country by device, while avoiding full materialization of high-cardinality fields like user ID. Fourth, trade-offs: pre-aggregation sacrifices ad-hoc query flexibility, increases storage because you keep both rolled-up and raw tables, and requires backfill pipelines when metric definitions change. Fifth, experiment-specific nuances: handling late-arriving events, sessionization, unique user counting via HLL or Theta sketches rather than exact distinct counts, and ensuring statistical guardrails like sample ratio mismatch detection still work on rolled-up data.

COMMON WRONG ANSWERS: Proposing only a Redis cache in front of the warehouse ignores the core problem of computing aggregations over billions of rows. Suggesting a full OLAP cube that materializes every dimension combination ignores cardinality explosion; with ten dimensions each having ten values, you already need billions of pre-computed cells. Recommending daily batch pre-aggregation alone misses the real-time requirement typical of experiment dashboards. Proposing to pre-aggregate every metric without keeping raw events makes debugging anomalous results impossible.

LIKELY FOLLOW-UPS: How do you handle late-arriving events that arrive after the rollup window has closed? What happens when an analyst needs a slice you did not pre-compute? How do you prevent a high-cardinality dimension like request ID from blowing up your cube? How do you backfill or replay a corrected event stream without rebuilding everything? What sketch algorithms would you use for distinct user counts in pre-aggregated tables?

ONE CONCRETE EXAMPLE: Imagine an A/B test on a checkout flow with one billion events per day across variant, country, device, and browser. Instead of querying raw events, you run a Flink job that emits five-minute tumbling windows grouped by variant, country, and device, storing sums, counts, and HLL sketches. Pinot ingests both these rollups and a sample of raw events. A product manager querying conversion rate by variant and country hits the pre-aggregated table and gets results in under fifty milliseconds. When an analyst later asks for conversion by browser, the query either falls back to raw events with higher latency or uses a secondary pre-aggregation that was built for that dimension pair, demonstrating the explicit flexibility cost.

Source: startree.ai