hello everyone and welcome back to another video on dbt in the last video we went through
installing and setting up your first project and now in this video we will focus on
on sources and adding sources to your project and trying to understand why you would need that
and how it's used so sources are ultimately there a yaml file in the terms of dbt and
the way i like to think about this is sources are the raw data tables that everything else
gets built on top of the default location for sources is set to the models directory
and you can see that if we hop back in here in our dbt project it's set here
you can change that for the sake of this we won't but just understand that that is an option
and you can learn a lot more about the different configurations and properties that you can set by
going to these links here and i'll make sure to attach this so what i will be doing here in my
for as an example in our snowflake environment is looking at the snowflake sample data database and
this is provided when you sign up for an account and it reminds me a lot of adventure works with
some of the microsoft stack if you're familiar with that and the schema we will look at is this
one right here just has a couple built-in tables within it so what we want to do is set something
like let's say store sales we want to be able to refer to this and as a source so how would we add
this as a source and that's what we'll do here so let's hop back in here if we go into models
remember we have this example folder but let's make a new one
and i'll call this snowflake sample data how you name this is completely up to you at the end
of the day everything in dbt is just a bunch of files and folders so you can add as many folders
and files as you want break it down how you want it's it's really up to you so we'll create a new
folder here and within this we'll just create a schema.yaml file understanding how yaml files work
is outside the scope of this video and the way i like to think about yaml is it's very similar
to json and the way it just it's nesting objects together now yaml files can be used to describe
models themselves you can add descriptions and tests to models as well as sources so you'll
see both of those often intermingling within dbt in this case as we said we're focused on sources
so how would we create a source yaml file so let's hop back to the documentation we're at sources
and here is an example like they show models filename.yaml it really doesn't
matter what you call it let's let's copy this just as a starting point
now the way this is broken down is up top you have version two that just needs to be there
by default i don't know exactly why that's needed but just understand that you have to put it there
sources here let's the engine recognize that this is for sources it's not for models
you give a name to the source and then you can identify the different tables involved in this
case there's two different sources with different tables for our sake here let's just focus on one
and we will say name i'm going to match it with the name of the database so i will call this
snowflake sample data and the table we will be concerned with right now is just store sales
some other information i'd like to add to this is the specific database and schema
and if we go to the source properties i believe it should break it down yeah so there's a lot
of information you can add here in our case i'd like to add these three actually
description database and schema i think are good ones to start with so we'll add this here
give a description to the to the source we'll just i mean it doesn't really matter we'll just say uh
a sample database provided by snowflake
database is the name and the schema is this guy right here which is this
and the table name so you can see here we're just giving the description of these different
attributes just so we don't confuse this here this is the name of the source this is how we will
refer to it and this is the name of the database in snowflake itself so anywhere we use that source
function we need to refer to it from this name here not none
not here just because they're the same here it doesn't matter this is
how it will be referred to throughout dbt so then we have again the schema and the table
we could add a description to this as well and say
details about the sales in the store not very useful but you get the idea and now if you
really wanted you could even get as specific as the columns you could say you could add columns
i think it's like this actually columns and go line by line through each column and add tests and
add descriptions and get really really specific but for the sake of this let's just keep it here
so now we have a source we've created our source we've given it a name a description
pointing it to a database schema etc now how can we use this let's create
a model that just selects from this sample data we'll create a model for the store sales
we'll name this snowflake sample data to underscore store sales
dot sql now the naming of this there's there's a whole bunch of opinions on how you would name it
but what is most important is that each model needs to have a unique name
you cannot have two models with the same name and the files need to be unique a common strategy is
prefix it with the name of the folder that way you can be sure that the file names are unique
that and then ultimately how you name the models are unique and you can kind of save yourself
there as we saw in the first video the models are just sql scripts with some ginger in it
that ultimately create the data model in our data warehouse there's also a ton of
documentation and discussion on the best ways to write your model scripts and to format your syntax
here again i'm not going to get too far into that there's there's a ton of information that
you can find on it but one of the most common ways to do this is to use ctes or common table
expressions to group up your information and then have a final select so for example here
with and then the name of what you want to call this so we'll call this source store
sales and then within these brackets is where you can do your select statement
and what we will do here is just select all from and in this case instead of putting the actual
full name of the table we can use the source shortcut so let's go back
using sources we can use this source jinja function just like they have it here
so let's do that
select all from the source source name table
i'm going to split this up here side by side now the source name again that is the name here so
let's put this in here in quotations and then the table name will be in this case store sales
so what this is saying is it's going to look for any source file that has this name
and the table with this name got it so now
we will select all from from that cte a way you'll actually see this is this actually puts
everything into a final table so even though we only have one cte it seems a
little redundant but again feel free to check out the documentation of why this is used
obviously this would be a lot more interesting if there was more logic but the idea is to finish
with a select from final in everything that you do start with your source you put everything in
ctes and then you select from a final cte so now let's try to run this if we open up a terminal
and let's do a dbt run and what this should do is actually look for all the models in
our project so it will also find these as well as this new one so it's running three models
and they were all successful that's great which means that this successfully used this source
to copy this information and in our case move it from this source to our other database where we're
pointing this dbt project and if you remember from the last video we pointed that to our
to the demo db so everything should land in this demo db
and we have pointed it to public and if we look in here we have the first ones as well as our new one
so here it is in the next video i'll show you a little bit more about
how this data is actually compiled under the hood and what it looks like in case you ever want to
debug and understand the actual logic going on behind it so thank you for watching if you have
any questions please leave a comment and we'll see you at the next video