Why testing ensures data quality in dbt - See It in Action

Why testing ensures data quality

📖 Scenario: You work as a data analyst in a company that uses dbt to manage data transformations. Your team wants to make sure the data is accurate and reliable before using it for reports.

🎯 Goal: You will create a simple dbt model and add tests to check data quality. This will help catch errors early and keep data trustworthy.

📋 What You'll Learn

Create a dbt model with sample data

Add a config variable to set a threshold

Write a test to check for null values in a column

Write a test to check that values meet the threshold

Display the test results

💡 Why This Matters

🌍 Real World

Data teams use testing in dbt to ensure data pipelines produce accurate and clean data before reports or dashboards use it.

💼 Career

Knowing how to write and run tests in dbt is a key skill for data analysts and engineers to maintain high data quality.

1

Create a dbt model with sample data

Create a dbt model file called models/sample_data.sql with a table that has columns id and value. Insert these exact rows: (1, 10), (2, 20), (3, NULL), (4, 40).

dbt

-- Create the model with the specified rows
-- Your code here

Need a hint?

Use a CTE with union all to create the rows.

2

Add a config variable for minimum value threshold

In the same model file models/sample_data.sql, add a config variable called min_value_threshold and set it to 15.

dbt

with source_data as (
    select 1 as id, 10 as value union all
    select 2 as id, 20 as value union all
    select 3 as id, null as value union all
    select 4 as id, 40 as value
)

-- Add config variable min_value_threshold = 15 below

select * from source_data

Need a hint?

Use the {{ config() }} Jinja function to add variables.

3

Add tests to check data quality

Create two tests in tests folder: one test called test_no_null_values.sql that checks value column has no nulls, and another test called test_value_above_threshold.sql that checks all value entries are greater than or equal to min_value_threshold variable.

dbt

-- Create test_no_null_values.sql to check no nulls in value
-- Create test_value_above_threshold.sql to check value >= min_value_threshold
-- Your code here

Need a hint?

Write SQL queries that return rows violating the test condition.

4

Run tests and display results

Run the dbt tests and print the summary output showing which tests passed or failed.

dbt

# Run dbt test command and print results
# Your code here

Need a hint?

Use the dbt test command in your terminal to run tests and see results.