dbt model contracts: enforcing column names and data types

1.Why a model needs a contract

A dbt model's columns are whatever its final select happens to return. That works well while you are iterating. Once other people and processes query the model, though, a renamed column or a changed type breaks their work. A model contract fixes the shape of the output in advance: column names, data types and, optionally, constraints. dbt checks during the build that the transformation still produces that shape. If it doesn't, the model fails to build.

Shape means more than names. A column can keep its name while its type changes from boolean (true/false) to integer (0/1), and downstream queries can then fail in surprising ways. An enforced contract pins down both the name and the type of every column.

Contracts are meant for "public" models that something downstream relies on. Inside dbt, that means models shared with other groups, other teams or other dbt projects. Outside dbt, it means the reports, dashboards and processes that expect a predictable structure, which you may already describe with exposures. A staging model that only its author touches gains little from one.

Adding governance too early has a cost. Contracts, like model access and versions, add structure, and that structure makes later changes and rollbacks harder. On a model whose columns still change every week, a contract mostly adds maintenance. Contracts also apply only to models. Snapshots, seeds and sources change structure over time, so they can't carry one.

Sources123

2.Enforcing a contract in YAML, project config or SQL

A contract is switched on by setting enforced: true under the contract config. Like other model configs, it can be set in three places:

- dbt_project.yml, with +contract on a folder or project, to enforce contracts consistently across many models - a properties YAML file, under a single model's config - a config() block at the top of the model's SQL file, for fine-grained control over one model

Turning enforcement on is the easy part; the YAML has to declare the shape. Every column the model returns needs an entry with a name and a data_type that your data platform understands. You can't write a partial contract that covers only the important columns. The contract applies to all of them, and requiring an explicit declaration is intentional. On table and incremental models, a column can also list constraints such as not_null. What each platform actually enforces is covered under [platform-level constraints](lesson:2.3).

models:
  - name: dim_customers
    config:
      contract:
        enforced: true
    columns:
      - name: customer_id
        data_type: int
        constraints:
          - type: not_null
      - name: customer_name
        data_type: string

{{ config(
  contract = { "enforced": true }  -- Enables contract enforcement for this model
) }}

select
  column_1_id,          -- replace with your column
  column_2_created_at,  -- replace with your column
  column_3_status       -- replace with your column
from {{ source('property_management', 'rental_applications') }}  -- replace with your source name and table

The SQL block only turns enforcement on. The column names and types still come from the model's YAML entry. The same applies to the project-level setting: +contract in dbt_project.yml tells every model in that folder to enforce a contract, and each of those models still needs its columns declared.

models:
  property_management:  # replace with your dbt project name
     ? :
      enforced: true

Sources13

3.What dbt checks when a contracted model builds

Enforcement changes two things about a build. First, dbt runs a preflight check: before anything is materialized, it compares the names and data types the query will return against the ones in the contract. Second, dbt writes the column names, data types and constraints into the DDL it sends to the platform. That DDL lists the columns in the contract's order, not the order the SQL selects them in.

Column order is never a reason for a contract failure. A missing column, an extra column, a renamed column or a wrong type is. Take a model that selects 'abc123' as customer_id while the contract declares customer_id as int. The run stops before the table exists, and the error lists each mismatched column with the reason:

20:53:45  Compilation Error in model dim_customers (models/dim_customers.sql)
20:53:45    This model has an enforced contract that failed.
20:53:45    Please ensure the name, data_type, and number of columns in your contract match the columns in your model's definition.
20:53:45
20:53:45    | column_name | definition_type | contract_type | mismatch_reason    |
20:53:45    | ----------- | --------------- | ------------- | ------------------ |
20:53:45    | customer_id | TEXT            | INT           | data type mismatch |

The type comparison is deliberately coarse. dbt aliases common type names, so string in a contract becomes text on Postgres and Redshift. A name dbt doesn't recognise is passed to the platform unchanged. To turn aliasing off, set alias_types: false under contract. dbt also ignores size, precision and scale, so varchar(256) against varchar(257) passes. If downstream consumers really depend on precision, a custom test can check it.

Ignoring precision has one catch. If you write a bare numeric, the platform applies its default precision and scale. A default scale of 0 stores only whole numbers, and that implicit coercion can make the contract fail. Declare a nonzero scale, such as numeric(38, 6). From dbt 1.7, dbt warns when a numeric type is declared without precision and scale.

