Trade-offs between pre-aggregated and raw event data for dashboards

WHAT THIS TESTS: 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.

A GOOD ANSWER COVERS: 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.

COMMON WRONG ANSWERS: 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.

LIKELY FOLLOW-UPS: 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.

ONE 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.

Source: docs.embeddable.com