Unit Testing in dbt: A Practical Guide

If a subtle SQL bug slips past code review, it can cascade through your entire data pipeline—corrupting dashboards, skewing reports, and eroding stakeholder trust. The worst part? You may not even notice until someone questions the numbers weeks later.

dbt™ unit testing was introduced precisely to prevent this. Available natively since version 1.8, dbt™ unit tests let you validate your SQL transformation logic using controlled, static inputs and expected outputs—before your model ever touches production data.

This guide walks you through everything you need to know: what dbt™ unit tests are, how they differ from dbt™ data tests, how to write them step by step, best practices, limitations, and how to integrate them into your CI/CD pipeline for maximum impact.

What is dbt unit testing?

dbt™ unit testing is a method to validate your SQL model logic using static, predefined inputs and expected outputs—all before the model runs in production. This feature became native in dbt™ version 1.8. Unlike data quality tests, which check the state of your data in the warehouse, dbt™ unit tests focus exclusively on the transformation logic itself. They answer the question: "Does my SQL logic produce the correct output for a known set of inputs?"

• Purpose: Validate transformation logic, not data quality.

• How it works: You define mock inputs and expected outputs in YAML; dbt™ runs your model against the mock inputs and compares the actual result to your expected output.

• Key benefit: Catch logic errors early in the development cycle, before they reach production and impact downstream data.

Here is a simple example from the dbt™ documentation to illustrate the concept:

This test validates that a simple hello_world model produces the expected output. For real-world models, you'll define mock data for upstream dependencies and assert more complex expected outputs.

Figure 1: How dbt™ unit tests work — mock inputs are run through your model, and the actual output is compared against the expected output.

dbt unit tests vs dbt data tests

It's crucial to understand the distinction between dbt™ unit tests, which validate logic with mock data, and dbt™ data tests, which perform data quality checks on actual data in your warehouse. This is one of the most common points of confusion for newcomers to dbt™ testing.

Figure 2: The execution order during dbt build — unit tests run before materialization, data tests run after.

When to use dbt unit tests

dbt™ unit tests are the right choice for verifying the correctness of your model's logic, especially in scenarios involving:

• Complex business logic — revenue calculations, customer segmentation rules, or scoring algorithms.

• Conditional statements (CASE WHEN) — ensuring every branch produces the correct result.

• Calculations and mathematical formulas — tax computations, discount logic, aggregations.

• Handling of edge cases — nulls, zeros, empty strings, negative numbers.

• String manipulations or type casting — regex extraction, date parsing, data type conversions.

When to use dbt data tests

dbt™ data tests (sometimes referred to as dbt data_tests in configuration) are appropriate for ensuring the quality and integrity of the data after your model has run. Use them for:

• Validating uniqueness of a primary key (unique test).

• Checking for not_null constraints on required columns.

• Enforcing referential integrity using the relationships test.

• Ensuring a column contains only accepted_values — for example, an order status column should only contain 'pending', 'completed', or 'cancelled'.

For even more generic data quality assertions—such as testing for row count ranges, column value distributions, or regex patterns—consider using packages like dbt-utils (often searched as dbt utils tests) or dbt-expectations. These complement both dbt™ unit tests and native dbt™ data tests.

How to write a dbt unit test

Creating a dbt™ unit test is a straightforward process that involves defining your inputs, specifying the expected output, and running the test. Let's walk through each step.

Step 1: Define your test inputs

First, you need to mock the upstream data your model depends on. This is done using the given block in your YAML test definition. You can mock data for any upstream dependency, including other models, sources, seeds, and snapshots.

The input field uses the standard ref() function for models and seeds, and source() for sources. For example, mocking a source looks like this:

Tip: You only need to define the columns that are relevant to your test. dbt™ will handle the rest by inserting default values for any unspecified columns.

Step 2: Set expected outputs

Next, use the expect block to define the exact rows your model should produce when it runs with the given inputs. This is the "expected" result that dbt™ will compare against the "actual" result.

Notice how the expected output for order_id: 1 shows a total_revenue of 75. This is because the logic should sum the success payment (100) and subtract the refund (25), yielding 100 - 25 = 75.

