Trade-offs between pre-aggregated and raw event data for dashboards
Balancing latency, cost, and freshness in analytics.
Pre-aggregations trade freshness for speed; raw queries preserve flexibility but spike cost and latency under load.
What's really being asked
This question tests whether you can architect analytics infrastructure for high concurrency by reasoning through the latency, cost, freshness, and flexibility trade-offs between pre-computed rollups and on-the-fly raw scans. Interviewers want to see that you understand database engine differences, caching mechanics, and operational maintenance, not just that you can repeat the word cache.
The full answer
First, define pre-aggregation as computing and storing aggregations like sums, counts, averages, and dimensions ahead of time so dashboards read from a small managed table rather than scanning raw events. Second, contrast the benefits: sub-second latency, reduced database load, and lower cost because one pre-aggregation can serve multiple charts simultaneously versus firing many raw queries. Third, contrast the costs: data becomes stale depending on the refresh_key schedule, drill-down is limited to the dimensions you pre-computed, and non-additive measures like distinct counts can cause cache misses or require extra rollups. Fourth, explain raw query strengths: perfect freshness and unlimited analytical flexibility since users can slice by any dimension without a predefined rollup. Fifth, discuss engine specifics: OLTP row stores such as Postgres or MySQL suffer from slow columnar aggregates under concurrent load, while OLAP warehouses like Snowflake or BigQuery can handle the computation but generate significant compute charges when many users query simultaneously. Sixth, propose a hybrid strategy: pre-aggregate the high-traffic summary KPIs that refresh every few minutes, keep raw event access for deep-dive exploratory queries, and use a refresh_key tuned to the business tolerance for staleness.
The mistakes people make
A red flag is recommending pre-aggregation as a free universal performance win without mentioning the refresh window or the fact that every new dimension combination may require another rollup table. Another mistake is claiming raw queries are always cheaper on cloud warehouses; the reference notes that concurrent dashboards on OLAP systems rack up real financial cost. Candidates also stumble by ignoring non-additive measure limitations or suggesting that pre-aggregations automatically handle arbitrary filters and time granularities without matching the pre-aggregation definition exactly.
What usually comes next
The interviewer may ask how you would handle a sudden requirement for real-time revenue metrics, how to structure refresh_key policies across time zones, or what you would do if a non-additive metric like unique user count keeps missing the pre-aggregation cache. They might also probe how you would migrate from raw-only to a hybrid model without breaking existing dashboards.
A concrete example
Imagine a high-traffic e-commerce dashboard viewed by five hundred operations users every morning. Pre-aggregating daily revenue, order count, and top product dimensions into a single rollup table drops query latency from eight seconds on Postgres to under two hundred milliseconds and cuts query volume by eighty percent. However, a support team needs minute-level refund tracking; for them you bypass the rollup and query raw events with a short time filter because freshness outweighs cost for that narrow use case.
Interview question
Why might a dashboard query for unique visitor count still trigger an expensive raw event scan even when pre-aggregated rollups exist?
- a.Because OLAP engines charge less for raw scans than rollup reads when many users query simultaneously
- b.Because pre-aggregated tables automatically exclude high-cardinality dimensions like user IDs to control storage
- c.Because distinct counts are non-additive and cannot be safely reused across dimension slices outside the rollup definitionCorrect
- d.Because the pre-aggregation refresh_key is too infrequent, forcing the dashboard to bypass stale rollups
Why? this is the answer
Non-additive measures such as distinct counts require carefully structured rollups and cannot be combined or re-sliced arbitrarily, causing cache misses when the query dimensions differ. Distractor B confuses staleness with structural mismatch; a stale rollup returns outdated results rather than forcing a fallback to raw scans.
Just read this? Test yourself on what you have been reading.
Read the original → docs.embeddable.com
- #analytics
- #pre-aggregations
- #data-engineering
- #performance
- #olap
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 analytics — each one lists the topics its interview covers.
See open roles