7 dbt Testing Best Practices

Gleb Mezhanskiy

June 15, 2023

So you want to do data quality testing in dbt the right way? We’ve got you covered.

We recommend the following testing practices to have the most comprehensive, automated, and accessible tests for your dbt models:

Build a foundation with generic tests
Leverage helpers and packages
Model with the testing in mind
Test data sources
Integrate alerting
Create testing documentation and establish clear ownership of tests
Always test your data during CI

But before we dive in let's start with creating two tests in dbt and an example table we can apply tests to.

Creating a test in dbt

Testing in dbt uses SELECT statements in SQL queries to confirm that the data models, data sources, and other resources in a dbt project work as intended. These tests are designed to return rows or records that fail to meet the assertion criteria specified in the test.

An important concept of dbt tests is that these SELECT statements attempt to find failing records, records that would show a test to be incorrect. This is a slightly different concept from normal software testing, where certain conditions are asserted to be correct or true.

There are two main types of tests provided by dbt:

Singular tests
Generic tests

Singular tests are very focused, written as typical SQL statements, and stored in SQL files. Like their name suggests, singular tests are often for specific testing requirements that are not commonly shared across different models, and require custom SQL to test what they need to.

For example, we might write a singular test to ensure there are no NULL amount records in a typical fact table. Note we can include jinja in these singular dbt tests.

SELECT order_id
FROM {{ ref('fact_transactions' )}}
WHERE amount IS NULL

Generic tests are written and stored in YML files, with parameterized queries that can be used across different dbt models. This same singular test above could be written as a generic test.

{% test null_orders(model, column_name) %}

with validation as (
    select
        {{ column_name }} as null_order
    from {{ model }}
),

validation_errors as (
    select
        null_order
    from validation
    where null_order IS NULL
)

select *
from validation_errors

{% endtest %}

There are several ways to implement testing with dbt depending on which data properties the tester is interested in. The dbt ecosystem helps in this regard through a variety of packages related to data testing that can be found on dbt hub.

Example - eCommerce

Let's pretend we are an Analytics Engineer at an ecommerce store selling widgets. A sales manager comes to us asking which widgets were sold when and for how much to determine revenue by product per month. We have our transaction data in a fact table and our product information in a dimension table but how do we know this data will stay reliable over time?

Let's take a closer look at how dbt testing best practices can detect many common data-related problems to help us calculate revenue accurately.

Transactions Table

transaction_id | transaction_date | product_id | quantity | amount
3498328        | 2022-01-01       | 55         | 10       | 5.55
9827346        | 2022-02-17       | 23         | 2        | 15.89
1239383        | 2022-03-15       | 12         | 5        | 6.99

Product Table

product_id | product_name | product_category | launch_date
55         | widget_4     | AB100            | 2021-01-01
23         | widget_2     | NB500            | 2020-07-01

Downstream impact and analytics

One of the most common data issues solved by dbt testing best practices is incorrect data and analytics cascading downstream from a fact table, like in our example. It would be very common to have a Monthly Product Sales report consumed by multiple business units, using aggregated data points from this report to drive business decisions.

Such a Monthly Product Sales report might be written in SQL as follows.

SELECT 
     p.product_name, 
     YEAR(f.transaction_date) as sales_year, 
     MONTH(f.transaction_date) as sales_month, 
     SUM(f.amount * f.quantity) as monthly_sales
FROM 
     fact_transactions as f
INNER JOIN 
     dim_products as p ON f.product_id = p.product_id
GROUP BY 
     p.product_name, 
     YEAR(f.transaction_date), 
     MONTH(f.transaction_date)
ORDER BY 
     sales_year, 
     sales_month DESC

The result of this SQL query displayed in the Monthly Product Sales report would look something like this:

product_name | sales_year | sales_month | monthly_sales
Widget_4     | 2022       | 03          | 5,600.00
widget_2     | 2022       | 04          | 10,500.00

Data-driven organizations need to protect and test for data bugs and issues that can cause incorrect downstream analytics and reporting.

