dbt tests: generic, singular, custom generic and unit tests

1.Every data test is a query for failing rows

dbt has two families of tests, and they answer different questions. Data tests are assertions about resources dbt has already built or reads from: models, sources, seeds and snapshots. They ask whether the data is right. Unit tests check a model's SQL logic against small, hand-written inputs before the model is built. They ask whether the code is right. Most of this page is about data tests, which come in three forms (built-in generic, singular and custom generic). Unit tests come last.

A data test works the opposite way from what many newcomers expect. It is a select statement that hunts for the records that disprove your claim. A uniqueness test selects duplicates, and a not-null test selects nulls. When dbt test runs, a query that comes back empty is a pass. Every returned row is a failure. Once you hold onto this, everything else on the page follows from it. A singular test is a hand-written query for bad rows, and a generic test is the same kind of query with the model and column left as parameters.

The YAML key changed when unit tests arrived. What used to be tests: is now data_tests:, renamed in dbt v1.8 so the two families can be told apart. The old tests: key still works as an alias. A single resource, such as one model, cannot use both tests: and data_tests: at the same time.

{% test not_null(model, column_name) %}

    select *
    from {{ model }}
    where {{ column_name }} is  ? 

{% endtest %}

Sources12

2.The four built-in generic tests on models and sources

dbt ships with four generic tests: unique, not_null, accepted_values and relationships. They are called generic because each one is a parameterized query. You attach it by name in a .yml file, and dbt fills in the model and column for you. The same unique definition can therefore guard a hundred columns across fifty models. That reuse is why generic tests should make up most of a project's test suite.

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
                values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('customers')
                field: id

Two kinds of setting sit next to a test name, and they go in different places. Inputs to the test's own query, such as values, to and field, go under arguments:. That nesting is available from v1.10.5, and older versions put these inputs at the top level. Framework options such as severity, where and store_failures go under config:. unique and not_null need no inputs, because dbt already knows the model and column from where the test sits in the YAML.

Generic tests are not limited to models. The same data_tests: property works on sources, seeds and snapshots, at both table level and column level. Putting not_null or unique on a source column catches a broken raw table before any model reads from it. Analyses are the one resource type where it does not work. Deciding which assumptions are worth encoding is covered in [testing assumptions for models and sources](lesson:5.2).

sources:
  - name: <source_name>
    tables:
    - name: <table_name>
      data_tests:
        - <test_name>
        - <test_name>:
            arguments: # available in v1.10.5 and higher. Older versions can set the <argument_name> as the top-level property.
              <argument_name>: <argument_value>
            config:
              <test_config>: <config-value>

Sources13

3.Singular tests for one-off assertions

Some rules apply to only one model, and no reusable test captures them. A singular test is the tool for those cases. You write the exact SQL that returns the bad rows and save it as a .sql file in the tests/ directory. Each file holds one select statement and defines one test, and the file name becomes the test name. Jinja works here as it does in models, so ref and source are both available.

-- Refunds have a negative amount, so the total amount should always be >= 0.
-- Therefore return records where total_amount < 0 to make the test fail.
select
    order_id,
    sum(amount) as total_amount
from {{ ref('fct_payments') }}
group by 1
having total_amount < 0

Three details cause most broken singular tests:

- Semicolons. A trailing semicolon can make the test fail, so leave it off. - Wiring it up. You don't attach the test anywhere. dbt test picks up every file in the tests directory automatically. Listing a singular test under a model's data_tests: causes an error, because dbt does not treat it as a generic test. - Documentation. To give it a description, add a .yml file in the tests directory, such as tests/schema.yml, with a top-level data_tests: entry that names the test.

Watch for the signal that a singular test has outgrown its form. If you keep copying the same query and changing only the model or column name, the test isn't singular any more. Turn it into a generic test.

Sources1

4.Writing custom generic tests

A custom generic test is a test block that you write yourself. It is shaped like a macro and named with {% test <name>(...) %}. You can save the SQL file in either of two places. One is tests/generic/, a special subfolder of your test paths. The other is macros/, which used to be the only option and is still convenient when the test depends on complex macro logic in the same file. Before writing one, check packages such as dbt-utils and dbt-expectations, which already define many generic tests (see [dbt packages](lesson:1.7)).

The signature determines how the test is called. Every generic test takes one or both of the standard arguments, model and column_name, and dbt supplies both from the YAML context. The argument is always called model, even when the test sits on a source, seed or snapshot, and it is templated out to that resource's relation name. Any extra inputs are added to the signature after the standard ones. The built-in relationships test, for example, is defined as relationships(model, column_name, field, to), and you pass field and to under arguments: in YAML.

