> ## 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": "/analyze-explore/sql/symmetric-aggregates",
  "feedback": "Description of the issue"
}
```

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

</AgentInstructions>

# Handling fan out with symmetric aggregates

> Use symmetric aggregates to ensure correct aggregation results in queries with table joins and fan outs, without manual adjustments.

Ensuring the correctness of data is every analyst's top priority. Symmetric aggregates allow you to ensure aggregations are always correct without needing to be aware of table relationships and fan outs.

## Understanding table fan out

To demonstrate why symmetric aggregates are so useful, let's start with explaining what table fan out means. Fan out occurs when a table containing metrics joins to a dimension table in a way that results in multiple matching rows, leading to inflated results during aggregation.

This example uses two tables - `customers` and `orders`.

Each row in the `customers` table represents a unique customer:

| customer\_id | name           | loyalty\_points |
| :----------- | :------------- | :-------------- |
| 1            | Bloberta Smith | 100             |
| 2            | Blobzilla Lee  | 200             |
| 3            | Blobina Chen   | 150             |

And each row in the `orders` table represents a unique order placed by a customer:

| order\_id | customer\_id | order\_date | amount |
| :-------- | :----------- | :---------- | :----- |
| 101       | 1            | 2025-03-25  | \$50   |
| 102       | 1            | 2025-03-26  | \$30   |
| 103       | 2            | 2025-03-27  | \$80   |
| 104       | 3            | 2025-03-28  | \$20   |
| 105       | 3            | 2025-03-29  | \$40   |

Because **one** customer can have **many** orders, this [relationship](/modeling/relationships) is `one-to-many`.

The fan out of data occurs when the tables are joined together. The following table demonstrates what a joined table might look like - note that the customer's `loyalty_points` are repeated for each order:

| customer\_id | name           | loyalty\_points | order\_id | amount |
| :----------- | :------------- | :-------------- | :-------- | :----- |
| 1            | Bloberta Smith | 100             | 101       | \$50   |
| 1            | Bloberta Smith | 100             | 102       | \$30   |
| 2            | Blobzilla Lee  | 200             | 103       | \$80   |
| 3            | Blobina Chen   | 150             | 104       | \$20   |
| 3            | Blobina Chen   | 150             | 104       | \$20   |
| 3            | Blobina Chen   | 150             | 105       | \$40   |

## Handling fan out from joins

If you were to sum the total `loyalty_points` for each customer from the join table, you'd end up overstating Bloberta's and Blobina's points by `x` the number of orders. To prevent incorrect results like these, it's important to declare a [`primary_key`](/modeling/dimensions/parameters/primary-key) for tables and, when defining joins, a [`relationship_type`](/modeling/relationships/parameters/relationship-type).

Before Omni calculates an aggregation on a fanned out table, Omni will check the table's primary key - the unique key per row - to ensure values aren't double-counted in the aggregation. If Omni detects potential for fan out, it will prompt you to define primary keys within your views.

To handle the example from the previous section, you could define the following in the model:

```yaml title="customers table" theme={null}
customer_id:
  primary_key: true
```

```yaml title="orders table" theme={null}
order_id:
  primary_key: true
```

```yaml title="Relationship between customers and orders" theme={null}
- join_from_view: customers
  join_to_view: orders
  join_type: always_left
  relationship_type: one_to_many
  on_sql: ${customers.customer_id} = ${orders.customer_id}
```

## Handling fan out in materialized tables

Let’s say you pushed the logic to join customers and orders down into your transformations layer. As this is now a single materialized table, Omni no longer has the context of the relationship between the underlying models.

In this scenario, you can leverage the [`aggregate_type`](/modeling/measures/parameters/aggregate-type) and the `custom_primary_key_sql` parameters to tell Omni how to properly aggregate on nested fields:

```yaml title="Symmetric aggregates on a materialized table" theme={null}
dimensions:
  order_id:
    primary_key: true # Defines the table's primary key
  customer_id: {}
  loyalty_points: {}
  amount: {}

measures:
  loyalty_points_sum:
    sql: ${loyalty_points}
    aggregate_type: sum_distinct_on
    custom_primary_key_sql: ${customer_id} # Dedupe on customer_id
  amount_sum:
    sql: ${amount}
    aggregate_type: sum
```

## Handling fan out from unnested columns

While similar to the [join scenario](/analyze-explore/sql/symmetric-aggregates#handling-fan-out-from-joins), this example performs an [unnest join](https://community.omni.co/t/how-can-i-unnest-my-json-data/51) on an array instead of joining two tables together. For example, this could occur when you have an `orders` table and an array of objects that represent the individual order items.

You can leverage the [`custom_primary_key_sql_for_quick_aggs`](/modeling/dimensions/parameters/custom-primary-key-sql-for-quick-aggs) parameter to handle this scenario:

```yaml title="Symmetric aggregates on an unnested column" theme={null}
dimensions:
  order_id:
    primary_key: true # Defines the table's primary key
  order_items: {} # Repeated field
  order_item_price:
    parent_field: order_items
    nested_on_field: order_items
    custom_primary_key_sql_for_quick_aggs: ${order_item_id}
    sql: price

measures:
  total_price:
    sql: ${order_item_price}
    aggregate_type: sum_distinct_on
    custom_primary_key_sql: ${order_item_id}
```

Quick aggregates for which a primary key is applicable - such as `SUM` but not `MIN` or `MAX` - based on the `order_item_price` dimension will be assigned a custom primary key of `order_item_id`. In this example, that will apply to the `total_price` measure.