one of the most common scenarios in any sort of data architecture is the loading of data from some
source to your data warehouse and in this video we're going to look through a way you can do that
in snowflake by using an external stage all right so there are a couple steps that we need to do in
order to make this work our to-do list here one we're going to add a file to our azure storage
we're going to create a stage create a file format in snowflake which
in our case will be csv we're going to create a table where we're ultimately going to put
this data we're going to copy the data from the stage to the table we'll add some new files to
the stage and see how that works in terms of how snowflake recognizes that and then
finally we're going to talk about how to remove or purge data from the stage so let's get into it
to start off i'm going to create an azure container
and i'll call this mba team data i'm going to set this as a blob create
and to give you a little preview here's what this data looks like it's just a list of teams
i'm trying to keep this as simple as possible with three columns and we're just going to load this to
azure and then it's a snowflake so let's hop in here and i'm going to upload one of those files
okay so we've uploaded data to azure step one complete
so now let's go to step two which is to create a stage in snowflake
now i'm logged in as account admin and i'm going to go to databases
stages and create here's where it lets you choose where your data is coming from in our case we're
going to do azure select next let's give it a name we're going to put this in the public schema
and we need the url now if we go to properties what we want to do is just select this part here
the url we don't need this https because that is implied with this here copy that in now we need
a token to get this token let's go up to to the container itself and i'm going to go to shared
shared access signature and here we'll create one i'm going to select it for container object
and generate connection string and here's our token so now i have this let's go back to stages
and paste this in here just like when you create any database or view or anything you can do show
sql and this is what behind the scenes is what will actually be run but we're doing it through
the ui here so it's a little easier now let's click finish and there we go my azure stage has
been created with no privileges we're going to do this whole video as account admin but
if you wanted to have any of your other users be able to interact with this stage you would need
to grant those privileges just like you would with any other object here to confirm that this
stage exists and there's no issues we're going to write the command list my azure stage and here we
can see it's recognizing within this container we have just this one file and another thing
to point out is while this is sitting here this is technically taking up storage on snowflake as well
there will be an impact to your credits in terms of storage for what's in here and we'll touch
on that a little bit later now that we've created the stage our next step is to create a file format
so why do we need a file format snowflake doesn't know exactly what it's working with here
it recognizes this files here but in order for it to copy it from that location into some sort
of table that snowflake is going to work with it needs to understand what it is now so to do that
we'll go back here to databases and let's go to file format and another
thing i should mention is all of this is happening on our demo db database we're
creating this stage there we're creating the file format there and all this is happening
in that context here okay file formats pretty straightforward create we're going to call this
csv we'll keep this the same public format here and this is where you can nudge snowflake so that
it understands what it's working with in our case csv there are different separators again comma
new line i'm going to say header lines to skip i'm going to say one because we do have
this header here you can adjust this as you need but we will finish with that so i'll say finish
and now we have a new file format created for csv and we're ready to go
okay step four we need to create a table in our snowflake environment for where we want
to actually put this data and to do this i'm going to just create a simple table
called mba teams with the columns that i know will be coming through now you'd actually need
permissions here for access and selecting and whatnot but again we're using account admin
all right great so now we have our table created the next step is to now
run the command to copy the data from the stage to the table
to copy data from a stage to a table is pretty easy the command is copy into and
i've put the name of the table which is the one we just created in mba teams from
and anytime you want to reference a stage you need to use the at symbol so just like we actually did
this up here at my azure stage do that again here so we're saying from here copy it into this table
and the file format is csv so this is where we're letting snowflake know what it's working with
first let me just prove that there's nothing in here now let's copy and here's the result we get
status loaded rows parsed loaded and no issues let's select from the table and we can see the
data is there so great that was a success we were able to copy the data from our azure environment
to a table in snowflake by using this stage
now let's see what happens when we add a new file to our azure storage
and then how we can make sure that that data then gets to
our table i'm going to upload another file here i'll do teams2 it's just a couple more teams
upload and if we go back to snowflake and now we list what's in our azure stage remember initially
it recognized that one initial csv file now it has two we can see teams.csv teams2.csv
so now this new file is available in our stage but it's not yet in the table we just have these first
five original ones but now let's run this again copy into our table and here it recognized just
the three new rows it's smart enough to know that we don't need to re-add those first five because
they're already there and if we select from the table we should have eight and we do now obviously
this is a simple example but imagine if this is a data lake where you're constantly adding new data
from various source systems and you know its scale is really big the principles are the same
you might be thinking at this point this is awesome we can just keep doing this we can load it
but if you remember what i mentioned in the beginning keeping data in your stages will
contribute to your storage costs for snowflake and a recommended practice here is to actually remove
or aka purge the data from your stages after it's been copied into a table so right now we have
these two guys here they're just sitting there and the way you would do this ad hoc is remove
and then the name of your stage so if we run this here it's going to remove these files
and if we list it again it's now empty but if we look at our our snowflake table this still
exists so we're all set however you probably don't want to have to remember to constantly clear this
out you know you probably want to have that just automatically so the way you can do that is to add
a command purge equals true to your copy statement so what this will do is it'll copy everything in
but once it's done it's going to essentially run that remove clause and clear it out so what
we'll do is add a third file here let's look at the stage now so okay so it's recognizing
that third one and let's run this command so right now it's showing it we'll run the copy
but have purge equals true and we would expect this to be cleared out once it's done okay so it
parse two let's confirm this data made it in and it's right here rockets and heat and also confirm
that this is cleared and it is so that's exactly what we hoped for
so in this video we were able to get through all these steps successfully and although this is a
very very simple example with just i mean what was it 10 records the principles remain the same
once you start to get to millions or billions or whatever amounts of data points and snowflake can
handle it i mean it's designed for huge amounts of data so the concepts are the same and that's
really what i wanted to get across in this video so as always thank you for watching
feel free to like comment and even subscribe if you found this helpful thanks again for watching