Step 3: Add the unit test to your YAML

Combine the given and expect blocks under the unit_tests key for the corresponding model in its .yml file (e.g., models/marts/schema.yml).

Here is the complete YAML structure:

Note: Unit tests must be defined in YML files within your models/ directory, and all table names must be aliased in your SQL if you are testing join logic. Refer to the official dbt™ unit test docs for the full reference.

Step 4: Run your dbt unit test

You can run all unit tests in your project using the test_type selector:

Alternatively, running dbt build will automatically execute unit tests before their corresponding models are materialized. To run a single unit test, select it by its name:

To run unit tests only for a specific model:

Figure 3: Two paths to run dbt™ unit tests — directly via dbt test or as part of the dbt build workflow.

dbt unit testing best practices

Follow these guidelines to write effective and maintainable dbt™ unit tests.

Test complex business logic first

Prioritize models with conditional logic, complex calculations, or critical business rules. A simple SELECT * FROM source_table model doesn't need a unit test. Focus your testing effort where bugs are most likely and most costly—think revenue calculations, customer segmentation, or compliance-critical transformations.

Keep test inputs minimal

Use the smallest possible mock dataset that is sufficient to prove your logic works. Two or three rows is often enough. Bloated mock data makes tests harder to read, slower to maintain, and more confusing when they fail.

Cover edge cases and null handling

Actively test for boundary conditions. What happens if an input is NULL? An empty string? Zero? A negative number? These are common sources of production bugs that are easily caught with a well-designed unit test.

Use descriptive test names

Name your tests clearly so that a failure immediately tells you what is broken. For example, test_revenue_calculation_handles_refunds is far more informative than unit_test_1. A good naming convention is: test__.

Organize tests by model domain

Group related tests together. For larger projects, consider using separate YAML files for tests in each domain (e.g., models/marts/_unit_tests.yml) or using dbt™ tags to categorize and selectively run them.

Mock data in different formats

dbt™ supports three formats for mock data: dict (the default, inline key-value pairs), csv (inline or from a fixture file), and sql (inline or from a fixture file). Use csv or fixture files for larger mock datasets that would clutter your YAML, and use sql format when you need to test models that depend on ephemeral models.

Limitations of dbt unit tests

While powerful, native dbt™ unit tests have some limitations you should be aware of.

Scalability in large projects

Writing and maintaining hundreds of unit tests by hand in YAML can become time-consuming and tedious. There is no built-in functionality for automatically generating test cases based on model logic. As your project grows, the volume of YAML can become difficult to manage without additional tooling or conventions.

No built-in impact analysis

dbt™ unit tests validate a model in isolation. They do not show the downstream effects of a change. If you modify a model, you need additional tooling to understand which downstream models, dashboards, or BI tools might be affected. A passing unit test gives you confidence in that model's logic, but it doesn't tell you whether the change breaks something downstream.

Limited incremental model support

Testing incremental models requires extra configuration. By default, dbt™ unit tests run models in full-refresh mode, which may not accurately reflect the behavior of an incremental run where is_incremental() is true. To test incremental logic, you must use the overrides configuration:

Notice the use of input: this to mock the current state of the incremental model and overrides.macros.is_incremental: true to simulate an incremental run. This pattern is documented in the official dbt™ unit testing reference.

Other constraints

• Only SQL models are supported (not Python models).

• Only models in the current project can be unit tested (not models from installed packages).

• Materialized views, recursive SQL, and introspective queries are not supported.

Running dbt unit tests in CI/CD pipelines

Integrating dbt™ unit tests into your CI/CD pipeline is where they deliver the most value—acting as an automated guardrail to catch bugs before code is merged into production.

Integrating with dbt build

The dbt build command is the easiest way to integrate unit tests into CI. It follows a logical execution order:

1. Unit tests run first for a given model.

2. Only if they pass does dbt™ proceed to materialize the model.

3. After materialization, data tests run against the actual output.

