Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 37 ยท Nested and Repeated Data

Nested and Repeated Schemas in Practice

Design BigQuery tables with nested and repeated fields: when to denormalize, a JSON schema for orders with items, loading JSON and Avro, querying patterns, trade-offs and common mistakes.

The previous two lessons gave you the pieces: ARRAY and STRUCT. Now we put them together into a schema design. In traditional databases you normalise: one table per entity, joined by keys. In BigQuery the recommended starting point for analytical data is often the opposite: denormalise by nesting child records inside the parent row. This lesson shows when that is a good idea, how to define such a schema, and how to query it.

Why denormalise in a warehouse?

Joins are the expensive part of a distributed query, because data must be shuffled between workers. A nested table stores an order and its items together, so reading them needs no shuffle. Storage is cheap and columnar, so the "repeated" parent values do not cost much.

flowchart LR
  subgraph Normalised["Normalised: 3 tables, 2 joins"]
    C1["customers"] --- O1["orders"] --- I1["order_items"]
  end
  subgraph Nested["Nested: 1 table, 0 joins"]
    N1["orders: customer STRUCT, items ARRAY of STRUCT"]
  end

The nested design trades some update flexibility for faster, cheaper reads.

Use nesting when:

  • The children belong to exactly one parent and are almost always read with it (items in an order, events in a session, line items in an invoice).
  • The data arrives as JSON, Avro or Protobuf and is already nested.
  • Queries mostly aggregate, filter and read, rather than update individual children.

Keep separate tables when:

  • Entities are shared and change independently (a product's name should not be copied into every order).
  • You need to update children often.
  • Several different parents refer to the same child, and each needs its own analysis.

A hybrid is common: keep dimension tables (customers, products) normal and nest facts inside their natural parent.

A concrete schema

Here is an orders table designed with nesting, written as a BigQuery DDL (read-only; it needs a real project):

SQL
CREATE TABLE `my-project.shop.orders_nested` (
  order_id    INT64 NOT NULL,
  ordered_at  TIMESTAMP NOT NULL,
  status      STRING,
  customer    STRUCT<
                id        INT64,
                email     STRING,
                country   STRING,
                address   STRUCT<city STRING, postcode STRING>
              >,
  items       ARRAY<STRUCT<
                product_id  INT64,
                name        STRING,
                quantity    INT64,
                unit_price  NUMERIC
              >>
)
PARTITION BY DATE(ordered_at);

Equivalently, as a JSON schema file for bq mk or bq load (shortened):

JSON
[
  {"name": "order_id", "type": "INTEGER", "mode": "REQUIRED"},
  {"name": "ordered_at", "type": "TIMESTAMP", "mode": "REQUIRED"},
  {"name": "customer", "type": "RECORD", "mode": "NULLABLE", "fields": [
    {"name": "id", "type": "INTEGER"},
    {"name": "country", "type": "STRING"}
  ]},
  {"name": "items", "type": "RECORD", "mode": "REPEATED", "fields": [
    {"name": "product_id", "type": "INTEGER"},
    {"name": "quantity", "type": "INTEGER"},
    {"name": "unit_price", "type": "NUMERIC"}
  ]}
]

In the JSON schema vocabulary a STRUCT is a RECORD, and an ARRAY is a field with mode REPEATED. The two descriptions are interchangeable.

Loading nested data

Newline-delimited JSON maps directly to nested fields. One line is one order:

JSON
{"order_id": 1, "ordered_at": "2025-03-01 10:15:00", "status": "shipped",
 "customer": {"id": 7, "country": "DE", "address": {"city": "Berlin", "postcode": "10115"}},
 "items": [{"product_id": 3, "quantity": 2, "unit_price": "9.99"},
           {"product_id": 8, "quantity": 1, "unit_price": "24.00"}]}

(On one physical line in the real file.) Avro and Parquet files with nested records load the same way and carry their own schema, so they are the safer choice. CSV cannot represent nesting.

Building a nested table from flat ones

If your source is flat, build the nested table with a query:

SQL
CREATE OR REPLACE TABLE `my-project.shop.orders_nested` AS
SELECT
  o.id AS order_id,
  o.ordered_at,
  STRUCT(c.id AS id, c.email AS email, c.country AS country) AS customer,
  ARRAY_AGG(STRUCT(i.product_id, i.quantity, i.unit_price)) AS items
FROM `my-project.shop.orders` AS o
JOIN `my-project.shop.customers` AS c ON c.id = o.customer_id
JOIN `my-project.shop.order_items` AS i ON i.order_id = o.id
GROUP BY o.id, o.ordered_at, c.id, c.email, c.country;

Joins happen once, when you build the table, and every later query benefits. The stand-in version builds the nested shape as JSON text and checks that order totals still match the flat tables:

SQL Stand-in: build nested orders and check totals ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The two totals match, which is the check you should always run after a denormalising rebuild.

Querying patterns

Goal BigQuery pattern
One row per item CROSS JOIN UNNEST(items) AS item
Keep orders with no items LEFT JOIN UNNEST(items) AS item
Order total without fan-out (SELECT SUM(i.quantity * i.unit_price) FROM UNNEST(items) AS i)
Count items ARRAY_LENGTH(items)
Filter orders containing product 3 EXISTS (SELECT 1 FROM UNNEST(items) AS i WHERE i.product_id = 3)
Rebuild an array with a change ARRAY(SELECT AS STRUCT i.* REPLACE (...) FROM UNNEST(items) AS i)

Fan-out appears when you UNNEST and then aggregate parent columns. This stand-in shows the same trap on nested data, and how aggregating inside the row avoids it:

SQL Stand-in: parent columns after unnesting are repeated ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The count of rows after unnesting equals the item count: any parent value summed here would be repeated that many times.

Trade-offs and mistakes

  • Updating a nested child means rewriting the array. Design for append-and-rebuild.
  • Over-nesting: more than a few levels makes queries hard to read. Prefer one or two levels.
  • Giant arrays: a row has a size limit (about 100 MB), so do not nest unbounded lists such as "all events ever of a user".
  • Copying changing data: if a customer's email changes, old orders keep the old copy. That is often what you want for history, but know it.
  • Forgetting the partition: nested tables are still big tables. Partition by date.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does denormalising with nested fields often make BigQuery queries cheaper and faster?

    Parent and children are in one row, so reading them together needs no join shuffle.
  2. 2. In a JSON schema file, how is an ARRAY of STRUCT described?

    A STRUCT is a RECORD, and repeated mode makes it an array.
  3. 3. Which format cannot represent nested data?

    CSV is flat. Use JSON, Avro or Parquet for nested loads.
  4. 4. When should you NOT nest children inside a parent row?

    Shared, frequently changing entities belong in their own tables.

Summary

  • Nest children inside parents (arrays of structs) to avoid joins for read-heavy analytics.
  • Keep shared dimensions in separate tables; nest facts within their parent.
  • Define schemas with STRUCT/ARRAY DDL or JSON RECORD/REPEATED fields.
  • Build nested tables once with ARRAY_AGG(STRUCT(...)) and verify totals.
  • Mind fan-out, array size limits and update cost.

Next: JSON and Array Aggregation Patterns