How to capture dbt expectations tests on Big query

Help
, , , ,
1

Hi All,

I am planning to use dbt_expectations package to do data quality checks in Big query using dbt.
After defining data quality checks in schema.yaml , when I run dbt TEST, it’s showing failures on console.

  1. Is there any way to call dbt_expectations macros in dbt models? (Like normal function calls in each model instead of defining in schema. Yaml)
  2. If I use dbt test , how to capture passed and failures report on Big query for each column level?

Basically I am looking for some framework which I can use in models to capture data quality checks on Big query.

Thanks!
Chandra

2

Hi @vennapuc ,

  1. I didn’t understand why it is bad for you to define the tests in the .yml and why would it be better to call a macro in the .sql. Anyway, as they are tests, I think you can only define them in the .yml.

  2. I am not sure this is what you want, but have you tried store_failures? store_failures | dbt Developer Hub

3

Hi @brunoszdl,
We have Raw | Staging | curation layers. So I need to apply controls in raw to staging layer.
If any records failed, so need to filter out and execute the curation layer for only for passed records. So I was looking for approach to call dbt_expectations macros and add audit columns in schema layer like status, reason for failed etc…

I have tried using store_failures- option but its only storing test and reason but not all columns from table.

So is there way to use dbt_expectations macros in .sql so that I can apply at column level and will add status and reason etc … to each schema table after calling macros so.

Thanks!.