dbt · Testing & Documentation

Schema tests: unique, not_null, accepted_values, relationships

Easytestsuniquenot_nullaccepted_valuesrelationships

Interview question

Explain the four classic dbt schema (generic) tests: unique, not_null, accepted_values, and relationships.

How to answer

The short answer

Schema tests (generic tests) are declared in YAML on columns or models. dbt generates SQL that should return zero failing rows.

1. not_null

Every value in the column must be non-null.

YAML
columns:
  - name: order_id
    tests:
      - not_null

2. unique

Every value must appear at most once (primary-key style check).

YAML
columns:
  - name: order_id
    tests:
      - unique

Often combined: unique + not_null ≈ "this is my grain / ."

3. accepted_values

Values must be in an allowed set.

YAML
columns:
  - name: status
    tests:
      - accepted_values:
          values: ['placed', 'shipped', 'cancelled']

4. relationships

Foreign-key style: every value must exist in another model's column.

YAML
columns:
  - name: customer_id
    tests:
      - relationships:
          to: ref('dim_customers')
          field: customer_id

Mental model

Interview tip: Schema tests are cheap declarative contracts. Say you put unique + not_null on every mart's grain key, and relationships on important FKs.