Testing: Generic and Singular Tests

A dbt Model Is an Assertion Until Something Tests It

A dbt model is just a SELECT statement. It compiles, it runs, and it produces a table or view — none of which tells you anything about whether the data in that table is actually correct. A model can run successfully every single day for months while quietly producing duplicate primary keys, unexpected null values, or foreign keys that point nowhere, because "the SQL executed without error" and "the resulting data is correct" are completely different claims. dbt's testing framework exists to close that gap — to let you write down, in SQL or in YAML, the assumptions your models depend on, and have dbt check them automatically every time the models run.

Without tests, data quality problems are discovered downstream — by an analyst noticing a dashboard number looks wrong, or a stakeholder asking why a report doesn't match another one. With tests, the same problems are caught at the source, immediately after the model that introduced them runs, with a clear failure pointing at exactly which model and which assumption broke.

dbt draws a line between two kinds of tests: generic tests, which are reusable and parameterized and get applied to models and columns declaratively through YAML, and singular tests, which are one-off, fully custom SQL files written for a specific business rule that doesn't generalize. Part 02 covers generic tests in depth; Part 04 covers singular tests.

Generic Tests: Reusable, Parameterized, Defined Once

A generic test is a parameterized assertion you define once and apply to as many columns and models as you like, through YAML rather than by writing SQL every time. dbt ships with four built-in generic tests that cover the large majority of everyday data quality checks:unique, not_null, accepted_values, andrelationships.

unique — no column value appears more than once

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

This asserts that every value in order_id appears in the fct_orderstable at most once. It is the single most common test in any dbt project, because almost every model has some column — a primary key, a surrogate key — that is supposed to uniquely identify each row, and a duplicate there usually means an upstream join fanned out unexpectedly.

not_null — no null values in this column

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

unique and not_null are almost always applied together on a primary key column — unique alone would still pass on a column full of nulls, since null values are not considered duplicates of each other by most warehouses' uniqueness semantics, sonot_null closes that gap.

accepted_values — this column can only ever contain values from a fixed list

models:
  - name: fct_orders
    columns:
      - name: order_status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'cancelled', 'refunded']

This is the right test for any column backed by a fixed, known set of states — an order status, a subscription tier, a shipping method. If the source system ever introduces a new status value that this model's downstream logic doesn't yet account for (a common real occurrence when an upstream team adds a new enum value without telling anyone), this test starts failing immediately rather than the new value silently falling through uncategorized in a dashboard.

relationships — a foreign-key-style referential integrity check

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

This asserts that every customer_id value in fct_orders also exists as a customer_id value in dim_customers — the referential-integrity guarantee a traditional relational database would enforce with an actual foreign key constraint, re-created here as a dbt test because most analytical warehouses don't enforce foreign keys at the database level at all.

accepted_range and unique_combination_of_columns — the well-known package extensions

The four generic tests covered above ship with dbt itself and need no extra installation. Beyond them, the widely used dbt_utils package (a common addition to almost every real project, covered in a later module on packages) adds several more generic tests that fill common gaps — most notably accepted_range, for asserting a numeric column falls within a min/max bound, and unique_combination_of_columns, for asserting that a combination of several columns together is unique even though no single one of them is.

models:
  - name: fct_orders
    columns:
      - name: total_amount
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 100000

  - name: fct_order_line_items
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_item_id

These are worth knowing about even before covering the packages module in depth, becauseunique_combination_of_columns in particular is the standard way to express a composite uniqueness assertion — the built-in unique test only ever checks a single column, and a line-item level fact table's real primary key is almost always a combination of two or more columns rather than one.

Every Generic Test Is Just a Parameterized SQL Query

The YAML syntax in Part 02 can make generic tests feel like a special declarative feature disconnected from SQL. They are not. Under the hood, each one is a Jinja macro that compiles down to an ordinary SELECT statement, and dbt runs that statement and checks whether it returned any rows. Understanding this demystifies testing entirely — there is no magic, just SQL you would otherwise have had to write by hand, generated for you from a couple of YAML lines.

not_null is the clearest example: it is, quite literally,SELECT * FROM model WHERE column IS NULL. If that query returns zero rows, no column value was null, and the test passes. If it returns any rows, those are the exact rows with a null value, and the test fails.

-- tests:
--   - not_null
-- applied to fct_orders.customer_id compiles to approximately:

select *
from analytics.fct_orders
where customer_id is null

-- Zero rows returned -> test PASSES
-- Any rows returned  -> test FAILS, and each returned row is a
--                        concrete example of a record with a null customer_id

-- tests:
--   - unique
-- applied to fct_orders.order_id compiles to approximately:

