Pre-aggregates

Speed up dashboards and reduce warehouse costs by serving queries from pre-computed, materialized summaries.

Availability: Pre-aggregates are a Beta feature available on Enterprise plans only.

Pre-aggregates let you define materialized summaries of your data directly in your dbt YAML. When a user runs a query in Lightdash, the system checks if the query can be answered from a pre-aggregate instead of querying your warehouse. If it matches, the query is served from the pre-computed results, making it significantly faster and reducing warehouse load.

This is especially useful for dashboards with high traffic or expensive aggregations that don’t need real-time data.

Any query that goes through the Lightdash semantic layer can hit a pre-aggregate — this includes the Lightdash app, the API, MCP, AI agents, the Embed SDK, and the React SDK.

Watch this video walkthrough for an overview of how to get started with pre-aggregates:

Getting started

Define pre-aggregates in your dbt project and configure scheduling.

Monitoring and debugging

Track materialization status, debug query matching, and view hit/miss stats.

CLI audit

Inspect dashboard coverage from the terminal and gate CI on hit rates.

How it works

Pre-aggregates follow a four-step cycle:

1. Define — You add a pre_aggregates block to your dbt model YAML, specifying which dimensions and metrics to include.
2. Materialize — Lightdash runs the aggregation query against your warehouse and stores the results. This happens automatically on compile, on a cron schedule you define, or when you trigger it manually.
3. Match — When a user runs a query, Lightdash checks if every requested dimension, metric, and filter is covered by a pre-aggregate.
4. Serve — If a match is found, the query is served from the materialized data instead of hitting your warehouse.

Example

Suppose you have an orders table with thousands of rows, and you define a pre-aggregate with dimensions status and metrics total_amount (sum) and order_count (count), with a day granularity on order_date.

Your warehouse data:

order_date	status	customer	amount
2024-01-15	shipped	Alice	$100
2024-01-15	shipped	Bob	$50
2024-01-15	pending	Charlie	$75
2024-01-16	shipped	Alice	$200
2024-01-16	pending	Charlie	$30
…	…	…	…

Lightdash materializes this into a pre-aggregate:

order_date_day	status	total_amount	order_count
2024-01-15	shipped	$150	2
2024-01-15	pending	$75	1
2024-01-16	shipped	$200	1
2024-01-16	pending	$30	1

Now when a user queries “total amount by status, grouped by month”, Lightdash re-aggregates from the daily pre-aggregate instead of scanning the full table:

order_date_month	status	total_amount
January 2024	shipped	$350
January 2024	pending	$105

This works because sum can be re-aggregated — summing daily sums gives the correct monthly sum.

Query matching

When a user runs a query, Lightdash checks whether a pre-aggregate can serve it first. A pre-aggregate matches when the query fits inside it on each axis:

• Fields are available — every dimension, metric, and filter dimension in the query exists somewhere in the pre-aggregate.
• Grain is reachable — if the query uses a time dimension, its granularity is equal or coarser than the pre-aggregate’s, so the rows can be rolled up. Month is a coarser grain than day. Day is a coarser grain than hour.
• Scope is compatible — if the pre-aggregate defines its own filters, the query includes an equal or narrower filter, so the subset can be filtered from the pre-aggregate base.
• Metrics re-aggregate cleanly — all metrics are supported types. No non-additive metrics (like count distinct, median, etc. which require recalculation per grouping) and nothing resolved at runtime (raw SQL table calculations, sql_filter metrics, or SQL dependent on Parameters and user attributes). These can’t be faithfully re-computed from stored rows.

A day pre-aggregate serves day, week, month, quarter, and year queries. A month pre-aggregate serves month, quarter, and year — but not day or week, since those need finer-grained data.

When multiple pre-aggregates match a query, Lightdash picks the smallest one (fewest dimensions, then fewest metrics as tiebreaker).

Filtered pre-aggregates

A pre-aggregate can define static filters so it materializes only a slice of the source data for a common query pattern, such as status = completed or a rolling order_date: inThePast 52 weeks window. A query then matches it only when it carries the same filter or a narrower one — the scope-compatibility rule above.

See Filtered pre-aggregates for the definition syntax and a worked matching example.

Dimensions from joined tables

Pre-aggregates support dimensions from joined tables. Reference them by their full name (for example, customers.first_name) in the dimensions list.

Supported metric types

Pre-aggregates support metrics that can be re-aggregated from pre-computed results:

• sum
• count
• min
• max
• average

Current limitations

Pre-aggregates support a narrower subset of the Lightdash semantic layer than regular warehouse queries.

Not supported

Pre-aggregates do not support:

• Personal warehouse connections. Materialization always runs under a single user’s credentials, so warehouse-level access rules are not applied per viewer. If you rely on personal warehouse connections to enforce data access, use results caching instead.
• Parameters — parameter values are picked at query time, so they cannot be resolved during materialization. Queries that use parameters fall back to the warehouse.
• User attributes when referenced from SQL. required_attributes and any_attributes are still supported through materialization_role.
• Custom metrics created in the Explorer
• Custom SQL dimensions created in the Explorer (Custom bin dimensions are supported)
• SQL table calculations (Formula table calculations are supported)

SQL compatibility

sql_filter (and its alias sql_where) runs both at materialization time and at query time on top of the materialized data.

• At materialization time, the filter is evaluated against your warehouse. If the SQL references Parameters or user attributes, the values injected come from the materialization context — you can pin this to a fixed identity or attribute set with materialization_role so the materialization captures the rows you need.
• At query time, the same filter is re-applied against the materialized data, which is served by DuckDB. If the sql_filter SQL uses warehouse-specific syntax that DuckDB doesn’t understand, the query will fail to run against the pre-aggregate and fall back to the warehouse.

Metrics that can’t be pre-aggregated

Pre-aggregates do not support metric types that cannot be re-aggregated from pre-computed results.

For example, consider count_distinct on a daily pre-aggregate. If the pre-aggregate stores “2 distinct customers on 2024-01-15” and “1 distinct customer on 2024-01-16”, you cannot sum those daily values to get the monthly distinct count, because the same customer can appear on multiple days.

order_date_day	status	distinct_customers
2024-01-15	shipped	2 (Alice, Bob)
2024-01-16	shipped	1 (Alice)

Re-aggregating gives 2 + 1 = 3, but the correct monthly answer is 2 (Alice, Bob). The pre-aggregate no longer knows which customers were counted.

We’re investigating supporting count_distinct through approximation algorithms. Follow this issue for updates.

For similar reasons, the following metric types are also not supported:

• sum_distinct, average_distinct
• median, percentile
• percent_of_total, percent_of_previous
• running_total
• Custom SQL / post-calculation metrics (including many number metrics) — Follow this issue
• number, string, date, timestamp, boolean

For metrics that can’t be pre-aggregated, consider using caching instead.

Pre-aggregates vs results caching

Pre-aggregates and results caching are independent systems that speed up queries in different ways, and they work best together: pre-aggregates serve matching queries from materialized summary tables — no warehouse hit, even on the first query — while results caching stores the exact result of any query shape after its first run. A query that hits a pre-aggregate can also have its result cached, layering the two.

For the full comparison — a feature-by-feature table and guidance on when to use each — see Results caching vs pre-aggregates.