In the following sections, we will discuss dbt testing best practices, and how in our example case, they can solve data problems causing downstream issues.

#1: Start with generic tests

It’s often tempting in data testing—just as in standard software-development testing—to start with the most complex cases and tests. Before long, test writers get mired in the minute details of these scenarios, and their test writing gets stalled. Instead, it’s best to start simple.

Create tests by starting with the most basic tests before moving to more advanced tests that deal with business logic. Begin by validating the structure and assumptions made of the data. If the basic assumptions of the data are wrong, this will affect the usefulness of any advanced tests built on top of these assumptions.

Generic tests

Let’s look at the following dbt test examples with our previously discussed fact table example.

Beginning with basic tests means beginning with generic tests, which are modular and reusable tests for ensuring data reliability and integrity. dbt has four generic tests (also known as “schema tests”) which come out of the box, and they can be deployed with minimal configuration:

1. not_null: A null value in data indicates that a value in a column is missing or unknown. Sometimes, null values are intentional and desired. Other times, they are an indication of a data issue. This kind of test identifies where null values exist. You’ll always add a not_null test to the primary key for your models to ensure PKs are not null.

It’s clear that our Monthly Product Sales report would be affected by NULL values in our or product_id column. If the product_id column contained NULL values, those sales would be thrown out of our analytics query.

version: 2

models:
  - name: fact_transactions
    columns:
      - name: product_id
        tests:
          - not_null

2. unique: This kind of test is used to ensure that there are no duplicate values or recurring rows in a dataset. This is important because duplicate values in data columns like primary keys can lead to misleading data metrics, inaccurate visualizations, and unreliable reports.

In our example tables, our dim_products table is particularly susceptible to having duplicate product_id values, and this would have a serious impact on our analytics query. If duplicate values existed, this would inflate the numbers reported in our Monthly Product Sales because each row in our fact_transactions table would be duplicated during the table join.

version: 2

models:
  - name: fact_transactions
    columns:
      - name: product_id
        tests:
          - not_null

Without this example unique test for our dim_products table, we could end up with duplicate product_id records. For example, we might have a scenario like the following, in which product_id 55 is reused in the table.

product_id | product_name | product_category | launch_date
55         | widget_4     | AB100            | 2021-01-01
23         | widget_2     | NB500            | 2020-07-01
55         | widget_5     | ZB900            | 2021-05-01

When our analytics query runs to populate the Monthly Product Sales report, the join between the dim_products and the fact_transactions tables now inflates our sales numbers by duplicating all records in fact_transactions that have a product_id of 55.

3. accepted_values: This kind of test verifies that all column values across all rows are in the set of valid values. For example, our product_category column might only have two valid values (AB100, NB500). If this test uncovers any values in the product_category column which are not one of these two, it will fail.

version: 2

models:
  - name: dim_products
    columns:
      - name: product_category
        tests:
          - accepted_values:
              values: [AB100, NB500]

4. relationships: This kind of test verifies referential integrity, ensuring all of the records in a child table have a corresponding record in the parent table. We could use this test to check that all product_id values in our fact_transactions table exist in the dim_products table.

version: 2

models:
  - name: fact_transactions
    columns:
      - name: product_id
        tests:
          - relationships:
              to: ref('dim_products')
              field: product_id

As an example, it would be a common data bug if new sales were being recorded in our fact_transactions table with a product_id that did not exist in our dim_products table.

transaction_id | transaction_date | product_id | quantity | amount
3498328        | 2022-01-01       | 55         | 10       | 5.55
7695842        | 2022-02-02       | 01         | 4        | 6.99

In the above example, we know that product_id 01 does not exist in dim_products. As a result, our Monthly Product Sales report will underreport our sales. This is why relational integrity checks are so important for catching those hard-to-detect problems that are introduced into a dataset.

Custom generic tests

When more complex logic or validation criteria is needed, data practitioners can write their own custom generic tests. A custom generic test is written with Jinja, a templating language combined with SQL.

When examining our table fact_transactions and the Monthly Product Sales analytics along with the basic dbt testing we already put in place, there are still data issues and business rules that might not fit well into these simple tests.

