Testing model outputs in dbt - Mini Project: Build & Apply

Testing model outputs

📖 Scenario: You are working with a data transformation project using dbt. You want to ensure that your model outputs are correct by writing tests that check the data quality and expected results.

🎯 Goal: Build a simple dbt model and write tests to verify the output data meets expected conditions.

📋 What You'll Learn

Create a dbt model with sample data

Add a configuration variable for a threshold

Write a dbt test to check model output against the threshold

Display the test results

💡 Why This Matters

🌍 Real World

Data engineers and analysts use dbt to transform data and ensure quality by writing tests that catch errors early.

💼 Career

Knowing how to write and run dbt tests is essential for roles involving data pipeline development and maintenance.

1

Create a dbt model with sample data

Create a dbt model file called models/sample_model.sql with a SELECT statement that returns these exact rows: id and value columns with values (1, 10), (2, 20), and (3, 30).

dbt

-- Create the model with id and value columns
-- Your code here

Need a hint?

Use union all to combine multiple SELECT statements with exact values.

2

Add a threshold configuration variable

In the same model file models/sample_model.sql, add a variable called threshold and set it to 15 using a with clause or CTE.

dbt

-- Add a CTE named threshold with value 15
with threshold as (select 15 as value)

-- Your code here

Need a hint?

Use a CTE to define the threshold value before the main SELECT statements.

3

Write a dbt test to check values above threshold

Create a test file tests/test_values_above_threshold.sql that selects rows from ref('sample_model') where value is less than or equal to 15. This test should fail if any such rows exist.

dbt

-- Write a test query to find rows with value <= 15
select * from {{ ref('sample_model') }} where value <= 15
-- Your code here

Need a hint?

The test query should return rows that violate the condition. Use ref('sample_model') to reference the model.

4

Display the test results

Run the dbt test command and print the output showing if the test passed or failed.

dbt

# Run 'dbt test' in your terminal to see test results
# Your code here

Need a hint?

Use the terminal command dbt test to execute tests and see results.