ExamFinch.com My Study

Study guide

3.4 Schema Definition & Evolution for Loaded Data

Practice this exam

Google Cloud Associate Data Practitioner Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

About the Associate Data Practitioner Exam

2 Chapter 1: Data Processing Paradigms & Ingestion Strategies

1.1 ETL, ELT, and ETLT Transformation Paradigms 1.2 Data Transfer and Migration Tools 1.3 Data Quality, Cleaning, and Validation

3 Chapter 2: Data Formats & Storage Solution Selection

2.1 File Formats & Serialization Characteristics 2.2 Cloud Storage Buckets, Location Types & Storage Classes 2.3 Selecting Google Cloud Databases and Storage Services

4 Chapter 3: Loading Data & Modern Ingestion Architectures

3.1 Batch Loading Data into BigQuery 3.2 Streaming Ingestion & Deduplication Patterns 3.3 BigQuery External Tables, BigLake & Federated Queries 3.4 Schema Definition & Evolution for Loaded Data

5 Chapter 4: BigQuery SQL Analytics & Query Foundations

4.1 BigQuery SQL Syntax & Analytical Functions 4.2 Complex Data Types: Arrays, Structs, and JSON 4.3 Answering Business Questions with BigQuery: Worked Analyses

6 Chapter 5: BigQuery Performance Optimization & Cost Governance

5.1 Table Partitioning Strategies 5.2 Table Clustering & Data Skipping 5.3 Query Optimization, Execution Plans, and Cost Controls

7 Chapter 6: Interactive Analysis, Notebooks & Visual Presentation

6.1 Exploratory Analysis with Jupyter & Colab Enterprise 6.2 Looker Studio (Data Studio) Reporting & Dashboard Design 6.3 Enterprise BI with Looker & LookML Foundations 6.4 Building, Modifying & Sharing Looker Dashboards

8 Chapter 7: Machine Learning with BigQuery ML & Vertex AI

7.1 Planning an ML Project: BigQuery ML, AutoML, or Pretrained Models? 7.2 Creating, Training & Evaluating Models with BigQuery ML 7.3 Running Inference and Organizing Models in Model Registry 7.4 Using Pretrained Gemini Models from BigQuery with Remote Connections

9 Chapter 8: Pipeline Engines & Transformation Services

8.1 Transformation Engine Selection Matrix 8.2 Apache Beam & Cloud Dataflow Core Concepts 8.3 Stream Processing: Windowing, Watermarks, and Triggers

10 Chapter 9: Workflow Orchestration, Automation & Monitoring

9.1 SQL Pipelines with Dataform & Scheduled Queries 9.2 Workflow Orchestration with Cloud Composer & Workflows 9.3 Event-Driven Ingestion: Pub/Sub to BigQuery & Eventarc Triggers 9.4 Monitoring Pipelines: Dataflow Job UI, Cloud Logging & Cloud Monitoring

11 Chapter 10: Access Control, Governance & Data Sharing

10.1 IAM Principles & Role Hierarchies for Data Services 10.2 Cloud Storage Security & Access Controls 10.3 Fine-Grained Security: Column-Level & Row-Level Controls 10.4 Secure Data Sharing with Analytics Hub (BigQuery Sharing)

12 Chapter 11: Lifecycle Management, Reliability & Compliance

11.1 Object Lifecycle Management & Storage Tiering 11.2 High Availability & Disaster Recovery Architectures 11.3 Data Retention, Time Travel, and Point-in-Time Recovery 11.4 Encryption Key Management & Privacy Compliance

3.4 Schema Definition & Evolution for Loaded Data

Key Takeaways Explain This Topic AI

Avro, Parquet, and ORC files carry their own schema; CSV and JSON loads should use a version-controlled JSON schema file rather than autodetect, which samples only up to 500 rows.

BigQuery column modes are NULLABLE (default), REQUIRED, and REPEATED; a RECORD in REPEATED mode stores an array of structs such as order line items.

Appends that fail because the source gained a column are fixed with the ALLOW_FIELD_ADDITION schema update option, not by rebuilding the table.

Adding a field inside an existing RECORD requires a JSON schema file with bq update or the API; SQL DDL and the console cannot add nested fields.

You can relax REQUIRED to NULLABLE, but BigQuery cannot tighten an existing NULLABLE column to REQUIRED.

Last updated: October 2026

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

Approach How it works Best fit Risk

Self-describing files Avro, Parquet, ORC, and Firestore/Datastore exports carry their own schema; BigQuery reads it Most production loads Upstream schema changes arrive automatically, so pair with schema update options

JSON schema file A file listing each column's name , type , mode , and optional description CSV and JSON loads that need exact types The file must be kept in version control and updated with the source

Inline schema --schema=name:STRING,age:INT64 on the bq command line Quick, flat tables Cannot set descriptions, modes, or nested RECORD columns

Autodetect BigQuery picks a file, samples up to the first 500 rows, and infers names and types Exploration and one-off loads Wrong guesses (for example, a ZIP code inferred as INT64 loses leading zeros)

  • 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:

  • Source structure BigQuery result
  • JSON nested object, Avro record, Parquet group RECORD / STRUCT column
  • JSON array, Avro array, Parquet repeated field REPEATED column ( ARRAY )
  • Array of objects REPEATED RECORD ( ARRAY<STRUCT<...>> )
  • CSV Flat columns only; CSV cannot carry nested data

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:

Change Supported? How

Add a new NULLABLE top-level column Yes ALTER TABLE ... ADD COLUMN , bq update with a schema file, or a load/query job with ALLOW_FIELD_ADDITION

Add a field inside an existing RECORD Yes, but not with SQL DDL or the console bq update with a JSON schema file (new field appended to the RECORD's fields ), or the API's tables.patch

Relax REQUIRED to NULLABLE Yes ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL , or ALLOW_FIELD_RELAXATION on a load/query job

  • Rename a top-level column Yes ALTER TABLE ... RENAME COLUMN old TO new
  • Drop a column Yes ALTER TABLE ... DROP COLUMN (a metadata change; storage is not rewritten immediately)

Change a data type Only some widening conversions ALTER COLUMN ... SET DATA TYPE (for example INT64 to NUMERIC); otherwise add a new column and backfill, or recreate the table

Tighten NULLABLE to REQUIRED No Recreate the table with the new schema

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.

  • Criterion Typed STRUCT/ARRAY columns Native JSON column
  • Schema enforcement At load time (schema-on-write) At query time (schema-on-read)
  • New source fields Need a schema update Stored immediately
  • Query performance Best Good, slightly slower
  • Typical use Core facts and dimensions Webhooks, logs, vendor payloads

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.