in this video we'll go over freshness in dbt that you can use to test and check
your sources to make sure that they are as updated or fresh as you expect them to be
freshness will ultimately be a block of code that we can add
to an entire source or to individual tables as well
here in visual studio i have a source file that's reflecting a table that i made that's just called
my favorite source and if we go on snowflake it looks something like this it's just very simple
a name a created at column a timestamp column and then i have a column here that i added to just let
me know how long has it been since this record has been inserted so we can see here all these
were inserted into this table about 20 minutes ago now what we want to do is test this table
to make sure that it's as updated as we expect it to be you know maybe 21 minutes is too long
you know man we want to be we want to be flagged when it crosses let's say 15 minutes or something
in this case we'll use we're going to base it off of this created at if we scroll down to an example
there's two places you can do this you can do this on this level here above the tables which means
that any table within this will be tested with the same settings here but maybe you have some
specific criteria for an individual table or you just want this to test on one table in your file
you could do it there as well i'll copy this into hours and we're going to just do it on the whole
schema it doesn't matter for our case because there's only one table so let's review this
so now let's review the syntax here of what's going on we have our freshness block that's
where you indicate that this is what's what's going on here when we run this source freshness
when do we want this to to warn us and when do we want it to error and here's where we can set that
count and period go hand in hand so in this case this is saying warn us after 12 hours and
error after 24 hours in our case if we remember it was about 20 minutes since they were uploaded
so let's say we want it to warn after 20 minute and error after 30 minute this can be either day
minute or hour loaded at field what field are we looking at here what are we basing this off of
in our case like we said we will use this created at field so i'll put that there now the last
option is filter we'll touch on that in a little bit now the command we want to run
is dbt source snapshot freshness and what this is going to do is
look through your project and identify any place where this freshness block exists
and if it exists it's going to compile some sql and run it as if it were a test so let's run this
and we can see this failed but if we look in snowflake it's only been 27 minutes
so why did that fail and the reason this failed is because this loaded at field is expecting a
utc value in my case my snowflake setting is not utc and to check what yours is you can run this
code right here if you're on snowflake show parameters like time zone
and mine is set to los angeles so it's in pacific time zone but dbt is comparing it to utc so it's
out of whack that's why it made it seem like it's so much further out and what they recommend here
is they say if using a non-utc timestamp cast it to utc first and that's what we'll do here
but what we'll want to do this is actually backwards for us we want this to go from
not utc to la we want it to go from los angeles to utc and so what this will do is then compare
this converted timestamp which will now be converted to utc against the current time
in utc so now let's run this again and now we see that it's warning that's because it's less than 50
but greater than 30. now if we said we want warren to be one hour and error
to be two hour we would expect this to pass now and it does the time zone is something you need
to be aware of if you're not already using a utc time stamp if you are then you don't have to worry
about that and then the count and period allows you to adjust how you want this to be tested
now the last part we'll touch on that we haven't already is this filter component here and this
will be used in that compiled sql essentially it's just going to take whatever you put here and add
it as a where clause and the reason you would want to have this to help with performance and make
sure you're not you know returning unnecessary records to compare when it really doesn't matter
so first what i'm going to do is insert some new records to give us something new to compare to
so now if i do this and we compare we can see 44 and then zero so let's add this filter and
the filter will go right below the worn after and error after in their case they did this
let's do instead of day let's do minute where it's less than 40. and we'll do that so that we can
you know prevent us from looking at these and with that being said we can say warn us if
greater than one minute but less than five minutes without this filter we would expect this to error
because we have all those records that are over 40 minutes that would error this but with this
filter we're now excluding those and saying of that remaining set run this test so let's try it
and we get warn which is what we would expect because at this point this is greater than one
they're now at two we do five and ten this this is likely to pass so that's how you can
use the filter to actually trim down your data set help with performance you can make
this as complicated as you want because all it's really going to do is add it as a where clause
so lastly what i'll do is show you you know where this information is stored and how this is
working under the hood a little bit so like many other things will go to the target
folder here and you'll get a new object called sources.json and if we open this up i'll format
it this is where it's storing information about your latest run your latest source freshness test
so to speak you can see the max loaded that time in seconds so that's really where it's taking the
results of those queries storing it looking at the difference and saying did it pass fail what
was this based off of what other conditions and this will break it down for each source
individually right now we only have it for one but if you had many you would see all of those here
and you could hop in here and just you know get a quick pulse on where things were what happened
when was the last time it ran etcetera that'll do it for this video i think source freshness can
be a really helpful tool especially if you have strict slas on how updated some sources need to be
or you just want to keep a pulse on it for yourself this is a great built-in way to do this
and it's pretty straightforward and easy to use once you understand you know the basics
of the code block as always thank you for watching if you found this helpful feel free
to like the video leave a comment i'll try to answer your questions and subscribe if you'd
like to see some more content like this as well thanks again and i'll see you at the next video