Flattening an array increases totals unexpectedly

Illustrative lab: an order has two items, but a report sums the order-level amount after expanding the items. This is a learning case, not a reported customer incident.

Observable symptoms

The order total appears once per item and an aggregate overstates revenue.

Likely cause

The query changes grain from order to item while retaining an order-grain measure.

Diagnosis steps

1. Count rows before and after FLATTEN.
2. State the intended output grain explicitly.
3. Separate item measures from order measures before aggregation.

Example

WITH orders AS (
  SELECT 1 AS order_id, 100 AS order_total,
         PARSE_JSON('["pen","book"]') AS items
)
SELECT o.order_id, o.order_total, f.value::STRING AS item
FROM orders o, LATERAL FLATTEN(input => o.items) f;

Expected result

Two rows for order 1, each with ORDER_TOTAL 100; ITEM is pen or book. Row order is not guaranteed.

Code walkthrough

• The CTE creates one order and a two-element JSON array.
• LATERAL FLATTEN emits one row per array element.
• The order-level amount repeats on each emitted row, explaining why a naive sum returns 200.

Solution and boundaries

Aggregate order totals from the order-grain relation. Expand items only for item-level analysis, or join correctly aggregated results at the same grain. SUM(DISTINCT order_total) is not a general fix because different orders can share the same amount.

Prevention and monitoring

Document grain in models and test counts and totals after one-to-many joins.

Interview question

Why can SUM(DISTINCT amount) hide one duplication bug and create another?

Official reference · Related technical guide