Trade-offs: Pre-aggregation vs. Querying Raw Data

What's really being asked

This tests your senior-level ability to analyze and articulate the fundamental trade-offs in analytical system design. The interviewer wants to see if you can move beyond simple definitions and apply concepts like latency, cost, data freshness, and flexibility to a real-world problem like a high-traffic dashboard. They are evaluating your practical judgment in balancing business needs (fast dashboards) with technical and financial constraints (database load, query costs).

The full answer

A good answer hits four points in order. First, define the two approaches. Pre-aggregation involves pre-computing and storing results, like a materialized view, to serve queries quickly. Querying raw data hits the underlying database directly for every request. Second, detail the trade-offs across four axes. Pre-aggregation offers low latency and cost but sacrifices data freshness (due to refresh schedules) and analytical flexibility (limited to pre-defined dimensions/measures). Querying raw data provides perfect freshness and flexibility but at high latency and cost, especially with many concurrent users on OLTP or OLAP databases. Third, propose a hybrid solution as the best practice for most high-traffic scenarios. Use pre-aggregations for the main dashboard widgets that 95% of users see. Then, provide "drill-down" functionality that queries the raw, granular data for users who need to explore specific details. Fourth, mention the mechanism for managing pre-aggregations, like a refresh key that triggers updates, which directly controls the freshness vs. cost balance.

The mistakes people make

A common red flag is presenting a one-sided argument, for instance, stating "pre-aggregation is always better for performance" without discussing the significant loss of flexibility and data freshness. Another mistake is failing to quantify the trade-offs. A junior answer says "it's faster"; a senior answer says "it can reduce query times from minutes to milliseconds but the data might be an hour stale." Also, candidates often forget the impact on the source database. High-frequency raw queries can cripple an OLTP database not designed for analytical workloads or run up huge bills on an OLAP database like BigQuery or Snowflake. Finally, not suggesting a hybrid approach shows a lack of practical experience.

What usually comes next

Expect follow-ups like: "How would you decide which queries to pre-aggregate?" (Answer: based on usage patterns, P95/P99 latency requirements, and query complexity). Or, "Describe a situation where you would never use pre-aggregation." (Answer: real-time fraud detection, where millisecond-level freshness is critical). Another could be: "How do you handle a cache miss in a pre-aggregation system?" (Answer: The query falls back to the raw data, but this should be monitored as it indicates a poorly designed aggregation or a new query pattern).

A concrete example

Imagine a sales dashboard for an e-commerce site with 1,000 concurrent sales reps. A key metric is "Total Sales Today." Querying the raw orders table (billions of rows) for every user's dashboard refresh would be incredibly slow and expensive. Instead, we create a pre-aggregation that calculates SUM(sales) grouped by hour. This table might have only 24 rows for the day. We can set it to refresh every 5 minutes. A user's dashboard query now hits this tiny summary table, returning in under 50ms. The data is at most 5 minutes stale, which is an acceptable trade-off. If a sales manager wants to see the individual orders for a specific hour, they can click to drill down, which then triggers a more expensive, targeted query against the raw data.