select order_id
from analytics.fct_orders
where order_id is not null
group by order_id
having count(*) > 1

-- Zero rows returned -> every order_id appears at most once -> PASSES
-- Any rows returned  -> those order_id values appear more than once -> FAILS

-- tests:
--   - accepted_values:
--       values: ['placed', 'shipped', 'delivered', 'cancelled', 'refunded']
-- applied to fct_orders.order_status compiles to approximately:

select order_status
from analytics.fct_orders
where order_status not in ('placed', 'shipped', 'delivered', 'cancelled', 'refunded')

-- Any row returned is a value that snuck outside the accepted list

-- tests:
--   - relationships:
--       to: ref('dim_customers')
--       field: customer_id
-- applied to fct_orders.customer_id compiles to approximately:

select fct_orders.customer_id
from analytics.fct_orders
left join analytics.dim_customers
  on fct_orders.customer_id = dim_customers.customer_id
where fct_orders.customer_id is not null
  and dim_customers.customer_id is null

-- Any row returned is a customer_id in fct_orders with no match in dim_customers

Once this clicks, a generic test stops looking like a special dbt-only concept and starts looking like exactly what it is: a SQL query you would have written anyway to sanity-check your data, wrapped in a small amount of Jinja so it can be parameterized by column name and reused across every model in the project without retyping the query each time.

severity — not every failure needs to block a build

Every generic test also accepts a severity config, either error (the default) or warn. An error-severity test that fails causesdbt build to stop downstream models from building on top of it, exactly as covered in Part 06. A warn-severity test that fails is reported clearly in the run output but does not block anything downstream — useful for a check that is genuinely worth surfacing to a human but is not, on its own, severe enough to halt a production pipeline.

models:
  - name: fct_orders
    columns:
      - name: shipping_address
        tests:
          - not_null:
              config:
                severity: warn
                # a missing shipping address is worth flagging,
                # but should not block fct_orders itself, or
                # anything downstream, from building

A useful default heuristic: error for anything a broken downstream model or dashboard genuinely cannot tolerate — a duplicate primary key, a broken foreign key relationship — and warn for a data quality signal that is worth a human's attention but does not, by itself, invalidate everything built on top of the model.

Singular Tests: One-Off SQL for Rules That Don't Generalize

Not every business rule fits the generic-test mold of "check one column against one condition, reusable everywhere." Some assertions are specific to one model and one rule — "no order should ever have a negative total," "a subscription's end date should never be before its start date." These are singular tests: plain .sql files placed directly in the project'stests/ directory, each one a self-contained query that follows the exact same contract as a generic test — if it returns any rows, the test fails.

-- A singular test: no filename-specific YAML wiring needed.
-- dbt discovers every .sql file in tests/ automatically and
-- runs it as a test. Failing means "this returned rows."

select
    order_id,
    total_amount
from {{ ref('fct_orders') }}
where total_amount < 0

There is no YAML required for a singular test at all — dbt automatically picks up every.sql file inside tests/ and treats it as a test named after the file. This one is named assert_no_negative_order_totals, and it will appear under that name in dbt test output.

-- Business rule specific to fct_subscriptions: end_date must never
-- be earlier than start_date. This rule doesn't generalize to any
-- other model in the project, so a singular test is the right fit
-- rather than trying to force it into a generic test.

select
    subscription_id,
    start_date,
    end_date
from {{ ref('fct_subscriptions') }}
where end_date < start_date

A cross-model consistency check — a case singular tests handle well

Singular tests are also the natural fit for assertions that span more than one model — a comparison a generic test's single-model, single-column shape cannot express at all. A common real example: asserting that a fact table's total revenue for a period matches an independently computed total from a separate finance-reported summary table, catching a transformation bug that would never show up as a null, a duplicate, or an out-of-range value within either table alone.

-- Cross-model consistency check: dbt-computed daily revenue must
-- match the independently maintained finance summary table, within
-- a small rounding tolerance.

with dbt_computed as (
    select
        order_date,
        sum(total_amount) as dbt_revenue
    from {{ ref('fct_orders') }}
    group by 1
),

finance_reported as (
    select
        report_date as order_date,
        reported_revenue
    from {{ source('finance', 'daily_revenue_summary') }}
)

select
    dbt_computed.order_date,
    dbt_computed.dbt_revenue,
    finance_reported.reported_revenue,
    abs(dbt_computed.dbt_revenue - finance_reported.reported_revenue) as discrepancy
from dbt_computed
join finance_reported using (order_date)
where abs(dbt_computed.dbt_revenue - finance_reported.reported_revenue) > 1.00