This prevents bad logic from ever being written to your warehouse. A typical CI job in a GitHub Actions workflow might look like:

Slim CI for faster test runs

For faster CI/CD runs, use dbt™'s state-based selection. The command:

intelligently runs and tests only the models that have been modified and any downstream dependencies. This dramatically speeds up CI jobs by avoiding redundant testing of unchanged code. You'll need a manifest.json artifact from your production environment to serve as the comparison baseline—this is commonly referred to as Slim CI.

Figure 4: A Slim CI workflow — only modified models and their dependents are built and tested, keeping CI fast.

Testing approaches that complement dbt unit tests

dbt™ unit tests are a critical part of a comprehensive data quality strategy, but they aren't enough on their own. Here are two approaches that fill important gaps.

Data diffing for regression detection

Data diffing involves comparing the output of a model between two different versions of your code—for example, your feature branch vs. the main branch. This is extremely useful for catching unintended changes and regressions during refactoring.

Suppose you refactor a complex fct_orders model. Your unit tests pass, but did the refactor accidentally change the output for edge cases you didn't think to test? A data diff would reveal any differences in the actual output across thousands of production rows, giving you confidence that your changes haven't broken anything.

Column-level lineage for impact analysis

Column-level lineage tools fill the impact analysis gap left by dbt™ unit tests. They provide a complete map of how data flows through your project, showing exactly which downstream models, columns, and BI dashboards are affected by a change you make.

For example, if you rename a column in a staging model, column-level lineage can immediately show you every downstream mart, metric, and Looker dashboard that references that column. This allows you to assess the full blast radius of your work before deploying.

Ship reliable dbt models faster with Paradime

Paradime provides a suite of tools designed to accelerate and de-risk your dbt™ development workflow.

Paradime's Code IDE, supercharged with DinoAI, can help you generate dbt™ tests—including unit tests—from your model's logic automatically. DinoAI analyzes your model's SQL structure, column types, and relationships to suggest appropriate test configurations. You can use natural language prompts and .dinorules files to enforce testing standards across your team.

Paradime Bolt's Turbo CI runs your dbt™ builds and tests on every pull request, building and testing models in a temporary schema before code is merged. Combined with Column-Level Lineage Diff Analysis, Turbo CI provides automated PR comments that list every downstream dbt™ model and BI dashboard affected by your changes—giving you full impact analysis on every change.

Start for free →

FAQs about dbt unit testing

What version of dbt supports native unit testing?

Native unit testing is available in dbt Core™ and dbt Cloud™ version 1.8 and later. For earlier versions, you must use third-party packages like dbt_unit_testing.

Can I unit test incremental models in dbt?

Yes, you can unit test incremental models, but it requires specific configuration. By default, dbt™ unit tests run models in full-refresh mode. You must override the is_incremental macro and mock this to simulate an incremental run and test the logic within an {% if is_incremental() %} block:

How do I mock sources and seeds in dbt unit tests?

You mock sources and seeds in the given block just like you mock models. Use the source() function for sources and ref() for seeds:

If you don't supply an input for a seed dependency, dbt™ will use the actual seed data as the input.

Do dbt unit tests run automatically during dbt build?

Yes. The dbt build command executes unit tests before materializing the associated model. If a unit test fails, the build process for that model stops, preventing incorrect data from being written to your warehouse. This makes dbt build the recommended command for CI/CD pipelines.

How many dbt unit tests should I write per model?

Focus on quality over quantity. Prioritize models with complex logic, and aim to write at least one test for each distinct business rule, calculation, or edge case you want to protect. A simple passthrough model may not need any dbt™ unit tests, while a complex model with multiple CASE WHEN branches, calculations, and null-handling logic might need several. The goal is to cover the logic that is most likely to break and most costly if it does.

Can I use dbt-utils tests alongside unit tests?

Yes. dbt-utils tests (such as expression_is_true, unique_combination_of_columns, and not_constant) are data tests that run after materialization. They complement dbt™ unit tests, which run before materialization. Use dbt™ unit tests to validate logic and dbt-utils tests to validate data quality patterns that native generic tests don't cover.