Schema Evolution in Table Formats

Concepts covered: Schema Evolution

3 ways to find a column

Once events land in lake tables, the file format's binding decides what each schema change costs. Positional files, such as CSV, read the third column as the third field. Self-describing files matched by name, the default for many columnar readers, find a column by its label. Some open table formats, or optional modes of them, assign every column an integer ID, stored in table metadata and in each data file's schema, and match by that ID, so a name is only a label the user sees. Others bind by name, so check how your format binds before relying on the guarantees below.

Read the table by column. ID-based formats turn most structural changes into a metadata commit that finishes in seconds whatever the table's size, which suits tables that evolve often. Positional files sit at the other end: nearly every change other than appending a column means rewriting every file.

Renaming shows the difference. On an ID-based table, renaming customer_id to buyer_id changes 1 entry in the metadata, and every file written before the rename still answers to field ID 2. On name-matched files, the same statement leaves 3 years of history returning null for buyer_id, though the data is still in the files.

ALTER TABLE curated.orders RENAME COLUMN customer_id TO buyer_id

Drop, then add the same name

This case separates the 2 binding styles most sharply. The marketplace dropped discount_cents in March when a promotion ended, and in October added a new discount_cents for loyalty discounts. A February file still contains the old promotion values.

The name-matched reader resurrects the old promotion amounts and reports them as loyalty discounts. The ID-based reader knows the October column has a new ID, finds no such ID in the February file, and returns null. Dropped IDs are retired and never reused, which is what makes that guarantee hold.

Partition evolution

Table formats that record the partition scheme per data file let that scheme change without a rewrite. When Japan's order volume makes daily partitions on created_at too coarse, the table switches to hourly: new files are written hourly, old files keep their daily layout, and the planner prunes each file by the scheme it was written with. Queries filter on created_at itself, so none of them change.

SELECT
  currency,
  sum(amount_minor) AS total_minor
FROM curated.orders
WHERE created_at >= TIMESTAMP '2026-10-01 00:00:00'
  AND created_at < TIMESTAMP '2026-10-01 06:00:00'
GROUP BY currency

The cost is uneven pruning. This 6-hour window reads 6 hourly partitions for recent data, while the same filter over last year scans whole days. Rewriting old partitions into the new scheme is optional and can run during quiet hours.

The sink job between stream and lake

The sink job that writes stream events into lake tables is where the stream's schema and the table's schema must agree. If the sink evolves tables automatically, any producer can add columns to any table it writes; if it never evolves them, new fields are dropped or the write fails. The layer policy applies here as well: raw tables auto-evolve so nothing is lost. Staging and curated tables change only through a migration that runs ALTER TABLE before the producer ships the field.

Time travel adds one question to check for each format: whether a query against an old snapshot uses that snapshot's schema or the table's current one. A backfill that compares a pre-rename snapshot against today's table depends on the answer. If it uses the snapshot's schema, the old snapshot exposes customer_id and the comparison must map it to buyer_id. If it uses the current schema, the old snapshot already shows buyer_id, the field ID carrying the values across.

About this lesson

This section is part of the Schema Evolution: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.