No generic test could express this rule declaratively — it needs to join two entirely different models together and compare an aggregate across both, which is exactly the kind of one-off, cross-model logic singular tests exist for. This is also a good illustration of why singular tests are not a lesser or fallback option compared to generic tests — some genuinely important business rules can only be expressed this way.

Building Your Own Reusable Generic Test as a Macro

The four built-in generic tests cover the most common structural checks, but real projects regularly need a reusable assertion the built-ins don't cover — "this column must always be positive," "this timestamp column must never be in the future." When the same rule needs to apply to more than one column or model, writing it as a custom generic test avoids copy-pasting the same singular-test SQL over and over with only the column name changed.

A custom generic test is a macro following the naming convention test_<name>, placed in tests/generic/. It takes two implicit arguments provided by dbt for every generic test — model (the relation the test is applied to) andcolumn_name (the specific column, when the test is applied at the column level) — plus any additional parameters you define.

{% test positive_value(model, column_name) %}

select
    {{ column_name }}
from {{ model }}
where {{ column_name }} <= 0

{% endtest %}

Once this macro exists, positive_value can be applied to any column in any model, exactly like a built-in generic test:

models:
  - name: fct_orders
    columns:
      - name: total_amount
        tests:
          - positive_value
  - name: fct_payments
    columns:
      - name: amount_paid
        tests:
          - positive_value

The same macro is reused across two different models and columns with zero duplication of the underlying SQL — precisely the payoff generic tests are built for. A more advanced custom generic test can accept additional configuration parameters beyond the implicitmodel and column_name, the same way accepted_valuesaccepts a values: list.

{% test value_within_range(model, column_name, min_value, max_value) %}

select
    {{ column_name }}
from {{ model }}
where {{ column_name }} < {{ min_value }}
   or {{ column_name }} > {{ max_value }}

{% endtest %}

models:
  - name: stg_reviews
    columns:
      - name: star_rating
        tests:
          - value_within_range:
              min_value: 1
              max_value: 5

Custom generic tests can reference other models too, not just the current column

The model and column_name arguments are just Jinja variables inside the macro — the query body can do anything a normal dbt model's SQL can do, including joining out to other tables via ref(). A common real pattern is a custom generic test checking a column's value against an aggregate computed from a separate model entirely, something none of the four built-in generic tests can express.

{% test not_exceeding_daily_average(model, column_name, factor) %}

with stats as (
    select avg({{ column_name }}) as avg_value
    from {{ model }}
)

select
    {{ column_name }}
from {{ model }}, stats
where {{ column_name }} > stats.avg_value * {{ factor }}

{% endtest %}

models:
  - name: fct_orders
    columns:
      - name: total_amount
        tests:
          - not_exceeding_daily_average:
              factor: 10

This kind of statistical outlier check is a genuinely useful complement to the four built-in structural tests — it does not catch a broken key or a null value, but it does catch a plausible but suspicious value, like an order total that is off by a decimal-place error upstream, that would otherwise sail through every structural test cleanly.

dbt test, dbt build, and Scoping to One Model

dbt test runs every test defined in the project — every generic test declared in YAML and every singular test file in tests/ — and reports a pass/fail result for each one. This is the command a CI pipeline typically runs after dbt run to validate that everything just built is actually trustworthy.

dbt test

Running 14 tests
PASS unique_fct_orders_order_id ................................ [PASS in 0.42s]
PASS not_null_fct_orders_order_id .............................. [PASS in 0.31s]
PASS not_null_fct_orders_customer_id ............................ [PASS in 0.29s]
FAIL relationships_fct_orders_customer_id__customer_id__ref_dim_customers_
  Got 3 results, configured to fail if != 0 ....................... [FAIL 3 in 0.55s]
PASS accepted_values_fct_orders_order_status .................... [PASS in 0.38s]
...
Done. PASS=13 WARN=0 ERROR=0 FAIL=1 TOTAL=14

dbt test --select model_name scopes the run to only the tests attached to one specific model, which is useful while actively developing or debugging a single model instead of waiting for the entire project's test suite to run.

dbt test --select fct_orders

dbt build — models and their tests together, in dependency order

dbt run builds models. dbt test tests them. dbt build does both together, and — critically — in dependency order: it builds a model, immediately tests it, and only proceeds to build a downstream model if its upstream dependency's tests passed. This matters because a test failure on an upstream model should stop downstream models from building on top of data that has already been shown to be wrong.

# dbt run, then dbt test, as two separate steps:
# 1. dbt run builds EVERY model, including downstream ones,
#    even if an upstream model's data is actually broken
# 2. dbt test runs afterward and reports the failure --
#    but downstream models already built on top of the bad data

