Generic tests with parameters in dbt - Mini Project: Build & Apply

Generic tests with parameters

📖 Scenario: You are working on a data warehouse project using dbt. You want to ensure data quality by creating generic tests that can be reused with different parameters on various tables and columns.

🎯 Goal: Build a reusable generic test in dbt that accepts parameters for table name, column name, and a threshold value, then apply this test to check if the number of null values in a column exceeds the threshold.

📋 What You'll Learn

Create a generic test SQL file that accepts parameters: model, column_name, and max_nulls

Write a SQL query inside the test that counts nulls in the specified column of the specified model

Fail the test if the count of nulls is greater than max_nulls

Configure the test in schema.yml with parameters for a specific model and column

Run the test and output the result

💡 Why This Matters

🌍 Real World

Data teams use generic tests in dbt to enforce data quality rules consistently across many tables and columns without rewriting SQL for each case.

💼 Career

Knowing how to write and configure generic tests with parameters is essential for data engineers and analysts working with dbt to maintain reliable data pipelines.

1

Create a generic test SQL file

Create a generic test SQL file named tests/not_too_many_nulls.sql with parameters model, column_name, and max_nulls. Write a SQL query that counts the number of nulls in {{ column_name }} of {{ model }}. The query should select the count of nulls as null_count.

dbt

-- Your code here to create tests/not_too_many_nulls.sql with parameters and count nulls

Need a hint?

Use Jinja templating to accept model and column_name parameters. Count rows where the column is null.

2

Add a threshold parameter for max nulls

Modify the generic test SQL file tests/not_too_many_nulls.sql to fail the test if the count of nulls is greater than the parameter max_nulls. Use a having clause or where clause to compare null_count with max_nulls.

dbt

{% raw %}-- Modify tests/not_too_many_nulls.sql to compare null_count with max_nulls
-- Your code here{% endraw %}

Need a hint?

Use having count(*) > {{ max_nulls }} to fail the test when nulls exceed the threshold.

3

Configure the test in schema.yml with parameters

In your schema.yml file, configure the generic test not_too_many_nulls for the model customers and column email. Set the parameter max_nulls to 5. Use the tests key with name and args to pass parameters.

dbt

# Your code here to add the test configuration in schema.yml

Need a hint?

Use the tests list under the column with the test name and parameters.

4

Run the test and display the result

Run the dbt test command to execute your generic test on the customers model. Then, print the test result summary showing if the test passed or failed.

dbt

# Your code here to run dbt test and print the result summary

Need a hint?

Use dbt test --select customers to run tests on the customers model.