skip to content

In Looker, why does a summed order amount inflate after joining order items?

level: middleimportance: must knowfreq 55%

answer

  1. the join changed how many rows exist
  2. each base row now appears several times
  3. the tool needs a way to tell copies apart
  4. hand-written aggregates skip the protection

basics

~20 s

Joining a one-to-many table fans out the base rows, so a plain SUM adds each order's amount once per matching item. Looker normally corrects this with symmetric aggregates, but only when the view declares a primary key and the measure uses a typed aggregate.

solid answer

~40 s

Joining `orders` to `order_items` repeats each order row once per item, so any naive `SUM(orders.amount)` counts the same amount several times — the classic fan-out. Looker's defence is **symmetric aggregates**: for typed measures such as `type: sum`, `type: average` and `type: count`, it rewrites the aggregate so duplicated rows are counted once, using the view's declared primary key to identify them. That protection has preconditions. The view must declare `primary_key: yes` on a genuinely unique dimension, the join's `relationship:` must describe reality, and the measure must be a typed aggregate — a `type: number` measure containing a hand-written `SUM(...)` bypasses symmetric aggregates entirely and will double-count. When numbers inflate, check those three things before blaming the data.

code

text · 8 lines
text
-- one order, three items: the join repeats the amount
orders.id | orders.amount | order_items.id
1         | 100.00        | 11
1         | 100.00        | 12
1         | 100.00        | 13

naive   SUM(orders.amount) = 300.00
correct de-duplicated sum  = 100.00

go deeper

for a junior

Recognise the symptom: after joining a table with many child rows, totals from the parent table are multiplied. Know that the join duplicating rows is the cause, not bad source data.

for a middle

Explain symmetric aggregates and their three preconditions — a real declared primary key, a typed aggregate rather than hand-written SQL, and an accurate relationship on every join in the path.

for a senior

Diagnose from the generated SQL, spot the silent undercount from a non-unique declared primary key, and decide when to solve the grain problem by modeling rather than by relying on the rewrite.

for a principal

Own the grain policy: which explores exist, whether mixing order-level and item-level fields in one surface is allowed at all, and the query-cost tradeoff of de-duplicating aggregates at scale.

## The shape of the bug One order, three items. Join them and the base table's row is repeated three times: ``` orders (before join) id | amount 1 | 100.00 orders JOIN order_items (after join) orders.id | amount | item_id 1 | 100.00 | 11 1 | 100.00 | 12 1 | 100.00 | 13 ``` `SUM(orders.amount)` over that result is 300.00, not 100.00. This is fan-out, and it is not a Looker quirk — any SQL that joins a one-to-many table and then aggregates the *one* side has it. What is Looker-specific is that the tool tries to fix it for you, which is why Looker developers can go a long time without meeting fan-out and then get burned by the one case where the fix does not apply. ## Symmetric aggregates When Looker detects that a join can duplicate the rows a measure aggregates, it rewrites the aggregate into a form that is insensitive to duplication. Conceptually it uses the primary key of the measure's view to identify each distinct source row and arranges the arithmetic so a repeated row contributes once — a de-duplicating sum rather than a raw one. The exact generated SQL varies by database dialect, and you can see it in the SQL tab of an explore. The requirements are concrete: 1. **A declared primary key.** The measure's view must have exactly one dimension marked `primary_key: yes`, and it must actually be unique in that table. A primary key declared on a non-unique column produces silently wrong results — two rows sharing a key value collapse into one. 2. **A typed aggregate.** `type: sum`, `type: average`, `type: count`, `type: count_distinct` and friends go through Looker's aggregate generation, so they can be rewritten. `type: number` with `sql: SUM(${TABLE}.amount) ;;` is opaque hand-written SQL — Looker passes it through and it double-counts. 3. **Honest join metadata.** `relationship:` on the join tells Looker whether fan-out is possible. `many_to_one` (the default when you omit it) means the joined side cannot duplicate the base rows, so no protection is applied. If the true relationship is `one_to_many` and you declared `many_to_one`, you have told Looker the fan-out cannot happen and it will not defend against it. ## Diagnosing an inflated number Work the checklist in order: - **Reproduce without the join.** Remove all fields from the fanning view. If the total is right, the join is the cause. - **Read the generated SQL.** Looker shows it per query. A plain `SUM(orders.amount)` where you expected symmetric aggregation is the confirmation. - **Check `primary_key:`** on the measure's view. Missing is the most common cause; declared-but-not-unique is the nastiest. - **Check the measure's type.** A hand-rolled aggregate inside `type: number` is the second most common cause. - **Check `relationship:`** on every join in the path, including intermediate ones. A many-to-many path through a bridge table can fan out even when each individual join looks safe. ## What symmetric aggregates do not do They are not a licence to ignore grain. A measure that is correct after de-duplication can still be the wrong number to show next to a fanned-out dimension: revenue per order shown on a row grained by order *item* is arithmetically correct but analytically confusing, because the reader assumes the number belongs to the item. Mixing grains in one visualization is a modeling decision, not something the tool can fix. They also do not extend across every dialect and aggregate type uniformly, and they cost something — the rewritten aggregate is more work for the warehouse than a plain SUM. On very large joined result sets that shows up in query time. ## The modeling alternative Sometimes the right answer is not to rely on the fix at all. If a report grained by item repeatedly needs order-level totals, consider modeling the order-level aggregate upstream, or exposing separate explores for the order grain and the item grain so users cannot mix them accidentally. That is the same discipline as any star-schema design: decide the grain of the fact you are reporting on, and do not silently mix two of them in one table. ## The interview version Say it in three beats: the join duplicates rows, Looker's symmetric aggregates de-duplicate typed aggregates using the declared primary key, and the protection lapses when the primary key is missing or wrong, the relationship is misdeclared, or the measure hand-writes its own SQL aggregate.

  • What happens if primary_key is declared on a column that is not actually unique?
    Symmetric aggregates trust that declaration. Two genuinely different rows sharing the key value are treated as duplicates of each other, so one is dropped from the aggregate and the total comes out too low — a silent undercount that is much harder to spot than an obvious inflation.
  • Why does declaring relationship: many_to_one on a truly one-to-many join cause wrong numbers?
    The `relationship:` parameter is how Looker decides whether fan-out is possible. Declaring `many_to_one` asserts the joined side cannot duplicate base rows, so Looker applies no protection and emits a plain aggregate over the duplicated result set.
  • Are symmetric aggregates free?
    No. The rewritten aggregate does more work than a plain SUM, and on large joined result sets it shows up in query time and warehouse cost. Where a grain is queried constantly, pre-aggregating upstream or splitting the explores is often the better answer.

Photocopying an invoice three times does not triple what the customer owes; the trick is recognising the copies, which is what a declared primary key lets Looker do.

saying these in an interview costs you the question

  • Blames duplicate data in the warehouse instead of the join
  • Writes type: number with SUM() and expects fan-out protection
  • Omits primary_key and assumes Looker infers it
  • Declares relationship as many_to_one to make an error go away
  • Thinks COUNT DISTINCT on any column fixes every fan-out

context

resolving links…