dbt constraints in YAML: enforcing data integrity in the warehouse

1.Constraints move validation into the data platform

A constraint is a rule that the data platform itself checks as rows are written. You declare it in a model's YAML. dbt doesn't evaluate it with a query of its own. Instead, dbt will include the column names, data types, and constraints in the DDL statements it submits to the data platform, so the warehouse checks every row as it creates or updates the table. When a row breaks the rule, the table creation or update fails and the operation is rolled back.

The difference from a data test is timing. A data test validates a model's content after the model is built, so it inspects a table that already exists. A constraint is checked at build time, and a violation stops the build itself. Here is the Postgres DDL that dbt renders for an id column with not_null, primary_key and check constraints. All three rules end up on the column definition, where Postgres enforces them on the insert that follows.

create table "database_name"."schema_name"."constraints_example__dbt_tmp"
(
    id integer not null primary key check (id > 0),
    customer_name text,
    first_transaction_date date
)
;

Sources12

2.Prerequisites: a table or incremental model with an enforced contract

Constraints are only applied when two conditions hold. If either is missing, the YAML is accepted but nothing reaches the warehouse.

- Materialization. The model must be table or incremental. Constraints are never applied on ephemeral models or on models materialized as view. - Contract. The model must declare and enforce a contract, so every column needs a data_type in YAML, not just the columns you constrain.

The contract requirement often surprises people. Adding not_null to one key column of a 40-column model means declaring all 40 columns with their types. The contract itself, meaning the name and type checks, its supported materializations and what counts as a breaking change, is covered under [model contracts](lesson:2.1). For constraints, what matters is that the contract is the vehicle that carries them into the DDL.

Views are a partial exception. A view can carry an enforced contract, but the contract only covers column names and data types. Views offer support for those, but not constraints, so a not_null declared on a view column is not applied.

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

Sources12

3.Writing constraints at the column and model level

A constraint can be attached to a single column or to the model as a whole. The general rule is to define a single-column constraint directly under that column. Use the model level when the rule spans more than one column, such as a composite primary key, a multi-column foreign key, or a check that compares two columns. One case requires the model level: a model with multiple primary_key constraints has to define them there, because defining multiple primary keys at the column level is not supported.

Every constraint is built from the same few fields:

- type (required): one of not_null, unique, primary_key, foreign_key, check or custom. - expression: free text that qualifies the constraint. Some types require it, for example a check needs its boolean condition. - name (optional): a human-friendly name, supported by some data platforms. - columns (model level only): the list of columns the constraint applies to.

    # model-level constraints
    constraints:
      - type: primary_key
        columns: [first_column, second_column, ...]
        warn_unsupported: True # show a warning if unsupported
      - type: foreign_key # multi_column
        columns: [first_column, second_column, ...]
        to: ref('my_model_to') | source('source', 'source_table')
        to_columns: [other_model_first_column, other_model_second_columns, ...]
      - type: check
        columns: [first_column, second_column, ...]
        expression: "first_column != second_column"
        name: human_friendly_name

Foreign keys take two extra inputs. to names the referenced relation with ref() or source(), and to_columns lists the primary or unique key columns in that relation. Because to uses ref, dbt records the dependency in the DAG and resolves the right schema in every environment. A hard-coded table name would point development builds at production. This syntax is available in dbt v1.9 and later.

        # column-level constraints
        constraints:
          - type: not_null
          - type: unique
          - type: foreign_key
            to: ref('my_model_to') | source('source', 'source_table')
             ? : [other_model_column]
            warn_unenforced: False # skips warning if supported but not enforced

Sources1

4.What each platform defines and enforces

Writing a constraint in YAML does not mean your warehouse will reject rows that break it. Every constraint type falls into one of three categories on a given platform:

- Definable and enforced: the model won't build if the data violates it. - Definable and not enforced: the constraint is written into the DDL as metadata, and the model builds even if the data violates it. - Not definable: the platform cannot express the constraint at all.

Transactional databases enforce strictly, while cloud warehouses mostly record metadata. Postgres enforces everything in the ANSI SQL standard plus row-level check. Most analytical platforms enforce not_null and little else. On Snowflake, unique, primary_key and foreign_key are accepted but not verified on insert. If you specify rely in the constraint expression, Snowflake can use them for query optimization. Unenforced constraints can still be useful for integrating with legacy data catalogs or entity-relationship diagram tools.

dbt warns whenever a constraint you wrote won't be enforced on your platform. Two optional fields, set on an individual constraint, turn those warnings off, and they behave differently:

- warn_unenforced: False silences the warning for a constraint the platform supports but doesn't enforce. The constraint is still included in the DDL. - warn_unsupported: False silences the warning for a constraint the platform doesn't support. That constraint is left out of the DDL.

On Snowflake, for example, a check constraint triggers a warning and dbt skips it.

Sources1

5.Choosing between a constraint and a data test

A constraint can replace a data test, but only when it is actually enforced. Swapping a not_null test for a not_null constraint guarantees the check at build time and probably needs less compute, because there is no separate query afterward. The swap is only safe when all three of these hold:

- The platform supports and enforces that constraint. Most platforms only enforce not_null. - The model is materialized as table or incremental. - The model has a full contract, with the name and data_type of every column.

When those conditions don't hold, the test stays. The usual case is a primary_key on Snowflake, Redshift or BigQuery. The platform rejects null keys but accepts duplicates, so the uniqueness half of the rule still needs a unique test. Tests also have strengths that constraints lack. You can set custom severity thresholds, query the already-built model to debug a failure, and store the failing records in the warehouse.

    columns:
      - name: id
        data_type: integer
        description: hello
        constraints:
          - type: not_null
          - type: primary_key # not enforced  -- will warn & include
          - type: check       # not supported -- will warn & skip
            expression: "id > 0"
        data_tests:
          - unique            # need this test because primary_key constraint is not enforced

Sources2

Exam traps

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

1. 1.Adding constraints to a view model makes the warehouse reject bad rows.Why is that wrong?

   Constraints only apply to table and incremental models. A view with an enforced contract gets name and type checks but no constraints.

   Covered in Prerequisites: a table or incremental model with an enforced contract

2. 2.You can add a not_null constraint to one column without declaring a contract or types for the other columns.Why is that wrong?

   Constraints need an enforced contract, which requires a data_type for every column in the model.

   Covered in Prerequisites: a table or incremental model with an enforced contract

3. 3.A primary_key constraint on Snowflake guarantees that the key column is unique.Why is that wrong?

   Snowflake only checks not-null, including the not-null part of a primary key. Uniqueness is metadata only.

   Covered in What each platform defines and enforces

4. 4.A composite key can be declared by putting primary_key under each of its columns.Why is that wrong?

   Multiple primary_key constraints must be declared once at the model level, with a columns list.

   Covered in Writing constraints at the column and model level