How to Update BigQuery Schemas With Nested Fields

  • 4/11/2022
  • One-minute read

Producers changed a nested payload and you must add columns to a BigQuery table without rewriting historical data.

Step-by-Step

export GCP_PROJECT=my-project
export BQ_TABLE=myDataset.myTable

gcloud config set project "$GCP_PROJECT"

# Export current schema
bq show --format=prettyjson --schema "$BQ_TABLE" > schema.json

# Edit schema.json: add new fields with "mode": "NULLABLE" or append to nested STRUCT definitions.

# Apply update (schema only)
bq update "$BQ_TABLE" schema.json

Rules to Remember

  • BigQuery allows additive changes only: you can add NULLABLE or REPEATED fields, but cannot remove or change types in-place.
  • Nested fields appear inside the fields array of a STRUCT—ensure you maintain ordering and commas when editing JSON.
  • For breaking changes, create a new table, backfill via CREATE TABLE ... AS SELECT, then swap references.

Tips

  • Keep schema files in source control; peer-review changes before running bq update.
  • For frequent schema evolution, consider using Dataform or Terraform to manage table definitions declaratively.
  • Test with a staging table before touching production datasets.
gcpbigqueryclouddatatutorial