Schema tests: unique, not_null, accepted_values, relationships

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.

columns:
  - name: order_id
    tests:
      - not_null

2. unique

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

columns:
  - name: order_id
    tests:
      - unique

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

3. accepted_values

Values must be in an allowed set.

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

4. relationships

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

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

Mental model

not_null         → no holes
unique           → no duplicates
accepted_values  → enum / domain check
relationships    → referential integrity to another table

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.