Store test failures for analysis in dbt - Mini Project: Build & Apply

Store test failures for analysis

📖 Scenario: You work in a data team that uses dbt to build and test data models. You want to capture the details of test failures so you can analyze and fix data quality issues more easily.

🎯 Goal: Create a dbt model that stores the results of test failures in a table for further analysis.

📋 What You'll Learn

Create a source table with sample test failure data

Add a configuration variable to filter failures by severity

Write a dbt model that selects failures based on the configuration

Output the filtered failures for review

💡 Why This Matters

🌍 Real World

Data teams use this approach to track and analyze data quality test failures over time, helping them prioritize fixes.

💼 Career

Knowing how to store and filter test failures is important for data analysts and engineers to maintain reliable data pipelines.

1

Create a source table with test failure data

Create a source table called test_failures with these exact columns and values: test_name (string), failure_reason (string), severity (string). Insert these rows exactly: ('null_check', 'Null values found', 'high'), ('unique_check', 'Duplicates found', 'medium'), ('value_range', 'Values out of range', 'low').

dbt

# Create the source table test_failures with the specified rows
# Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO statements with the exact column names and values.

2

Add a configuration variable for severity filter

Create a dbt variable called severity_filter and set it to the string 'high' to filter failures by severity.

dbt

create table test_failures (
    test_name varchar,
    failure_reason varchar,
    severity varchar
);

insert into test_failures (test_name, failure_reason, severity) values
('null_check', 'Null values found', 'high'),
('unique_check', 'Duplicates found', 'medium'),
('value_range', 'Values out of range', 'low');

# Define the severity_filter variable below
# Your code here

Need a hint?

Use dbt Jinja syntax to set the variable severity_filter to 'high'.

3

Write a dbt model to select failures by severity

Write a dbt model SQL query that selects all columns from test_failures where severity equals the variable severity_filter.

dbt

create table test_failures (
    test_name varchar,
    failure_reason varchar,
    severity varchar
);

insert into test_failures (test_name, failure_reason, severity) values
('null_check', 'Null values found', 'high'),
('unique_check', 'Duplicates found', 'medium'),
('value_range', 'Values out of range', 'low');

{% set severity_filter = 'high' %}

select * from test_failures where severity = '{{ severity_filter }}'
# Your code here

Need a hint?

Use a SELECT statement with a WHERE clause comparing severity to the variable severity_filter using Jinja templating.

4

Output the filtered test failures

Add a print statement to output the results of the dbt model query that selects failures with severity equal to severity_filter.

dbt

create table test_failures (
    test_name varchar,
    failure_reason varchar,
    severity varchar
);

insert into test_failures (test_name, failure_reason, severity) values
('null_check', 'Null values found', 'high'),
('unique_check', 'Duplicates found', 'medium'),
('value_range', 'Values out of range', 'low');

{% set severity_filter = 'high' %}

select * from test_failures where severity = '{{ severity_filter }}'

# Print the filtered failures below
# Your code here

Need a hint?

Use the dbt log function to print the filtered severity value.