dbt Custom Generic Tests: Building Reusable Data Quality Checks

A generic test is a reusable "validation macro" that you call from YAML. Build one and you can apply it across columns and models, which is a direct path to standardizing your data quality rules.

This article walks through the full picture: a minimal custom test, multi-relation patterns, the execution flow and storage of results, and best practices. We highlight the terminology distinctions and configuration nuances that exam questions love to target.

Custom Generic Test Basics and How They Differ from Built-in Tests

A dbt generic test is defined as a Jinja macro that produces SQL returning "only the failing rows" via SELECT. The defining trait is that you call it from YAML (schema.yml) with arguments, using the same mechanism as not_null or unique — you can build your own with that exact pattern.

By contrast, a singular test is a one-off SQL file under tests/. It's suited for one-shot checks rather than reuse. Exams frequently test the distinction between these two and how generic tests handle arguments and config.

• Defined as a macro (Jinja), but executed via dbt test's test materialization
• Generic tests are applied to models/columns from YAML; singular tests run as standalone SQL files
• The return value is always "failing rows only". 0 rows passes; 1+ rows fails (warn/error depending on severity)

Built-in not_null / unique tests called from schema.yml

models:\n  - name: fct_orders\n    columns:\n      - name: order_id\n        tests:\n          - not_null\n          - unique

Minimal Implementation: Build a Range Check Called accepted_range

Let's start by building accepted_range to verify "the value falls between a min and max". The key insight for a generic test macro is that it only needs to generate a SELECT that returns the failing rows.

Match your project's conventions for file placement. Putting it at tests/generic/accepted_range.sql makes it easy to spot, and keeps things tidy when you package the test for distribution.

• Return value is failing rows only (0 rows when passing)
• Arguments accept model (Relation), column_name (string), and any extra parameters
• Reference the column with {{ column_name }} and the relation with {{ model }}

Minimal example: tests/generic/accepted_range.sql and the schema.yml call

{% test accepted_range(model, column_name, min_value, max_value, inclusive=True) %}\nselect\n  *\nfrom {{ model }}\nwhere\n  {% if inclusive %}\n    ({{ column_name }} < {{ min_value }} or {{ column_name }} > {{ max_value }})\n  {% else %}\n    ({{ column_name }} <= {{ min_value }} or {{ column_name }} >= {{ max_value }})\n  {% endif %}\n{% endtest %}\n\n# Schema.yml invocation example\nmodels:\n  - name: fct_orders\n    columns:\n      - name: amount\n        tests:\n          - accepted_range:\n              min_value: 0\n              max_value: 100000\n              inclusive: true

Splitting Arguments and Config Correctly

Pass the "variable logic" of a test via arguments, and control "runtime behavior" (warning level, storage, row filtering, etc.) via config. The YAML side can override config on a per-test-instance basis.

The configs you reach for most often are severity (warn/error), where (pre-filter target rows), limit (cap stored or logged volume), and store_failures (persist failing rows as a table). Rather than baking where conditions into the test SQL, specifying them via config keeps the test more reusable.

• Arguments change the macro's logic (e.g., min_value, max_value)
• Config controls peripheral evaluation/runtime behavior (e.g., severity, where, limit, store_failures)
• Config can be set independently per call. Shared defaults can also be overridden from dbt_project.yml

YAML test config example: warn-level severity, target filtering, and persisting failed rows

models:\n  - name: fct_orders\n    columns:\n      - name: amount\n        tests:\n          - accepted_range:\n              min_value: 0\n              max_value: 100000\n              config:\n                severity: warn\n                where: "order_status != 'CANCELLED'"\n                limit: 100\n                store_failures: true

Writing Tests That Receive Multiple Relations

Like relationships, you can implement generic tests that take an additional referenced table. YAML passes ref() or source() into to:, and the test macro joins on it to detect orphaned keys.

On exams, expect questions on the fact that model is passed as a Relation and can be referenced as {{ model }} directly, and that to is likewise received as a Relation. They are not strings, so there's no need to call ref() again inside the macro.

• Both model and to are Relations. Expand them directly in SQL as {{ model }} and {{ to }}
• For foreign-key validation, mind NULL handling (NULLs are typically skipped)
• Absorb dialect differences (case sensitivity, quoting) at the column-name/identifier layer

An exists_in test that takes 2 relations, plus the YAML

{% test exists_in(model, column_name, to, field) %}\nselect\n  src.{{ column_name }} as fk_value\nfrom {{ model }} as src\nleft join {{ to }} as tgt\n  on src.{{ column_name }} = tgt.{{ field }}\nwhere src.{{ column_name }} is not null\n  and tgt.{{ field }} is null\n{% endtest %}\n\n# Schema.yml invocation (column-level test)\nmodels:\n  - name: fct_orders\n    columns:\n      - name: customer_id\n        tests:\n          - exists_in:\n              to: ref('dim_customers')\n              field: id\n              config:\n                severity: error

Execution Flow, Performance, and Storing Failed Rows

dbt test compiles each generic test macro and ships a SELECT that returns failing rows to the target warehouse. 0 rows passes; 1+ rows fails. With severity=warn it logs a warning; with error it fails the job.

Setting store_failures: true persists failing rows as a table in a dedicated schema (by default a tests schema under the target schema). Combining where with limit is effective for both narrowing scope and capping how much you store.

• Column tests over huge datasets are expensive. Narrow with where, or stage runs with scheduling
• Use selective dbt test -s model_name: to limit scope and speed up the dev iteration loop
• Tune concurrency against warehouse quotas and lock contention; dependency order is handled automatically

Generic test execution flow

Commands for running and persisting failures

# Test by selecting a model\ndbt test -s fct_orders\n\n# Select by test name (you can include package names)\ndbt test -s test_type:accepted_range\n\n# Run with failed-row storage\ndbt test -s fct_orders --store-failures

Best Practices and Pitfalls

Names should be noun phrases that convey "what is being validated" rather than verbs, and arguments should be kept to a minimum. When you need dialect-specific functions inside a test, absorb the differences with adapter.dispatch.

Column-name quoting and case sensitivity are common pain points across dialects. Use adapter.quote where appropriate, or stick to lowercase ASCII identifiers with underscores. While NULL semantics rarely differ between RDBMSs, FK validation typically excludes NULL by design.

• Keep tests deterministic (avoid non-deterministic functions and dependencies on current time)
• Use where in YAML for filtering, keeping the macro itself generic
• For external distribution, adopt package namespaces and semantic versioning

adapter.dispatch template for handling dialect differences (used inside a test)

{% macro cast_to_numeric(expr) %}\n  {{ adapter.dispatch('cast_to_numeric', 'my_project')(expr) }}\n{% endmacro %}\n\n{% macro my_project__cast_to_numeric(expr) %}\n  cast({{ expr }} as numeric)\n{% endmacro %}\n\n{% macro snowflake__cast_to_numeric(expr) %}\n  try_to_number({{ expr }})\n{% endmacro %}\n\n# Usage inside a test\n# where {{ cast_to_numeric(column_name) }} >= 0

Check Your Understanding

Frequently Asked Questions