Implementing dbt Tests Study Guide — dbt Analytics Engineering Certification

Testing and Documentation

This domain covers how dbt ensures data quality through tests and how teams communicate model intent through documentation. It represents 24% of the exam, making it the second-largest domain. Questions test precise syntax knowledge, configuration behavior, threshold defaults, and the distinction between test types. Expect scenario-based questions where you must choose the correct YAML key, severity setting, or documentation approach for a given requirement.

Generic Tests: unique, not_null, accepted_values, relationships

Generic tests (called "built-in" or "schema tests" in older dbt versions) are reusable test definitions applied to columns or models via YAML configuration. As of dbt 1.6+, the canonical term is generic tests.

The Four Built-In Generic Tests

Configuration Syntax

models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'returned']
      - name: customer_id
        tests:
          - relationships:
              to: ref('customers')
              field: id

Key details:

• accepted_values uses quote: true by default for string comparisons; set quote: false for numeric values.
• relationships test checks that non-null values in the child column exist in the parent. Null values in the child column pass by default.
• unique ignores null values — multiple nulls do not cause failure.
• Tests can be applied at the model level or column level in YAML.

Common Traps

• relationships and nulls: The test does not flag null values in the FK column as failures. Exam distractors suggest nulls cause failures — they do not.
• accepted_values and quote: Forgetting quote: false for integer columns is a classic misconfiguration trap.
• unique and nulls: Multiple null values in a "unique" column do not trigger a failure. Distractor answers claim they do.
• Test location: Generic tests live in .yml files (schema files), not .sql files. Confusing this with singular tests is a common distractor.

Singular Tests and Custom Generic Tests

Singular Tests

A singular test is a single .sql file placed in the tests/ directory. It contains a SELECT statement; if the query returns any rows, the test fails. If it returns zero rows, it passes.

-- tests/assert_total_revenue_is_positive.sql
select
    order_id,
    total_revenue
from {{ ref('orders') }}
where total_revenue < 0

• Singular tests are model-specific and not reusable across models.
• File name becomes the test name in run results.
• They support Jinja and ref()/source() macros.

Custom Generic Tests

A custom generic test is a reusable test defined as a macro in the tests/generic/ directory (or macros/ directory). It uses a specific macro signature:

-- tests/generic/is_positive.sql
{% test is_positive(model, column_name) %}
select
    {{ column_name }}
from {{ model }}
where {{ column_name }} < 0
{% endtest %}

Once defined, it is referenced in YAML exactly like built-in generic tests:

columns:
  - name: revenue
    tests:
      - is_positive

Key differences:

Common Traps

• Macro signature: The first two parameters of a custom generic test must be model and column_name (in that order). Extra parameters are added after these two.
• Return behavior: Both singular and custom generic tests fail on non-empty result sets. Distractor answers suggest the opposite.
• File location: Custom generic tests placed in tests/ (not tests/generic/) are treated as singular tests, not generic ones.
• {% test %} vs {% macro %}: Custom generic tests use {% test %} blocks, not {% macro %} blocks.