Singular tests in DBT for Snowflake (Part VII)

Introduction

Singular tests are tests that are not the same as standard tests. There are four standard tests but it is also possible to define your own tests in DBT. In this blogpost I show you the way how to do this in DBT. 

This blogpost is a blogpost in a serie of blogposts :

• Build a data warehouse with DBT and Snowflake on AdventureWorksLT (PART I)
• Build Staging and Dimensions in DBT for Snowflake (PART II)
• Sources in DBT for Snowflake (PART III)
• Data freshness for DBT in snowflake (Part IV)
• Snapshots in DBT for Snowflake (Part V)
• Generic tests in DBT for Snowflake (PART VI)
• Singular tests in DBT for Snowflake (Part VII)
• Add packages to your DBT project (Part VIII)
• Adding documentation to your DBT project (Part IX)
• Working with hooks in DBT  (Part X)
• Ephemeral tables in DBT (Part XI)

Singular test

I added a file to the test folder in the DBT project and I named it SalesOrderDetail_Qty_Greater_0.sql.

And the content of the file is the following SQL statement :

SELECT * FROM {{ref('stg_SalesOrderDetail')}} WHERE ORDERQTY > 0

Now, when I executed the statement dbt test I got the following error message. The test failed. So, the test has to be written in the opposite proposition : If the falsify expression is false then the test is succeeded ;-).

Here is the correct statement

SELECT * FROM {{ref('stg_SalesOrderDetail')}} WHERE ORDERQTY < 0

And this is the result. No records do match the expression and therefore the test is succeeded.

Final thoughts

This blogpost is an example of a singular test in DBT. 

Hennie