Why advanced testing catches subtle data issues in dbt

Introduction

Advanced testing helps find hidden problems in data that simple checks might miss. This keeps data accurate and trustworthy.

When you want to ensure data quality beyond basic checks

Before making important decisions based on data

When data comes from many sources and might have hidden errors

To catch rare or complex data problems early

When you want to automate data quality checks in your workflow

Syntax

dbt

tests:
  - unique
  - not_null
  - accepted_values:
      values: ['value1', 'value2']
  - relationships:
      to: ref('other_model')
      field: id

dbt tests are defined in YAML files inside your models or separately.

You can use built-in tests or write custom SQL tests for complex checks.

Examples

Checks that all values in a column are unique.

dbt

tests:
  - unique

Ensures no missing values in a column.

dbt

tests:
  - not_null

Checks that column values are only from a specific list.

dbt

tests:
  - accepted_values:
      values: ['active', 'inactive']

Verifies foreign key relationships between tables.

dbt

tests:
  - relationships:
      to: ref('users')
      field: user_id

Sample Program

This dbt schema file defines advanced tests for the 'orders' table. It checks that order IDs are unique and not missing, status values are valid, and user IDs link to existing users.

dbt

version: 2
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']
      - name: user_id
        tests:
          - relationships:
              to: ref('users')
              field: id

Important Notes

Advanced tests can catch subtle issues like invalid references or unexpected values.

Custom SQL tests let you write any logic to find complex data problems.

Running tests regularly helps keep data reliable over time.

Summary

Advanced testing finds hidden data problems that simple checks miss.

dbt supports built-in and custom tests for thorough data validation.

Using these tests improves trust in your data and decisions.