Unit Testing

Unit testing allows you to validate your SQL modeling logic on small sets of static input data before deploying your models to production. Unlike data tests that verify completed models, unit tests help you catch logical errors during development—bringing the test-driven development approach to data transformations.

Unit testing is available in dbt™ Core v1.8+ and in dbt™ Cloud on the "Latest" release track.

Why Unit Testing Matters

Traditional data tests validate the quality of existing data or the structure of materialized datasets. However, these tests can only be executed after a model has been built. Unit tests serve a different purpose:

• Test before building – Validate logic without materializing models

• Verify transformations – Ensure your SQL logic handles edge cases correctly

• Support test-driven development – Write tests first, then implement the model

• Catch errors early – Find bugs before they reach production

• Improve code reliability – Maintain confidence during refactoring

When to Use Unit Tests

Unit tests are particularly valuable when your models include:

• Complex transformations – Regular expressions, date calculations, window functions

• Critical business logic – Calculations that impact important metrics

• Known edge cases – Scenarios that have caused issues in the past

• Models undergoing refactoring – Changes to existing transformation logic

• Frequently used models – Core models that many others depend on

Unit tests complement data tests but serve a different purpose. Data tests verify the quality of your data after models are built, while unit tests validate your transformation logic before deployment.

---

Unit Test Structure

A unit test in dbt™ consists of:

1. Mock inputs – Sample data for source tables and referenced models

2. Model under test – The model whose logic you want to validate

3. Expected outputs – The exact results you expect after transformation

Example Unit Test

Here's how a unit test is defined in YAML:

unit_tests:
  - name: test_customer_email_validation
    description: "Verify email validation logic handles invalid formats and domains"
    model: dim_customers
    given:
      - input: ref('stg_customers')
        rows:
          - {email: "[e-mail]", email_domain: "example.com"}
          - {email: "no-at-symbol.com", email_domain: "no-at-symbol.com"}
          - {email: "missing.domain@", email_domain: ""}
      - input: ref('valid_domains')
        rows:
          - {domain: "example.com"}
    expect:
      rows:
        - {email: "[e-mail]", is_valid_email: true}
        - {email: "no-at-symbol.com", is_valid_email: false}
        - {email: "missing.domain@", is_valid_email: false}

---

Creating Unit Tests

Unit tests are defined in YAML files within your models directory, typically alongside the model they're testing.

Key Components

1. Test name – Descriptive identifier for the test

2. Model – The model being tested

3. Given inputs – Mock data for model dependencies

4. Expected outputs – The anticipated results

Mock Data Formats

You can provide mock data in several formats:

• Dictionary format – Define rows as YAML dictionaries

• CSV format – Provide data in CSV format

• SQL format – Write SQL queries to generate test data

• External fixtures – Reference data from separate files

---

Running Unit Tests

To run unit tests, use the dbt test command with appropriate selectors:

# Run all unit tests
dbt test --select test_type:unit

# Run unit tests for a specific model
dbt test --select my_model,test_type:unit

# Run a specific unit test
dbt test --select my_specific_unit_test

---

Unit Testing Special Cases

Incremental Models

When testing incremental models, you can override the is_incremental() macro to test both full refresh and incremental scenarios:

unit_tests:
  - name: test_incremental_full_refresh
    model: my_incremental_model
    overrides:
      macros:
        is_incremental: false
    # Test data here...

  - name: test_incremental_update
    model: my_incremental_model
    overrides:
      macros:
        is_incremental: true
    # Test data here including 'this' input...

Ephemeral Models

To test models that depend on ephemeral models, use SQL format for the input:

unit_tests:
  - name: test_model_with_ephemeral_dependency
    model: my_model
    given:
      - input: ref('ephemeral_model')
        format: sql
        rows: |
          select 1 as id, 'test' as name
    # Expected output here...

---

Limitations & Best Practices

Limitations

Current unit testing in dbt™ has some limitations:

• Only supports SQL models (not Python models)

• Can only test models in your current project

• Doesn't support materialized views

• Doesn't support recursive SQL or introspective queries

• Requires all table names to be aliased in join logic

Best Practices

1. Focus on logic, not functions – Test your business logic rather than built-in database functions

2. Use descriptive test names – Clearly explain what each test is verifying

3. Test edge cases – Include unusual scenarios your logic needs to handle

4. Only mock what's needed – Define only the columns relevant to your test

5. Run in development – Use unit tests during development, not in production

6. Use in CI/CD – Integrate unit tests into your CI/CD pipeline