I tried to use recency from dbt-utils to check if data is coming as expected to source tables. For example I want to see if there is data at all in source tables for (-70 minutes from now).
But whatever parameters I use in my source.yml I always get PASS for tests done.
I am using something like this in my sources.yml:
- name: uat_demand_nosales_flatten
One thing that sticks out to me is that your datepart is
minutes when it should probably be the singular
minute. I would expect that to throw an error as opposed to silently passing though.
I would start by looking at the code in the
target directory, which will include the rendered versions of your queries to see what was actually run against the database.
If you run that query you can see why no records are being returned and iterate on the query until you find the cause.
You talked about testing your sources, but your example code refers to models. Keep in mind that if you are testing sources (the original raw tables that dbt uses in its models as opposed to the ones it creates), then there is native source freshness available: freshness | dbt Developer Hub
Thank you Joel for your reply!
By checking recency on model I’m actually checking weather data is properly coming in since this model is first in line,
But I’ll be also using source freshness on source table.
minute but didn’t observed any difference.
Checking target directory SQLs generated, that’s useful I’ll have to do more checks and report back.
target folder for my case and from what I observed I see that
current_timestamp used in recency is using:
select current_timestamp::timestamp_ntz which gives:
2022-12-01T02:22:07.700000-08:00 if you use
And in our model we are running refresh by hour like:
select trunc(dateadd(hour,-1,SYSDATE()),'HOUR') which currently gives
2022-12-01T09:00:00 and if
timestamp_tz is used thats:
I was checking difference between SYSDATE and CURRENT_DATE here. Although this is for Oracle I believe that same apply for Snowflake
Wouldn’t it be logical than that you should use SYSDATE in recency instead of CURRENT_DATE ?
Are you saying that when you change the recency query defined here to use
sysdate() instead of
current_date() that you can cause your test to fail as you expect?
Each warehouse can have a specific implementation of the
dbt.current_timestamp() macro. It looks like this is how the Oracle adapter has implemented it:
This is based on dbt utils 0.9.6 (the latest shipping version) - which version of dbt utils are you using?