Test in dbt

Posted on by By admin, in DBT | 0

Singular tests

A singular test is a SQL SELECT statement that makes an assertion in reference to a specific model and its specific columns. Singular tests are sql files that live in the tests folder and use the ref function to call a specific model.

— This is a singular test designed to test that the average of a returning customer’s order is greater than or equal to one. {{ config(enabled = false) }} select customer_id, avg(amount) as average_amount from {{ ref(‘orders’) }} group by 1 having count(customer_id) > 1 and average_amount< 1

Custom generic tests

We can promote singular tests to generic tests, a function built in SQL and Jinja, that utilizes input parameters, like a macro. Rather than a Jinja macro tag, generic tests use a test tag, and we store the generic test in the tests/generic folder. Custom generic tests make it easy to add testing logic in one place and apply it to several models.

{% test average_dollars_spent_greater_than_one( model, column_name, group_by_column) %}
{{ group_by_column }},
avg( {{ column_name }} ) as average_amount
from {{ model }}
group by 1
having average_amount< 1
{% endtest %}

Packages in dbt project

There are three packages that are must-haves for any dbt project: dbt_utils, dbt_expectations, and audit_helper.


dbt_utils is a one-stop-shop for several key functions and tests that you’ll use every day in your project.

Here are some useful tests in dbt_utils:



dbt_expectationscontains a large number of tests that you may not find native to dbt or dbt_utils. If you are familiar with Python’s great_expectations, this package might be for you!

Here are some useful tests in dbt_expectations:



This package is utilized when you are making significant changes to your models, and you want to be sure the updates do not change the resulting data. The audit helper functions will only be run in the IDE, rather than a test performed in deployment.

Here are some useful tools in audit_helper:


We at Helical have more than 10 years of experience in providing solutions and services in the domain of data and have served more than 85+ clients. We are also DBT partners, hence in case if you are looking for certain assistance, consulting, services please do reach out on nikhilesh@Helicaltech.com

Thank You
Nikitha Rastapuram
Helical IT Solutions

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments