dbt · Testing & Documentation

Schema tests vs data tests

Mediumschema testsdata testssingular testsgeneric tests

Interview question

What is the difference between schema (generic) tests and data (singular) tests in dbt?

How to answer

The short answer

dbt has two common testing styles.

Schema / generic tests

Declared in YAML, parameterized, reusable. Built-ins: unique, not_null, accepted_values, relationships. You can also write custom generic tests that take arguments.

YAML
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

Data / singular tests

A standalone .sql file under tests/ that returns failing rows. If the query returns any rows, the test fails.

SQL
-- tests/assert_positive_order_amounts.sql
select order_id, amount
from {{ ref('fct_orders') }}
where amount < 0

Comparison

Output
Generic (schema) tests          Singular (data) tests
-------------------------       ------------------------
YAML + reuse                    One-off SQL file
Great for column contracts      Great for business rules
Args: column, values, to=       Full SQL freedom

When to use which

  • Generic: PK/FK/null/enum checks shared across many models.
  • Singular: "revenue this month should match finance extract within 1%" or multi-table invariants that are awkward as a generic.

Interview tip: "Generic tests for reusable column contracts; singular tests for one-off business assertions expressed as SQL that should return zero rows."