Why advanced testing catches subtle data issues in dbt - Visual Breakdown

Concept Flow - Why advanced testing catches subtle data issues

Start: Data Load

↓

Basic Tests Run

↓

Detect Obvious Errors?

No→Proceed

|Yes↓

Fix Errors

↓

Advanced Tests Run

↓

Detect Subtle Issues?

No→Data Ready

|Yes↓

Investigate & Fix

↓

Repeat Advanced Tests

Data first passes basic tests to catch clear errors, then advanced tests find subtle issues before data is ready.

Execution Sample

dbt

select * from users where email is null;
-- Basic test: check missing emails

select count(distinct user_id) from orders;
-- Advanced test: check unique user orders count

select count(*) from orders where order_amount < 0;
-- Advanced test: check for negative amounts

This code runs basic and advanced tests to find missing emails and subtle data problems like duplicates or negative values.

Execution Table

Step	Test Description	Query Result	Issue Detected	Action Taken
1	Basic test: missing emails	3 rows found	Yes - missing emails	Flag and fix missing emails
2	Advanced test: unique user orders count	Count matches expected	No	No action needed
3	Advanced test: negative order amounts	2 rows found	Yes - negative amounts	Investigate and correct data
4	Re-run advanced tests after fixes	No issues found	No	Data ready for use

💡 All tests passed after fixes, data is clean and ready

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
missing_emails_count	unknown	3	3	0	0
unique_user_orders_count	unknown	unknown	expected count	expected count	expected count
negative_order_amounts_count	unknown	unknown	unknown	2	0

Key Moments - 2 Insights

Why do basic tests not catch negative order amounts?

Basic tests check obvious errors like missing values (see Step 1 in execution_table), but negative amounts are subtle and require advanced tests (Step 3).

Why run advanced tests after fixing basic errors?

Fixing basic errors can reveal or affect subtle issues, so advanced tests are repeated to ensure all problems are caught (Step 4).

Visual Quiz - 3 Questions

Look at the execution table, how many missing emails were found in the basic test?

A 3

B 2

C 0

D 5

💡 Hint

Check Step 1 under 'Query Result' in the execution_table

At which step were negative order amounts detected?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for 'negative order amounts' in the 'Test Description' column

If the advanced test for unique user orders count failed, what would change in the execution table?

A Step 2 would show 'No' under 'Issue Detected'

B Step 2 would show 'Yes' under 'Issue Detected'

C Step 1 would show 'Yes' under 'Issue Detected'

D Step 4 would be skipped

💡 Hint

Check how 'Issue Detected' is marked for each step in the execution_table

Concept Snapshot

Advanced testing in dbt:
- Runs after basic tests catch obvious errors
- Finds subtle data issues like duplicates or invalid values
- Helps ensure data quality before use
- Requires writing specific SQL tests
- Repeat tests after fixes to confirm data is clean

Full Transcript

In dbt, data quality is checked first by basic tests that catch clear errors like missing values. Then, advanced tests run to find subtle problems such as duplicates or negative amounts. The process starts with loading data, running basic tests, fixing any found errors, then running advanced tests. If advanced tests find issues, those are investigated and fixed, and tests are repeated until no issues remain. This layered testing ensures data is reliable and ready for analysis or reporting.