Custom singular tests in dbt - Mini Project: Build & Apply

Creating Custom Singular Tests in dbt

📖 Scenario: You are working on a data project using dbt to ensure your data quality. You want to create a custom singular test to check that no user has a negative age in your users table.

🎯 Goal: Build a custom singular test in dbt that checks for any negative values in the age column of the users table.

📋 What You'll Learn

Create a SQL file for the custom singular test with the exact name no_negative_age.sql.

Define a SQL query that selects rows where age < 0 from the users table.

Add a test configuration variable called model_name with the value users.

Use the model_name variable inside the SQL query to refer to the table dynamically.

Print the final SQL query string to verify the test logic.

💡 Why This Matters

🌍 Real World

Data teams use custom singular tests in dbt to ensure data quality by writing specific checks tailored to their data models.

💼 Career

Knowing how to write custom tests in dbt is valuable for data analysts and engineers to maintain trustworthy data pipelines.

1

Create the custom singular test SQL file

Create a file named no_negative_age.sql and write a SQL query that selects all rows from the users table where the age column is less than 0. Use the exact table name users in the query.

dbt

-- Write a SQL query to select rows where age is less than 0 from users table
-- Your code here

Need a hint?

Write a simple SQL SELECT statement filtering age < 0 from users.

2

Add a configuration variable for the model name

Add a variable called model_name and set it to the string users. This variable will be used to refer to the table dynamically in the test.

dbt

model_name = 'users'

-- Your code here

Need a hint?

Define a Python variable model_name with value 'users'.

3

Use the variable inside the SQL query

Modify the SQL query to use the model_name variable instead of the hardcoded table name. Use an f-string to insert model_name into the query string.

dbt

model_name = 'users'

query = f"select * from {model_name} where age < 0"
# Your code here

Need a hint?

Use an f-string to build the query with model_name inside curly braces.

4

Print the final SQL query

Print the variable query to display the final SQL query string that will be used in the custom singular test.

dbt

model_name = 'users'

query = f"select * from {model_name} where age < 0"

# Print the query
# Your code here

Need a hint?

Use print(query) to show the SQL query string.