Pre-aggregates let you define materialized summaries of your data directly in your dbt YAML. When a user runs a query in Qyra, 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 Qyra semantic layer can hit a pre-aggregate — this includes the Qyra 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. Qyra materializes the rollup on your warehouse, stores the result, and serves matching queries from that stored copy. You only write the definition.
• External pre-aggregates delegate the materialization to you. You point a pre-aggregate at a warehouse table you build and refresh yourself, and Qyra uses the definition only for matching and routing. See External pre-aggregates for the full workflow.

Managed and external pre-aggregates can coexist on the same model, and the matching rules are identical on both paths.

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 — Qyra 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, Qyra 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:

Qyra materializes this into a pre-aggregate:

Now when a user queries "total amount by status, grouped by month", Qyra 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.

Defining pre-aggregates

Pre-aggregates are defined under the pre_aggregates key in your model configuration.

If you're using Qyra YAML instead of dbt model YAML, see the Qyra YAML syntax guide for the surrounding model structure.

models:
  - name: orders
    config:
      meta:
        pre_aggregates:
          - name: orders_daily_by_status
            dimensions:
              - status
            metrics:
              - total_order_amount
              - average_order_size
            filters:
              - order_date: inThePast 52 weeks
            time_dimension: order_date
            granularity: day

models:
  - name: orders
    meta:
      pre_aggregates:
        - name: orders_daily_by_status
          dimensions:
            - status
          metrics:
            - total_order_amount
            - average_order_size
          filters:
            - order_date: inThePast 52 weeks
          time_dimension: order_date
          granularity: day

type: model
name: orders

pre_aggregates:
  - name: orders_daily_by_status
    dimensions:
      - status
    metrics:
      - total_order_amount
      - average_order_size
    filters:
      - order_date: inThePast 52 weeks
    time_dimension: order_date
    granularity: day

Configuration reference

Query matching

When a user runs a query, Qyra 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. Raw SQL table calculations and SQL that depends on Parameters are resolved at query time and are never eligible. Model sql_filter is eligible as long as every field it references is a pre-aggregate dimension — see sql_filter and pre-aggregates.

When multiple pre-aggregates match a query, Qyra 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

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 — expressed with the same filter operator.

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.

Filtered pre-aggregates

Use filters when you want a pre-aggregate to materialize only a subset of the source data.

For example, this pre-aggregate only stores data for the last 52 weeks:

models:
  - name: orders
    config:
      meta:
        pre_aggregates:
          - name: recent_orders_daily
            dimensions:
              - status
            metrics:
              - total_order_amount
              - order_count
            filters:
              - order_date: inThePast 52 weeks
            time_dimension: order_date
            granularity: day

models:
  - name: orders
    meta:
      pre_aggregates:
        - name: recent_orders_daily
          dimensions:
            - status
          metrics:
            - total_order_amount
            - order_count
          filters:
            - order_date: inThePast 52 weeks
          time_dimension: order_date
          granularity: day

type: model
name: orders

pre_aggregates:
  - name: recent_orders_daily
    dimensions:
      - status
    metrics:
      - total_order_amount
      - order_count
    filters:
      - order_date: inThePast 52 weeks
    time_dimension: order_date
    granularity: day

This is useful when a rolling time window is queried frequently and deserves its own smaller materialization.

How query matching works with filters

Filtered pre-aggregates are only used when the query filters are compatible with the pre-aggregate definition:

• A query with the same or narrower filter can use the pre-aggregate
• A query without the filter, or with a broader or incompatible filter, falls back to another pre-aggregate or the warehouse

For the example above:

• order_date inThePast 12 weeks can use the pre-aggregate
• order_date inThePast 52 weeks can use the pre-aggregate
• order_date inThePast 104 weeks cannot use the pre-aggregate
• order_date is 2026-01-15 cannot use the pre-aggregate, even though the date falls inside the last 52 weeks (see the operator-matching note below)
• no order_date filter: cannot use the pre-aggregate

A field used only for filtering still belongs in the pre-aggregate's dimensions list, so Qyra can match and re-aggregate queries correctly.

Required filters and pre-aggregates

Models can declare required_filters that every query on the explore must apply. Pre-aggregates coexist with required filters, with a few rules on both sides.

How required filters are applied

Required filters are applied when a query reads from the pre-aggregate, not baked permanently into the materialized table. The materialization stores rows across every value of the required-filter field, and Qyra re-applies the filter each time a query hits the rollup. This lets users override the required filter's default value (where the model allows it) and still be served from the pre-aggregate — they don't silently get an incomplete result from a materialization that only holds one value.

Every required-filter field must be a pre-aggregate dimension

