Optimizing query performance with aggregate awareness

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 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, 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

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 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:

order_items table

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:

Pre-aggregated table definition

dimensions:
  date: {}
  total_sale_price: {}
  order_items_count: {}
  user_distinct_count: {}

Add a materialized_query parameter

In Omni’s model IDE, add a materialized_query parameter to the optimized view to enable Omni to match the aggregate table to the underlying base views:

Pre-aggregated table daily_sales

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, 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 for an example.

Verify the setup

Verify that aggregate awareness is working by inspecting the generated SQL using Omni’s SQL inspector. The inspector will show whether Omni is querying the aggregate table or the granular table.

Query rewritten to use the aggregate table

-- 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:

Define a sketch measure in the base 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.

order_items view

measures:
  user_sketch:
    sql: HLL_ACCUMULATE(${user_id})
    hidden: true

Define an estimate measure that reads the sketch

Add a second measure for the value your users query. This measure must reference the sketch measure, not the raw column.

order_items view

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.

Materialize the sketch in an aggregate table

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.

daily_user_sketches aggregate table

SELECT
  DATE_TRUNC('DAY', created_at) AS date,
  HLL_ACCUMULATE(user_id) AS user_sketch
FROM order_items
GROUP BY 1

Map the sketch measure in materialized_query

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.

daily_user_sketches view

materialized_query:
  fields:
    "order_items.created_at[date]": date
    order_items.user_sketch: user_sketch
  base_view: order_items

Verify the setup

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.

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:

daily_user_sketches view

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 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 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 parameter
Define a cache_policy
Bring Omni in sync with your database with schema refreshes
Use the Workbook inspector to debug your queries

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

Optimizing query performance with aggregate awareness

Requirements

How it works

Setting up aggregate awareness

Build an aggregate or rollup table

Add a materialized_query parameter

Verify the setup

Using sketch-based approximate aggregates

Supported sketch functions

Setting up sketch-based aggregate awareness

Define a sketch measure in the base view

Define an estimate measure that reads the sketch

Materialize the sketch in an aggregate table

Map the sketch measure in materialized_query

Verify the setup

Importing Snowflake sketches

Troubleshooting

Next steps

​Requirements

​How it works

​Setting up aggregate awareness

Build an aggregate or rollup table

Add a materialized_query parameter

Verify the setup

​Using sketch-based approximate aggregates

​Supported sketch functions

​Setting up sketch-based aggregate awareness

Define a sketch measure in the base view

Define an estimate measure that reads the sketch

Materialize the sketch in an aggregate table

Map the sketch measure in materialized_query

Verify the setup

​Importing Snowflake sketches

​Troubleshooting

​Next steps

Requirements

How it works

Setting up aggregate awareness

Using sketch-based approximate aggregates

Supported sketch functions

Setting up sketch-based aggregate awareness

Importing Snowflake sketches

Troubleshooting

Next steps