dbt Unit Testing Best Practices

In speaking with data practitioners, many companies use either unit tests or data tests in their dbt projects as their differences (and limitations) are not well understood. (And that’s if they test their data at all!) 

If you want a robust data quality strategy, you need to have several types of dbt tests in play as they have different purposes and compensate for each other’s weaknesses. 

In this post, I’ll explore how using recently available dbt unit testing functionality can help prevent data quality issues resulting from complex SQL logic and look at which scenarios they’re most useful for. I’ll share a practical example of how you can implement dbt unit tests for complex business logic in dbt. 

If you’re already familiar with dbt unit tests, scroll to the end where I rank the three common dbt tests–unit tests, data tests, and data diffs–based on their effectiveness in catching data quality issues based on implementation efforts, code coverage, scalability, and other factors. Together, the three tests ensure that every inch of your project’s data quality is covered, from critical business logic to the long tail of "unknown unknowns."  

What is dbt unit testing

dbt unit tests are dbt tests that validate the model logic with predefined inputs and outputs. 

Unlike a "classic" dbt data test, which runs an assertion against a model built with database data, unit tests require the developer to define "test cases" with input data and expected output. Thus, they allow more granular testing and isolate the validation of source data from the validation of the model code itself.

Why dbt data tests are usually not enough

One of the big leaps that dbt made relative to prior data orchestrators is having a native data testing framework – dbt tests. dbt’s generic tests allow developers to execute assertions such as that all values in a given column are unique, non-null, or conform to a particular set. While easy to define, dbt data tests have a few disadvantages at scale

dbt tests compare one version of your data

Because dbt data tests run assertions on "live" data (which is prone to change over time and with code changes), it creates two challenges:

  1. Tests can be noisy because they react to changes in both code and data, and it can be hard to separate those two effects.
  2. Tests can be costly in terms of time and compute, especially for larger datasets with many tested columns and/or rows.

Because dbt unit tests use fixed input and output data, they run extremely fast, are highly specific, and not prone to noise due to changing data.

When dbt unit testing became available

Quite recently, in dbt core 1.8!

Some background: The initial proposal for unit testing appeared four years ago, and this year after an extensive design discussion with the community unit testing was finally implemented in dbt in May 2024!

dbt data tests vs. dbt unit tests

In a typical dbt data test, we define assertions (or expectations) about the built model and its columns, i.e., "column X should look like this," e.g., your primary key should never be null, so you would add a not_null test for it. In a data test, we can’t say, "Given input X, the output should be Y." In a data test, the input and expected output are not strictly defined, whereas in a unit test, they are.

dbt unit testing best practices

Unit tests can be beneficial in the following example scenarios:

  1. Parsing strings with regular expressions or string functions, e.g., extracting UTM parameters from a URL or validating emails
  2. Complex CASE WHEN statements
  3. Formulas involving arithmetic expressions and dates
  4. Anything with high business criticality, e.g., key metric or payout calculations.

In such scenarios, validating the model logic with a data test can be very hard but easily done with a unit test.

Another rule of thumb is to use dbt unit tests as "prospective" or "retrospective" tests:

  1. Retrospective: Add unit tests to the parts of code where bugs have been reported previously.
  2. Prospective: Add unit tests to cover edge cases that don’t yet exist in the current data but are important to handle properly—another technique impossible with data tests!

It’s important to mention that unlike dbt data tests, which can be run during both development and each dbt run in production, dbt unit tests are relevant only to the development process – when changes to the dbt code are introduced. Including dbt unit tests in your dbt CI is a great idea to test changes during the pull/merge request review stage.

However, running dbt unit tests in production is pointless and will only increase the execution time of your dbt run.

How to implement dbt unit tests in your dbt project

In the following example, we’re adding a unit test to the  "dim_employees" model that calculates appropriate seniority and department for each employee based on their title:

