Design a pre-aggregation architecture for low-latency experiment results
Tests OLAP-at-scale trade-offs. Strong answers design streaming rollups into a real-time OLAP store, use partial cubes for high-cardinality dimensions, and retain raw events.
What's really being asked
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.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
An experiment pipeline ingests one billion events daily across variant, country, device, and high-cardinality user ID. Which design best satisfies sub-second dashboard latency without causing storage explosion?
- a.Build a complete OLAP cube materializing every combination of variant, country, device, and individual user ID
- b.Run a nightly batch job to pre-aggregate all metrics by variant and country, then serve dashboards from the resulting daily tables
- c.Use a stream processor to emit partial rollups by variant, country, and device into a real-time OLAP engine while retaining raw events and using HLL sketches for unique user countsCorrect
- d.Cache all experiment queries in Redis and compute aggregations directly over the raw event store on each cache miss
Why? this is the answer
Partial rollups on high-traffic dimensions with HLL sketches deliver sub-second latency without storage explosion, whereas materializing every combination including user ID creates billions of cells and exhausts resources.
Just read this? Test yourself on what you have been reading.
Read the original → startree.ai
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on growth — each one lists the topics its interview covers.
See open roles