Trade-offs: Pre-aggregation vs. querying raw event data

What's really being asked

This question assesses your ability to architect data solutions for real-world constraints. Interviewers are looking for a nuanced understanding of the trade-offs between performance, cost, data freshness, and analytical flexibility. They want to see that you can move beyond theoretical ideals and design a practical system that balances competing business and user needs for a high-traffic analytics dashboard.

The full answer

A strong answer methodically evaluates both approaches against the four factors provided. First, define pre-aggregation as creating summary tables (materialized views) to serve common queries, and on-the-fly as querying raw, granular data directly. Second, analyze the trade-offs: pre-aggregation offers low latency (<1s) and low query cost (especially on OLAP systems like BigQuery/Snowflake) at the expense of data freshness (minutes/hours stale) and analytical flexibility (queries must exactly match the pre-aggregated columns). Third, contrast this with on-the-fly queries, which provide perfect freshness and flexibility but suffer from high latency (seconds/minutes) and high cost at scale. Finally, conclude that a hybrid approach is usually best: use pre-aggregations for common dashboard views and allow drill-downs to query raw data for ad-hoc analysis.

The mistakes people make

The most common red flag is absolutism—declaring one approach universally superior without considering context. A junior candidate might say "always pre-aggregate for speed," ignoring use cases that demand real-time data. Another mistake is ignoring the scale implied by "high-traffic." A solution that works for 10 users will fail for 10,000. Finally, a subtle error is failing to mention the challenges of pre-aggregating non-additive measures (like distinct counts), which can lead to cache misses and negate the performance benefits.

What usually comes next

Expect questions like: "How would you decide which queries to pre-aggregate?" (By analyzing query logs for common patterns). "How do you handle requests for data not in the summary table?" (The system should gracefully fall back to querying the raw data). "Your P95 latency target is 200ms. How does that change your design?" (It forces aggressive pre-aggregation for almost all initial views).

A concrete example

For a popular e-commerce dashboard, the main view shows sales by day. Pre-aggregating this into a summary table of (date, total_sales) makes the main chart load in <200ms, even with billions of raw order events. A query to the raw table might take 15 seconds and cost $0.50 per user refresh on BigQuery. The summary table is refreshed every 5 minutes, which is an acceptable freshness trade-off for this view. If a user wants to see individual orders for a specific hour, they can drill down, triggering a slower, more expensive query against the raw data, which is an acceptable user experience for a less common action.