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

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

  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

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