Join Fanout and Symmetric Aggregates: Why Your Looker Numbers Do Not Reconcile

Someone in finance opens a dashboard, sums Order Revenue, and gets a number 30% higher than the one in the accounting system. The SQL looks fine. The join looks fine. Nothing is broken — the explore is fanning out, and the measure is counting the same order row several times.

This tutorial covers join fanout: what causes it, how Looker's symmetric aggregates protect you, when they stop protecting you, and how to structure explores so the numbers are right the first time. It matters more in 2026 than it used to, because natural-language tools built on the semantic layer (Conversational Analytics, Gemini in Looker, an MCP client) will happily aggregate any measure in any explore without a human noticing that the join grain changed.

What fanout actually is

Fanout happens whenever a join multiplies rows. If one order has three order items, then joining orders to order_items produces three rows per order:

order_id	order_amount	item_id	item_price
1001	300	1	100
1001	300	2	150
1001	300	3	50

A naive SUM(orders.order_amount) over that result returns 900, not 300. The item-level measures are fine — SUM(order_items.item_price) is 300 — but every measure defined on the one side of a one-to-many join is now inflated.

Two rules to keep in your head:

• Measures on the many side of a join are safe.
• Measures on the one side are duplicated by the fanout factor.

And fanout compounds: join orders to order_items and also to order_shipments, and you get a cartesian product of items × shipments per order. Item-level measures are now wrong too.

Symmetric aggregates: Looker's safety net

Looker handles the simple case for you. When it detects that a measure sits on the one side of a one-to-many join, it rewrites the aggregation as a symmetric aggregate instead of a plain SUM. In BigQuery the generated SQL looks roughly like this:

( COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(orders.order_amount,0)*(1000000*1.0)) AS NUMERIC)) +
  CAST(FARM_FINGERPRINT(CAST(orders.id AS STRING)) AS NUMERIC) * 1000000000000000000 )
  - SUM(DISTINCT CAST(FARM_FINGERPRINT(CAST(orders.id AS STRING)) AS NUMERIC) * 1000000000000000000)
  ) AS FLOAT64) ,0) / (1000000*1.0) )

Ugly, but the idea is simple: hash the primary key, add it to the scaled value, SUM(DISTINCT ...) so duplicate rows collapse, then subtract the hashes back out. Each order contributes once no matter how many item rows it fanned into.

This is why Looker is strict about primary keys. Symmetric aggregates only work if the join grain is knowable:

view: orders {
  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${order_amount} ;;
    value_format_name: usd
  }
}

If primary_key is missing or is not actually unique, symmetric aggregates either fail loudly or — worse — silently produce wrong results. Missing or wrong primary keys are the single most common root cause behind "the dashboard number is wrong" tickets we see on audits.

Where symmetric aggregates stop helping

Symmetric aggregates are not universal. Know the gaps:

1. They only apply to type: sum, type: average, type: count_distinct, and their _distinct variants. type: count becomes COUNT(*) on the fanned-out result set unless it is counted against a primary key. Prefer:

measure: order_count {
  type: count_distinct
  sql: ${id} ;;
}

over a bare type: count on any view that can appear on the one side of a join. It costs nothing and it survives fanout.

2. type: number measures built from other measures are safe; type: number built from raw SQL is not.

# Safe: composes two already-symmetric measures
measure: avg_order_value {
  type: number
  sql: ${total_revenue} / NULLIF(${order_count},0) ;;
  value_format_name: usd
}

# Dangerous: raw aggregation, no symmetric protection
measure: bad_revenue {
  type: number
  sql: SUM(${TABLE}.order_amount) ;;
}

