BigQuery Schema Evolution

Managing Schema Evolution Without Losing Data

Source systems change: a new column gets added, a field gets renamed, a data type gets widened. If your pipeline isn't built to handle that gracefully, a schema change upstream can silently break a dashboard downstream — or worse, silently drop data without throwing any error at all.

📘 What "Schema Evolution" Means

Schema evolution is the practice of letting your tables and pipelines adapt to structural changes in source data over time, without requiring a full rebuild or causing data loss. The three changes you'll deal with most: adding a column, renaming a column, and changing a data type.

🧪 Handling New Columns in BigQuery

BigQuery supports schema auto-detection and relaxation for many load jobs, but for controlled pipelines it's safer to be explicit:

ALTER TABLE analytics.orders
ADD COLUMN IF NOT EXISTS discount_code STRING;

Adding a column is the safe, additive case — existing queries keep working because they don't reference the new column, and old rows simply get NULL for it.

⚠️ The Dangerous Cases

1. Renamed columns

A rename at the source (e.g., cust_id → customer_id) looks to your pipeline like the old column disappeared and a new, unrelated one appeared. If your pipeline maps columns by name, this silently produces a column full of NULLs instead of erroring — which is far more dangerous than a hard failure, because nothing alerts you.

-- Defensive pattern: alias explicitly instead of relying on name matching
SELECT
  COALESCE(cust_id, customer_id) AS customer_id
FROM staging.raw_orders;

2. Type changes

A source column changing from INT to STRING (common when an ID field starts including letters) will break strict-typed downstream tables. Widening a type (e.g., INT32 → INT64) is usually safe; narrowing or changing type family is not.

🛠️ Practical Strategies

1. Always land raw data first, untyped or loosely typed, before transforming into strict analytics tables (this is one more reason ELT beats rigid ETL for evolving sources).
2. Version your transformation logic so you can trace which pipeline version processed which data when a schema change is discovered.
3. Add schema validation as an explicit pipeline step, not an afterthought — fail loudly on unexpected changes rather than silently coercing or dropping data.
4. Use a schema registry (e.g., with Kafka/Debezium pipelines) so producers and consumers agree on structure and version changes are tracked centrally.

🔍 A Simple Schema-Diff Check

SELECT column_name, data_type
FROM `dataset`.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'raw_orders'
ORDER BY ordinal_position;

Running this before and after a source system deploy, and diffing the output, is a simple but effective way to catch upstream changes before they cause downstream damage.

⚠️ Common Mistakes

• Relying purely on schema auto-detection for production pipelines instead of explicit validation
• Silently coalescing renamed columns without alerting anyone that the rename happened
• Not keeping raw/untyped data, making it impossible to reprocess once a schema issue is found

🔗 Related Posts

• Change Data Capture (CDC): Real-Life Use Cases and Pitfalls
• ETL vs ELT: Choosing the Right Strategy for Your Workflow