> ## 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. # Optimizing query performance with aggregate awareness > Optimize query performance by routing queries to pre-aggregated tables in your data warehouse. Aggregate awareness lets you optimize query performance by routing queries to pre-aggregated tables in your data warehouse. End users can access data at different levels of granularity, as Omni will pick the most efficient table based on the query's structure. ## Requirements To follow the steps in this guide, you'll need **Querier**, **Modeler**, or **Connection Admin** permissions. ## How it works You build pre-aggregated (or rollup) tables in your preferred transformation tool, such as dbt, and register them with a [`materialized_query`](/modeling/views/parameters/materialized-query) parameter on the aggregate's view. This gives you full control over how the aggregation is defined and refreshed. At query runtime, Omni inspects the requested fields and rewrites the SQL to use the aggregate table when it contains everything the query needs. Because the aggregate is smaller than the base table, the rewritten query runs faster and uses fewer warehouse resources. The rewrite is automatic, meaning end users don't need to choose which table to query. Omni uses the aggregate: * **For single-table queries** — All queried dimensions and measures are mapped in the aggregate's `materialized_query.fields`. * **For joined queries within a topic** — All queried fields from the base view are mapped in the aggregate's `materialized_query.fields`, *and* the aggregate also includes the join keys to the other views. Omni then joins the aggregate to the dimension views instead of the base table. If the aggregate is missing any required field, including a join key, Omni falls back to the base tables. Omni can also roll up [sketch-based approximate aggregates](#using-sketch-based-approximate-aggregates), such as HyperLogLog distinct counts, from the grain stored in the aggregate table to the grain of the query. ## Setting up aggregate awareness Once you've identified the query you'd like to optimize: 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. 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 order_items table wrap theme={null} dimensions: 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 defined the following materialized table, which has pre-aggregated daily metrics: ```yaml Pre-aggregated table definition wrap theme={null} dimensions: date: {} total_sale_price: {} order_items_count: {} user_distinct_count: {} ``` In Omni's 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 base views: ```yaml Pre-aggregated table daily_sales wrap theme={null} materialized_query: fields: "order_items.created_at[date]": 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 ``` 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. Time dimensions need a timeframe qualifier that matches the grain of the aggregate table, such as `created_at[date]` for a daily table. Quote the key, because the brackets are otherwise not valid YAML. To apply aggregate awareness across joins in a topic, include the join keys to the other views in `fields`. See the [`materialized_query` reference](/modeling/views/parameters/materialized-query#examples) for an example. Verify that aggregate awareness is working by inspecting the generated SQL using [Omni's SQL inspector](/analyze-explore/workbook-inspector). The inspector will show whether Omni is querying the aggregate table or the granular table. ```sql title="Query rewritten to use the aggregate table" 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, user_distinct_count FROM "daily_sales" ) AS "t" GROUP BY 1 ORDER BY 1 NULLS FIRST LIMIT 999 ``` ## Using sketch-based approximate aggregates Aggregate awareness also works with sketch-based approximate aggregates. A sketch is a compact binary summary of a column. Warehouses use sketches to calculate approximate results, such as distinct counts with HyperLogLog (HLL), approximate percentiles, and top-K values. Sketches are additive, which is what makes them work with aggregate awareness. Omni can combine the daily sketches stored in an aggregate table into a weekly or monthly result, so queries at a coarser grain don't need the base table. This is the workaround for `count_distinct`, which can't be re-aggregated from pre-aggregated values. These functions return estimates, not exact values. Use them where a small margin of error is an acceptable trade for faster queries. ### Supported sketch functions Use a function from the **Sketch function** column in your sketch measure, and the matching **Estimate function** in your estimate measure. Omni applies the rollup function on its own when it combines sketches. | Warehouse | Sketch function | Rollup function | Estimate function | | - | - | - | - | | Snowflake | `HLL_ACCUMULATE` or `HLL_COMBINE` | `HLL_COMBINE` | `HLL_ESTIMATE` | | Snowflake | `APPROX_PERCENTILE_ACCUMULATE` or `APPROX_PERCENTILE_COMBINE` | `APPROX_PERCENTILE_COMBINE` | `APPROX_PERCENTILE_ESTIMATE` | | Snowflake | `APPROX_TOP_K_ACCUMULATE` or `APPROX_TOP_K_COMBINE` | `APPROX_TOP_K_COMBINE` | `APPROX_TOP_K_ESTIMATE` | | Snowflake | `MINHASH` or `MINHASH_COMBINE` | `MINHASH_COMBINE` | `APPROXIMATE_SIMILARITY` \* | | BigQuery | `HLL_COUNT.INIT` or `HLL_COUNT.MERGE_PARTIAL` | `HLL_COUNT.MERGE_PARTIAL` | `HLL_COUNT.EXTRACT` | | Databricks | `HLL_SKETCH_AGG` or `HLL_UNION_AGG` | `HLL_UNION_AGG` | `HLL_SKETCH_ESTIMATE` | \* `APPROXIMATE_SIMILARITY` compares two MINHASH sketches instead of estimating a single value. On warehouses that aren't listed here, Omni can't roll up sketches and falls back to the base tables. Don't use BigQuery's `HLL_COUNT.MERGE` in a sketch measure. That function returns a final estimate, which Omni can't merge again. Use `HLL_COUNT.INIT` or `HLL_COUNT.MERGE_PARTIAL` instead. ### Setting up sketch-based aggregate awareness A sketch needs two measures in the base view and one mapping in the aggregate view: In Snowflake, build the sketch with `HLL_ACCUMULATE`, which accepts a raw column. Use `HLL_COMBINE` only if the base table already stores a sketch column, because it accepts sketches rather than raw values. A sketch is binary data and isn't readable in query results, so set `hidden: true` to keep the measure out of the field picker. ```yaml title="order_items view" wrap theme={null} measures: user_sketch: sql: HLL_ACCUMULATE(${user_id}) hidden: true ``` Add a second measure for the value your users query. This measure must reference the sketch measure, not the raw column. ```yaml title="order_items view" wrap theme={null} measures: user_approx_distinct_count: sql: HLL_ESTIMATE(${user_sketch}) ``` Keep the sketch and the estimate in two separate measures. If you write the full expression in one measure, such as `HLL_ESTIMATE(HLL_ACCUMULATE(${user_id}))`, and then map that measure in `materialized_query`, Omni can't route the query. Omni routes the query only when the aggregate table stores the sketch and a separate estimate measure derives the value from it. In your transformation layer, build a table that calculates the sketch in advance. Aggregate at the finest grain you plan to query. This example builds a daily sketch, which Omni can roll up to weeks or months. ```sql title="daily_user_sketches aggregate table" wrap theme={null} SELECT DATE_TRUNC('DAY', created_at) AS date, HLL_ACCUMULATE(user_id) AS user_sketch FROM order_items GROUP BY 1 ``` In the aggregate view's definition, map the sketch measure in the `materialized_query.fields` parameter. Don't map the estimate measure — Omni derives that from the sketch at query time. ```yaml title="daily_user_sketches view" wrap theme={null} materialized_query: fields: "order_items.created_at[date]": date order_items.user_sketch: user_sketch base_view: order_items ``` Query `order_items.user_approx_distinct_count` and check the generated SQL in the workbook. Omni reads the aggregate table and wraps the stored sketch in the rollup and estimate functions, as in `HLL_ESTIMATE(HLL_COMBINE(user_sketch))`. If the SQL reads from `order_items` instead, Omni didn't route the query. See [Troubleshooting](#troubleshooting). ### Importing Snowflake sketches If your aggregate table stores sketches in Snowflake's object format — for example, sketches written by `HLL_EXPORT` or built outside Omni — the rollup functions can't read them. Add `HLL_IMPORT` to the aggregate view's `sql` statement to convert them to the binary format: ```yaml title="daily_user_sketches view" wrap theme={null} name: daily_user_sketches sql: | SELECT date, HLL_IMPORT(user_sketch) AS user_sketch FROM daily_user_sketches ``` Sketches built with `HLL_ACCUMULATE`, as in the steps above, are already binary and don't need `HLL_IMPORT`. ## 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. * **Missing join keys**: If your query joins the base view to other tables in a topic, the materialized view must include the join keys to those tables. Without the join keys, Omni cannot optimize queries that involve joins. * **Incompatible queries**: `count_distinct` over a different level of aggregation than the aggregate table cannot use aggregate awareness, because the count cannot be re-aggregated from the pre-aggregated values. To get an approximate distinct count that can be re-aggregated, use a [sketch measure](#using-sketch-based-approximate-aggregates) instead. * **Sketch measures**: If a sketch isn't routing, check that the sketch and the estimate are defined as two separate measures, and that `materialized_query` maps the sketch measure rather than the estimate measure. * **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. If a query cannot be answered entirely by the `materialized_query`, aggregate awareness is not likely to be used on that query. ## Next steps * Learn more about the [`materialized_query`](/modeling/views/parameters/materialized-query) parameter * Define a [`cache_policy`](/modeling/topics/parameters/cache-policy) * Bring Omni in sync with your database with [schema refreshes](/modeling/develop/schema-refreshes) * Use the [Workbook inspector](/analyze-explore/workbook-inspector) to debug your queries This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.