For example, let’s say the business requires that we have no orders for over 500 units, as the company cannot fulfill such large orders with current inventory levels. This is the perfect use case for customer generic tests.

We could write the following custom test for verifying that none of the orders coming into our fact_transactions table exceed the 500 unit order quantity:

{% test order_limit(model, column_name) %}

with validation as (
    select
        {{ column_name }} as limit_field
    from {{ model }}
),

validation_errors as (
    select
        limit_field
    from validation
    where limit_field > 500
)

select *
from validation_errors

{% endtest %}

This custom generic test can now be used in a model:

version: 2

models:
  - name: fact_transactions
    columns:
      - name: quantity
        tests:
          - order_limit

Singular tests

When a testing scenario is unique to a single case—meaning it would not apply generically across several cases—the data practitioner can write a singular test. A singular test is based on the SQL statement which would return failing records. The dbt documentation provides the following example of a singular test, stored in a .sql file.

In our case, maybe we would like to determine if any orders have a negative order quantity:

select
    transaction_id,
    from {{ ref('fact_transactions' )}}
where quantity < 0

Looking to level up data testing? Download our new guide to testing with dbt

Discover the goals of testing with dbt, types of tests, principles and best practices for an effective testing strategy.

#2: Leverage helpers and extensions

The dbt ecosystem is growing at a rapid pace. Because dbt is open source, its users can create packages that address specific needs within dbt. These packages can be found at the dbt Package hub. Examples of useful packages include dbt-expectations and dbt-utils.

dbt-expectations

The dbt-expectations package allows dbt users to deploy tests in the style of Great Expectations directly to their data warehouse. The package includes over 50 tests that are commonly used.

To install and use dbt-expectations, modify your dbt project’s packages.yml to include the following. See the dbt-expectations GitHub README for more specifics.

packages:
  - package: calogica/dbt_expectations
    version: [">=0.5.0", "<0.6.0"]

With dbt-expectations built-in tests, data teams can take dbt testing to the next level, implementing and checking complex tests and business requirements.

Let’s return to our fact_transactions and Monthly Product Sales report example. We recall that our report includes SQL that sums up and groups by products, by month.

SELECT p.product_name, YEAR(f.transaction_date) as sales_year, MONTH(f.transaction_date) as sales_month, SUM(f.amount) as monthly_sales
FROM fact_transactions as f
INNER JOIN dim_products as p ON f.product_id = p.product_id
GROUP BY p.product_name, YEAR(f.transaction_date), MONTH(f.transaction_date)
ORDER BY sales_year, sales_month DESC

Again, the results of this report would look as follows.

product_name | sales_year | sales_month | monthly_sales
Widget_4     | 2022       | 03          | 5,600.00
Widget_4     | 2022       | 04          | -1,000.00
widget_2     | 2022       | 04          | 10,500.00

When the sales manager looks at the Monthly Product Sales report in a BI tool such as Looker, the negative monthly sales would jump out to them. And as we all know, negative sales means something has gone seriously wrong. They might also add if sales for any particular widget were very large—let’s say over 100,000—that might also indicate something has gone wrong.

These specific cases could be easily alerted with the dbt-expectations pre-built expect_column_sum_to_be_between test.

tests:
  - dbt_expectations.expect_column_sum_to_be_between:
      min_value: 0
      max_value: 100000
      group_by: [product_id, year(transaction_date), month(transaction)date)]

Now, we can easily test if our monthly sales meet the criteria given to us by the business, catching any anomalies or errors.

dbt-utils

dbt-utils package is another open source dbt managed that was developed by dbt Labs, and it contains additional schema tests and utility methods, extending and enhancing dbt.

By using pre-built packages such as these and others, data practitioners can save time and stand on the shoulders of those who have gone before. These advanced testing capabilities—developed by stakeholders in the data industry—provide an added layer of functionality that data teams can leverage.

Similar to dbt-expectations, dbt-utils comes with a number of out-of-the-box tests we can use on our datasets. For example, we can check to ensure our fact_transactions table doesn’t contain any negative quantity values that would negatively affect the Monthly Product Sales report.

