Since the release of dbt-core v0.20.0, dbt allows you store test results by providing the --store-failures flag at your dbt test command. Providing this flag, dbt will automatically store corrupt validations in a table per test. For more information, see the official documentation here.
This feature is great, since it provides us the opportunity to track failing tests over time. Although dbt does not support this out of the box, we can implement this ourselves through Jinja, Macros, and the dbt snapshot functionality.
Jinja & macros
First, we need to create a macro to retrieve all the test results. Lets name this macro generate_tests_stats() and store it under macros/utils/generate_tests_stats.sql in your dbt project root.
The file should have the following code, don’t worry, we’ll go over this step by step.
Step by step explanation of the macro
First, we need to get all resulting dbt test --store-failures tables.
The section above defines a call — which we could see as a query, or function — called tests_tables_query. This query then selects all the names and modified dates of all the tables in the provided schema, note that '{{ schema }}' will be filled by{%- macro generate_tests_stats(schema) -%}. We’ll get to this later.
After this call, it stores the result in a variable called table_list. The results are stored in a list of tuples.
Note: this code works on Azure Synapse, if you use another db, please rewrite this section.
Then, we need to loop over these tables to get the actual results.
Here we use a for loop. We loop over all results that we just stored in table_list and do another call, named 'test_table_count'. As you may remember, dbt only stores the non-conforming records. So a successful test will have a count(*) of 0.
We retrieve all counts from these tables, and store them in a variable called count_result. But we’re not done!
As you may have noticed, we have not yet closed our for loop. That is because we want to include the count we just saved, and include them into a SELECT.
The snippet above takes the test name and date from the variable table_name, and the number of failures from count_result and puts it all in a SELECT UNION statement.
Jinja has this nice feature called if not loop.last, which we can use to generate a correct SQL statement. FYI, the result of this statement will look something like this:
Perfect! We have all the scores together in a select statement. Now comes the magic of the dbt snapshot!
Daily snapshot
Lets create the daily snapshot now. Paste the following code into snapshots/dbt/tests_snapshot.sql
You should run this code daily by running dbt snapshot --select tag:tests. What this code does is when run, it generates the failure counts for all tests in the schema 'tests' as a SELECT UNION ALL, as we’ve just seen. Through the snapshot functionality, it looks at the test_name as the unique key, and the snapshot_date as updated_at to historise your results.
For it to work, you need to configure your tests schema first. You can can do this in your dbt_project.yml by:
tests:
+schema: "tests"
Please note that if you change this schema, you also need to change this in your snapshot.
And there you have it! The macro combined with the daily snapshot will track your dbt tests performance over time! Now you can build awesome dashboards and stay on top of your data quality 🎉 Good luck!