Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Nested and repeated fields vs joins

Data modeling · Modern Modeling Approaches

Nested and repeated fields vs joins

Mediumdata-modeling-50
nested-repeatedbigquerydenormalizationperformance

Question

In BigQuery, when would you store order items as a nested repeated field instead of a separate table?

Solution

In BigQuery, store order items as nested and repeated record fields when child records are naturally hierarchical, created together, and almost always queried alongside the parent record. This keeps parent and child data co-located within the same physical storage blocks, completely avoiding expensive shuffle joins.

Nested repeated records in BigQuery

BigQuery uses Google Capacitor format to store nested arrays of structs directly inside a row:

CREATE TABLE orders (
  order_id STRING,
  order_date DATE,
  customer_id STRING,
  items ARRAY<STRUCT<
    product_id STRING,
    quantity INT64,
    unit_price NUMERIC
  >>
);

Querying this structure uses the UNNEST operator:

SELECT
  order_id,
  item.product_id,
  item.quantity * item.unit_price AS line_total
FROM orders,
UNNEST(items) AS item;

Because the line items reside physically inside the order row, BigQuery reads them without shuffling data across the network, saving compute slots and execution time.

When to normalize line items

Nested records become problematic when child records require frequent independent updates or stand on their own as primary analytical entities. Updating a single item in an array requires rewriting the entire parent row, which burns DML quotas and compute credits.

Choosing the right design

Use nested repeated fields for immutable transactional payloads, mobile event clickstreams, and invoice line items that finalize at checkout. Choose separate normalized tables when line items undergo independent lifecycle states (such as partial returns or delayed individual item shipments) or when analytics teams frequently query products without caring about the parent order.

PreviousNext