When would you keep BigQuery child data nested in ARRAY<STRUCT> rather than splitting it into a separate table?
answer
- ask what the dominant query joins
- children that never change are the easy case
- a single element edit rewrites the whole row
- some collections have no natural bound
- consumers still expect flat rectangles
basics
~20 sNest when children are always read with their parent and never independently: the join disappears, the row stays atomic, and leaf columns are still pruned. Split them out when children are updated individually, queried on their own, or unbounded in number.
solid answer
~50 sNesting keeps the parent and its children in one row, so the most common query — parent plus children — needs no join and no shuffle, and writes of a parent and its children are atomic. Because each leaf of the repeated record is still stored as its own column, referencing one field of the array does not read the rest, so nesting costs nothing in scan volume. The prices are real, though. Any DML touching one element rewrites the whole parent row including the entire array, so high-churn children are a poor fit. A field inside a repeated record cannot be the table's partitioning column. Consumers must understand flattening, and BI tools and generic SQL clients often need a flattened view. Unbounded arrays make individual rows enormous and skew processing. The rule of thumb: nest bounded, co-accessed, write-once children; keep independently queried or frequently updated children in their own table.
go deeper
Know that BigQuery lets one row carry its child records as a repeated field, which removes the join that a separate child table would require.
Explain why nesting costs nothing in scan volume — each leaf is its own column — and why updating a single element still rewrites the whole parent row.
Weigh the real production tradeoffs: churn rate of children, whether children are queried independently, partitioning constraints, and the flattening mistakes analysts will make against the table.
Own the platform consequence — which shape is the source of truth, what flattened surfaces you publish for BI and for child-centric workloads, and what grain assertions keep derived models honest.
## The decision, framed BigQuery is unusual among warehouses in making denormalisation into the row a first-class schema option. `ARRAY<STRUCT<...>>` lets a parent carry its children inline, which is why exported event data — analytics sessions with hit arrays, ad platform exports, application event streams — arrives that way. The design question is when to keep it and when to normalise into parent and child tables. ## What nesting buys **The join disappears.** A parent-with-children query on a flat schema needs a join, and a large-to-large join means redistributing both sides across the workers. Nested, the children are already co-located with their parent: the engine reads one column set and the relationship is structural. For the dominant access pattern of "give me orders with their lines," this removes the most expensive operator in the plan. **Atomicity of the write.** A parent and its children land in one row, so there is no window where an order exists without its lines, and no orphan-child cleanup. For streamed events this is a real operational simplification. **No scan penalty.** This is the point candidates most often miss. Nesting does *not* mean you read the whole array to touch one field. Each leaf of the repeated record is stored as its own column, so a query referencing `items.sku` reads the SKU leaf and nothing else. Nesting is cheap precisely because the storage layer stays columnar underneath it. **Grain is unambiguous.** One row is one order. Counting orders is `COUNT(*)`, with no risk of a fan-out inflating parent-level measures — as long as nobody flattens. ## What nesting costs **Update granularity.** BigQuery has no way to modify a single array element in place. Changing one line item means a `MERGE` or `UPDATE` that rewrites the parent row, array and all. If children mutate frequently and independently — status transitions, per-item fulfilment updates — the write amplification is severe and the child table is the better shape. **Partitioning constraints.** A field inside a repeated record cannot serve as the table's partitioning column. If the natural time dimension of the children differs from the parent's, nesting takes away your ability to partition on it, and every query pays the full parent scan. **Query difficulty for consumers.** Flattening is a skill. Analysts hitting nested tables produce exactly the two defects this dialect is famous for: dropping parents with empty arrays via a cross join, and inflating parent measures via fan-out. Every nested table you publish is an ongoing training and review burden. **Tool compatibility.** Many BI tools, JDBC/ODBC consumers and downstream systems expect flat rectangles. In practice you end up publishing flattened views alongside the nested table, which means maintaining two surfaces. **Unbounded growth.** An array with no natural bound — every event a user has ever produced, appended forever — makes individual rows enormous. Processing skews toward whichever worker holds the fat rows, and the read-modify-write cost of appending grows with the history. Bounded collections (order lines, session hits, address list) are safe; unbounded ones are not. **Independent access.** If a substantial workload queries children without reference to parents — "all line items for SKU X across all orders" — nesting forces every such query through the parent table. That can still be fine when the leaf columns prune well, but if the children carry their own filters and clustering needs, they deserve their own table. ## A practical rule Nest when *all* of these hold: the children are bounded in count, they are effectively immutable once written, they are almost always read together with the parent, and their filters are the parent's filters. Break them out when any of those fails — particularly independent mutation and independent querying. Hybrids are legitimate and common. Keep the nested table as the raw, atomic record of what happened, and publish a flattened child table (or a materialised flattened view) for the workloads that want children as first-class rows. You pay storage twice, which in a warehouse is usually the cheapest of the resources in play, and you get the right shape for each workload. ## What to say in the interview Lead with the access pattern, not with the type system. State the dominant query, state whether children mutate, state whether the collection is bounded. Then name the join and shuffle you save, the rewrite cost you take on, and the consumer burden you are creating — and say how you would mitigate it, usually with a certified flattened view and grain assertions in the pipeline.
- Does nesting children make queries that touch only one child field read more data?No. Each leaf of the repeated record is stored as its own column, so referencing one field of the array reads only that leaf. That is the key property that makes nesting cheap in BigQuery: the row-level grouping is logical, while the physical layout remains columnar per leaf field.
- How do you handle a workload that mostly reads parents but occasionally needs children as first-class rows?Keep the nested table as the atomic source of truth and publish a flattened child table or a materialised flattened view for the child-centric workload. Storage is the cheap resource; the win is that each workload queries the shape that suits it, and analysts never hand-write the flatten that drops empty arrays or inflates parent measures.
- What makes a repeated field a bad choice when the children change often?There is no in-place edit of a single array element. Any change rewrites the whole parent row including the entire array, so a high-churn collection produces write amplification proportional to array size on every update. Children with independent lifecycles — status transitions, fulfilment updates — belong in their own table keyed to the parent.
saying these in an interview costs you the question
- Claims nesting always beats a join with no conditions attached
- Thinks touching one array field reads the entire array
- Nests an unbounded, ever-growing collection of events
- Ignores that a single element update rewrites the parent row
- Publishes deeply nested tables to BI tools with no flattened view