# Fan-out and how Holistics resolves it

> Holistics resolves fan-out automatically. This page explains the mechanism, contrasts it with symmetric aggregates, and shows how to fix raw-SQL measures that fall outside the guarantee.

**Holistics resolves fan-out automatically.** Normally, a one-to-many join can duplicate rows and inflate your aggregates. However, in Holistics, we detect this risk from the relationship cardinality you already declared and then rewrites the query so every entity counts exactly once. There is nothing to configure: measures defined in AQL and aggregations in the exploration UI are all **fan-out-safe** by default.

## What fan-out does to your numbers

Fan-out happens when a join duplicates rows on the "one" side of a **one-to-many** relationship. Aggregates then count those duplicates as if they were real data.

Take an ecommerce dataset where one `users` record relates to many `orders` records. Ask a natural question: how many users do we have, grouped by order status? A naive query joins the two models, then aggregates:

```sql
SELECT
  orders.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY 1
```

<ReactFlowDiagram id="fanout-users-orders" height={420} fixed />

The join repeats each user once per order they placed. A user with five delivered orders adds five to the "delivered" count instead of one. At real data volumes, the inflated numbers still look plausible. That is what makes fan-out one of the most dangerous silent errors in analytics.

For how Holistics generates joins from relationships in general, see [how joins work](/docs/joins/how-joins-work).

## How the industry handles fan-out: symmetric aggregates

Most join-based BI tools run the fanned-out join anyway, then repair the aggregate with arithmetic. The technique is known as [symmetric aggregates](https://docs.cloud.google.com/looker/docs/best-practices/understanding-symmetric-aggregates). It hashes each row's primary key into an enormous number and adds the measure value on top. It then sums the distinct results and subtracts the hashes back out:

```sql
SUM(DISTINCT hash(pk) * 1000000 + value) - SUM(DISTINCT hash(pk) * 1000000)
```

Why does this work? The duplicated rows of one entity all produce the same `hash + value`, so `DISTINCT` collapses them to one. Two entities that share a value still stay separate, because their hashes differ. Subtract the hash sum, and only the true measure total remains.

The math is clever, but everything around it carries a cost:

- Every fanned-out aggregate computes a hash (typically MD5) per row, then runs big-decimal `SUM DISTINCT` arithmetic on the results.
- The generated SQL becomes a wall of nested hash, cast, and coalesce calls. No human can realistically read, debug, or hand-optimize it.
- The hash and the measure share one `DECIMAL(38,0)` budget. Measure values beyond roughly 14 digits overflow and fail the query.
- Correctness rests on the primary key being declared correctly. If it isn't unique in reality, the query still runs and returns wrong numbers, with no warning.

## How Holistics resolves fan-out instead

Holistics doesn't repair the fan-out after the fact. It never creates one. Two pieces of information make that possible, and you have already provided both:

1. **Relationship cardinality.** Every relationship in your dataset declares which side is "one" and which is "many". That tells Holistics exactly when a join path could duplicate rows.
2. **The measure's aggregation structure.** [AQL measures](/reference/aml/field#measure) and aggregations in the exploration UI all declare what they aggregate. Holistics can read that structure directly.

With both in hand, Holistics restructures the query so each entity contributes exactly once. It aggregates at the measure's native grain first. It reduces the "many" side to distinct (join key, dimension) pairs. Then it joins the two results back together. For the users-by-order-status question above, the generated query becomes:

```sql
WITH user_status_pairs AS (
  SELECT user_id, status
  FROM orders
  GROUP BY 1, 2
)
SELECT
  s.status,
  COUNT(users.id) AS total_users
FROM users
LEFT JOIN user_status_pairs s ON users.id = s.user_id
GROUP BY 1
```

Here is the same rewrite with the data from the example above:

<ReactFlowDiagram id="fanout-resolved-users-orders" height={420} fixed />

Each user now counts once per status they actually have orders in, not once per order. The exact rewrite varies with the query shape, but the principle is always the same: deduplicate before aggregating, never after.

Because correctness comes from the query shape rather than hash arithmetic, the trade-offs of symmetric aggregates disappear:

- No per-row hashing and no decimal overflow, because there are no hashes.
- The generated SQL stays readable. If you ever want to verify a number, open the query tab and follow the rewrite step by step.
- There is nothing to configure per query or per explore. The resolution falls out of the relationships you already declared.

## Why do you still see the fan-out error?

Holistics resolves fan-out automatically. So why does this error still appear?

It appears because your query uses a **SQL-defined measure**. Holistics can't see what a raw SQL snippet aggregates, so it can't guarantee a correct result across the join. Instead of returning wrong data, it throws this error to make you aware.

:::info When you'll see this error
Only custom measures written in raw SQL trigger it. Measures defined in AQL and aggregations added in the exploration UI are resolved automatically.
:::

### The fix: define the measure in AQL

Rewriting the measure in AQL makes its aggregation structure explicit. That puts it back inside the guarantee.

For a simple aggregation, declare the measure with an AQL definition:

```aml
Model users {
  // ...
  measure total_users {
    label: 'Total Users'
    type: 'number'
    definition: @aql count(users.id) ;;
  }
}
```

Composite measures work the same way, because AQL expressions are built from aggregations Holistics understands. For example, a delivery rate that divides one conditional count by another:

```aml
Model orders {
  // ...
  measure delivery_rate {
    label: 'Delivery Rate'
    type: 'number'
    definition: @aql safe_divide(
      count(case(when: orders.status == 'delivered', then: orders.id)),
      count(orders.id)
    ) ;;
  }
}
```

Both measures combine freely with fields from related models. Holistics applies fan-out resolution wherever the join path needs it.