Pre-aggregates

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:

​

Managed and external pre-aggregates

Pre-aggregates come in two flavors, distinguished by who owns the underlying table:

• Managed pre-aggregates are the default. Lightdash materializes the rollup on your warehouse, stores the result, and serves matching queries from that stored copy using in-memory DuckDB workers. You only write the definition — Lightdash handles the rest.
• External pre-aggregates delegate the materialization to you. You point a pre-aggregate at a warehouse table you build and refresh yourself (with dbt, Airflow, a scheduled query, or a warehouse-native materialized view). Lightdash uses the definition only for matching and routing — matching queries run on your project warehouse against your table. See External pre-aggregates for the full workflow.

Managed and external pre-aggregates can coexist on the same model. Matching, filter compatibility, granularity rules, and supported metric types are identical on both paths — the difference is only where the data lives and who refreshes it.

​

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: Lightdash materializes this into a pre-aggregate: 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: 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. Non-additive metrics like count_distinct, median, and percentile can’t be faithfully re-computed from stored rows, so they only match on an exact match of the pre-aggregate. Anything resolved at runtime — raw SQL table calculations, sql_filter metrics, or SQL dependent on Parameters and user attributes — is never eligible.

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

​

Exact match queries

A query is an exact match of a pre-aggregate when its selected dimensions are set-equal to the pre-aggregate’s dimensions and its time dimension is at exactly the pre-aggregate’s granularity. On an exact match, each result row is served from a single materialization row without any re-aggregation. This unlocks metric types that can’t otherwise be re-aggregated — see Non-additive metrics on exact matches. For a query to count as an exact match:

• Every pre-aggregate dimension must appear in the query’s selected dimensions, and vice versa.
• The time dimension must be selected at exactly the pre-aggregate’s granularity — not coarser, not finer.
• Filters on selected dimensions are allowed. They only subset the stored rows, so the match still holds.
• A pre-aggregate dimension referenced only by a query filter does not count as selected, and the query is no longer an exact match.
• A dimension reached through a custom bin does not count as selected either — bins collapse groups and break the one-row-per-result guarantee.

Selecting a subset of the pre-aggregate’s metrics is still an exact match, as long as the dimension set matches.

​

Filtered pre-aggregates

Pre-aggregates can define static filters in their YAML definition. This lets you materialize a smaller slice of data for a common query pattern, such as status = completed or order_date: inThePast 52 weeks. When a pre-aggregate has definition filters:

• Matching queries must include the same filter or a narrower one
• Queries without the filter, or with a broader or incompatible filter, fall back to another pre-aggregate or the warehouse

This prevents Lightdash from serving incomplete results from a filtered materialization.

​

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 two kinds of metrics. Re-aggregatable metrics work for any matching query, including ones at a coarser grain or on a subset of the pre-aggregate’s dimensions:

• sum
• count
• min
• max
• average

Exact-only metrics are non-additive and only match on an exact-match query:

• count_distinct
• sum_distinct
• average_distinct
• median
• percentile

You can add exact-only metrics to any pre-aggregate. They are materialized (or expected in the external table’s column contract) and served whenever a query matches the pre-aggregate exactly. Non-exact queries that include an exact-only metric miss with the reason non_additive_metric_requires_exact_match — the fix is to select exactly the pre-aggregate’s dimensions at exactly its granularity.

​

Non-additive metrics on exact matches

Non-additive metrics like count_distinct, sum_distinct, average_distinct, median, and percentile normally can’t be re-aggregated from a rollup, because combining group-level values produces the wrong answer (see Metrics that need re-aggregation to combine). But on an exact match there is nothing to re-aggregate: each result row corresponds to exactly one materialization row, so the stored value is already the correct answer. This is useful when a count_distinct (or another non-additive metric) is the slowest part of a query. Define a pre-aggregate whose dimensions and time-dimension granularity match how the metric is queried, and Lightdash serves those queries from the materialization instead of hitting the warehouse.

​

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 need re-aggregation to combine

Non-additive metrics — count_distinct, sum_distinct, average_distinct, median, and percentile — can’t be re-computed from a rollup. On a non-exact match, Lightdash would need to combine stored group-level values to answer the query, and that produces the wrong number. 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. 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. For that reason, non-additive metrics are only served on exact-match queries, where no combining happens. A non-exact query that includes one falls back to the warehouse with the miss reason non_additive_metric_requires_exact_match. We’re investigating serving count_distinct on non-exact matches through approximation algorithms. Follow this issue for updates.

​

Metrics that are never eligible

The following metrics can’t be pre-aggregated at all, on exact or non-exact matches:

• percent_of_total, percent_of_previous
• running_total
• Custom SQL / post-calculation metrics, including type: number metrics that reference a non-additive metric — 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

Lightdash has two independent systems for speeding up queries: results caching and pre-aggregates. They work differently and are designed to be used together, not as replacements for each other.

​

Results caching

Results caching stores the exact result of any query that runs through Lightdash, keyed by a hash of the generated SQL. The first time a query runs, Lightdash executes it against your warehouse and caches the result in S3. Subsequent identical queries are served from the cache until it expires (24 hours by default). Any change to the query — a different filter, column, limit, or user attribute — produces a new SQL hash, a new cache entry, and another warehouse query. Results caching covers every query shape, including custom metrics, table calculations, and SQL runner queries. See the caching guide for details.

​

Pre-aggregates

Pre-aggregates are summary tables you define in your dbt YAML. Lightdash materializes them on a schedule (or on compile, or manually) and stores the results in S3. When a user query matches the pre-aggregate’s dimensions, metrics, filters, and granularity, Lightdash serves the query from the materialized data using in-memory DuckDB workers. The warehouse is not touched at query time, even on the first query. A single pre-aggregate can serve many different queries. A daily pre-aggregate with five dimensions can answer day, week, month, quarter, and year queries across any subset of those dimensions and with any narrower filter. Results caching, in contrast, needs one cache entry per unique SQL.

​

Key differences

​

When to use which

Use pre-aggregates when:

• You have high-traffic dashboards with predictable query patterns
• You want to reduce warehouse cost or improve latency on the first query, not just repeat visits
• The metrics are re-aggregatable (sum, count, min, max, average), or they’re non-additive (count_distinct, median, etc.) and the queries you want to speed up hit a pre-aggregate exactly
• You’re willing to design and schedule the materializations

Use results caching when:

• Query patterns are ad-hoc or unpredictable
• You need unsupported features listed above, such as Parameters, sql_filter, or raw SQL table calculations, or you need count_distinct and similar non-additive metrics across query shapes that aren’t exact matches
• You’re using the SQL runner
• You don’t want upfront configuration work

​

Using both together

When both systems are enabled, they act as two layers of caching. A query that matches a pre-aggregate is served from the materialized data by DuckDB workers. The result of that DuckDB query can then be stored in the results cache, so subsequent identical requests skip even the DuckDB step and return the cached result directly. This means pre-aggregates eliminate the warehouse hit, and results caching eliminates repeated computation on top of that.