Testing data on existing database before creating new transformed table

Help
, , ,
1

I’m creating a transformation table from 6 existing tables on a database.

Before my model runs and creates the table I need to create a test to see if the data in the existing tables on the database is what i expect it to be eg. Int values, not below - 6 ect.

How do i create the tests that scan the tables in the database.

2

Hey @Rainier, I’ve wrote out a quick example below that works for me. I’m using dbt-core and connected to a PostgresDB so you may need to adjust the syntax/check for the pg_typeof() equivalent if you’re using another platform.

with test_cte as (
    
    select * from {{source('superstore_playground','superstore_orders')}}
    where pg_typeof(sales)::oid != 23
    or sales < -6

)

select * from test_cte

If I then run ‘dbt test’ this will show an error with the underlying table, or if executing ‘dbt build’, then I’ll get an error and models downstream of this will be skipped if there are values in my [sales] field that are below -6 (or the field isn’t an int4).

3

Hey @Rainier, are you using this data in your database as a source in dbt?

If so, you can add tests to your sources.

Here is how you can add tests to your sources: About data tests property | dbt Developer Hub (click on the sources tab)

If you need to know more about tests you can check this link: Add data tests to your DAG | dbt Developer Hub