By default, Qyra owns the entire pre-aggregate lifecycle: it materializes the rollup on your warehouse, stores the result, and serves matching queries from that stored copy. See Pre-aggregates for how the managed path works.

An external pre-aggregate flips ownership of the materialization. You point a pre-aggregate at a warehouse table that you build and refresh yourself, and Qyra uses it purely for query routing.

Managed vs external at a glance

Managed pre-aggregates are the default. Use external pre-aggregates when you already maintain warehouse rollups (for example, BigQuery materialized views that refresh incrementally per partition) and don't want Qyra to rebuild the same data on your warehouse.

How it works

1. Declare the external pre-aggregate in your dbt YAML with a table key pointing at the warehouse relation you'll build.
2. Get the column contract with the CLI. Qyra generates the exact column names, types, and materialization SQL your table must conform to.
3. Build the table in your warehouse using any orchestration you like — dbt models, Airflow DAGs, scheduled queries.
4. Verify the built table matches the contract with the same CLI command, and wire the check into CI.
5. Deploy the YAML change.

At query time, matching is identical to managed pre-aggregates (dimension, metric, filter, and granularity coverage). When a query matches an external pre-aggregate, Qyra compiles it against your table in your warehouse's SQL dialect and runs it on your project warehouse — DuckDB and Qyra storage are not involved. Serve errors (missing table, missing column, type mismatch) fall back to the warehouse per query.

1. Declare the pre-aggregate

Add a table key on the pre-aggregate. Its value is a warehouse relation reference, injected verbatim into the generated SQL — quote and qualify it exactly as your warehouse expects.

models:
  - name: orders
    config:
      meta:
        pre_aggregates:
          - name: orders_daily_by_status
            dimensions:
              - status
            metrics:
              - total_order_amount
            time_dimension: order_date
            granularity: day
            table: '`analytics-prod`.`marts`.`orders_daily_by_status_mv`'

models:
  - name: orders
    meta:
      pre_aggregates:
        - name: orders_daily_by_status
          dimensions:
            - status
          metrics:
            - total_order_amount
          time_dimension: order_date
          granularity: day
          table: '`analytics-prod`.`marts`.`orders_daily_by_status_mv`'

type: model
name: orders

pre_aggregates:
  - name: orders_daily_by_status
    dimensions:
      - status
    metrics:
      - total_order_amount
    time_dimension: order_date
    granularity: day
    table: '`analytics-prod`.`marts`.`orders_daily_by_status_mv`'

Everything else on the definition — dimensions, metrics, filters, time_dimension, granularity — is the same as a managed pre-aggregate and drives matching identically. The materialization-only keys refresh, sorts, max_rows, and materialization_role are silently ignored on an external definition; you own the build, so Qyra cannot honor them.

2. Get the column contract

Your table must conform to a column contract that Qyra generates from the pre-aggregate definition. The qyra pre-aggregate-check-external CLI command prints the expected columns and the exact materialization SQL for each external pre-aggregate. Its full flag set is in the CLI reference.

qyra pre-aggregate-check-external --all

For each external pre-aggregate, the output prints:

• The expected column contract — column name, type, and role (dimension, time dimension + grain, metric, or metric component)
• An actual column check against the declared table when it exists (or a warning that the table is missing)
• The materialization SQL — the exact SELECT Qyra expects to serve from

orders / orders_daily_by_status → `analytics-prod`.`marts`.`orders_daily_by_status_mv`
  ⚠ Could not introspect declared table: notFound - Table ... was not found

  EXPECTED                        TYPE       ROLE
  orders_status                   string     dimension
  orders_order_date_day           timestamp  time dimension (day)
  orders_total_order_amount       number     metric (sum)

  Materialization SQL (bigquery):
  SELECT
    `orders`.status AS `orders_status`,
    TIMESTAMP_TRUNC(`orders`.order_date, DAY) AS `orders_order_date_day`,
    SUM(`orders`.amount) AS `orders_total_order_amount`
  FROM `analytics-prod`.`raw`.`orders` AS `orders`
  GROUP BY 1, 2

