Add unit test for a dbt model

Additional Resources

• Spec Reference - All required and optional YAML keys for unit tests
• Examples - Unit test examples across formats (dict, csv, sql)
• Incremental Models - Unit testing incremental models
• Ephemeral Dependencies - Unit testing models depending on ephemeral models
• Special Case Overrides - Introspective macros, project variables, environment variables
• Versioned Models - Unit testing versioned SQL models
• BigQuery Caveats - BigQuery-specific caveats
• BigQuery Data Types - BigQuery data type handling
• Postgres Data Types - Postgres data type handling
• Redshift Caveats - Redshift-specific caveats
• Redshift Data Types - Redshift data type handling
• Snowflake Data Types - Snowflake data type handling
• Spark Data Types - Spark data type handling

What are unit tests in dbt

dbt unit tests validate SQL modeling logic on static inputs before materializing in production. If any unit test for a model fails, dbt will not materialize that model.

When to use

You should unit test a model:

• Adding Model-Input-Output scenarios for the intended functionality of the model as well as edge cases to prevent regressions if the model logic is changed at a later date.
• Verifying that a bug fix solves a bug report for an existing dbt model.

More examples:

• When your SQL contains complex logic:
  • Regex
  • Date math
  • Window functions
  • case when statements when there are many whens
  • Truncation
  • Complex joins (multiple joins, self-joins, or joins with non-trivial conditions)
• When you're writing custom logic to process input data, similar to creating a function.
• Logic for which you had bugs reported before.
• Edge cases not yet seen in your actual data that you want to be confident you are handling properly.
• Prior to refactoring the transformation logic (especially if the refactor is significant).
• Models with high "criticality" (public, contracted models or models directly upstream of an exposure).

When not to use

Cases we don't recommend creating unit tests for:

• Built-in functions that are tested extensively by the warehouse provider. If an unexpected issue arises, it's more likely a result of issues in the underlying data rather than the function itself. Therefore, fixture data in the unit test won't provide valuable information.
  • common SQL spec functions like min(), etc.

General format

dbt unit test uses a trio of the model, given inputs, and expected outputs (Model-Inputs-Outputs):

1. model - when building this model
2. given inputs - given a set of source, seeds, and models as preconditions
3. expect output - then expect this row content of the model as a postcondition

Workflow

1. Choose the model to test

Self explanatory -- the title says it all!

2. Mock the inputs

• Create an input for each of the nodes the model depends on.
• Specify the mock data it should use.
• Specify the format if different than the default (YAML dict).
  • See the "Data formats for unit tests" section below to determine which format to use.
• The mock data only needs include the subset of columns used within this test case.

Tip: Use dbt show to explore existing data from upstream models or sources. This helps you understand realistic input structures. However, always sanitize the sample data to remove any sensitive or PII information before using it in your unit test fixtures.

# Preview upstream model data
dbt show --select upstream_model --limit 5

3. Mock the output

• Specify the data that you expect the model to create given those inputs.
• Specify the format if different than the default (YAML dict).
  • See the "Data formats for unit tests" section below to determine which format to use.
• The mock data only needs include the subset of columns used within this test case.

Minimal unit test

Suppose you have this model:

-- models/hello_world.sql

select 'world' as hello

Minimal unit test for that model:

# models/_properties.yml

unit_tests:
  - name: test_hello_world

    # Always only one transformation to test
    model: hello_world

    # No inputs needed this time!
    # Most unit tests will have inputs -- see the "real world example" section below
    given: []

    # Expected output can have zero to many rows
    expect:
      rows:
        - {hello: world}

Executing unit tests

Run the unit tests, build the model, and run the data tests for the hello_world model:

dbt build --select hello_world

This saves on warehouse spend as the model will only be materialized and move on to the data tests if the unit tests pass successfully.

Or only run the unit tests without building the model or running the data tests:

dbt test --select "hello_world,test_type:unit"

Or choose a specific unit test by name:

dbt test --select test_is_valid_email_address

Excluding unit tests from production builds

dbt Labs strongly recommends only running unit tests in development or CI environments. Since the inputs of the unit tests are static, there's no need to use additional compute cycles running them in production. Use them when doing development for a test-driven approach and CI to ensure changes don't break them.

Use the --resource-type flag --exclude-resource-type or the DBT_EXCLUDE_RESOURCE_TYPES environment variable to exclude unit tests from your production builds and save compute.

More realistic example

unit_tests:

  - name: test_order_items_count_drink_items_with_zero_drinks
    description: >
      Scenario: Order without any drinks
        When the `order_items_summary` table is built
        Given an order with nothing but 1 food item
        Then the count of drink items is 0

    # Model
    model: order_items_summary

    # Inputs
    given:
      - input: ref('order_items')
        rows:
          - {
              order_id: 76,
              order_item_id: 3,
              is_drink_item: false,
            }
      - input: ref('stg_orders')
        rows:
          - { order_id: 76 }

    # Output
    expect:
      rows:
        - {
            order_id: 76,
            count_drink_items: 0,
          }

For more examples of unit tests, see references/examples.md

Supported and unsupported scenarios

• dbt only supports unit testing SQL models.
  • Unit testing Python models is not supported.
  • Unit testing non-model nodes like snapshots, seeds, sources, analyses, etc. is not supported.
• dbt only supports adding unit tests to models in your current project.
  • Unit testing cross-project models or models imported from a package is not supported.
• dbt does not support unit testing models that use the materialized view materialization.
• dbt does not support unit testing models that use recursive SQL.
• dbt does not support unit testing models that use introspective queries.
• dbt does not support an expect output for final state of the database table after inserting/merging for incremental models.
• dbt does support an expect output for what will be merged/inserted for incremental models.

Handy to know

• Unit tests must be defined in a YAML file in your model-paths directory (models/ by default)
• Fixture files for unit tests must be defined in a SQL or CSV file in your test-paths directory (tests/fixtures by default)
• Include all ref or source model references in the unit test configuration as inputs to avoid "node not found" errors during compilation.
• If your model has multiple versions, by default the unit test will run on all versions of your model.
• If you want to unit test a model that depends on an ephemeral model, you must use format: sql for the ephemeral model input.
• Table names within the model must be aliased in order to unit test join logic

YAML for specifying unit tests

• For all the required and optional keys in the YAML definition of unit tests, see references/spec.md

Inputs for unit tests

Use inputs in your unit tests to reference a specific model or source for the test:

• For input:, use a string that represents a ref or source call:
  • ref('my_model') or ref('my_model', v='2') or ref('dougs_project', 'users')
  • source('source_schema', 'source_name')
• For seed inputs:
  • If you do not supply an input for a seed, we will use the seed's CSV file as the input.
  • If you do supply an input for a seed, we will use that input instead.
• Use “empty” inputs by setting rows to an empty list rows: []
  • This is useful if the model has a ref or source dependency, but its values are irrelevant to this particular unit test. Just beware if the model has a join on that input that would cause rows to drop out!

models/schema.yml

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: [email protected],     email_top_level_domain: example.com}
         - {email: [email protected],     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}
      - input: ref('irrelevant_dependency')  # dependency that we need to acknowlege, but does not need any data
        rows: []
...

Data formats for unit tests

dbt supports three formats for mock data within unit tests:

1. dict (default): Inline YAML dictionary values.
2. csv: Inline CSV values or a CSV file.
3. sql: Inline SQL query or a SQL file.

To see examples of each of the formats, see