WITH employees AS (
    SELECT * FROM {{ ref('stg_employees') }}

employee_mapping_stg AS (
            WHEN LOWER(title) LIKE '%executive%' OR LOWER(title) LIKE '%chief%' THEN 'Executive'
            WHEN LOWER(title) LIKE '%director%' THEN 'Director'
            WHEN LOWER(title) LIKE '%manager%' THEN 'Manager'
            ELSE 'Individual Contributor'
        END AS seniority,
            WHEN LOWER(title) REGEXP '.*finance.*|.*accounting.*' THEN 'Finance'
            WHEN LOWER(title) REGEXP '.*engineering.*|.*developer.*|.*programmer.*' THEN 'Engineering'
            WHEN LOWER(title) REGEXP '.*marketing.*' THEN 'Marketing'
            WHEN LOWER(title) REGEXP '.*sales.*' THEN 'Sales'
            WHEN LOWER(title) REGEXP '.*human resources.*|.*hr.*' THEN 'Human Resources'
            ELSE 'Other'
        END AS department
    FROM employees

SELECT * FROM employee_mapping_stg

As we can see, the logic for "seniority" and "department" mappings is quite complex and can easily lead to unexpected results if not properly tested. We can define a simple test (test_employee_seniority_and_department) to check for a few expected permutations:

  - name: test_employee_seniority_and_department
    description: "Check if the seniority and department are correctly derived from the employee's title"
    model: dim_employees
      - input: ref('stg_employees')
          - {employee_id: 1, first_name: 'Buzz', last_name: 'Lightyear', title: 'Chief Financial Officer'}
          - {employee_id: 2, first_name: 'Elsa', last_name: 'Arendelle', title: 'Marketing Manager'}
          - {employee_id: 3, first_name: 'Woody', last_name: 'Pride', title: 'Senior Software Engineer'}
          - {employee_id: 4, first_name: 'Mulan', last_name: 'Fa', title: 'HR Coordinator'}
          - {employee_id: 5, first_name: 'Simba', last_name: 'Pride', title: 'Sales Representative'}
        - {employee_id: 1, seniority: 'Executive', department: 'Finance'}
        - {employee_id: 2, seniority: 'Manager', department: 'Marketing'}
        - {employee_id: 3, seniority: 'Individual Contributor', department: 'Engineering'}
        - {employee_id: 4, seniority: 'Individual Contributor', department: 'Human Resources'}
        - {employee_id: 5, seniority: 'Individual Contributor', department: 'Sales'}

The test can be invoked with the following command:

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

The above command will run all of the unit tests on dim_employees. Note that to execute a unit test successfully, its direct parents (“stg_employees” in our example) need to exist – but can be empty since they are mocked by the unit test.

Limitations of dbt unit tests


Despite their many powers, dbt unit tests have one serious limitation: they require much more effort to write since the developer needs to define the input and output data for every test case. It’s best to use dbt unit tests tactically for the most complex, error-prone, and business-critical parts of your dbt code base.

Impact analysis

dbt unit tests are laser-focused by design, which is extremely effective for testing specific parts in the code. However, this approach still poses a challenge for impact analysis during deployment and pull request review: if a test fails, what downstream models, dashboards, data products, and stakeholders would be affected?

Data Diff: The scalable, automated testing approach that complements unit tests

Given the high friction of creating dbt unit tests, it is reasonable to expect ~1% of columns to be covered with unit tests. Furthermore, the % of covered possible data errors can be 10-100x smaller given the complexity of both data and code. So, what do you do about the remaining 99.99% of potential bugs not covered with unit tests?

What is data diffing?

This is where data diffing – an automated strategy for dbt testing can be very helpful.

Data diff is a value-level and statistical comparison of datasets. For your dbt project, data diffing can be leveraged to compare the data in production (the ground truth) and development/staging, allowing the developer and the code reviewer to fully assess the nature and impact of changes to the business logic.

A Data Diff between the staging and production version of a DIM_ORGS model

Implemented in CI and coupled with column-level lineage that extends to BI integrations, data diff safeguards against unforeseen changes, reducing the time needed to review and test the code and accelerating the overall team velocity.

How dbt data, unit tests, and data diff create bulletproof data quality

A strong data quality strategy will use several test types and includes unit tests for a small share of critical/complex business logic, data tests for standard quality checks, and data diff for the vast majority of "unknown unknowns" that make up data quality issues:

dbt unit tests cover testing for your most complex logic, dbt generic tests establish data quality baselines, and data diffing captures it all!

Putting it all together, an effective strategy utilizes all three types of dbt testing to maximize each method’s impact and ROI.

dbt data tests dbt unit tests Data Diff
Effort to implement Medium

Requires test cases to be written

Requires test cases and input/output dataset curation

Requires no manual test set-up
Expected code coverage Medium

Relatively easy to add generic tests that cover data quality fundamentals

Due to the lift required to build them, unit tests are often only applied to the most complex or business-critical logic logic

Automatically catches all changes to your data
Specificity (how clear are test results) Medium

Reacts to changes in code and data

Focused on testing code given fixed input

Requires the user to interpret acceptable-ness of data differences
Use Case Testing general rules such as not-nullness, uniqueness, value ranges, and referrential integrity Testing complex business logic Understanding the potential impact from your code change on your data and downstream assets
Scalibility with data volume Low

Can slow down your project if tested rows becomes too large

Independent of data volume since test rows are defined by the user

Scales well with filtering and sampling

Datafold is the fastest way to validate dbt model changes during development, deployment & migrations. Datafold allows data engineers to audit their work in minutes without writing tests or custom queries. Integrated into CI, Datafold enables data teams to deploy with full confidence, ship faster, and leave tedious QA and firefighting behind.

Datafold is the fastest way to test dbt code changes