hi everyone welcome to another video on dbt
oftentimes you'll find there are repeatable actions that you want to take either at the start
or the end of your run or maybe before and after each step along the way and the way that
dbt handles this is through what's called hooks and essentially all they are are snippets of sql
that can be executed by using these preset indicators here so in this video we'll walk
through how to use it and specifically we'll look to populate an audits table
so if we were to simply just do this in snowflake the steps we would take is one create this table
so now we can see here we have this table created and the insert statement that we'll do here
is a simple insert into this table there's a column called audit type and it's going
to pre-populate with an auto incrementing id and a timestamp to say when it was created
now let's hop back into our project here and what we'll do is add
commands here on run start on run end we'll start with these and there's two ways you can do this
you can straight up just write the sql here or you could do it as a macro and call the macro so we'll
go one at a time first we'll just put the sql here just copy and paste it we'll change this
to run start and this one will run end so what would happen here is anytime we were to run this
the first thing it's going to do before it touches the models is run this insert statement
and then at the very end it would run this one and it'll log start and end so let's let's test
that out i'm just going to run one of the very simple models here that's just a select one and
as this starts here we can see it began with the hook then it ran the model and then it ran the on
run end hook so this ran successfully and if we want to see exactly what happened here you
know let's say this was something a little more complicated just like everything else we can go
to our targets folder and this time under the project and under the hooks we can see
you know exactly what is being run here on run start on run end and there it is
name would be true if we wanted to add the pre-hook
so if you remember pre and post hook are executed for each model so if you're doing a run for all
of your models and that's 50 100 models you know depending on how you lay this out this
hook is going to run for each and every one of those so in our case let's do we'll add pre hook
and i'm going to add the same statement and instead of run i'll just say model end
post hook this as well and we would expect the same thing to happen and before we run this i'm
going to go back and just show you that these two did did actually insert so we can see these here
run start run end and this was our original one so now let's run this whole thing again
we would expect onrun start to insert on run n to insert and then we're going to run
one model so we would expect to see the model let's update this the model run
we would expect this to also insert for model start and model end so let's save and run this
it won't actually display here by default that the hooks are running we're only going to see the
on run start and on run end but behind the scenes this is happening and let's go back into snowflake
and check this again and we can see here that these did insert as well at our on run start log
the time for each model it did the same thing and on run end now you could spice this up a
little bit and include the name of the model the name of the schema and get real granular
with your actual auditing but for the sake of this demo i just wanted to show you the fact
that you can insert at that point it's up to you what you know what kind of sql you want to run
now to take this a step further as we mentioned in the beginning this can call a macro as well
it doesn't have to just be you know straight sql right here and by doing that it makes it
a little bit cleaner so what we'll do is create a new file in our macros folder and we'll call
this audits operations and essentially what we'll do is just add the logic here instead of
hard-coding it each time over and over here we'll just call this insert audit and copy
this logic here and this part will need to be dynamic so we'll add we'll add a parameter here
and i'll call this action name doesn't matter now to call a parameter
or an argument put it between the two curly brackets this is inside the
apostrophes as well because this is going to be a string now let's do this
if we come back in here we can delete this and here we can this is where we'll
put audit type information that we want we'll call this one run start and we can copy this
now for each of these and to recap here every time it comes to the run start it's going to call
this macro and we're passing in the value here which will be different for each one
as this argument here which is then getting passed here so it's more dynamic now let's run this again
so because this is a macro i made a rookie mistake and forgot to surround this macro with
the double curly braces so i went through and added those here and we should be good to go now
all right so we can see this ran successfully and if we check in snowflake
we can see these added as well obviously there's a lot more you can do here this
is a very simple example you could use hooks to add grants to run seeds
to do other inserts creating a simple audit table is just one example of how you could do it
now if you are curious specifically on auditing there is there's actually a dbt package already
created that has a lot of these macros already built and all you have to do is
plug and play essentially and you can have an audit table the name of that is called
dbt event logging and i'll add a link to the description here for you to take a look at if
you're interested and that can get you going and you don't even have to worry about most of this
stuff it's already built into it i hope you found this helpful if you did please feel free to like
the video it really does help the channel and if you really like it feel free to subscribe thanks
as always for watching hope you have a great rest your day and see you at the next video