What is Pre-Aggregation? Definition and Performance Impact

Pre-aggregation is a caching strategy that materializes commonly-queried aggregations into summary tables ahead of time. Instead of scanning millions of rows on every request, the pre-aggregated result returns in milliseconds. It trades storage for speed. This is distinct from database-level materialized views: pre-aggregation in a semantic layer is aware of your metric definitions, time dimensions, and tenant scoping, so it can match incoming queries to the correct rollup automatically without manual query rewriting.

How does pre-aggregation work?

The system analyzes query patterns and identifies which combinations of measures, dimensions, and time granularities get requested most often. It materializes those results into rollup tables: pre-computed summaries that contain the final aggregated values.

When a new query arrives that matches a rollup, the engine reads from the summary table instead of scanning the full source data. A query that would scan 50 million rows in your warehouse returns from a pre-aggregated table with a few thousand rows. The rollups refresh on a schedule you define. For B2B SaaS products with hundreds of tenants, this is critical: without pre-aggregation, each tenant's dashboard triggers a full warehouse scan. With it, every tenant gets sub-second responses from shared rollup tables, and your warehouse compute bill stays predictable.

pre_aggregations:
  - name: orders_by_day
    measures:
      - total_revenue
      - order_count
    dimensions:
      - status
    time_dimension: created_at
    granularity: day
    refresh_key:
      every: 1 hour

When should you use pre-aggregation?

High-traffic dashboards. Customer-facing analytics pages hit the same queries thousands of times per day. Without pre-aggregation, every page load runs the full query against your warehouse. With it, the response is cached and returns in single-digit milliseconds.

Customer-facing analytics. Your customers expect sub-second load times. They will not wait three seconds for a chart to render. Pre-aggregation is the difference between a product that feels fast and one that feels broken.

Repeated aggregations on large tables. If your events table has hundreds of millions of rows and every dashboard computes COUNT(DISTINCT user_id) grouped by day, that aggregation should be materialized once and reused. Scanning the full table on every request wastes warehouse compute and money.

How Bonnard handles pre-aggregation

The pre-aggregation cache serves as the engine for this. You define rollups in YAML alongside your cube definitions. bon deploy creates and schedules the rollup tables. The pre-aggregation cache handles the storage, indexing, and automatic query routing. You specify which measures, dimensions, and time granularities to materialize, and Bonnard builds the rollup tables accordingly.

When a query matches a rollup, Bonnard serves the result from the pre-aggregation cache instead of hitting your warehouse. When it doesn't match, the query goes to the warehouse as normal. This is transparent to the consumer: AI agents, React components, and API clients all get the same fast responses without knowing whether the result came from cache or a live query.

Rollup refresh runs on the schedule you configure. For most customer-facing use cases, hourly or daily refresh balances freshness with performance. You can set different refresh intervals per rollup: real-time metrics refresh every few minutes, while historical reports refresh daily. Read more about the semantic layer that powers this.

Related terms

• Semantic Layer