We can extend our dbt capabilities through packages just like packages in other languages like nuget in dotnet. In fact, we might already been using packages already, if you are following previous tutorials where we use materialization and sources keyword - this package is called dbt_utils.
Let's see how we can confugure it :-
packages:
- package: dbt-labs/dbt_utils
version: ">=1.3.0"
- package: dbt-labs/audit_helper
version: 0.14.0
And them run "dbt deps"
That's how we can install it If you're looking out for more dependencies then please go to dbt hub.https://hub.getdbt.com/
So you have the dbt util install how can you use it? Let's try dbt_utils.equal_rowcount -
this is quite a simple example to compare 2 table in your test if they have the same row count. In your model directory and look for your model that you like to compare. In my case it is model/snowflake_sample_data
and then add the tests in
models:
- name: snowflake_sample_data_store_sales
description: List of store sales records with basic cleaning and transformation applied.
columns:
- name: SS_ITEM_SK
description: The unique key for each location.
data_tests:
- not_null
tests:
- dbt_utils.equal_rowcount:
arguments:
compare_model: ref('snowflake_sample_data_store_sales')
unit_tests:
- name: test_source_and_output_consistency
description: "Check that opened_at timestamp is properly truncated to a date."
model: snowflake_sample_data_store_sales
given:
- input: source('SNOWFLAKE_SAMPLE_DATA', 'store_sales') # ← Fix: Use proper source reference
rows:
- SS_ITEM_SK: 1
SS_SOLD_DATE_SK: 20160901
SS_SOLD_TIME_SK: 123456
SS_NET_PAID: 80.00
SS_NET_PAID_INC_TAX: 100.00
SS_NET_PROFIT: 30.00
expect:
rows:
- SS_ITEM_SK: 1
SS_SOLD_DATE_SK: 20160901
SS_SOLD_TIME_SK: 123456
SS_NET_PAID: 80.00
SS_NET_PAID_INC_TAX: 100.00
SS_NET_PROFIT: 30.00
- SS_ITEM_SK: 1
SS_SOLD_DATE_SK: 20160901
SS_SOLD_TIME_SK: 123456
SS_NET_PAID: 80.00
SS_NET_PAID_INC_TAX: 100.00
SS_NET_PROFIT: 30.00
because this is a test so we run "dbt test", we will get an error
and as you can see here after fixing up our code, we have the verification successfully passed.
Comments