dbt Model Contracts

A model contract is a formal guarantee about a model's structure. When you define a contract, dbt enforces that the model's output always has exactly the columns and data types you specified. If the SQL produces a different column name or type, the run fails immediately — before bad data reaches downstream consumers.

Why Contracts Matter

Without contracts, a model's output shape is defined implicitly by whatever the SELECT statement produces. Another team building dashboards on top of your model assumes a column called order_id of type INTEGER exists. You rename it to id during refactoring. Their dashboard breaks silently overnight. A contract catches this at build time, not at query time.

Without contract:  You rename order_id → id in the SQL  dbt run succeeds  Dashboard breaks next morning when report runs  Team spends hours debugging

With contract:  You rename order_id → id in the SQL  dbt run FAILS immediately with a clear message:  "Contract violation: expected column 'order_id', found 'id'"  You catch the issue in development, not production

Defining a Model Contract

Contracts are defined in the model's YAML configuration block inside schema.yml:

# models/marts/schema.yml
version: 2
models:
  - name: fct_orders
    config:
      contract:
        enforced: true          ← enables contract enforcement
    columns:
      - name: order_id
        data_type: integer
        constraints:
          - type: not_null
          - type: primary_key
      - name: customer_id
        data_type: integer
        constraints:
          - type: not_null
      - name: order_date
        data_type: date
        constraints:
          - type: not_null
      - name: amount_dollars
        data_type: numeric
      - name: status
        data_type: varchar

Contract Requirements

For a model to support contracts, it must meet two conditions:

Condition 1: Table or Incremental Materialization

Contracts only work with models materialized as table or incremental. Views and ephemeral models do not support contracts because dbt cannot inspect their column structure before executing them.

Condition 2: Every Column Must Be Listed

When enforced: true, every column the SELECT produces must appear in the YAML column list with a data_type specified. Extra columns in the SQL that are missing from the YAML cause a contract violation. Missing columns in the SQL also cause a contract violation.

What dbt Checks

Check 1: Column name exists  SQL produces: order_id, customer_id, order_date, amount_dollars, status  YAML declares: order_id, customer_id, order_date, amount_dollars, status  Result: PASS ✓

Check 2: Data type matches  YAML says amount_dollars is numeric  SQL casts it as varchar  Result: FAIL ✗ — contract violation, run stops

Check 3: Not_null constraint  YAML says order_id has constraint: not_null  SQL produces NULLs in order_id  Result: FAIL ✗ — constraint violation, run stops

Supported Constraints

Constraint Type    Meaning
---------------    -------
not_null           Column must have no NULL values
unique             Column values must be distinct
primary_key        not_null + unique (logical, not always enforced in warehouse)
foreign_key        References another table's column
check              Custom SQL expression must be true for all rows

columns:
  - name: status
    data_type: varchar
    constraints:
      - type: check
        expression: "status in ('pending','completed','cancelled')"

Model-Level Constraints

Constraints can also apply to combinations of columns at the model level:

models:
  - name: fct_order_items
    config:
      contract:
        enforced: true
    constraints:
      - type: primary_key
        columns: [order_id, product_id]    ← composite primary key
    columns:
      - name: order_id
        data_type: integer
      - name: product_id
        data_type: integer
      - name: quantity
        data_type: integer

Contracts and Model Versioning

Contracts become especially powerful when combined with model versioning (Topic 30). Consumers reference a versioned model knowing its contract guarantees the output shape across all versions. Breaking changes require a new version rather than modifying an existing contract.

Data Type Names by Warehouse

Concept          Snowflake     BigQuery      Postgres      Redshift
--------         ---------     --------      --------      --------
Whole number     integer       int64         integer       integer
Decimal          number        numeric       numeric       numeric
Text             varchar       string        varchar       varchar
Date             date          date          date          date
Timestamp        timestamp_tz  timestamp     timestamptz   timestamp
True/False       boolean       bool          boolean       boolean

Always use the data type name that matches your warehouse. dbt passes the type string directly to the warehouse.

When to Add Contracts

• Any mart model consumed by a BI tool, dashboard, or external team
• Any model referenced by more than one downstream team or project
• Any model used in a dbt Mesh cross-project reference
• Any model that is the output of a completed, stable transformation