materialized_query - Views

Configures aggregate awareness between two views. This parameter should be used on the aggregated view, which will allow Omni to match the aggregated table to the underlying views it aggregates.

Check out the Aggregate awareness guide for a comprehensive look at implementing aggregate awareness.

Syntax

The fields parameter accepts two formats: an object (recommended) or an array.

Object (recommended)
Array

materialized_query:
  fields:
    <view_name>.<field_name>: <aggregate_table_column_name>
    ...
  base_view: <view_name>

materialized_query:
  fields: [ <view_name>.<field_name>, ... ]
  base_view: <view_name>

Properties

object | array

required

Maps field names from the base table to columns in the aggregated view, specified as view_name.field_name.

Object format (recommended): Each key is a field from the base view and each value is the corresponding column name in the aggregate table. This format is more explicit and easier to maintain.
Array format: A positional array of field names that correspond to columns in the aggregated view in the order they appear in the view’s sql statement.
When using the array format, field order matters! Make sure the fields listed match the column order in the view’s sql statement.

Time dimensions must include a timeframe qualifier that matches the grain of the aggregate table, specified as view_name.field_name[timeframe] — for example, order_items.created_at[month] for a monthly table. In the object format, quote the key, because the brackets are otherwise not valid YAML.When the base view is used in a topic with joins to other tables, you must include the join keys to those dimension tables in the fields mapping for aggregate awareness to work across those joins. Without the join keys, Omni cannot optimize queries that involve joins.

string

required

The name of the base view that this aggregated view summarizes.

Examples

Mapping fields to an aggregate table

In this example, you have an order_items table that contains a few metrics. Using aggregate awareness, you can create a user_facts table that rolls up these metrics. Let’s take a look at how the fields in the underlying views will map to the fields in the aggregated user_facts view:

Underlying view fields		Aggregated `user_facts` fields
`users.id`	becomes	`user_id`
`order_items.created_at[month]`	becomes	`month`
`order_items.count`	becomes	`count`
`order_items.total_sale_price`	becomes	`total_sale_price`

To create this view in Omni, the user_facts.view file would look like this:

Object (recommended)
Array

user_facts.view file

name: user_facts
sql: |
  SELECT user_id, month, count, total_sale_price
  FROM user_facts
dimensions:
  user_id: {}
  month: {}
  count: {}
  total_sale_price: {}
materialized_query:
  fields:
    users.id: user_id
    "order_items.created_at[month]": month
    order_items.count: count
    order_items.total_sale_price: total_sale_price
  base_view: order_items

user_facts.view file

name: user_facts
sql: |
  SELECT user_id, month, count, total_sale_price
  FROM user_facts
dimensions:
  user_id: {}
  month: {}
  count: {}
  total_sale_price: {}
materialized_query:
  fields:
    [
      users.id,
      "order_items.created_at[month]",
      order_items.count,
      order_items.total_sale_price
    ]
  base_view: order_items

Notice that users.id is included in the fields mapping. This is the join key between order_items and users, which enables aggregate awareness to work across joins in topics. When a query joins these tables, Omni can use the user_facts materialized view and perform the join against the pre-aggregated table rather than the full order_items table. Without aggregate awareness, a query requesting these fields would join the underlying tables and aggregate at query time:

Query without aggregate awareness

SELECT users.id as user_id,
       order_items.created_at[month] as month,
       COUNT(order_items.id) as count,
       SUM(order_items.sale_price) as total_sale_price
FROM order_items
JOIN users ON users.id = order_items.user_id
GROUP BY 1,2

With materialized_query configured, Omni recognizes that user_facts already contains these results and rewrites the query to hit the aggregate table directly, eliminating the join:

Query with aggregate awareness

SELECT user_id, month, count, total_sale_price
FROM user_facts

Using sketch-based approximate aggregates

You can also materialize and map sketch-based approximate aggregates, such as HyperLogLog (HLL). A sketch is a compact binary summary of a column that Omni can roll up from a finer grain to a coarser one. Sketches need two measures in the base view. In this Snowflake example, the base order_items view builds the sketch in one measure and derives the estimate in another:

order_items.view file

measures:
  # Builds the sketch. Hidden because sketches are binary and unreadable in results.
  user_sketch:
    sql: HLL_ACCUMULATE(${user_id})
    hidden: true

  # Derives the estimate from the sketch. This is the measure users query.
  user_approx_distinct_count:
    sql: HLL_ESTIMATE(${user_sketch})

The aggregated view maps the sketch measure to the sketch column:

daily_user_sketches.view file

name: daily_user_sketches
sql: |
  SELECT date, user_sketch
  FROM daily_user_sketches
dimensions:
  date: {}
  user_sketch: {}
materialized_query:
  fields:
    "order_items.created_at[date]": date
    order_items.user_sketch: user_sketch
  base_view: order_items

The sketch column is a stored value in the aggregate table, so declare it as a dimension in the aggregated view even though it maps to a measure in the base view. When you query order_items.user_approx_distinct_count, Omni routes the query to the materialized sketch in daily_user_sketches instead of the base table.

Map the sketch measure, not the estimate measure. If one measure contains the full expression, such as HLL_ESTIMATE(HLL_ACCUMULATE(${user_id})), and you map that measure in fields, Omni can’t route the query.

The Aggregate awareness guide covers the supported sketch functions for Snowflake, BigQuery, and Databricks, and when a Snowflake sketch needs HLL_IMPORT.

Getting started

Manage content

Analyze & build

Visualize & present

Deliver content

Model data

Manage integrations

materialized_query - Views

Syntax

Properties

Examples

Mapping fields to an aggregate table

Using sketch-based approximate aggregates

​Syntax

​Properties

​Examples

​Mapping fields to an aggregate table

​Using sketch-based approximate aggregates

Syntax

Properties

Examples

Mapping fields to an aggregate table

Using sketch-based approximate aggregates