Skip to main content

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.

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

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

​
Properties

​
fields
array
required
An array of field names from the base table that correspond to columns in the aggregated view, specified as view_name.field_name.
Field order matters! Make sure the fields listed here match the column order in the view’s sql statement.
​
base_view
string
required
The name of the base view that this aggregated view summarizes.

​
Examples

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 fieldsAggregated user_facts fields
users.idbecomesuser_id
order_items.created_at[month]becomesmonth
order_items.count becomescount
order_items.total_sale_pricebecomestotal_sale_price
To create this view in Omni, the user_facts.view file would look like this:
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
Which would translate to the following SQL query when Omni builds the user_facts view:
SQL for user_facts table
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