Why advanced testing catches subtle data issues in dbt - See It in Action

Why advanced testing catches subtle data issues

📖 Scenario: Imagine you work with a sales database. You want to make sure the data is clean and correct before using it for reports. Simple checks catch obvious mistakes, but some problems are hidden and need smarter tests.

🎯 Goal: You will create a small sales data table, add a configuration for a threshold, write an advanced test to find subtle data issues, and then display the test results.

📋 What You'll Learn

Create a dbt model with sample sales data

Add a config variable for minimum valid sales amount

Write a custom test to find sales below the threshold or with missing customer IDs

Run the test and show the results

💡 Why This Matters

🌍 Real World

In real companies, data often has hidden problems that simple checks miss. Advanced testing helps catch these early to keep reports accurate.

💼 Career

Data analysts and engineers use advanced tests in dbt to ensure data quality before analysis or reporting.

1

Create a dbt model with sample sales data

Create a dbt model called sales_data.sql with a table containing these exact rows: (1, 'Alice', 100), (2, 'Bob', 50), (3, NULL, 30), (4, 'Dana', 0), (5, 'Eve', 200). The columns should be sale_id, customer_name, and amount.

dbt

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

Need a hint?

Use a CTE with union all to create the rows exactly as shown.

2

Add a config variable for minimum valid sales amount

In the same model file, add a variable called min_valid_amount and set it to 10. This will be used to check for subtle data issues later.

dbt

with sales as (
    select 1 as sale_id, 'Alice' as customer_name, 100 as amount union all
    select 2, 'Bob', 50 union all
    select 3, null, 30 union all
    select 4, 'Dana', 0 union all
    select 5, 'Eve', 200
)

-- Add a variable min_valid_amount set to 10
-- Your code here

Need a hint?

Use a second CTE named config to hold the variable.

3

Write a custom test to find subtle data issues

Create a dbt test SQL file called test_subtle_issues.sql that selects rows from sales_data where amount is less than min_valid_amount or customer_name is NULL. Use a join with the config CTE to get min_valid_amount. Use exact column names sale_id, customer_name, and amount.

dbt

-- Create a test query to find sales with amount < min_valid_amount or customer_name is NULL
-- Your code here

Need a hint?

Use a cross join with config to access min_valid_amount in the WHERE clause.

4

Run the test and show the results

Write a SQL statement to run the test query and print the rows that fail the test. Use select sale_id, customer_name, amount from and the test query from Step 3.

dbt

-- Print the rows that fail the test
-- Your code here

Need a hint?

The output should show rows with sale_id 3 and 4 because they have subtle data issues.