{% test warn_if_odd(model, column_name) %}

    {{ config(severity = 'warn') }}

    select *
    from {{ model }}
    where ({{ column_name }} % 2) = 1

{% endtest %}

A config() block inside the test definition sets defaults for every use of the test. Here, every column tested with warn_if_odd warns instead of erroring, unless one instance overrides the setting in its own .yml properties. The same mechanism lets you replace a built-in test. If you add a test block named unique to your project, dbt uses your version instead of the global one.

Custom tests can be documented too. List the test under macros: in a YAML file, with a description for the test and for each argument. Use the macro's name, which is the test name with a test_ prefix: a test block called not_empty_string is documented as test_not_empty_string.

Sources4

5.Unit tests: mocked inputs and expected output

Data tests have a timing problem: they can only check a model after it has been built, against whatever data happens to exist. A unit test turns this around. You supply a few static input rows for each upstream ref or source, along with the rows the model should produce from them. dbt runs the model's SQL against those mocks and compares the result with what you expected, all before the full model is materialized. This supports test-driven development, where the test is written before the logic.

unit_tests:
  - name: test_is_valid_email_address # this is the unique name of the test
    model: dim_customers # name of the model I'm unit testing
    given: # the mock data for your inputs
      - input: ref('stg_customers')
        rows:
         - {email: cool@example.com,     email_top_level_domain: example.com}
         - {email: cool@unknown.com,     email_top_level_domain: unknown.com}
         - {email: badgmail.com,         email_top_level_domain: gmail.com}
         - {email: missingdot@gmailcom,  email_top_level_domain: gmail.com}
      - input: ref('top_level_email_domains')
        rows:
         - {tld: example.com}
         - {tld: gmail.com}
    expect: # the expected output given the inputs above
      rows:
        - {email: cool@example.com,    is_valid_email_address: true}
        - {email: cool@unknown.com,    is_valid_email_address: false}
        - {email: badgmail.com,        is_valid_email_address: false}
        - {email: missingdot@gmailcom, is_valid_email_address: false}

Each input in given names one upstream ref or source, and expect holds the output rows. The rows only need the columns the logic touches. Here, four email addresses cover the edge cases: a missing @, a missing dot, and an unknown domain. Rows can be written in dict format (the default), csv or sql, either inline under rows or in a separate file named with fixture. Every ref and source the model uses has to appear as an input, or compilation fails with "node not found". If the model joins tables, the tables must be aliased.

Unit tests have firm limits. They work only on SQL models, and only on models in the current project. Models that use the materialized view materialization, recursive SQL or introspective queries cannot be unit tested. If a model has several versions, the unit test runs on all of them by default.

Choose what to unit test based on where bugs are likely. Good candidates include:

- Regex, date math, window functions and case when statements with many branches - Logic that has had bugs reported before - Edge cases that don't yet appear in real data - Logic you're about to refactor - High-criticality models, such as public, contracted models or models directly upstream of an exposure

A plain min() is a poor candidate, because the warehouse already tests it extensively. Since the inputs are static, running unit tests in production wastes compute, so dbt Labs recommends running them only in development and CI. dbt test --select "test_type:unit" runs them on their own. Where they fit in a pipeline is covered in [testing steps in the workflow](lesson:5.3).

Sources5

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Unit tests are tests, so their YAML belongs in the tests/ directory.Why is that wrong?

   Unit test YAML is discovered from model-paths and must live there, for example in models/. The tests/ directory is reserved for data tests. Only fixture files go in tests/fixtures/.

   Covered in Unit tests: mocked inputs and expected output

2. 2.A singular test has to be listed under the model's data_tests: before it will run.Why is that wrong?

   Singular tests in the tests directory run automatically with dbt test. Referencing one in model YAML causes an error.

   Covered in Singular tests for one-off assertions

3. 3.severity is a test input, so it goes under arguments: along with values, to and field.Why is that wrong?

   arguments: holds inputs to the test's query. severity, where and store_failures are framework options and go under config:.

   Covered in The four built-in generic tests on models and sources

4. 4.A custom generic test meant for sources needs a source argument instead of model.Why is that wrong?

   The first standard argument is always named model, whatever the resource type, and dbt templates it to the relation name.

   Covered in Writing custom generic tests