Store test failures for analysis in dbt - Step-by-Step Execution

Concept Flow - Store test failures for analysis

Run dbt tests

↓

Check test results

↓

Identify failures?

No→End

Yes↓

Extract failure details

↓

Store failures in table

↓

Use stored data for analysis

This flow shows how dbt test failures are detected, extracted, and stored for later analysis.

Execution Sample

dbt

select * from dbt_test_results
where status = 'fail';

insert into failure_analysis
select * from dbt_test_results
where status = 'fail';

This code selects failed test results and inserts them into a failure analysis table.

Execution Table

Step	Action	Query Executed	Result	Next Step
1	Run dbt tests	dbt test	Tests run, results stored in dbt_test_results	Check test results
2	Check test results	select count(*) from dbt_test_results where status = 'fail'	3 failures found	Identify failures?
3	Identify failures?	Condition: failures > 0	True	Extract failure details
4	Extract failure details	select * from dbt_test_results where status = 'fail'	3 rows of failure details	Store failures in table
5	Store failures in table	insert into failure_analysis select * from dbt_test_results where status = 'fail'	3 rows inserted	Use stored data for analysis
6	Use stored data for analysis	select * from failure_analysis	3 failure records ready for analysis	End

💡 All failures extracted and stored; analysis can proceed.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
failures_count	0	3	3	3
failure_details_rows	empty	empty	3 rows	3 rows
failure_analysis_table	empty	empty	empty	3 rows inserted

Key Moments - 2 Insights

Why do we check if failures exist before storing them?

Checking failures_count > 0 (see execution_table step 3) avoids unnecessary inserts when no failures occurred.

What happens if we skip extracting failure details before inserting?

Without extracting (step 4), we cannot insert correct failure rows; the insert would fail or be empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, how many failures were found?

A 0

B 3

C 1

D 5

💡 Hint

Check the 'Result' column in execution_table row 2.

At which step are failure details extracted from the test results?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for 'Extract failure details' in the 'Action' column.

If no failures were found, what would happen at step 3?

A Proceed to extract failure details

B Insert empty rows into failure_analysis

C Skip storing failures and end

D Run tests again

💡 Hint

Refer to the 'Identify failures?' condition and next step in execution_table step 3.

Concept Snapshot

Store test failures for analysis in dbt:
1. Run dbt tests to get results.
2. Check if any tests failed.
3. Extract failure details only if failures exist.
4. Insert failure details into a dedicated table.
5. Use stored failures for further analysis.

Full Transcript

This visual execution trace shows how to store dbt test failures for analysis. First, dbt tests run and results are saved. Next, we count how many tests failed. If failures exist, we extract their details and insert them into a failure_analysis table. This stored data can then be used for deeper analysis. Key points include checking for failures before inserting and extracting failure details properly. The execution table and variable tracker show each step and how variables change. This helps beginners see exactly how failure data flows from test run to storage.