skip to content

In dbt, when would you write a singular test instead of a generic one?

level: middleimportance: should knowfreq 55%

answer

  1. one is reusable, the other is bespoke
  2. a bare .sql file in a directory
  3. no YAML entry needed for one of them
  4. tests/ folder versus a {% test %} macro

basics

~20 s

Write a dbt singular test when the assertion is one-off and specific to particular models: a plain SQL file in tests/ that selects offending rows. Generic tests are parameterised macros declared in YAML and reused across many models and columns.

solid answer

~50 s

A **singular test** in dbt is just a `.sql` file dropped into the `tests/` directory that selects the rows that should not exist — no Jinja signature, no YAML declaration. You reach for one when the rule is bespoke: a cross-model reconciliation such as "the sum of line-item amounts must equal the order header total", or a business invariant like "no shipped order may have a null ship_date". A **generic test** is the same idea wrapped in a `{% test %}` macro that takes `model` and `column_name`, so it can be attached to dozens of columns from YAML with one line each. The rule of thumb: if you would write the assertion once, make it singular; if you would write it for the fifth time, promote it to a generic test. Both should still `ref()` their models so they sit correctly in the DAG.

code

sql · 16 lines
sql
-- tests/assert_order_totals_match_line_items.sql
{{ config(severity = 'error') }}

with header as (
    select order_id, order_total
    from {{ ref('fct_orders') }}
),
lines as (
    select order_id, sum(amount) as line_total
    from {{ ref('fct_order_lines') }}
    group by 1
)
select h.order_id, h.order_total, l.line_total
from header h
join lines l using (order_id)
where abs(h.order_total - l.line_total) > 0.01

go deeper

for a junior

Know that a file in the tests/ directory is a test and that it selects the bad rows. Be able to point at the directory and explain what dbt does with what it finds there.

for a middle

Explain the trade-off crisply: bespoke and cross-model assertions go singular, repeated column rules become generic macros, and both should ref() their models so the DAG is right.

for a senior

Demonstrate judgment about when abstraction pays — a generic test used once is worse than plain SQL — and about reaching for dbt_utils before hand-rolling something the package already provides.

for a principal

Set the convention: which assertions belong in shared generic tests owned centrally, which stay local to a domain team, and how test code is reviewed so the suite stays meaningful instead of ballooning.

## Two shapes, one contract dbt has exactly two shapes of data test, and both obey the same rule — return the rows that should not exist. - **Generic** (formerly "schema tests"): a parameterised Jinja macro, declared against a model or column in a YAML file. `unique`, `not_null`, `accepted_values` and `relationships` are the built-in ones; packages and your own project add more. - **Singular** (formerly "data tests"): a plain SQL file in the `tests/` directory. Whatever it selects, dbt counts as failures. ## Writing a singular test Create `tests/assert_order_totals_match_line_items.sql`: ```sql with header as ( select order_id, order_total from {{ ref('fct_orders') }} ), lines as ( select order_id, sum(amount) as line_total from {{ ref('fct_order_lines') }} group by 1 ) select h.order_id, h.order_total, l.line_total from header h join lines l using (order_id) where abs(h.order_total - l.line_total) > 0.01 ``` That is the whole thing. No YAML entry is needed — dbt discovers every `.sql` file under the configured `test-paths` (default `tests/`) and registers it as a test node. The file name becomes the test name, which is why the convention is a descriptive `assert_*` name. Note the `{{ ref(...) }}` calls. They are not optional in spirit: they are what places the test node downstream of both models in the DAG, so `dbt build` runs it after those models are built, and `dbt test --select fct_orders+` picks it up. A singular test that hardcodes `analytics.fct_orders` will still run, but it becomes an orphan node with no dependencies and will execute at the wrong time. ## When singular is the right call - **Cross-model reconciliation.** Sums, counts or balances that must agree between two models. Generic tests are column-shaped; this is relationship-shaped across whole tables. - **Business invariants with real logic.** "A subscription cannot have a cancel date before its start date, unless the status is 'migrated'." Encoding the exception in a macro signature is worse than just writing the SQL. - **A regression guard.** A bug shipped once; you write the exact query that would have caught it and freeze it as a test. - **Anything you will write once.** Abstraction has a cost. One clear SQL file beats a macro with three keyword arguments used in a single place. ## When to promote to generic The moment the same assertion appears for the third or fourth model, convert it. A generic test takes `model` (and `column_name` for column-level tests) plus any extra keyword arguments, lives in `tests/generic/` or `macros/`, and is then a one-line YAML declaration per use. Before writing your own, check `dbt_utils` — it already ships `unique_combination_of_columns`, `expression_is_true`, `accepted_range`, `not_null_proportion` and others that cover most of what teams hand-roll. ## Selecting and configuring them Both kinds are selectable. `dbt test --select test_type:singular` runs only the hand-written ones; `test_type:generic` runs the declared ones. A singular test can also carry configs — either through a `{{ config(severity='warn') }}` block at the top of the SQL file, or by declaring it in YAML under a `data_tests:` entry, or project-wide under the `tests:` (dbt 1.8+: `data_tests:`) key in `dbt_project.yml`. ```sql {{ config(severity = 'warn', tags = ['finance']) }} select ... ``` ## The naming history worth knowing Older dbt documentation and older codebases call generic tests "schema tests" and singular tests "data tests". Then dbt 1.8 introduced **unit tests** — a genuinely different thing, which asserts a model's SQL logic against fixture inputs rather than checking rows in the warehouse — and the umbrella term for both generic and singular became "data tests", with `data_tests:` as the preferred YAML key. If an interviewer says "data test", clarify which era they mean; getting the vocabulary shift right is itself a signal that you have used the tool recently.

  • Why should a singular test use ref() rather than the physical table name?
    `ref()` registers the model as a parent of the test node in the DAG. That is what makes `dbt build` run the test after those models and lets graph selectors such as `--select fct_orders+` pick it up. A hardcoded relation name still executes, but as a dependency-free orphan that may run before the data it checks exists.
  • How do you set severity on a singular test with no YAML entry?
    Put a `{{ config(severity='warn') }}` block at the top of the test's SQL file — the same config block models use. You can alternatively declare the test in YAML to attach configs there, or set defaults for all tests under the tests/data_tests key in dbt_project.yml.
  • When would you convert a singular test into a generic one?
    When the same assertion is copy-pasted onto a third or fourth model. Wrapping it in a `{% test %}` macro that accepts model and column arguments turns each future use into one YAML line, and fixing the logic once fixes it everywhere. Below that threshold, the plain SQL file is clearer.

saying these in an interview costs you the question

  • Thinks singular tests must also be declared in YAML
  • Says a singular test returns true when the data is valid
  • Hardcodes table names instead of ref() in a test file
  • Claims generic tests cannot take extra arguments
  • Confuses singular tests with dbt 1.8 unit tests

context

resolving links…