The table and the job both matter
A BigQuery schema change is an agreement between existing data, new input, and consuming queries. Adding a field to a producer does not automatically authorize every load job to evolve its destination table. Conversely, a table update can succeed while downstream code still assumes a different shape or interprets new null values incorrectly.
For an existing table, ordinary added columns must be nullable or repeated; adding a new required top-level column is not a compatible way to describe rows that already lack that value. Relaxing a required field to nullable and adding a field are also different operations. Supported load or query jobs can request explicit schema-update behavior, and the relevant write mode affects what is allowed.
Prepare a test dataset, a copy of the actual job configuration, representative historical rows, and the consuming query inventory. Obtain metadata and job-detail access. Identify whether the pipeline uses batch loads, query append, or another ingestion interface; do not transfer options from one API to another without checking support.
Review an intended evolution
- Read
bq show --schema --format=prettyjson PROJECT_ID:DATASET.TABLE. Compare names, types, modes, nesting, and ordering with the incoming schema. Preserve this current definition in the change record. A producer's generated schema is not evidence of the destination's actual state. - Inspect the failed or proposed job's destination, write disposition, source format, and schema-update options. For the supported append path, distinguish
ALLOW_FIELD_ADDITIONfromALLOW_FIELD_RELAXATION. Review each separately rather than enabling every option to bypass an unexplained error. - Reproduce the change in a test table containing older rows. Load representative new rows with the same job settings used by production. Verify null behavior for old records, nested field shape, and any consumer that uses positional or exhaustive field assumptions.
- Apply the reviewed migration and producer rollout in a compatible order. Where a required business value is needed, introduce a nullable field, populate and validate it, then enforce the business rule in the appropriate supported layer. Do not describe a storage-schema constraint as a substitute for business validation.
Illustrative scenario
A fictional order pipeline adds fulfillment_region to incoming files. The existing destination has historical orders without that value, while the load job appends with no field-addition option. The team first defines the new field as nullable, tests the approved schema evolution path, and updates reports to handle unknown historical regions. Making the field required would turn a manageable transition into a compatibility failure.
| Error or symptom | Inspect | Corrective direction |
|---|---|---|
| New field rejected on append | Job options and destination schema | Use the intended supported addition path |
| Required-field addition fails | Mode and existing rows | Design a compatible nullable transition |
| Load succeeds, report breaks | Consumer field assumptions | Update and test downstream queries |
| Nested data differs from expectation | Full record schema and source format | Correct the nested contract explicitly |
Verify data, not only job status
Inspect the resulting schema and query known old and new records. Check row counts against the input manifest and review rejected-row handling. Validate that new values have the intended meaning and are not silently null because a source field was misspelled. Keep a test query that expresses the business acceptance rule without scanning unrelated datasets.
Operating procedure
Version schema changes with pipeline configuration and consumer updates. Monitor failed loads, unexpected null rates, and destination-schema drift. Require an owner for every new field and document whether historical values are absent, backfilled, or derived. Rehearse nested-field changes and ingestion-interface upgrades before applying them to the production table.
Explore Clouvenix cloud architecture for implementation and operational handover.