Data Contracts, Schema Evolution & Governance

A senior interviewer asks: forty dashboards use fct_orders. You must change its grain, or rename a column. How?

The answer is a process, not a trick. This chapter teaches the process for a warehouse model that many teams read.

Recap: the chapter Schema Evolution & Data Contracts covered the feed view. It taught safe and breaking changes in a feed, a YAML contract, and expand and contract. It also taught schema registries, and a change of meaning such as cents.

You already know the grain (what one row means), dbt layers and tests, and the fct_orders and dim_customer tables.

Here is fct_orders, with the keys from Modern Warehouse Modeling. A real customer_key is a long text hash. The table shows 1001 to 1006 to keep it short:

What you will learn

1. A dbt contract on a model: what it checks, and what it cannot check.
2. Which changes are safe, and which break a query that already exists.
3. How to find the people who depend on a model.
4. How to ship a new version of a model and move people to it.
5. Who owns a model, who may read it, and how to mask personal data.

A data contract is a promise about a table: its columns, their types, and what they mean. In dbt you can make the build check part of that promise.

Add contract with enforced: true. Then you must declare every column and its data type. This is the contract for fct_orders:

models:
  - name: fct_orders
    description: One row for each order.
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: int
        constraints:
          - type: not_null
      - name: customer_key
        data_type: varchar
      - name: order_date
        data_type: date
      - name: status
        data_type: varchar
      - name: amount
        data_type: numeric(10,2)

Now a developer renames amount to order_amount in the SQL, and forgets the YAML. Before dbt builds the table, it compares the model with the contract:

The build stops in the developer’s own run. No dashboard sees a broken table.

A contract checks names, types and the number of columns. It also adds constraints, such as not_null, where the warehouse supports them. It cannot check what a column means, or what one row stands for.

A change is backward compatible when every query that worked before still works, with the same answer. Two model changes are usually backward compatible: add a column, and widen a type.

Add a column

A dashboard has this query. It names its columns. Then you add item_count to fct_orders:

SELECT order_id, amount
FROM fct_orders
WHERE status = 'paid'
ORDER BY order_id;

Widen a type

Widening means the new type holds every old value. Changing amount from numeric(10,2) to numeric(12,2) keeps 40.00 as 40.00. It only makes room for bigger numbers.

The other direction is narrowing. Going from numeric(12,2) back to numeric(10,2) fails for any value above 99,999,999.99. Treat a narrowing as a breaking change.

A dbt contract compares the type name only, not the size. It does not catch numeric(12,2) becoming numeric(10,2). Check that yourself.

Three more changes break readers: rename a column, change the grain, and change the meaning of a column. They break in different ways. Watch what each one does to a query that already exists.

Rename: the query fails

The dashboard query from the last section stays the same. You rename amount to order_amount:

SELECT order_id, amount
FROM fct_orders
WHERE status = 'paid'
ORDER BY order_id;

Grain: the number is wrong

Now you change the grain from one row for each order to one row for each order item. Order 101 has two items, so it now has two rows.

Each item row repeats the whole order amount, not the price of that item. Run the old paid revenue query:

SELECT SUM(amount) AS paid_revenue
FROM fct_orders
WHERE status = 'paid';

Meaning: the number moves

Last, the name and the type stay the same, but the meaning changes. Refunded orders now store a negative amount, so order 103 holds -60.00. A Sales dashboard sums every order with no filter:

SELECT SUM(amount) AS revenue
FROM fct_orders;

Here are the three side by side:

The silent changes are the dangerous ones. The dashboards keep working and show wrong numbers.

A producer is the team that owns a model. A consumer is anyone who reads it: a model, a dashboard, a script. A change is only safe when you know every consumer.

dbt knows the models that call ref(). Its lineage graph is the map of which model reads which. You can add a dashboard to the graph as an exposure:

exposures:
  - name: sales_dashboard
    type: dashboard
    maturity: high
    owner:
      name: Sales analytics
      email: sales-analytics@example.com
    depends_on:
      - ref('fct_orders')

The command dbt ls --select fct_orders+ lists everything after fct_orders in the graph, and fct_orders itself. A shortened result looks like this:

shop.marts.fct_orders
shop.marts.mart_orders
shop.marts.obt_orders
exposure:shop.sales_dashboard

dbt also lists the tests on these models. They are left out here. Then you add the owner and the columns by hand:

The last row is the problem, because dbt cannot see a notebook or a script. The warehouse query log can see them. It records the queries that touched the table, often for up to one year.

Column-level lineage tools also show which column each consumer reads. Examples are OpenLineage with Marquez, and DataHub.

