Schema Evolution & Data Contracts

So far the shop data has kept the same shape. Real sources do not. The app team renames a column, adds one, or changes a type, and the daily job reads the new shape.

This chapter is about those changes. The source is the app database that sends the orders feed. Your pipeline and the dashboard are the readers of that feed.

What you already know

• Data modeling: Asha can move from IN to DE. That changes a value. This chapter changes the shape.
• Python: a validation contract lists the rules for a row, and too many bad rows stop the load.
• Data quality: a check is a query. A failed check can warn, quarantine or block.
• Orchestration: the check task sits between build and publish, and a failed task skips the rest.

What you will learn

1. What a schema is.
2. Safe changes and breaking changes.
3. What a breaking change does to the job.
4. A schema check in the job.
5. A data contract.
6. A safe way to rename a column.
7. A change in meaning that no schema check sees.
8. Optional: how Parquet, Avro, Delta and Iceberg handle change.

We use the shop. When an example needs new rows, we add them in that section and say they are made up.

A schema is the shape of a table: the column names, the type of each column, and the rules for the values. A type says what kind of value a column holds, such as a whole number, a decimal number, text or a date.

The producer is the team that makes the data. For the shop, the app team makes the orders feed.

A consumer is anything that reads it, such as our daily job and the dashboard. In this chapter we also say reader.

This is the shape the shop job expects today:

Most tools store only the names and the types. Only people know the rules and the meaning, unless you write them in checks.

Both can change too. A new allowed status, or a new unit for amount, is a change just as much as a new name.

An additive change only adds something. Old readers still work. A breaking change changes or removes something a reader uses, so old readers fail.

Here are four changes the app team could make to the orders feed. Each is sorted into one group:

An additive change can still break a load. The new column is safe only if readers name the columns they want.

These two rows are made up. They are the first orders of 2026-01-22, and they arrive with the new currency column. The shop sells in USD:

-- raw_orders has 5 columns. The feed now has 6.

-- Way 1: take every column the feed has.
INSERT INTO raw_orders
SELECT * FROM feed_orders;

-- Way 2: name the columns you want.
INSERT INTO raw_orders (order_id, customer_id, status, amount, order_date)
SELECT order_id, customer_id, status, amount, order_date
FROM   feed_orders;

A breaking change can fail loudly or silently. A loud failure is a task error, and a person gets an alert. A silent failure is worse, because a wrong number reaches the dashboard.

Say the app renames amount to total_amount. What happens depends on the loader, the task that copies the feed into raw_orders.

Some loaders match columns by name. When a column is missing, they write NULL. The made-up orders 110 and 111 arrive like this, and the build adds them up:

SELECT order_date, SUM(amount) AS revenue
FROM   raw_orders
WHERE  order_date = '2026-01-22'
  AND  status = 'paid'
GROUP  BY order_date;

Check 2 (Valid) from the data quality chapter would catch this: a paid order with a NULL amount. Without that check, every task succeeds.

With it, you learn about a rename only from a NULL. The next section finds the rename itself.

Way 2 from the last section stops with an error: the feed has no amount. That is better, because it is loud. But not every loader does this, so do not rely on it.

Now say amount arrives as text. The values look the same, but text sorts like words. Here is a query for the biggest order of the day:

SELECT MAX(amount) AS biggest_order
FROM   raw_orders_text
WHERE  order_date = '2026-01-22';

In many databases, SUM on a text column fails with an error. Some databases turn the text into numbers first. So the same change can be loud in one query and silent in another.

The fourth change, a dropped status column, can go either way. If the column is gone, the build stops with an error. That is loud.

If a loader writes NULL instead, no row is paid. The build returns no row for the day, and nothing fails.

A schema check compares the feed’s columns with the columns you expect. It is a data quality check, like the ones in the data quality chapter.

But it runs earlier: as a new task between load and build. So a changed shape stops the job before the build. The other checks still run after the build.

Say the feed now has total_amount and currency, and no amount. The expected shape is in one table, and the new shape is in the other.

A FULL OUTER JOIN keeps unmatched rows from both sides. Here it lists every column that does not match:

SELECT COALESCE(e.column_name, a.column_name) AS column_name,
       CASE WHEN a.column_name IS NULL THEN 'missing'
            WHEN e.column_name IS NULL THEN 'new'
            ELSE 'type changed' END AS problem
FROM   expected_columns e
FULL OUTER JOIN feed_columns a
       ON a.column_name = e.column_name
WHERE  a.column_name IS NULL
   OR  e.column_name IS NULL
   OR  e.data_type <> a.data_type
ORDER  BY column_name;

information_schema.columns is a built-in table that lists every column of every table. In many databases, it gives you the feed_columns list. If the feed is a file, you read the column list from the file.

Now choose what each problem does. Use the same warn or block choice as the data quality chapter:

In our example, amount is missing, so the job blocks. A person sees the rename in the alert, before the dashboard shows a NULL.

