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) %}
select
{{ 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
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:
unique_where not_null_where unique_combination_of_columns
dbt_expectations
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:
expect_column_values_to_be_between expect_row_values_to_have_data_for_every_n_datepart expect_column_values_to_be_within_n_moving_stdevs
audit_helper
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:
compare_relations compare_queries compare_column_values compare_relation_columns
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