transaction_id | transaction_date | product_id | quantity | amount
3498328        | 2022-01-01       | 55         | -10      | 5.55

Using dbt-utils expression_is_true test could find any bad records like the one shown above.

version: 2

models:
  - name: fact_transaction
    tests:
      - dbt_utils.expression_is_true:
          expression: "quantity > 0"

#3: Model with the testing in mind

Create data models and tables with the data tests in mind. For example, tables that are related to one another should be designed with the mindset that relationship tests will be run to verify referential integrity.

Again, using dbt-expectations makes it very straightforward to create tests when creating the initial tables to hold our metrics, like monthly product sales.

Specific tests like expect_column_to_exist could check to ensure table structure has not been changed and can hold the values we expect when our Monthly Product Sales report runs. For example, we would want to ensure the monthly_sales column always exists in our table so the downstream analytics will always be available.

product_name | sales_year | sales_month | monthly_sales
Widget_4     | 2022       | 03          | 5,600.00

tests:
- dbt_expectations.expect_column_to_exist

We can even test to ensure our fact_transactions table regulates getting new records and that something has not gone wrong upstream. To do this, we would use tests like expect_row_values_to_have_recent_data with dbt-expectations.

tests:
  - dbt_expectations.expect_row_values_to_have_recent_data:
        datepart: day
        interval: 3

By beginning with a test in mind—right when we are creating new data tables, sources, and analytics—the technical details are fresh, and it’s easier to create broad data-testing coverage upfront.

#4: Test data sources

Beyond just testing models, strong data testing includes testing of sources too. Data sources can include CSV files, event data, streams, data from Facebook ads, and even another data warehouse. Testing for data integrity, freshness, and accurate assumptions should be performed on data at the source-level and helps catch inconsistencies early in the ETL process.

dbt supports sources to have tests and freshness standards set, so your team can be alerted when raw source data diverges from your expectations.

#5: Integrate alerting

A strong testing practice includes ensuring your data test failures are actually found and fixed—using alerting! If your test runs are automated—meaning they might be scheduled or triggered with an orchestrator or certain events—then your data team may not always be aware that tests are running (and failing). When failures occur in automated tests, your team needs to know about them immediately.

In dbt, alerting can be used in the traditional sense: to alert the data team (or a specific person) when tests fail so they know when something is wrong with the code or data. Alert notifications can be delivered by email or via Slack.

Alerts can also be used in coordination with tests that validate data in the ETL process. For example, the data team can be alerted to the following issues:

Incorrect or unexpected data is coming in from the data pipeline.
There is a discrepancy in terms of data quality incoming from data sources.

If there is a difference in the data quality, alerts can be given different classifications. For example a “not urgent” alert can signify a data issue that is important but not needing immediate intervention. This type of classification nudges the data team to review the new data at an opportune time and understand where it has changed. Alert classifications help reduce the tendency to abandon current tasks and focus immediate attention on solving test issues irrespective of their severity.

Another option related to alerting is using dbt logs. By gathering and placing them in many existing logging technologies, you are able to aggregate and trigger certain log messages and tags. It’s also possible to enable structure logging with JSON, which of course provides more options for richly processing all types of logs and events.

#6a: Establish test ownership

Unclear expectations about triaging tests within a team will lead to failing tests being left ignored or broken code being left unfixed. When end users use broken data they will lose trust across all data. This severely damages a company’s ability to make informed decisions.

Some organizations segment ownership by scheduling, whereby each team member deals with any test failures that occur within a set window of time. This kind of approach spreads out responsibility and encourages all data practitioners to gain a better understanding of why tests fail. In addition, team members not currently scheduled to handle test failures can focus on their other tasks without the threat of being pulled away to deal with test issues.

In a large environment many different people may have created the original dbt tests. While the generic ones are fairly easy to evaluate it can be useful to label who created the test as an owner of it within dbt so that whoever is investigating the failure can reach out to them. While dbt doesn’t (yet) have an owner property that can be associated with models, providing additional context, such as a test author or owner, is possible through the use of key-value pairs in the meta config, like the following:

