Schema tests vs data tests

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.

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.

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

Comparison

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."