Any time you write SUM(, COUNT(, or AVG( by hand inside a type: number measure, you have opted out of the safety net. Always compose from typed measures instead.

3. Symmetric aggregates de-duplicate; they do not fix logic. MIN, MAX, MEDIAN, and percentile measures on the one side are not symmetric-aggregated at all. MIN/MAX happen to be duplicate-insensitive, so they survive. Medians and percentiles do not — they are computed over the fanned-out rows and are quietly skewed toward orders with many items.

4. Symmetric aggregates are expensive. They force SUM(DISTINCT) over a wide numeric expression, disable some warehouse optimizations, and can be a real cost line on BigQuery. If a heavily used explore leans on them for its headline metrics, that is a signal to fix the grain instead.

Declaring relationships honestly

Looker decides whether to apply symmetric aggregates from your relationship parameter. Getting it wrong is how you get wrong numbers.

explore: orders {
  join: order_items {
    type: left_outer
    relationship: one_to_many   # one order, many items
    sql_on: ${orders.id} = ${order_items.order_id} ;;
  }

  join: users {
    type: left_outer
    relationship: many_to_one   # many orders, one user
    sql_on: ${orders.user_id} = ${users.id} ;;
  }
}

• many_to_one and one_to_one — no fanout from this join, no symmetric aggregates needed.
• one_to_many and many_to_many — fanout; Looker protects measures on the parent side.

The default when you omit relationship is many_to_one. That default is silently wrong for the most common join people write, and Looker will not warn you. Set it explicitly on every join. Two more rules of thumb:

• A join on a date range, or on 1=1, is almost always many_to_many regardless of what you wish it were.
• If a join key is nullable on either side, verify the relationship against real data before trusting it.

Proving it, not guessing it

Never assume grain. Test it.

Check uniqueness of every primary key with a LookML data test that runs in CI:

test: orders_pk_is_unique {
  explore_source: orders {
    column: id {}
    column: row_count { field: orders.count }
    sorts: [row_count: desc]
    limit: 1
  }
  assert: pk_is_unique {
    expression: ${orders.count} = 1 ;;
  }
}

Check totals against a known-good source:

test: revenue_matches_ledger {
  explore_source: orders {
    column: total_revenue {}
    filters: [orders.created_date: "2026-01"]
  }
  assert: revenue_is_correct {
    expression: ${orders.total_revenue} = 4821994.55 ;;
  }
}

Wire both into spectacles or looker validate in your CI pipeline and fanout regressions stop reaching production.

Read the SQL. In any Explore, open the SQL tab. If you see the SUM(DISTINCT ... FARM_FINGERPRINT ...) pattern, symmetric aggregates are active — the number is protected but you are paying for it. If you see a plain SUM() on a view that is on the one side of a one_to_many join, stop and find out why.

Designing the fanout away

Symmetric aggregates are a rescue mechanism. Good explore design avoids needing them:

Pick one grain per explore and name it. An explore called order_items whose base view is order_items sets the expectation that every row is one item. Users who want order-grain analysis get an orders explore. Two explores over the same joins, each honest about its grain, beats one explore where half the measures are conditionally wrong.

Pre-aggregate the many side. If you only need a count and a sum from a child table, roll it up before joining:

view: order_item_rollup {
  derived_table: {
    sql: SELECT order_id,
                COUNT(*) AS item_count,
                SUM(item_price) AS item_revenue
         FROM order_items
         GROUP BY 1 ;;
    datagroup_trigger: nightly_etl
  }
  dimension: order_id { primary_key: yes hidden: yes type: number }
  measure: item_revenue { type: sum sql: ${TABLE}.item_revenue ;; }
}

Now the join is many_to_one, there is no fanout, and no symmetric aggregates are generated. On a large explore this alone can halve query cost.

Hide measures that are wrong in context. If a view is joined into an explore where its measures cannot be trusted, use a refinement to hide them rather than hoping nobody clicks:

include: "/views/shipments.view.lkml"

view: +shipments {
  measure: shipping_cost {
    hidden: yes   # duplicated by the item-level fanout in this explore
  }
}

Use fanout_on for unnested/repeated structures. For BigQuery ARRAY/STRUCT columns exposed through explore_source in a native derived table, tell Looker which joined view fans out so it aggregates correctly:

explore_source: orders {
  column: id {}
  column: item_revenue { field: order_items.item_revenue }
  derived_column: ...
}

Keep a note in the view description of any explore where the grain is not obvious. Your future self, and any AI agent querying through the semantic layer, will read it.

Why this matters more with AI on top

A human analyst who sees revenue triple usually notices. A natural-language interface does not. When Conversational Analytics or an MCP-connected agent picks fields, it trusts your relationship declarations, your primary keys, and your measure types absolutely. Every fanout hazard you leave in the model becomes an answer someone pastes into a board deck.

Practical hardening before you expose an explore to any AI surface:

1. Every view has a correct, verified primary_key.
2. Every join has an explicit relationship.
3. No type: number measure contains a raw aggregate function.
4. Every count is count_distinct on the primary key, or is on the base view.
5. Medians and percentiles are only exposed on explores where their view is the base or on the many side.
6. Data tests assert both key uniqueness and at least one headline total.

A quick triage checklist

When someone reports a wrong number, work this order — it resolves most cases within ten minutes:

1. Reproduce in an Explore with the same filters, then open the SQL tab.
2. Remove all fields from joined views. Does the number become correct? If yes, it is fanout.
3. Check the relationship on each join in the explore.
4. Check the primary_key on the view that owns the wrong measure — and verify it is actually unique.
5. Check the measure type. Is it a type: number with a hand-written aggregate?
6. Only then start suspecting the data.

Fanout is not an exotic edge case; it is the default behaviour of relational joins, and Looker's symmetric aggregates hide it well enough that models drift for years before anyone checks. Declare your grain, verify your keys, test your totals, and the number in the dashboard matches the number in the ledger.

If your Looker model has a trust problem — numbers that do not reconcile, measures nobody is sure about, or explores you would not want an AI agent querying — get in touch. Vistelio's Looker consultants do exactly this kind of model audit and remediation.