models:
  - name: source_table
    config:
      meta:
        owner:
          name: John Doe

#6b: Create testing documentation

It is vital to create good documentation for your data tests so future stakeholders, such as data analysts or engineers can easily understand their purpose and extend them when appropriate. Documentation provides a clear template that others can utilize, establishes who owns data test failures, and it helps your stakeholders to be autonomous. With documentation in hand, these stakeholders will not need to seek out the author of the tests to ask questions.

Documentation should provide the purpose of the test and why a test would fail. dbt can auto-generate documentation with information about your project and your data warehouse. You can add a description key in your test files to provide context or guidance regarding a test. For example:

version: 2

models:
  - name: fact_transaction
    description: This table contains primary transaction data, including product, quantity, and amount
    tests:
      - dbt_utils.expression_is_true:
          expression: "quantity > 0"

Documentation increases development velocity because there are clear cues and pointers as to what the tests are analyzing, along with the expected results of those tests.

#7: We cannot iterate this enough: Please test your data during CI

While all of the above recommendations are great for ensuring you and your team are testing your data while you develop your dbt models, the best way to guarantee data quality is tested is through continuous integration (CI) testing.

For those who are interested in learning more about what CI is, check out this guide here.

Having your dbt project use CI enables the following:

Ensure data quality testing standards you set for your team are always run for every proposed dbt code change
Allows your team to review code changes and test results before changes are merged into production
Run additional tests like Datafold or linters like SQLFluff, so you’re code is always following best practices

Usually in a CI process, you’ll have the following added as a GitHub action:

Build, run, and test dbt model(s) changed in a PR’
Run a SQL linter to ensure your code changes are formatted properly
Run Datafold’s data diff, so you know exactly how your code changes will change your production data
Optionally, check for code coverage and change database grants as needed

For more information on how to implement CI for your dbt project, check out the following resources:

Datafold Cloud has a native CI integration that automatically runs data diffs and shows you potential changes, impacts to downstream models and even BI tool assets (!!). To learn more about Datafold Cloud, reach out to our team today!

Beyond dbt tests: automating testing with data diff

Implementing dbt tests can be an effective way to proactively improve data quality. However, the major pitfall of dbt tests is scalability: each dbt test needs to be manually defined, implemented and tuned, making it virtually impossible to cover all potential failure scenarios. Another risk is blindspots: we write tests to check for failure scenarios that either have occurred or that we anticipate, often missing major potential issues. In other words, we don't know what we don't know when it comes to testing.

This is where data diff – a tool for comparing datasets – can come handy. Similarly how we run dbt tests on staging environment to validate changes to the dbt SQL, we can run data diff to compare the staging data with production data to see how each code change will impact the data. The power of data diff is in its ability to highlight all changes and therefore helping analytics engineers catch all potential issues without having to write tests beforehand.

Datafold's data diff shows you row-by-row and value-by-value how two tables might differ (left), as well as let you know during your PR how your code changes will change production data (left).

Datafold Cloud leverages data diff to provide seamless integrations with dbt Cloud and dbt Core that enable dbt developers to:

Easily compare data between staging and production with powerful UI visualizations
Implement data diff in CI/CD to automate pull request reviews and test every change to the dbt code base
Prevent breakages in downstream data apps and BI tools with column-level lineage analysis

So, we just through a lot of information at you. If we had to sum it up, we always recommend:

Start off simply and work towards complexity: Always you generic tests to cover your foundational assertions about your data
Establish accountability: By creating documentation and ownership, ensure no test failure goes unsolved
Test your data during CI: Ensure every single PR undergoes the same testing standards and data quality is transparent
Diff your data: The only way to prevent unforeseen data quality issues is by diffing your dev and prod versions of a table, so you know exactly how your data differs between the two

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.

Looking to level up data testing? Download our new guide to testing with dbt

Discover the goals of testing with dbt, types of tests, principles and best practices for an effective testing strategy.

Datafold is the fastest way to test dbt code changes

Book a Demo