Schema Evolution: Handling Changing Data Structures

Source systems change. A column is added for a new fee, a field is renamed in a refactor, an ID becomes a different numeric type. Schema evolution is how a data platform absorbs those changes without breaking pipelines or, worse, silently loading wrong data. It is easier to reason about with a real case than in the abstract, and a well-known public dataset provides one.

A real dataset that changed three ways

New York City's Taxi and Limousine Commission publishes yellow taxi trips as monthly Parquet files (NYC TLC: trip record data). Comparing the January files for 2022, 2024 and 2025 shows what happens to a long-lived feed. The script reports the differences, tries to merge the schemas, and then reads all three files as one table:

import urllib.request
from pathlib import Path
import duckdb
import pyarrow as pa
import pyarrow.parquet as pq

files = {year: f"yellow_tripdata_{year}-01.parquet" for year in (2022, 2024, 2025)}
for path in files.values():
    if not Path(path).exists():
        urllib.request.urlretrieve(f"https://d37ci6vzurychx.cloudfront.net/trip-data/{path}", path)
schemas = {year: pq.read_schema(path) for year, path in files.items()}

# 1. what changed between versions of the same dataset
previous = None
for year, schema in schemas.items():
    cols = {f.name: str(f.type).replace("large_string", "string") for f in schema}
    if previous is None:
        print(f"{year}: {len(cols)} columns")
    else:
        print(f"{year}: added {[c for c in cols if c not in previous]}, removed {[c for c in previous if c not in cols]}")
        print(f"      retyped {[f'{c}: {previous[c]} -> {cols[c]}' for c in cols if c in previous and previous[c] != cols[c]]}")
    previous = cols

# 2. a strict merge refuses; a permissive merge keeps both spellings of the renamed column
try:
    pa.unify_schemas(list(schemas.values()))
except pa.ArrowException as err:
    print("strict merge:", type(err).__name__)
merged = pa.unify_schemas(list(schemas.values()), promote_options="permissive")
print("permissive merge:", len(merged.names), "columns, including", [n for n in merged.names if "airport" in n.lower()])

# 3. reading all three by name: one table, new columns null where they did not exist
paths = list(files.values())
print(duckdb.sql(f"""
    SELECT regexp_extract(filename, '(\d{{4}})', 1) AS year, COUNT(*) AS trips,
           COUNT(airport_fee) AS has_airport_fee, COUNT(cbd_congestion_fee) AS has_cbd_fee,
           typeof(ANY_VALUE(passenger_count)) AS passenger_type
    FROM read_parquet({paths}, union_by_name = true, filename = true)
    GROUP BY year ORDER BY year""").fetchdf().to_string(index=False))

2022: 19 columns
2024: added ['Airport_fee'], removed ['airport_fee']
      retyped ['VendorID: int64 -> int32', 'passenger_count: double -> int64', 'RatecodeID: double -> int64', 'PULocationID: int64 -> int32', 'DOLocationID: int64 -> int32']
2025: added ['cbd_congestion_fee'], removed []
      retyped []
strict merge: ArrowTypeError
permissive merge: 21 columns, including ['airport_fee', 'Airport_fee']
year   trips  has_airport_fee  has_cbd_fee passenger_type
2022 2463931          2392428            0         DOUBLE
2024 2964624          2824462            0         DOUBLE
2025 3475226          2935077      3475226         DOUBLE

The three kinds of change

A renamed column. Between 2022 and 2024, airport_fee became Airport_fee. To a tool that compares names exactly, that is one column removed and another added. A pipeline that selects airport_fee fails on the 2024 file; a pipeline that merges permissively ends up with two half-empty columns, as the 21-column merge shows.

Changed types. Five columns changed type: passenger count and rate code from floating point to integers, and three ID columns from 64-bit to 32-bit integers. Type changes are the dangerous kind, because some engines convert silently. Widening, such as int to long, is safe; narrowing, or number to text, can truncate or fail.

An added column. The 2025 file gains cbd_congestion_fee, which the data dictionary explains is a per-trip charge for New York's Congestion Relief Zone that began on 5 January 2025 (TLC: data dictionary). Additions are the most common and least harmful change, provided old rows are treated as null, not zero.

A fourth kind, a column removed outright, does not appear in these files, but it is the one that breaks consumers most abruptly: every query that selects the column fails. Sources should deprecate columns before dropping them, and pipelines should notice when an expected column stops arriving.

What merging does

A strict schema merge refuses outright: pyarrow raises ArrowTypeError because the types differ. That is the safe default; it forces a decision. A permissive merge succeeds by promoting types and taking the union of names, which is how the two spellings of the airport fee both survive.

Reading by name is the third option. DuckDB combines files by column position by default, and with union_by_name aligns them by name, filling columns a file lacks with NULL (DuckDB docs: combining schemas). The result above is one table of nearly nine million rows. The congestion fee is present in every 2025 row and absent before; passenger count is promoted to a floating-point type throughout, because the 2022 file used one. DuckDB's column names are case-insensitive, so it folded both spellings of the airport fee into a single column; a case-sensitive engine would not. Convenient as that is, it shows that the outcome of a merge depends on the tool's rules, which is why they should be chosen deliberately.

Compatibility: which changes are safe

Streaming platforms formalise this. Confluent's Schema Registry defines compatibility types (Confluent: schema evolution):

The practical rule: adding an optional field with a default is safe almost everywhere; renaming or retyping a field is a breaking change and should be done as add-new, migrate, remove-old. Avro implements this with reader and writer schemas resolved by field name, defaults for missing fields and safe promotions such as int to long (Avro specification); Parquet vs ORC vs Avro compares the formats.

Handling evolution in pipelines

• Detect before loading. Compare each incoming schema with the expected one, as the first part of the script does, and fail or alert on anything other than an approved addition.
• Keep raw data as it arrived, so a mishandled change can be replayed correctly; see ETL vs ELT.
• Map names and types explicitly in the cleaned layer: one canonical name for the airport fee, one type for passenger count, whatever the source year.
• Use table formats that enforce schemas. Delta Lake rejects writes that do not match, and evolves only when asked.
• Treat missing as null, never zero. A fee that did not exist in 2024 is unknown for that year, not free.
• Agree changes in advance with the producing team; data contracts make the schema an explicit promise.
• Test for it. A schema check belongs beside the row checks in data quality testing.

Data transformation pipelines, data quality and validation, and real-time data pipelines are part of the Data Engineering course.

Sources

• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Data dictionary: yellow taxi trip records (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Combining schemas (DuckDB documentation) — DuckDB
• Schema evolution and compatibility (Confluent documentation) — Confluent
• Apache Avro 1.12.0 specification (Apache Avro) — Apache Avro