Which schema changes are backward compatible for downstream consumers?

Adding a nullable (or defaulted) column and widening a type, such as int to bigint. Renaming, dropping, narrowing or changing a type, or changing a column's meaning, are breaking.

What is an expand-and-contract schema migration?

Introducing the new form alongside the old (expand), writing both while consumers migrate, then removing the old form once nobody reads it (contract). It avoids breaking consumers during the change.

What does a schema registry do for event pipelines?

It stores versioned schemas for each topic and checks new versions against compatibility rules (backward, forward, full), rejecting producer changes that would break consumers.

Schema Evolution — ETL and Data Pipelines

Scenario

Example company: over one quarter, the orders service makes three changes: it adds a coupon_code column, changes customer_id from integer to string (to support UUIDs), and drops the legacy_status column.

What you see in production

The added column is silently ignored for weeks; analysts ask why coupons never appear.
The type change fails the load with a cast error at 02:00. In another pipeline it is silently cast, turning UUIDs into NULL.
Dropping the column breaks a dbt model and two dashboards that still referenced it.

The naive code

-- target table created once, by hand; the loader maps columns by position
INSERT INTO raw.orders SELECT * FROM staging.orders_export;

Reproduce it

The simulation classifies each change between two schema versions by whether existing consumers keep working.

Which schema changes are safe?Try it yourself

old = {"order_id": "int", "customer_id": "int", "legacy_status": "str", "total": "decimal"}
new = {"order_id": "int", "customer_id": "str", "total": "decimal", "coupon_code": "str"}

safe_widenings = {("int", "bigint"), ("float", "double"), ("int", "decimal")}

for col in sorted(set(old) | set(new)):
    if col not in old:
        verdict = "added: safe for readers (if nullable/defaulted); load it explicitly"
    elif col not in new:
        verdict = "dropped: BREAKING for anything that reads it"
    elif old[col] == new[col]:
        continue
    elif (old[col], new[col]) in safe_widenings:
        verdict = f"widened {old[col]} -> {new[col]}: usually safe"
    else:
        verdict = f"type changed {old[col]} -> {new[col]}: BREAKING (casts fail or lose data)"
    print(f"{col:<14} {verdict}")

Output

coupon_code    added: safe for readers (if nullable/defaulted); load it explicitly
customer_id    type changed int -> str: BREAKING (casts fail or lose data)
legacy_status  dropped: BREAKING for anything that reads it

Root cause

The pipeline assumed the schema never changes: columns mapped by position, types fixed by hand, and no process for producers to announce changes or for consumers to adapt.

The fix

Map columns by name, never by position, and load new columns explicitly (or into a semi-structured "extra" column) so they don't disappear.
Classify changes:
- Backward-compatible: adding a nullable column, widening a type (int → bigint).
- Breaking: renaming, dropping, narrowing or changing a type, changing meaning (cents vs dollars).
Roll out breaking changes in expand and contract steps: add the new column, write both, migrate consumers, then remove the old one after an agreed window.
For event streams, use a schema registry with compatibility rules (Avro, Protobuf or JSON Schema), so incompatible producer changes are rejected at publish time.
Use table formats and warehouses that support schema evolution safely (adding columns, widening types), and test consumers in CI against the new schema.

Trade-offs

Auto-adding every new source column keeps data flowing but can expose unexpected sensitive fields; review additions.
Strict schema enforcement stops bad data but stops the pipeline too; quarantine plus alerts is a middle path.

Detect it in production

Diff the source schema against the last known schema on every run and alert on any change.
Use lineage to list consumers of a column before approving a change to it.

Key points

Additive changes are usually safe; renames, drops and type changes break consumers.
Map by name, load new columns explicitly, and detect schema changes on every run.
Use expand and contract migrations and schema registries with compatibility rules.