> ## Documentation Index
> Fetch the complete documentation index at: https://lightdash-refactor-full-audit-reorg.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Pre-aggregates
> Speed up dashboards and reduce warehouse costs by serving queries from pre-computed, materialized summaries.
**Availability:** Pre-aggregates are a [Beta](/help/feature-maturity-levels) 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](/api-reference/v1/introduction), [MCP](/agents/lightdash-mcp), [AI agents](/agents), the [Embed SDK](/embed/reference), and the [React SDK](/embed/react-sdk).
Watch this video walkthrough for an overview of how to get started with pre-aggregates:
Define pre-aggregates in your dbt project and configure scheduling.
Track materialization status, debug query matching, and view hit/miss stats.
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](#supported-metric-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`](/semantic-layer/tables#sql-filter-row-level-security) metrics, or SQL dependent on [Parameters](/semantic-layer/parameters) and [user attributes](/workspace-admin/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](/semantic-layer/pre-aggregates/getting-started#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](/personal-settings/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](/semantic-layer/caching) instead.
* [Parameters](/semantic-layer/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](/workspace-admin/user-attributes) when referenced from SQL. [`required_attributes`](/semantic-layer/tables#required-attributes) and [`any_attributes`](/semantic-layer/tables#any-attributes) are still supported through [`materialization_role`](/semantic-layer/pre-aggregates/getting-started#materialization-role).
* [Custom metrics](/explore/create-custom-fields#custom-metrics) created in the Explorer
* [Custom SQL dimensions](/explore/create-custom-fields#custom-sql) created in the Explorer ([Custom bin dimensions](/explore/create-custom-fields#bin) are supported)
* SQL table calculations ([Formula table calculations](/explore/table-calculations/formulas) are supported)
### SQL compatibility
[`sql_filter`](/semantic-layer/tables#sql-filter-row-level-security) (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](/semantic-layer/parameters) or [user attributes](/workspace-admin/user-attributes), the values injected come from the materialization context — you can pin this to a fixed identity or attribute set with [`materialization_role`](/semantic-layer/pre-aggregates/getting-started#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](https://github.com/lightdash/lightdash/issues/21536) 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](https://github.com/lightdash/lightdash/issues/21537)
* `number`, `string`, `date`, `timestamp`, `boolean`
For metrics that can't be pre-aggregated, consider using [caching](/semantic-layer/caching) instead.
## Pre-aggregates vs results caching
Pre-aggregates and [results caching](/semantic-layer/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](/semantic-layer/caching#results-caching-vs-pre-aggregates).