# dbt build, as one command:
# 1. builds stg_orders
# 2. tests stg_orders -- if this fails, dbt build stops here
#    for everything that depends on stg_orders
# 3. only if stg_orders' tests pass, proceeds to build fct_orders
# 4. tests fct_orders
# 5. only if fct_orders' tests pass, proceeds to models that ref() it

dbt build

This is the meaningful practical difference: dbt run followed by dbt testwill happily build every downstream model on top of upstream data that later turns out to have failed a test, because the test doesn't run until everything is already built. dbt buildcatches the failure at the point it happens and prevents anything downstream from compounding on bad data in the same invocation.

store_failures — keeping a queryable record of exactly what failed

By default, a failed test's result set is only shown transiently in the run's console output — it is not persisted anywhere. store_failures tells dbt to additionally write the failing rows to a real table in the warehouse, which is invaluable for a test that fails intermittently or whose failing rows are too numerous to usefully read from console output.

models:
  - name: fct_orders
    columns:
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                store_failures: true
                schema: dbt_test_failures

FAIL relationships_fct_orders_customer_id__customer_id__ref_dim_customers_
  Got 47 results, configured to fail if != 0 ...................... [FAIL 47 in 1.2s]

  -- the 47 failing customer_id values are now persisted at:
  -- analytics.dbt_test_failures.relationships_fct_orders_customer_id__...
  -- queryable directly with ordinary SQL, no need to re-run the test

store_failures can be set project-wide in dbt_project.yml for every test, or scoped to just the tests worth the extra storage cost — typically the tests on the highest-traffic mart models, where a failure needs to be investigated by more than one person and a persisted, queryable record saves everyone from re-running the test just to see what broke.

Testing Near the Source vs Testing at the Mart

Tests are not free to write or free to run, and not every test belongs at every layer of the DAG. Where a test lives changes what kind of problem it catches, and how early it catches it.

Source and staging-level tests — catching bad raw data early

Tests placed on sources and staging models catch structural problems in raw data as close to its origin as possible — a source table's primary key becoming non-unique, a required field starting to arrive null, a foreign key from an upstream system pointing at something that no longer exists. Catching this here means the problem is flagged before a single downstream model has had a chance to build on top of it.

sources:
  - name: raw_ecommerce
    tables:
      - name: orders
        columns:
          - name: order_id
            tests:
              - unique
              - not_null

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

Mart-level tests — catching business-logic bugs before they reach a dashboard

Tests placed on mart-level models — the fact and dimension tables that dashboards and reports actually query — catch a different category of problem: bugs introduced by the transformation logic itself, not by the raw source data. A join that fans out unexpectedly, an aggregation that double-counts a row, a business rule that was implemented slightly wrong — none of these would show up as a problem in the raw source data; they only appear once the model's own logic runs.

models:
  - name: fct_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: order_status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'delivered', 'cancelled', 'refunded']

The unique test on order_id at the staging layer and the same test again at the mart layer are not redundant — they catch different failure modes. A duplicate at staging means the raw source itself has a data quality problem. A duplicate that only appears at the mart layer, despite staging being clean, means a join inside the transformation logic between staging and the mart fanned out and created duplicates that didn't exist in the source at all.

Intermediate-layer tests — a middle ground, used more sparingly

Between staging and marts, many projects have an intermediate layer of models — joins and aggregations that aren't yet the final, dashboard-facing fact or dimension table, but are more than a thin staging rename. Testing at this layer is usually more selective than at staging or marts: rather than testing every column, teams typically test only the specific transformation this intermediate model is responsible for, to pinpoint exactly which join or aggregation step introduced a problem when a downstream mart-level test eventually fails.

models:
  - name: int_orders_joined_to_items
    columns:
      - name: order_id
        tests:
          - unique
          # Only testing uniqueness here, specifically because this is
          # the exact model where an order_items join could fan out an
          # order_id into multiple rows. Not every column needs a test
          # at every layer -- test where a specific risk actually lives.

This targeted approach avoids two failure modes at once: testing nothing at all in the middle of the DAG (which means a fan-out bug is only caught once it reaches the mart, with less precision about which step caused it), and testing every column at every layer (which multiplies the number of tests to maintain without a proportional increase in how quickly a real bug gets localized).

Five Misconceptions About dbt Testing

Three Test Failures That Caught Real Bugs Before They Reached a Dashboard

5 Interview Questions — With Complete Answers

Five Mistakes Engineers Make Writing Their First dbt Tests

dbt Testing Errors — And Exactly Why They Happen