Do not make a breaking change in place. Instead you publish a second version beside the first. Both versions exist as tables, and each consumer moves when it is ready.

Model versions came in dbt 1.5. The details change between dbt releases, so check the docs for yours. Here v1 keeps amount and v2 renames it to order_amount:

models:
  - name: fct_orders
    latest_version: 1
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: int
      - name: customer_key
        data_type: varchar
      - name: order_date
        data_type: date
      - name: status
        data_type: varchar
      - name: amount
        data_type: numeric(10,2)
    versions:
      - v: 1
        defined_in: fct_orders
        config:
          alias: fct_orders
        deprecation_date: 2026-12-15
      - v: 2
        columns:
          - include: all
            exclude: [amount]
          - name: order_amount
            data_type: numeric(10,2)

The SQL file for v2 is the old select, with the new name:

select
  order_id,
  customer_key,
  order_date,
  status,
  amount as order_amount
from {{ ref('int_orders_joined') }}

A consumer chooses a version with ref(). Both queries return 160.50, because only the column name changed:

-- not moved yet
select sum(amount) as paid_revenue
from {{ ref('fct_orders', v=1) }}
where status = 'paid'

-- moved
select sum(order_amount) as paid_revenue
from {{ ref('fct_orders', v=2) }}
where status = 'paid'

This is expand and contract from Schema Evolution & Data Contracts, done with dbt versions. Follow these steps:

1. Publish v2 beside v1. Keep latest_version at 1, so nobody moves by surprise.
2. Compare v1 and v2 row by row. They must agree, apart from the change you meant.
3. Move consumers one by one: dbt models to v=2 in ref(), dashboards to the table fct_orders_v2. Start with the ones you own.
4. Set latest_version to 2 only after step 3. Every ref() with no version now reads v2, so write v=1 in the ref() of any model that stays behind.
5. Remove v1 only when the query log shows that nothing reads it.

The deprecation_date is the day after which nobody should use v1. It is a message, not a switch: dbt warns a model that still uses v1. A dashboard gets no warning, and dbt does not delete v1.

Governance means rules for who owns data and who may use it. Without it, nobody knows whom to tell about a change. Write the rules as code, in the same repository as the model.

In dbt, a group names an owner, and access says who may call ref() on a model. A role is a named set of permissions that you give to people. The grants setting says which roles may read the table:

groups:
  - name: finance_data
    owner:
      name: Finance data team
      email: finance-data@example.com

models:
  - name: fct_orders
    group: finance_data
    access: public
    config:
      grants:
        select: ['reporter', 'analyst']

The access setting has three values:

Make a public model a contract model too, because other teams build on it. The group also tells you whom to call when you plan a change.

For a dashboard in a BI tool, the section Who sees what, in Modeling For Consumption: The BI Handoff, shows row-level rules.

Personal data is data that points to one person, such as a name. Many teams call it PII, short for personally identifiable information. First you classify each column, which means you label how sensitive it is.

In dbt you can write the label next to the column, so people and tools can find it:

models:
  - name: dim_customer
    columns:
      - name: name
        tags: [pii]
        meta:
          classification: personal

A masking policy hides a column for some roles. This one is Snowflake syntax, and other warehouses use other syntax. Only the role pii_reader sees real names:

CREATE MASKING POLICY mask_name AS (val STRING)
RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() = 'PII_READER'
       THEN val
       ELSE '*****'
  END;

ALTER TABLE dim_customer
  MODIFY COLUMN name SET MASKING POLICY mask_name;

Now a person with the role analyst reads the current customers:

SELECT customer_id, name, country
FROM dim_customer
WHERE valid_to = DATE '9999-12-31'
ORDER BY customer_id;

To erase a person on request, see Production Data Modeling: Scale, Loading & Behavior. You update the personal columns in the dimension for that customer_id. The facts do not change.

Five ideas to keep:

1. A dbt contract checks names and types. It cannot check grain or meaning.
2. Adding a column or widening a type is safe for queries that name their columns.
3. A rename fails with an error. A change of grain or meaning returns wrong numbers with no error.
4. Find every consumer first. Then publish a new version beside the old one, and move people in steps.
5. Give each model an owner, grant access by role, and mask personal data.

Check yourself

• Why does a grain change make paid revenue 280.50 and not 160.50?
• Why can dbt not see a notebook that reads fct_orders?
• What does the deprecation_date do, and what does it not do?

Each exercise opens a canvas for an entity-relationship diagram (ERD). You draw the tables, and the review checks them against sample rows.

Start at the top. The two Advanced exercises are harder, so they are optional.