Sources13

4.Which models can carry a contract

Whether a contract works depends on the model's language and materialization. Contracts are for SQL models. A view can have one, but only for column names and data types: constraints are never applied to views. Table and incremental models get the full contract, including constraints, and incremental models have one more condition.

The on_schema_change rule exists because an incremental table can drift away from its contract without dbt noticing. Suppose you add a column to both the SQL and the YAML, but on_schema_change is unset or ignore. dbt never adds the column to the existing table, yet the merge still succeeds, because it only writes the destination columns that already exist. The contract now describes a column the table doesn't have. append_new_columns adds the new column, and fail stops the run, so either keeps the table and the contract consistent. sync_all_columns is excluded too: it also drops columns that are missing from the query, and removing a column is a breaking change for a contracted model.

Sources34

5.Contracts versus data tests, and what counts as a breaking change

A contract governs shape, and data tests check content. A contract stops the build when the columns or types are wrong. A data test runs against the model after it is built, so it can check anything you can write as a query. Data tests also support settings such as custom severity thresholds, and their failures are easier to debug: you can query the built model or store the failing rows in the warehouse. The test types themselves are covered in [dbt data tests](lesson:5.1).

Sometimes a constraint can replace a test. The check then happens at build time, often for less compute. Three conditions must all hold:

- the platform supports and enforces the constraint you need, and most platforms enforce only not_null - the model is materialized as table or incremental, not view or ephemeral - the model has a full contract, with a name and data_type for every column

Tests stay outside the contract for the same reason uptime isn't part of a software API's contract: the contract is the structure of the response. Changing that structure in a backwards-incompatible way is a breaking change. When dbt compares the project to a previous [project state](lesson:7.1), it reports a contract error for each of these breaking changes:

- removing an existing column - changing the data_type of an existing column - removing or modifying a constraint on an existing column (dbt v1.6 or higher) - deleting, renaming or disabling a contracted model (dbt v1.9 or higher)

The last change is not treated the same for every model. On a versioned model it raises an error, and on an unversioned model it raises only a warning. When a breaking change can't be avoided, [model versions](lesson:2.2) give consumers a migration window instead of an overnight break.

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A contracted view enforces its not_null and other constraints, just as a table does.Why is that wrong?

   On a view, a contract enforces only column names and data types. Constraints apply only to table and incremental models.

   Covered in Which models can carry a contract

2. 2.A contracted incremental model should use on_schema_change: sync_all_columns so the table always matches the YAML.Why is that wrong?

   Contracts require append_new_columns or fail. sync_all_columns can drop existing columns, which is a breaking change.

   Covered in Which models can carry a contract

3. 3.A contract fails the build when the SQL selects columns in a different order from the YAML.Why is that wrong?

   The preflight check ignores column order, and the generated DDL orders columns as the contract lists them.

   Covered in What dbt checks when a contracted model builds

4. 4.Because dbt ignores precision, declaring a bare numeric in a contract is always safe.Why is that wrong?

   A bare numeric takes the platform's default scale, which may be 0. That implicit coercion can make enforcement fail, so declare a nonzero scale.

   Covered in What dbt checks when a contracted model builds

Practise it for real

Watch an enforced contract stop a build on a type mismatch, then fix it

1. 1.In a properties YAML file, give dim_customers materialized: table and contract: enforced: true, then declare customer_id with data_type: int and customer_name with data_type: string.

   Why: An enforced contract needs a name and data_type for every column the model returns.

   You should see: The project parses, and dim_customers now has a two-column contract.

2. 2.Write models/dim_customers.sql as: select 'abc123' as customer_id, 'My Best Customer' as customer_name

   Why: customer_id now returns text while the contract declares an integer.

   You should see: The SQL compiles. It doesn't match the contract yet.

3. 3.Run dbt run --select dim_customers.

   Why: The preflight check compares the query's columns against the contract before anything is materialized.

   You should see: A Compilation Error saying 'This model has an enforced contract that failed', with customer_id listed as TEXT against INT and a data type mismatch. No table is created.

4. 4.Make customer_id return an integer value in the SQL, then run the model again.

   Why: Names, data types and the number of columns must all match the contract.

   You should see: dim_customers builds as a table, with its columns in the order the contract lists them.