Tests — Built-in and Custom

This lesson on Tests — Built-in and Custom is hands-on and example-driven. You will implement automated data quality testing across your dbt models and raw sources using built-in generic tests and custom singular SQL tests. You will master writing inverted failure queries, applying schema-level assertions in YAML, and debugging failed tests using compiled SQL.

What You'll Be Able To Do

• Write singular SQL tests that isolate and return invalid data records.
• Configure generic tests including unique, not_null, and accepted_values within YAML schema files.
• Attach generic data integrity constraints directly to raw data source definitions.
• Execute targeted test suites using the dbt test CLI command and model selection flags.
• Debug failing tests by extracting and running dbt's compiled SQL queries against the database.

Detailed Concept Walkthrough

1. Singular Data Tests and Inverted Failure Logic

Singular tests are custom SQL queries stored in the tests directory that assert business-specific data validation rules. They operate on an inverted failure mechanism where returning any records indicates a test failure.

• Mechanism: You write a standalone SELECT query designed to identify and return only records that violate a business rule.
• Under the Hood: dbt runs the query and assesses the row count; zero returned rows means the test passes, while one or more rows causes a test failure.
• Best Practice: Name singular test files descriptively based on the rule being asserted, such as assert_stage_payments_amount_is_positive.sql.

-- tests/assert_stage_payments_amount_is_positive.sql
-- Fails if any payment record contains a negative amount
select
    payment_id,
    amount
from {{ ref('stage_payment') }}
where amount < 0

Key Takeaway: Singular tests must return zero rows to pass, requiring query logic that selects only non-compliant records.

2. Built-in Generic Schema Tests

Generic tests are pre-built, parameterized assertion macros defined declaratively inside YAML configuration files for models and columns.

• Mechanism: Built-in checks such as unique, not_null, accepted_values, and relationships are added directly under the tests key for a column.
• Under the Hood: dbt compiles these YAML declarations into SQL queries backed by Jinja macros and executes them against your database during test runs.
• Syntax Rule: Tests requiring arguments, such as accepted_values, use nested YAML key-value pairs to pass acceptable parameter lists.

version: 2

models:
  - name: stage_customer
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

Key Takeaway: Generic tests allow you to enforce common data integrity constraints declaratively without writing repetitive custom SQL.

3. Applying Tests to Data Sources

Generic tests can be attached directly to raw data sources in YAML to catch data corruption before running transformation models.

• Mechanism: Define sources in dedicated YAML files and attach column-level generic tests within the tables block under the sources root.
• Execution Flow: Testing raw sources prevents invalid upstream data from polluting downstream staging and dimensional marts.
• Syntax Rule: Downstream staging models query defined sources using the {{ source('source_name', 'table_name') }} macro instead of raw table names.

version: 2

sources:
  - name: stripe
    tables:
      - name: payment
        columns:
          - name: id
            tests:
              - unique
              - not_null

Key Takeaway: Testing source tables validates raw data at ingestion and prevents errors from propagating across the transformation DAG.

4. CLI Test Execution and Failure Debugging

The dbt CLI provides granular test execution selectors and compiles failure queries for rapid root-cause analysis.

• Mechanism: Run dbt test to execute the full suite, or provide the -s selector to test specific models, singular tests, or sources.
• Under the Hood: When a test fails, dbt logs the failure count and writes the compiled test SQL into the target/compiled/ directory.
• Best Practice: Copy the compiled SQL path from the CLI failure log and execute it interactively in your SQL editor to inspect invalid rows.

# Run all tests in the project
dbt test

# Run only tests for stage_payment
dbt test -s stage_payment

# Run tests for a specific source
dbt test -s source:stripe

Key Takeaway: Debug failed tests by running dbt's compiled test SQL directly in your warehouse to inspect the offending rows.

Topics Covered in Tests — Built-in and Custom

1. Core Test Categories (0:00 - 2:44) — Differentiate between declarative generic YAML tests and custom singular SQL tests.
2. Configuring Data Sources (2:44 - 3:40) — Define raw source schemas in YAML and update staging models to use the source macro.
3. Creating Singular Tests (3:40 - 5:07) — Author a custom SQL test checking for negative payment amounts using inverted failure logic.
4. Applying Generic Tests (5:07 - 5:57) — Add unique and not_null constraints to customer ID columns in schema YAML files.
5. Accepted Values and Debugging (5:57 - 7:38) — Configure accepted_values tests on status columns and debug failures using compiled SQL.
6. Testing Data Sources (7:38 - 8:04) — Extend declarative generic tests directly to raw source table configurations in YAML.

dbt Cheat Sheet

• dbt test — Executes all generic and singular tests

  dbt test
  

• dbt test -s <model> — Executes tests for a specific model

  dbt test -s stage_payment
  

• unique — Asserts column has no duplicate values

  tests:
    - unique
  

• not_null — Asserts column contains no null values

  tests:
    - not_null
  

• accepted_values — Asserts column values match allowable list

  - accepted_values:
      values: ['placed', 'shipped']
  

• {{ source('source_name', 'table_name') }} — References a declared source table

  select * from {{ source('stripe', 'payment') }}
  

Comparison Table

Common Pitfalls

• Mistake: Writing singular test queries that select valid records instead of invalid records. Avoid: Invert the query logic to select only records that violate your rule.
• Mistake: Hardcoding raw database table names in staging models rather than using sources. Avoid: Use the source macro to reference tables declared in your YAML files.
• Mistake: Guessing why an accepted_values generic test failed without checking data. Avoid: Run the compiled failure SQL to view the exact unexpected data values.
• Mistake: Deferring data testing until final mart models are created. Avoid: Add unique and not_null tests directly to upstream raw source YAML definitions.

FAQs

• Why did my singular test fail if the query returned expected data? dbt tests fail whenever any rows are returned. Singular test queries must return zero rows to pass.
• What are the four built-in generic tests in dbt? The four built-in generic tests are unique, not_null, accepted_values, and relationships.
• Where can I find the exact query executed by a failing generic test? dbt prints the compiled SQL file path in the terminal, located inside the target/compiled/ directory.
• Can I run tests on raw sources before executing downstream models? Yes, you can target source tests specifically using the command dbt test -s source:source_name.