When to Use Pre-aggregation vs Alternatives

The Decision Framework: Pre-aggregation is not a universal solution. The decision depends on query patterns, data volume, freshness requirements, and exploration needs. Understanding when NOT to pre-aggregate is as important as knowing when to do it. Choose Pre-aggregation When: Your query patterns are predictable and repetitive. If 80 percent of queries hit the same group by combinations, pre-aggregating those specific combinations gives massive wins. For example, if your dashboards consistently query daily active users by country and device, maintaining that exact aggregate cuts latency from 20 seconds to 100 milliseconds. The storage overhead is minimal because you're only materializing high value aggregations. Your data volume makes raw queries too expensive. If scanning raw data costs $50 per query and you run 10,000 queries daily, that's $500,000 per day. Pre-aggregating for $10,000 per day in pipeline costs plus $5,000 per day in storage is a 97 percent cost reduction. You need consistent low latency for user facing analytics. If product requirements demand p99 under 2 seconds for customer dashboards, and raw queries are 5 to 15 seconds, pre-aggregation is likely your only path to meeting Service Level Objectives (SLOs). Choose On-Demand Alternatives When: Query patterns are highly exploratory and unpredictable. Data scientists slicing by arbitrary dimension combinations won't benefit from pre-aggregation because you can't predict which aggregates to materialize. A scalable columnar warehouse optimized for scans is better here. Systems like modern cloud warehouses can scan terabytes in single digit seconds with sufficient parallelism. Dimensions change frequently. If product taxonomy, geographic hierarchies, or user segments change weekly, maintaining pre-aggregated cubes becomes a maintenance nightmare. Every schema change requires rebuilding aggregates. On-demand computation from raw data with good partitioning is simpler operationally. Freshness is critical and data volume is moderate. If you need real time analytics on sub minute freshness, streaming pre-aggregation adds complexity. For moderate data volumes, such as under 100 million events per day, a fast OLAP engine can often query raw data in under 500 milliseconds, making pre-aggregation unnecessary. Hybrid Approaches: Many production systems use both. Pre-aggregate for the 80 percent of queries that are predictable: executive dashboards, finance reports, operational monitoring. Leave ad hoc exploration to on-demand systems. Add result caching for hot dashboards that don't need real time freshness. This combination often provides 95 percent of pre-aggregation's benefits with much less complexity than trying to pre-aggregate everything. Approximate Alternatives: For certain metrics, approximate algorithms sidestep pre-aggregation entirely. HyperLogLog sketches for distinct counts or T-Digest for percentiles can be computed incrementally with tiny memory footprints. A sketch that gives distinct count within 2 percent accuracy using just 1 KB of space can replace heavy pre-aggregation for many use cases. This works when approximate answers are acceptable, which is surprisingly often for analytical workloads.