A stream is a feed that sends each event as it happens, not once a day. For streams, a schema registry does this check when the producer sends a new schema. It is a service that stores each version of a schema and rejects a version that breaks readers.

A registry guards the stream only. It does not guard your warehouse tables.

A schema check finds a change after it happens. A data contract is a written agreement between the producer and its consumers. It says what the data is and how it may change.

A contract has four parts: an owner, the columns with their types, the rules for the values, and a change policy.

Here is the contract for the orders feed. It is in YAML, the format from the dbt tests. The field names are made up to keep it small:

dataset: orders_feed
version: 1.0.0
owner: app-team
contact: app-team@shop.example

columns:
  - name: order_id
    type: integer
    required: true
    unique: true
  - name: customer_id
    type: integer
    required: true
  - name: status
    type: text
    allowed: [paid, pending, refunded]
  - name: amount
    type: numeric
    description: Order total in the main currency unit (25.00 means 25), not in cents.
    min: 0
    max: 500
    empty_only_when: status = pending
  - name: order_date
    type: date
    required: true

change_policy:
  additive: tell consumers, no notice period
  breaking: 30 days notice and a new major version

required: true means never empty. unique: true means never repeated.

allowed lists the only values. empty_only_when says when a value may be empty.

It is like the validation contract from the Python chapter, but two teams agree on it.

Look at what the contract adds. The owner says who to ask. The description, min and max say what amount means.

The change policy says that a rename needs notice.

The version number tells readers what kind of change happened. A change from 1.0.0 to 1.1.0 is additive. A change to 2.0.0 is breaking.

The last number is for small fixes, such as a clearer description.

A contract is useful only when something reads it. The schema check can use its columns, and the quality checks can use its rules. Keep the file in the same repository as the code that makes the data.

Sometimes the app team really must rename amount to total_amount. You cannot make that change safe in one move. So you split it into small safe steps.

The pattern is called expand and contract. These are the four steps, in order:

1. Expand: the producer adds total_amount and still sends amount. Both columns hold the same value.
2. Move: each consumer switches from amount to total_amount, one at a time.
3. Prove: check the query logs, the database’s list of queries people ran. Make sure nobody reads amount any more.
4. Contract: the producer stops sending amount, after the notice date in the contract.

During the overlap, the feed carries both columns. Our job moves in two places: the load also copies total_amount into raw_orders, and the build sums total_amount.

Here are the made-up orders 110 and 111 in raw_orders, and the new build:

SELECT order_date, SUM(total_amount) AS revenue
FROM   raw_orders
WHERE  order_date = '2026-01-22'
  AND  status = 'paid'
GROUP  BY order_date;

Add one check during the overlap: no row where amount and total_amount differ. Use IS DISTINCT FROM, so a NULL on one side also shows. If the two columns disagree, stop before anyone moves.

Rows loaded before the new column existed have no total_amount. Fill them from amount first. This is a backfill, as in the incremental load chapters.

One kind of change passes the schema check. The names stay the same, the types stay the same, and the values are still allowed. Only the meaning changes.

Say the app team starts to send amount in cents, not in currency units. This is a made-up order on 2026-01-23: Carla pays 25.00, and the feed sends 2500.00. The build adds up the day:

SELECT order_date, SUM(amount) AS revenue
FROM   raw_orders
WHERE  order_date = '2026-01-23'
  AND  status = 'paid'
GROUP  BY order_date;

The contract can catch this one. It says amount is at most 500.

The biggest real order so far is 80.00, so 500 leaves room. This check reads that rule:

SELECT order_id, amount
FROM   raw_orders
WHERE  amount > 500;

After the change, every new row is in cents, so far more than 5% of rows fail. That is above the quarantine limit, so the job blocks.

The rule is rough: an order under 5.00 is under 500 even in cents, so it passes.

A smaller change also passes the check. Say amount moves from "before tax" to "after tax". Every value is still under 500.

Two defences remain. Write the meaning in the contract, so a change shows up in a review. And compare your totals with the payment provider, as you saw with reconciliation.

This section is optional. The next chapter teaches how these formats store data. Here you only see how each one treats a schema change.

A file format or table format stores the schema with the data. Each one handles change in its own way:

Look at the Parquet row. A Parquet file is only a file, so the format cannot protect you. Delta and Iceberg are table formats, so they track the schema for the whole table.

None of them knows what a column means. A cents change passes in every format. Contracts and checks still do that job.

Five points make a complete answer:

1. Sort the change: a rename is breaking. Adding a column is additive.
2. Check the contract: who owns the feed, and how much notice does the policy say?
3. Use expand and contract: send both columns, move readers, prove, then drop the old one.
4. Protect the job: a schema check blocks a missing column. A new column only warns.
5. Name the hidden risk: a change of meaning passes every schema check.

Two words may come up. Backward compatible means a reader on the new schema can read old data. Forward compatible means a reader on the old schema can read new data.

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.