After flattening a repeated field in BigQuery, why does SUM(o.order_total) come back inflated?
answer
- the cross join copies the parent columns
- the inflation factor varies per row
- ask what one output row now represents
- aggregate the array without changing grain
- DISTINCT on a measure is a false friend
basics
~20 sFlattening repeats each parent column once per array element, so a parent-level measure is summed once per child and multiplied by the array length. Aggregate the array in a scalar subquery instead, keeping the query at one row per parent.
solid answer
~50 s`UNNEST` correlated to a row is a cross join: an order with four line items becomes four rows, each carrying the same `order_total`. Summing that column adds it four times, so the total is inflated by the per-order array length — not by a constant factor, which is why the number looks plausible rather than obviously wrong. The clean fix is not to flatten at all when the measure lives on the parent: compute array-level measures with a scalar subquery, `(SELECT SUM(i.qty) FROM UNNEST(o.items) AS i)`, so the query stays at one row per order and `SUM(order_total)` is correct. If you must flatten, aggregate the parent and the children in two separate queries and join the results, or pre-aggregate the array to one row per parent. `SUM(DISTINCT order_total)` is not a fix — it collapses two orders that genuinely share the same total.
go deeper
Recall that flattening a repeated field repeats the parent's columns once per element, so summing a parent column after a flatten counts it many times.
Explain the cross-join arithmetic behind the inflation and show the scalar-subquery form that computes array measures without changing the grain of the result.
Diagnose it in a real dashboard: compare row counts across the flatten, identify which measures live at which grain, and know why SUM(DISTINCT) and SELECT DISTINCT are wrong repairs.
Own the prevention — declared grain and uniqueness assertions on every model built over nested sources, plus certified aggregate tables so analysts are not hand-writing flattens against raw event exports.
## The mechanism Flattening a repeated field multiplies rows. Given a table `orders(order_id, order_total, items ARRAY<STRUCT<sku STRING, qty INT64>>)`, the query ```sql SELECT SUM(o.order_total) AS revenue, SUM(item.qty) AS units FROM orders AS o, UNNEST(o.items) AS item; ``` produces one row per line item. Every one of those rows carries a full copy of `order_total`, because a cross join repeats the left side's columns. `SUM(order_total)` therefore adds each order's total once per line item. `SUM(item.qty)` is correct — it is a child-level measure at child granularity — but `revenue` is inflated by each order's array length. The reason this survives code review is that the inflation factor is not constant. If every order had exactly two lines you would notice a doubling. Instead you get a number that is, say, 3.4× the truth, varying month to month with basket size. Dashboards drift, nobody can date the regression, and the query looks like ordinary SQL. This is the exact dual of the empty-array trap. Flattening loses parents with zero children and duplicates parents with many. Both come from the same cross join. ## Fix one: don't change granularity The best answer in BigQuery is usually to leave the query at one row per parent and aggregate the array in place with a scalar subquery over `UNNEST`: ```sql SELECT SUM(o.order_total) AS revenue, SUM((SELECT SUM(i.qty) FROM UNNEST(o.items) AS i)) AS units, SUM(ARRAY_LENGTH(o.items)) AS line_count FROM orders AS o; ``` No row multiplication happens, so `revenue` is right and the child measures are still available. `ARRAY_LENGTH`, `EXISTS (SELECT 1 FROM UNNEST(...))` and `ARRAY(SELECT ... FROM UNNEST(...) WHERE ...)` cover most of what people reach for a flatten to do. The engine is perfectly happy with these — the array is already materialised in the row, so evaluating a subquery over it does not add scanned bytes. ## Fix two: aggregate to one row per parent before joining When the query genuinely needs both granularities and the array logic is complex, pre-aggregate: ```sql WITH per_order AS ( SELECT o.order_id, o.order_total, SUM(item.qty) AS units FROM orders AS o, UNNEST(o.items) AS item GROUP BY o.order_id, o.order_total ) SELECT SUM(order_total) AS revenue, SUM(units) AS units FROM per_order; ``` The inner query is at line-item granularity, the `GROUP BY` collapses it back to one row per order, and the outer aggregate is then safe. Note this variant also inherits the empty-array loss: orders with no lines are gone unless the inner query uses `LEFT JOIN UNNEST`. ## Fix three: two queries, one join Compute the parent measures and the child measures independently and join on the key. This is the most verbose form but the easiest to review, and it is what you want when several different repeated fields are involved — flattening two arrays in the same `FROM` clause produces the *product* of their lengths, which inflates everything including the child measures of the other array. ## What is not a fix - `SUM(DISTINCT o.order_total)` deduplicates *values*, not rows. Two different orders with the same total collapse into one. The result is wrong in a new and harder-to-detect direction. - `SUM(o.order_total) / COUNT(*)`-style corrections only work if every parent has the same array length. - A window function such as `SUM(order_total) OVER ()` computed after the flatten sees the same duplicated rows and is inflated identically. - Wrapping the flattened result in `SELECT DISTINCT` before aggregating appears to work until two orders share every selected column. ## How to catch it Compare the row count before and after flattening: `SELECT COUNT(*) FROM orders` versus `SELECT COUNT(*) FROM orders, UNNEST(items)`. Any difference means the grain changed, and every parent-level measure downstream of that point is suspect. In a transformation pipeline, assert the grain — a uniqueness test on the model's declared key catches an accidental fan-out on the first run, which is why dbt-style unique-key tests are the standard defence for nested BigQuery sources. ## The reviewing heuristic When you see `UNNEST` in a `FROM` clause, ask what the grain of the result is and which measures live at that grain. Anything from the parent must either be aggregated with a grain-restoring `GROUP BY` first, or not be aggregated in that query at all.
- Why is SUM(DISTINCT order_total) a dangerous fix for this inflation?It deduplicates values rather than rows. Two genuinely different orders that happen to have the same total collapse into one contribution, so revenue is now understated instead of overstated — and the error depends on how often totals coincide, which grows as volume grows. Restore the grain with a GROUP BY on the parent key, or avoid the flatten entirely.
- What happens if a query flattens two different repeated fields in the same FROM clause?You get the Cartesian product of the two arrays per parent row: an order with 4 items and 3 shipments yields 12 rows. Now both child measures are inflated as well as the parent one. Flatten one array per query and join the pre-aggregated results, or aggregate each array with its own scalar subquery.
- Does avoiding the flatten reduce the bytes BigQuery bills for the query?No. Billing follows the leaf columns referenced, and both formulations reference the same ones. The scalar-subquery form is about correctness and grain, not cost. It can reduce slot time by avoiding a large intermediate row set, but the on-demand bytes-scanned figure is essentially unchanged.
saying these in an interview costs you the question
- Adds SELECT DISTINCT before aggregating and calls it fixed
- Uses SUM(DISTINCT measure) to remove duplicate parent rows
- Believes the inflation is a constant factor you can divide out
- Thinks a window function computed after the flatten is unaffected
- Flattens two repeated fields in one FROM without noticing the product