Because the filter is applied at query time, its target field has to exist as a column in the materialization. If any required_filters target on the model isn't listed in the pre-aggregate's dimensions, the pre-aggregate is ineligible for that explore and Qyra queries the warehouse instead. This applies to fields on the base table and on joined tables. Sibling time-dimension grains (for example, a required filter on created_at_week when the pre-aggregate's time dimension is created_at at day grain) also need the underlying dimension in the pre-aggregate.

Only filters actually marked required: true count. Model filters marked as not required don't need to be in the pre-aggregate.

Don't duplicate required-filter targets in filters

The pre-aggregate's own filters narrow the materialization at build time and can't be overridden at query time. Setting an explicit pre-aggregate filter on the same field as a required_filters target creates a conflict — the required filter is meant to be overridable by the user, but the pre-aggregate filter isn't. Qyra treats these queries as a miss (pre_aggregate_filter_not_satisfied) rather than silently returning partial results.

Keep required-filter fields out of the pre-aggregate's filters block. If you need to narrow the materialization on a required-filter field, split it into a separate pre-aggregate that doesn't overlap.

Multiple pre-aggregates per model

You can define multiple pre-aggregates on the same model, each targeting different query patterns. It is better to have multiple small, focused pre-aggregates rather than a single one containing all metrics and dimensions. Including too many dimensions increases the number of unique combinations, which generates large materialization files — this defeats the purpose of pre-aggregates, since they are meant to be smaller and faster than querying the warehouse directly.

For example, you might want a fine-grained daily pre-aggregate for detailed dashboards and a coarser monthly one for summary views:

models:
  - name: orders
    config:
      meta:
        pre_aggregates:
          - name: orders_daily_by_status
            dimensions:
              - status
            metrics:
              - total_order_amount
              - order_count
            time_dimension: order_date
            granularity: day
          - name: orders_monthly_summary
            dimensions:
              - status
            metrics:
              - total_order_amount
            time_dimension: order_date
            granularity: month
            max_rows: 1000000

When a query matches multiple pre-aggregates, Qyra picks the smallest one.

Scheduling refreshes

By default, pre-aggregates are materialized when your dbt project compiles. You can also schedule automatic refreshes using cron expressions, using your project's configured timezone (defaults to UTC):

pre_aggregates:
  - name: orders_daily_by_status
    dimensions:
      - status
    metrics:
      - total_order_amount
    time_dimension: order_date
    granularity: day
    refresh:
      cron: "0 6 * * *"  # Every day at 6:00 AM UTC

Materialization triggers

Pre-aggregates can be materialized through four different triggers:

Row limits

You can set max_rows to cap the size of a materialization. If the aggregation produces more rows than the limit, the result is truncated.

Materialization sort order

Use sorts to control the order rows are written in the materialized table. Sorting the materialization on the dimensions you filter and group by most often can make downstream reads faster.

sorts is a list of entries. Each entry has:

• fieldId — the canonical field ID of a dimension included in the pre-aggregate. Joined-table fields use the table.field form.
• descending — boolean, required. true sorts high to low, false sorts low to high.

pre_aggregates:
  - name: orders_daily_by_status
    dimensions:
      - status
    metrics:
      - total_order_amount
    time_dimension: order_date
    granularity: day
    sorts:
      - fieldId: orders_order_date_day
        descending: true
      - fieldId: orders_status
        descending: false

The sorts key accepts four shapes, each with a different meaning:

Materialization role

materialization_role is useful when access to the model depends on required_attributes or any_attributes.

For example, if a joined table is only available to users with region_access: emea, then materializing a pre-aggregate without a fixed access context could produce different results depending on who triggered the build.

Use materialization_role to make materialization run with a stable set of user attributes.

This is intended for access control fields such as:

• required_attributes
• any_attributes

models:
  - name: orders
    config:
      meta:
        joins:
          - join: customers
            sql_on: ${customers.customer_id} = ${orders.customer_id}
        pre_aggregates:
          - name: orders_daily_by_region
            dimensions:
              - customers.region
            metrics:
              - total_order_amount
            time_dimension: order_date
            granularity: day
            materialization_role:
              email: materialize@acme.com
              attributes:
                region_access: emea

models:
  - name: orders
    meta:
      joins:
        - join: customers
          sql_on: ${customers.customer_id} = ${orders.customer_id}
      pre_aggregates:
        - name: orders_daily_by_region
          dimensions:
            - customers.region
          metrics:
            - total_order_amount
          time_dimension: order_date
          granularity: day
          materialization_role:
            email: materialize@acme.com
            attributes:
              region_access: emea

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 Qyra serves those queries from the materialization instead of hitting the warehouse.

Execution fallback

When a query matches a pre-aggregate but the pre-aggregate execution itself fails — an unreadable materialization file, a DuckDB error, or a missing external table — Qyra retries the query against the source warehouse by default. Dashboards stay available while you investigate the broken materialization.

To turn that retry off, set pre_aggregate_execution_fallback: false under defaults in qyra.config.yml. The query returns an error instead of silently running on the warehouse, so a broken pre-aggregate surfaces immediately rather than re-introducing warehouse latency and cost.

Execution fallback only covers a matched query whose serve fails. Queries that don't match any pre-aggregate always run against the warehouse — see monitoring for miss reasons.

Current limitations

Pre-aggregates support a narrower subset of the Qyra 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 referenced from a dimension or metric SQL expression. They're only resolved at serve time in sql_filter; required_attributes and any_attributes are 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_filter and pre-aggregates

sql_filter (and its alias sql_where) is applied both when the pre-aggregate materializes and when a query is served from it. On the serve pass, the filter is rewritten to run against the materialization's columns instead of the source tables:

• ${field} references — including joined ones like ${customers.segment} — resolve to the materialized column for that field. Every field the sql_filter references must be one of the pre-aggregate's dimensions; if a referenced field isn't covered, matching records a sql_filter field not in pre-aggregate miss and the query falls back to the warehouse. Adding the referenced field to dimensions is also what makes the aggregation grain correct.
• ${qyra.attribute_name} references are substituted with the querying user's user attribute values at serve time. If the user is missing a referenced attribute, the pre-aggregate fails closed and the query falls back to the warehouse.
• Non-field references — ${TABLE}.some_column or hand-written some_table.some_column — pass through verbatim. For external pre-aggregates these columns must exist in the external table under their raw source names; on managed pre-aggregates a warehouse-specific column reference that DuckDB doesn't understand causes the query to fall back to the warehouse. Correctness of the grain when non-field references filter on non-dimension columns is on you.

At materialization time, the same filter is evaluated against your warehouse under the materialization identity. Use materialization_role to pin that identity when the filter references user attributes, so the materialization captures a stable slice of rows regardless of who triggered the build.

Metrics that need re-aggregation to combine

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.

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.

Complete example

Here's a full model definition with a pre-aggregate, including joins, scheduling, and row limits:

models:
  - name: orders
    config:
      meta:
        joins:
          - join: customers
            sql_on: ${customers.customer_id} = ${orders.customer_id}
        pre_aggregates:
          - name: orders_daily_by_status
            dimensions:
              - status
              - customers.country
            metrics:
              - total_order_amount
              - average_order_size
            filters:
              - status: completed
            time_dimension: order_date
            granularity: day
            max_rows: 5000000
            refresh:
              cron: "0 6 * * *"
    columns:
      - name: order_date
        config:
          meta:
            dimension:
              type: date
      - name: status
        config:
          meta:
            dimension:
              type: string
      - name: amount
        config:
          meta:
            metrics:
              total_order_amount:
                type: sum
              average_order_size:
                type: average

models:
  - name: orders
    meta:
      joins:
        - join: customers
          sql_on: ${customers.customer_id} = ${orders.customer_id}
      pre_aggregates:
        - name: orders_daily_by_status
          dimensions:
            - status
            - customers.country
          metrics:
            - total_order_amount
            - average_order_size
          filters:
            - status: completed
          time_dimension: order_date
          granularity: day
          max_rows: 5000000
          refresh:
            cron: "0 6 * * *"
    columns:
      - name: order_date
        meta:
          dimension:
            type: date
      - name: status
        meta:
          dimension:
            type: string
      - name: amount
        meta:
          metrics:
            total_order_amount:
              type: sum
            average_order_size:
              type: average

type: model
name: orders

joins:
  - join: customers
    sql_on: ${customers.customer_id} = ${orders.customer_id}

pre_aggregates:
  - name: orders_daily_by_status
    dimensions:
      - status
      - customers.country
    metrics:
      - total_order_amount
      - average_order_size
    filters:
      - status: completed
    time_dimension: order_date
    granularity: day
    max_rows: 5000000
    refresh:
      cron: "0 6 * * *"

dimensions:
  - name: order_date
    type: date
  - name: status
    type: string

metrics:
  total_order_amount:
    type: sum
    sql: ${TABLE}.amount
  average_order_size:
    type: average
    sql: ${TABLE}.amount

With this pre-aggregate, the following queries would be served from materialized data:

• Total order amount by status, grouped by day, week, month, or year
• Average order size by status, grouped by month
• Total order amount filtered to completed orders
• Order amount by customer country, grouped by quarter

These queries would not match and would query the warehouse directly:

• Queries grouped by a dimension not in the pre-aggregate (for example, customer_id)
• Queries with hourly granularity (finer than the pre-aggregate's day)
• Queries without status = completed or with a broader status filter
• Queries with Parameters, or with user attributes referenced from a dimension or metric SQL expression (user attributes in sql_filter are supported)
• Queries including a non-additive metric like count_distinct unless they select exactly the pre-aggregate's dimensions at exactly its granularity (see Exact match queries)
• Queries with raw SQL table calculations