TESTS

YOUR FIRST TEST

Add two lines of yml next to a model. That's the whole setup:

# models/staging/schema.yml
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

Run it:

$ dvt test --select stg_orders

1 of 2 START test not_null_stg_orders_order_id ........... [RUN]
1 of 2 PASS not_null_stg_orders_order_id ................. [PASS in 0.05s]
2 of 2 START test unique_stg_orders_order_id ............. [RUN]
2 of 2 PASS unique_stg_orders_order_id ................... [PASS in 0.07s]

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

Under the hood every test is just a query that selects violating rows. Zero rows returned means the assertion holds — the test passes. Any rows returned means it fails, and the count is reported. data_tests: is the modern key; the older tests: spelling still works.

THE FOUR BUILT-IN GENERIC TESTS

Four assertions cover most of what a pipeline needs to promise. They attach to any column of any model, seed, snapshot, or source:

# models/staging/schema.yml
version: 2

models:
  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique                    # no duplicate values
          - not_null                  # no NULLs
      - name: status
        data_tests:
          - accepted_values:          # only these values, ever
              values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:            # referential integrity
              to: ref('stg_customers')
              field: customer_id

Sources take tests the same way — put the columns: block under a table in sources.yml:

# models/staging/sources.yml
sources:
  - name: crm
    meta:
      connection: oracle_ops
    tables:
      - name: customers
        columns:
          - name: customer_id
            data_tests:
              - unique
              - not_null

One thing to know before you test a source on another engine: read the federation note below — DVT is honest about which of these tests it can actually run.

SINGULAR TESTS

When an assertion is too specific for a generic test, write it as SQL. Any .sql file under tests/ is a singular test: a select that returns the rows that would violate your assumption.

-- tests/assert_no_negative_amounts.sql
-- Returns every order with a negative amount.
-- Zero rows = pass. Any rows = fail, and dvt shows you the count.

select order_id, amount
from {{ ref('stg_orders') }}
where amount < 0

Full Jinja is available — ref(), source(), macros, all of it. The file name becomes the test name in the output, so name it like the sentence it asserts: assert_no_negative_amounts, not test1.

CUSTOM GENERIC TESTS

Wrote the same singular test for the third column in a row? Promote it to a generic test. A {% test %} block under tests/generic/ (or in macros/) defines a reusable assertion that takes model and column_name — plus any arguments you invent:

-- tests/generic/at_least.sql
{% test at_least(model, column_name, value) %}

select {{ column_name }}
from {{ model }}
where {{ column_name }} < {{ value }}

{% endtest %}

Now it works in yml exactly like the built-ins:

columns:
  - name: amount
    data_tests:
      - at_least:
          value: 0

SEVERITY, WHERE, AND STORING FAILURES

By default a failing test is an error. Every test — built-in, custom, or singular — takes a config: block that tunes what failure means:

columns:
  - name: email
    data_tests:
      - not_null:
          config:
            severity: warn            # report it, don't fail the run
      - unique:
          config:
            where: "created_at >= current_date - 30"   # test recent rows only
  - name: discount_pct
    data_tests:
      - accepted_values:
          values: [0, 10, 20]
          config:
            severity: error
            error_if: ">100"          # error only past 100 bad rows
            warn_if: ">10"            # warn past 10

where: filters the relation before the test runs — the go-to for large tables where you only care about fresh data. warn_if / error_if turn a binary pass/fail into thresholds.

store_failures: true writes the failing rows to an audit table in the warehouse, so you can inspect them with a query instead of re-running the test by hand. DVT records those audit tables in its materialization ledger on pass and fail alike — an empty audit table is still a table — so dvt generate-sources never mistakes one for raw source data.

WHEN TESTS RUN: DVT TEST AND DVT BUILD

dvt test runs tests on demand — all of them, or a selection with the same --select / --exclude syntax every DVT command shares. Selecting a model selects its tests with it:

dvt test                                # every test in the project
dvt test --select stg_orders            # tests attached to one model
dvt test --select source:crm            # tests attached to one source
dvt test --select test_type:singular    # only singular tests

dvt build weaves tests into the DAG: seeds, models, snapshots, and tests run in dependency order, and each test runs immediately after the node it tests. If a test with severity: error fails, everything downstream of the tested node is skipped — bad data stops moving at the first gate instead of flowing to the end of the pipeline.

THE HONEST PART: TESTS AND FOREIGN CONNECTIONS

Tests compile and execute against the default target— the engine your profile points at. That's exactly what you want for models: whether a table was built natively or landed by a federated model, the relation lives on the default target, and its tests run there.

Source tests are different. A test on a source declared on another connection — meta.connection: oracle_ops when your default target is Postgres — asserts against a relation the default engine cannot reach. The engine would compile it into a query against a table that doesn't exist there.

dvt build refuses to fake it and refuses to explode. It identifies every source test bound to a non-default connection and skips them by name, with the reason, as a counted warning:

WARNING: Skipping 2 source test(s) on non-default connections
(the engine cannot reach them from 'pg_dev'):
not_null_crm_customers_customer_id, unique_crm_customers_customer_id

A bare dvt testpasses straight through to the engine, so foreign-source tests invoked directly will surface the engine's own error — exclude them with --exclude source:crm, or better, don't aim tests there at all.

The pattern that works: test data where it lands. A bronze model over a foreign source materializes that data onto the default target — put your unique and not_null there. Same assertions, same coverage, and the engine can actually check them on every build.

# Instead of testing the Oracle source directly...
models:
  - name: bronze_customers        # f_table over source('crm', 'customers')
    columns:
      - name: customer_id
        data_tests:
          - unique                # runs on the default target — always
          - not_null

TEST CONFIG REFERENCE