Two column-contract details are worth calling out:

• Average metrics decompose into components. An average metric stores as <fieldId>__sum and <fieldId>__count columns, and the materialization SQL emits SUM(expr) and COUNT(expr). You never store an average value directly; Qyra re-computes the average from the components at query time.
• Joins are baked in at materialization time. If your pre-aggregate lists dimensions from joined tables (for example, customers.country), the generated SQL includes the join. Your external table stores the joined-dimension columns as flat columns — no join happens at serve time.

3. Build the table

Any orchestration works. The CLI's --json flag is designed for scripting the build itself — for example, wrapping every generated SELECT in a CREATE OR REPLACE TABLE:

qyra pre-aggregate-check-external --all --skip-dbt-compile --json \
  | jq -r '.[] | "CREATE OR REPLACE TABLE \(.table) AS\n\(.sql)"'

You can then pipe each statement into your warehouse (bq query, snowsql, psql, etc.). More commonly, teams register the generated SQL as a dbt model or a warehouse-native materialized view and refresh it on their existing orchestration cadence.

4. Verify the table matches

Re-run the check with --fail-on-mismatch after the build. This exits with code 1 on any column drift, so you can wire it into CI next to the table build:

qyra pre-aggregate-check-external --all --skip-dbt-compile --clear-cache --fail-on-mismatch

orders / orders_daily_by_status  ✓ Table matches — 3/3 columns.

--clear-cache matters right after a rebuild: BigQuery and Snowflake result caches key on query text and can return the pre-rebuild schema if you don't invalidate them. On mismatch the output lists per-column ✗ (missing / type_mismatch) alongside the rebuild SQL.

See Auditing pre-aggregates from the CLI for related audit tooling that reports hit and miss coverage across dashboards.

5. Deploy

Deploy the YAML change with qyra deploy (or your CI deploy) as normal. External pre-aggregates take effect on the next compile — there is no materialization job to wait for.

What Qyra owns vs what you own

External pre-aggregates split responsibility explicitly:

Qyra owns:

• Matching — same rules as managed pre-aggregates (field coverage, granularity, filter compatibility, non-additive metrics on exact matches only, smallest-wins)
• Compiling the served query against your table in your warehouse's dialect
• Re-truncating the stored time dimension for coarser-grain queries (a day-grain table serves week, month, quarter, year)
• Re-aggregating __sum and __count components into average metrics at query time
• Applying sql_filter — rewritten onto the external table's columns, with ${qyra.*} substituted for the querying user — plus model required filters and required attributes at serve time. Field references in sql_filter must be pre-aggregate dimensions; non-field references (${TABLE}.col, hand-written table.col) pass through verbatim and must exist in the external table under those raw names.
• Falling back to the warehouse per query if serving errors (missing table, missing column, type mismatch)
• Recording hits and misses in pre-aggregate analytics and the dashboard audit

You own:

• Building the table with the exact column names and types Qyra generates
• Refreshing the table on whatever cadence and orchestration you choose
• Keeping the table complete — missing rows produce silently incomplete results
• Applying the pre-aggregate's filters at build time — Qyra does not re-apply them at serve time on external tables
• Access — every credential that queries Qyra must be able to read the table, including any per-user warehouse credentials

Limitations

External pre-aggregates are opt-in per pre-aggregate. Definitions without a table key run as managed pre-aggregates.

External pre-aggregates:

• Do not check the table's existence or schema at deploy time. Use qyra pre-aggregate-check-external (locally and in CI) to catch drift.
• Do not check freshness — Qyra trusts that the table is current.
• Silently ignore the materialization-only keys (refresh, sorts, max_rows, materialization_role) on the definition.
• Do not show a dedicated "external" indicator in the Project Settings pre-aggregates UI. Hits and misses still show up in analytics and dashboard audits.