> ## 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.

# materialized_query - Views

> Configures aggregate awareness between two 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.

<Tip>
  Check out the [Aggregate awareness guide](/analyze-explore/performance/aggregate-awareness) for a comprehensive look at implementing aggregate awareness.
</Tip>

## Syntax

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

<Tabs>
  <Tab title="Object (recommended)">
    ```yaml theme={null}
    materialized_query:
      fields:
        <view_name>.<field_name>: <aggregate_table_column_name>
        ...
      base_view: <view_name>
    ```
  </Tab>

  <Tab title="Array">
    ```yaml theme={null}
    materialized_query:
      fields: [ <view_name>.<field_name>, ... ]
      base_view: <view_name>
    ```
  </Tab>
</Tabs>

## Properties

<ParamField path="fields" type="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.

      <Warning>
        When using the array format, field order matters! Make sure the fields listed match the column order in the view's `sql` statement.
      </Warning>

  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.
</ParamField>

<ParamField path="base_view" type="string" required>
  The name of the base view that this aggregated view summarizes.
</ParamField>

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

<Tabs>
  <Tab title="Object (recommended)">
    ```yaml title="user_facts.view file" wrap theme={null}
    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
    ```
  </Tab>

  <Tab title="Array">
    ```yaml title="user_facts.view file" wrap theme={null}
    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
    ```
  </Tab>
</Tabs>

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:

```sql title="Query without aggregate awareness" theme={null}
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:

```sql title="Query with aggregate awareness" theme={null}
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:

```yaml title="order_items.view file" wrap theme={null}
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:

```yaml title="daily_user_sketches.view file" wrap theme={null}
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.

<Warning>
  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.
</Warning>

The [Aggregate awareness guide](/analyze-explore/performance/aggregate-awareness#using-sketch-based-approximate-aggregates) covers the supported sketch functions for Snowflake, BigQuery, and Databricks, and [when a Snowflake sketch needs `HLL_IMPORT`](/analyze-explore/performance/aggregate-awareness#importing-snowflake-sketches).