hey everyone welcome back to another video on dbt in this video we'll take a look at creating
a custom schema and the changes you need to make in your project in order to make that happen
so custom schemas why would you need to do that
by default everything that we deploy here with no changes is going to deploy to our target schema
that we've put in our profiles which right now is public and if we look back in snowflake everything
is in public right now and that works but it's generally considered a best practice
not to use public and to instead use some sort of custom schema that makes a little bit more sense
and there's all sorts of documentation in terms of how you might want to go about structuring
your project in this example we will look to create a schema called staging which generally is
considered the name of you know that first layer that sits just on top of the raw data sources
if we hop back into visual studio there's two ways you can do this the first is you could actually go
into your specific model and use this config function here in this case this is going to
override anything that you have set in your project so for example here it's saying this
particular model should materialize as a table even though here we're saying
do everything as a view this will override it so you could theoretically say schema
schema equals you know staging if you wanted to we're not going to do that here instead what we
will do is set it at the project level i think this is much easier and it allows you to match
the way you create your dbt project with what actually shows up in snowflake so for example
let's say we wanted everything under this example folder to deploy to a custom schema called staging
the way we would do that is under here just under materialized add a plus and then schema the plus
is new from some recent updates to dbt i think you could actually do this here too if you wanted
and we will say staging
one thing to note as well is if you don't have the schema already built dbt will create it for you
just like it will create tables and views as it deploys it will create the schema as well now just
looking at this we would assume that this is going to just deploy to a new schema called just staging
and if we run this we will actually notice it does something a little bit different
you can see here start table model it's deploying to public underscore staging why is that why isn't
it just going to staging and if we go back into here that's not a typo it made a new
schema called public staging and deployed those models so why didn't it just go to
just straight up staging and the reason is the default setting in dbt is actually target schema
underscore custom schema it's going to concatenate it so in our case it's taking this public
and concatenating it with this so really it looks like you know public undersore staging
it's taking this and concatenating it here but we don't want that how do we get rid of that
and the way to do that is actually to update a macro that is built in
to dbt and this is one of the cool things about open source projects you're able to actually
adjust anything that comes with it and they actually suggest it now if you're following along
this will be our first example of a macro and the way that we can use this is just to copy this code
and create it on our project and it will override it so instead of using the default setting it will
recognize that we have this macro as well and use the code that we wrote under our macros folder
here let's create a new file the name of the file isn't as important as the name of the macro
but it's best practice generally to match the name it's just a little easier to work with
so we'll call this generate schema name dot sql copy this in here or paste this in here and really
what's important is this this is what it's looking for this particular macro and right now we can see
this is where that logic is happening with the underscore so if we take this out all it's going
to do is say if there is no custom schema it's going to give it the default but otherwise use the
custom one but now we've removed the underscore and this should work the way we expect it to
so it will see we have a custom schema run this logic
and just give the name of that schema so let's try again
and here we go it worked we can see it's just staging dot model name there's no
public underscore and if we go into our project or into our snowflake environment
reset we now have a staging schema with these models and we can just drop schema
public staging let's get rid of that one
and here we go so now everything in here anything under example will deploy to staging but like i
said it's also generally best practice to match the name of the folder with your environment so
why don't we change this as well let's rename this oops let's rename this to staging for it
didn't exactly matter but this will matter as well we want this this needs to align with the names
so if we wanted to keep the notes here now this is going to recognize models staging
and we're saying schema staging so now our data warehouse environment aligns with our dbt project
and we can continue to build this out in the way that we want now i hope you found this helpful
now you understand a little bit of how to create custom schemas using dbt and the slight changes
you need to make to your project thanks for watching and i'll see you at the next video