Schema Tests

Analogy categoryAnalogies

What Are Schema Tests?

A schema test in dbt is a data quality assertion you declare in a .yml file rather than write as SQL by hand — you attach a test to a column or a model, and dbt compiles it into a SELECT query that should return zero rows if the assertion holds. These are called 'generic' tests because the same underlying macro (unique, not_null, accepted_values, relationships) can be reused across any column in any model just by referencing its name in YAML.

🏏

Cricket analogy: This is like a DRS (Decision Review System) check applied automatically to every close call in a match — you don't write a new rule for each delivery, you just invoke the same standard review logic wherever it's needed.

The Four Built-in Generic Tests

dbt ships with four generic tests out of the box. unique asserts that every value in a column appears at most once, which is essential on primary-key-like columns such as order_id. not_null asserts that a column never contains NULL, which matters most on keys used for joins or aggregation, since a NULL join key silently drops rows.

🏏

Cricket analogy: This is like verifying that no two players on a scorecard share the same jersey number (unique), and that every player listed actually has a recorded batting position (not_null) before the match report is published.

accepted_values asserts that every value in a column falls within a fixed list, useful for enumerated fields like order status. relationships asserts referential integrity — that every value in a child column exists in a specified parent model's column, catching orphaned foreign keys such as an order pointing to a customer_id that was never actually loaded.

🏏

Cricket analogy: This is like confirming a dismissal type only ever appears as one of 'bowled', 'caught', 'lbw', 'run out', or 'stumped' (accepted_values), and that every bowler credited with a wicket actually appears on that match's playing XI list (relationships).

yaml

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned', 'cancelled']
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Severity, Thresholds, and Test Configuration

Every test accepts a config block where you can set severity to warn instead of the default error, which lets a failing test surface in logs without breaking the dbt run or CI pipeline — useful while you're still investigating a known data issue. For tests where a small number of failures is tolerable, error_if and warn_if accept thresholds like '>10' so the test only fails once a certain count of bad rows is exceeded, rather than on the very first violation.

🏏

Cricket analogy: This is like a team management staff flagging a bowler's slightly elevated no-ball count as a warning to monitor, rather than immediately suspending him from the next match over a single infraction.

Run dbt test to execute every schema and data test in the project, or scope it with dbt test --select fct_orders to test just one model and its columns. Add store_failures: true in the test config to persist the actual failing rows into a table in your warehouse for easier debugging.

Running Tests and Reading Failures

When a test fails, dbt prints the compiled SQL query it ran along with the number of failing rows, and you can copy that query directly into your warehouse's SQL editor to inspect the offending records. This compiled-query-first workflow is what makes schema tests debuggable rather than a black box — the assertion isn't magic, it's just a SELECT statement dbt generated for you from the YAML shorthand.

🏏

Cricket analogy: This is like a third umpire showing the exact ball-tracking replay used to overturn an LBW decision, so the on-field umpire can see precisely why the call was made rather than trusting it blindly.

The relationships test is effectively a LEFT JOIN under the hood — a child value of NULL will not fail the test even though it isn't 'in' the parent table, because dbt treats NULLs as not applicable to referential integrity. Pair relationships with a separate not_null test on the same column if NULL foreign keys are also a problem you want to catch.

Schema tests are YAML-declared assertions compiled by dbt into SELECT queries that must return zero rows to pass.
The four built-in generic tests are unique, not_null, accepted_values, and relationships.
relationships checks referential integrity against a parent model, catching orphaned foreign keys.
Set severity: warn to surface failures without breaking the run; use error_if/warn_if for row-count thresholds.
dbt test runs all tests; dbt test --select <model> scopes to one model.
store_failures: true persists failing rows to a table in the warehouse for easier debugging.
relationships uses LEFT JOIN semantics and will not flag a NULL foreign key — pair it with not_null when needed.