Custom Data Tests

Analogy categoryAnalogies

Singular Tests: One-Off Business Logic in SQL

A singular test is just a .sql file inside the tests/ directory containing a SELECT statement — there's no special syntax, and the same rule applies as with schema tests: if the query returns any rows, the test fails. This is the right tool for a one-off business rule, like asserting that no order's discount_amount ever exceeds its subtotal, that doesn't need to be reused across other models or columns.

🏏

Cricket analogy: This is like a team analyst writing a one-off query to check whether any player's declared batting average in a scorecard exceeds their actual runs divided by dismissals — a specific check for a specific inconsistency.

sql

-- tests/assert_discount_not_greater_than_subtotal.sql
select
    order_id,
    subtotal,
    discount_amount
from {{ ref('fct_orders') }}
where discount_amount > subtotal

Custom Generic Tests: Reusable Test Macros

When you find yourself writing the same singular test logic against multiple columns, promote it to a custom generic test — a macro defined with the {% test %} block (or the older generic_test materialization) that accepts standardized arguments like model and column_name, plus any extra parameters you define. Once defined in macros/, it's referenced from schema.yml exactly like unique or not_null, giving your team a reusable, parameterized assertion instead of copy-pasted SQL.

🏏

Cricket analogy: This is like a coaching manual codifying a specific fielding drill, once perfected for the slip cordon, into a standard exercise that any fielding coach can now apply to any position with adjusted parameters.

sql

-- macros/test_positive_value.sql
{% test positive_value(model, column_name) %}

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

{% endtest %}

yaml

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: subtotal
        tests:
          - positive_value

Scoping Tests with where and limit

Both singular and generic tests accept a config() block that supports where, which appends a WHERE clause to the compiled test query so you can exclude known, accepted exceptions (like test/demo rows) rather than weakening the underlying assertion. limit caps how many failing rows get returned or stored, which keeps store_failures tables from growing unbounded on a test that's expected to have thousands of known violations during an incremental cleanup effort.

🏏

Cricket analogy: This is like excluding rain-abandoned matches from a bowling-average calculation using a filter, rather than changing the definition of a bowling average itself.

Use dbt test --select test_type:singular or test_type:generic to run only one category of test, which is handy when you want to smoke-test custom business rules separately from the built-in column checks during a large refactor.

Overusing where filters on tests to silence failures is a common anti-pattern — every filter should map to a documented, intentional exception, not a quiet way to make a genuinely broken pipeline pass CI. Track filtered exceptions in a comment or a linked ticket so they get revisited.

Singular tests are standalone SQL files in tests/ — any returned row is a failure, with no special macro syntax required.
Custom generic tests are macros defined with {% test %}, reusable across columns/models via schema.yml just like built-in tests.
Generic test macros accept model and column_name automatically, plus any extra parameters you define.
config(where=...) filters out known, documented exceptions without weakening the underlying assertion logic.
config(limit=...) caps how many failing rows are returned or stored, useful during large-scale cleanup efforts.
dbt test --select test_type:singular|generic lets you run one category of test at a time.
Avoid using where filters as a silent way to hide broken data — document every exception.