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

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.omni.co/feedback

```json
{
  "path": "/modeling/views/parameters/materialized-query",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

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

Which would translate to the following SQL query when Omni builds the `user_facts` view:

```sql title="SQL for user_facts table" 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
```