3.4 Schema Definition & Evolution for Loaded Data

3.4 Schema Definition & Evolution for Loaded Data

Core Focus: Every load job needs a schema, and every production table eventually needs a schema change. The exam tests whether you know where a schema comes from (self-describing files, a schema file, or autodetection), how nested and repeated data maps into BigQuery, and which schema changes BigQuery allows without rebuilding a table.

Section 3.1 covered how data gets into BigQuery. This section covers what shape it takes when it arrives and how that shape changes safely over time. Querying nested data with UNNEST and JSON functions is covered in Section 4.2.

---

1. Four Ways to Supply a Schema

A JSON schema file for an orders table with nested and repeated data looks like this:

[
  {"name": "order_id", "type": "STRING", "mode": "REQUIRED", "description": "Order UUID"},
  {"name": "customer_id", "type": "STRING", "mode": "NULLABLE"},
  {"name": "order_ts", "type": "TIMESTAMP", "mode": "REQUIRED"},
  {"name": "items", "type": "RECORD", "mode": "REPEATED", "fields": [
    {"name": "sku", "type": "STRING", "mode": "NULLABLE"},
    {"name": "quantity", "type": "INT64", "mode": "NULLABLE"},
    {"name": "unit_price", "type": "NUMERIC", "mode": "NULLABLE"}
  ]}
]

bq load --source_format=NEWLINE_DELIMITED_JSON \
  --schema=./schemas/orders.json \
  retail_dw.orders gs://landing/orders/2026-10-08/*.json

---

2. Column Modes and Nested Data

Every column has one of three modes:

• NULLABLE (the default): the value may be missing.
• REQUIRED: a missing value fails the load or insert.
• REPEATED: the column holds an array of values of one type.

A RECORD (shown as STRUCT in SQL) groups related fields, such as an address. A RECORD in REPEATED mode is an array of structs, such as the line items of an order. BigQuery maps source data as follows:

Nested and repeated fields let one row hold an entity with its children, which avoids large joins at query time. You can also define them with DDL. Note that clustering columns must be top-level, non-repeated columns, so keep a top-level copy of any key you want to cluster on:

CREATE TABLE `retail_dw.orders` (
  order_id STRING NOT NULL,
  customer_id STRING,                        -- top-level copy used for clustering
  order_ts TIMESTAMP NOT NULL,
  customer STRUCT<email STRING, loyalty_tier STRING>,
  items ARRAY<STRUCT<sku STRING, quantity INT64, unit_price NUMERIC>>
)
PARTITION BY DATE(order_ts)
CLUSTER BY customer_id;

---

3. Evolving a Schema Safely

Sources change. The question is which changes BigQuery accepts in place:

The most common exam scenario is an append that fails because the source gained a column. Instead of recreating the table, add a schema update option to the job:

# Avro files now contain a new optional field; keep appending without a rebuild
bq load --source_format=AVRO \
  --write_disposition=WRITE_APPEND \
  --schema_update_option=ALLOW_FIELD_ADDITION \
  retail_dw.orders gs://landing/orders/2026-10-09/*.avro

-- DDL alternatives for top-level changes
ALTER TABLE `retail_dw.orders` ADD COLUMN discount_code STRING;
ALTER TABLE `retail_dw.orders` RENAME COLUMN discount_code TO promo_code;
ALTER TABLE `retail_dw.orders` ALTER COLUMN order_ts DROP NOT NULL;

---

4. Semi-Structured Payloads: Typed Columns or the JSON Type?

When a payload's fields are unpredictable (third-party webhooks, polymorphic events), you can load it into a single column of BigQuery's native JSON type. BigQuery parses it at ingestion into an efficient internal format, and new keys need no schema change. When the structure is known and stable, typed STRUCT and ARRAY columns give stricter validation, better compression, and faster queries.

A common pattern combines both: typed columns for the fields you report on, plus a JSON column that keeps the full raw payload for later.

---

Common Exam Traps

• Autodetect in production: Autodetect samples a few hundred rows, so it can mistype identifiers or miss columns that appear later. Production loads should use self-describing formats or a version-controlled schema file.
• Rebuilding a table for a new column: If an append fails because the source added a field, use ALLOW_FIELD_ADDITION (or ADD COLUMN) rather than truncating and reloading history.
• Using DDL to add a nested field: ALTER TABLE ... ADD COLUMN adds top-level columns only. A new field inside a RECORD needs a JSON schema file with bq update or the API.
• Tightening modes: You can relax REQUIRED to NULLABLE, but you cannot change NULLABLE to REQUIRED on an existing table.