> ## Documentation Index
> Fetch the complete documentation index at: https://docs.omni.co/llms.txt
> Use this file to discover all available pages before exploring further.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.omni.co/feedback

```json
{
  "path": "/analyze-explore/performance/aggregate-awareness",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

# Optimizing query performance with aggregate awareness

> Aggregate awareness enables you to optimize query performance by intelligently routing queries to pre-aggregated tables in your data warehouse.

This allows end users to access data at different levels of granularity without needing to manually specify which table to query. Omni automatically determines the most efficient table to use based on the query's structure.

## Key concepts

* **Aggregate awareness** - Omni understands the level of aggregation required by a query and dynamically selects the appropriate table (granular or aggregated) in the model to retrieve the data.
* **Bring your own aggregates** - You, the user, create the aggregated tables using your preferred transformation tool. This provides flexibility and control over the aggregation process. Omni then leverages these tables for optimized query performance.

## Setup

Once you've identified the query you'd like to optimize, follow these steps to implement aggregate awareness:

<Steps>
  <Step title="Build an aggregate or rollup table">
    Build an aggregate or rollup table within Omni or some other transformation layer such as dbt.

    If you do this outside of Omni and don't see your table, [refresh the schema](/modeling/develop/schema-refreshes) to have Omni build it. You can also navigate to **Model > Refresh schema** in the model IDE to trigger a refresh for specific tables.

    To demonstrate, let's say you have a table with the following definition and you want to optimize the performance of the daily aggregations:

    ```yaml title="order_items table with aggregation you want to optimize" wrap theme={null}
    dimensions:
      order_items.created_at: {}
      sale_price: {}
      user_id: {}

    measures:
      count:
        aggregate_type: count
      sale_price_sum:
        aggregate_type: sum
        sql: ${sale_price}
      user_id_count_distinct:
        aggregate_type: count_distinct
    ```

    In your transformation layer, you have defined the following materialized table, which has pre-aggregated the metrics daily:

    ```yaml title="Pre-aggregated table definition" wrap theme={null}
    -- pre-aggregated table "daily_sales"
    dimensions:
      date: {}
      total_sale_price: {}
      order_items_count: {}
      user_distinct_count: {}
    ```
  </Step>

  <Step title="Add a materialized_query parameter">
    From the model IDE, add a [`materialized_query` parameter](/modeling/views/parameters/materialized-query) to the optimized view to enable Omni to match the aggregate table to the underlying views:

    ```yaml title="Implementation of aggregate awareness" wrap theme={null}
    materialized_query:
      fields:
        order_items.created_at: date
        order_items.sale_price_sum: total_sale_price
        order_items.count: order_items_count
        order_items.user_id_count_distinct: user_distinct_count
      base_view: order_items
    ```

    <Note>
      The `fields` parameter maps each field from the base view to the corresponding column name in the aggregate table. You can also use the [array format](/modeling/views/parameters/materialized-query#syntax), though the object format shown here is recommended for clarity.
    </Note>
  </Step>

  <Step title="Verify the setup">
    Now, at query runtime, Omni will dynamically swap in the aggregated table for the underlying table if it contains all the necessary fields to execute the query. Because the aggregated table is smaller than the underlying table, queries running against it will be faster and more efficient.

    You can verify that aggregate awareness is working by inspecting the generated SQL using [Omni's SQL inspector](/analyze-explore/workbook-inspector). The SQL inspector will show whether Omni is querying the aggregate table or the granular table.

    ```sql title="Example of fallback to pre-aggregated view" wrap theme={null}
    -- Query rewritten to use materialized view "daily_sales".
    -- The original unoptimized SQL is commented-out below
    -- SELECT DATE_TRUNC('DAY', "created_at") AS "order_items.created_at[date]__raw",
    --     COALESCE(SUM("sale_price"), 0) AS "order_items.sale_price_sum",
    --     TO_CHAR(DATE_TRUNC('DAY', "created_at"), 'YYYY-MM-DD') AS "order_items.created_at[date]"
    -- FROM "order_items"
    -- GROUP BY 1
    -- ORDER BY 1 NULLS FIRST
    -- LIMIT 999

    SELECT 
        DATE_TRUNC('DAY', "date") AS "order_items.created_at[date]__raw",
        COALESCE(SUM(CAST("total_sale_price" AS DOUBLE PRECISION)), 0) AS "order_items.sale_price_sum",
        TO_CHAR(DATE_TRUNC('DAY', "date"), 'YYYY-MM-DD') AS "order_items.created_at[date]"
    FROM (  
      SELECT 
          date,
          total_sale_price,
          order_items_count,
          users_distinct_count
      FROM "daily_orders"
        ) AS "t"
    GROUP BY 1
    ORDER BY 1 NULLS FIRST
    LIMIT 999
    ```
  </Step>
</Steps>

## Troubleshooting

If you expect Omni to use the aggregated table but it's not, consider the following:

* **Missing fields**: Ensure that all the fields being queried are present in the aggregated table and correctly mapped in the `materialized_query` parameter.

* **Incompatible queries**: Certain queries, such as `count_distinct` over a different level of aggregation than the aggregate table, may not be compatible with aggregate awareness.

* **Cache**: If the query results are being retrieved from the cache, Omni may not need to rewrite the SQL.

  * Try clearing the cache to test if aggregate awareness is working as expected.
  * You can also test by setting the [`cache_policy`](/modeling/topics/parameters/cache-policy) to `0` so that it never uses cache.

<Check>
  If a query cannot be answered entirely by the `materialized_query`, aggregate awareness is not likely to be used on that query.
</Check>