Getting Started with Data Analysis in Snowflake using Python and SQL

Hello everyone and thank you for joining today's Code along. My name is Rhys and I'll be your moderator today. We're going to kick off today's session in a couple of minutes. We're just waiting so everyone has a chance to join. In the meanwhile, though, we'd love to hear from you. So let us know where you're joining from using the chat or the comments to pin, depending on what platform you're watching on. And yeah, tell us something that you'd like to get out today's session. A few notes before we get started. The session is being recorded and the recording will be emailed to everyone that's registered for the event. If you haven't registered already, then head over to datacamp.com forward/webinars or you can go find the link that I've put in the chat. I'll be posting it again shortly, so you can do that. But yeah, make sure you get registered so that we can send you the recording as well as any other resources. As well as. Well, another note, we are going to be doing code along in Jupyter notebooks today. You are going to have to have Git and Python installed, but if you haven't done that already, then we're going to be doing that during the session itself. So yeah, don't get. Don't get put off by that. Last thing to note is we are going to have a Q and A at the end of the session, so if you have any questions at any point throughout the session, then please let us know in the chat and the comments. And if you have any trouble copying the links that I've put in the chat, you are going to have to copy and paste them, unfortunately, especially on LinkedIn. LinkedIn will not allow hot links, so you'll have to copy and paste them. Apologies for that. Brilliant. I'll be back to repeat these messages for any new joiners shortly, but until then, enjoy the background music. Hello everyone and thank you for joining today's Code Long. My name is Rhys and I'll be your moderator today. We're going to kick off today's session in about a minute or so. We're just waiting so everyone has a chance to join. Let us know where you're joining from using the chat in the comments, depending on what platform you're watching from. And yeah, tell us something that you'd like to get out of today's session. We are going to be using Jupyter notebooks today, so if you can have Git and Python installed to Help along with the code along today, that'd be great. But if you haven't got those installed, we are going to be doing it as part of the session as well. Keep your eyes peeled in the chat because I'm going to be sharing links, the first of which is to register for the event so that you can get the recording and resources sent to you. And I'll also be sending a link to the slides that we're going to be running through today and that will help you with the code along. So yeah, keep your eyes peeled for those and I will also put them on the QR code on screen as well. Brilliant. We're also going to be having a Q and A at the end of the session today, so if you have any questions at any point throughout the session then please let us know in the comments and we will help you out there. And yeah, run through them at the end. Brilliant. I think that's everything from me. So now I'll hand you over to your host for today's session, Richie. Richie, please take it away.

Hi there, Data Scamps and Data Champs, this is Richie. Now, Snowflake is one of the hottest data as a service platforms around. Now what that means is it's essentially a fancy modern cloud data warehouse and Snowflake skills are fast becoming one of the key differentiators for getting work as a data practitioner and at datacamp in particular. We've been getting very excited about it because we've recently launched our first course on Snowflake, namely Introduction to Snowflake. So today you're going to be learning to access data from Snowflake and then analyze it using Python and SQL. So it's great that you can reuse those existing skills for the analysis part. However, just before we get started, there is a bit of a technical note here. So Snowflake is supported in datacamp Workspace. So that's the hosted Jupyter notebook platform that we typically use in these code alongs. We did just discover a few months ago that some of the code code around working with schemas is a little bit problematic. In Workspace it doesn't work particularly well. So at the moment, so that means that we're going to show you how to connect to Snowflake in Workspace to begin with. But then for the rest of this I do suggest that you try just a vanilla Jupyter notebook. So that means either running a local Jupyter notebook or maybe something like Google Colab. So apologies for the inconvenience there. We are going to have a lot of fun and get some valuable advice on using Snowflake anyway. Right.

I think we're just having some problems with your mic there.

Oh, hang on, let me just change the sound settings.

Bear with me.

Interesting. Okay. Is that better?

Yep, better.

All right, super apologies for the sound interruptions. So today's guest is Vino Duracemi, developer advocate at Snowflake. So you'd be getting going to be getting advice straight from the source. Now Vino spends her time helping companies make better use of Snowflake and she's previously worked as a data engineer at Apple and was a researcher in explainable AI at Arizona State University. All right, so without further ado, please take it away, Vino.

Thanks, Ruchi. Hello everyone. I'm excited to be here today and we are going to use Snowflake. We will get started right away. Here are the slides. I'm sure you already have the links to your slide slides as a PDF. Keep that handy. We will use that to go through the instructions for the follow along. As the host already mentioned, we would need git, Python and a free trial account of Snowflake to follow through this code along session. As some of you have already asked in the chat, Snowflake is available for free to use. You can sign up for a free trial and if you are a student you will also be able to get, I guess 120 day free trial instead of a simple standard 30 day one. So you have the software available to you free to use. Check it out in your spare time when you're, you know, learning Snowflake as you go. And I guess a little bit about me before we get started on what we're going to do today. I started out as a software engineer and then I did my Master's, moved into the data and ML engineering world and that was the next big thing in tech that was happening. But now currently I work as a developer advocate at Snowflake. Like Richie mentioned, I focus on helping Snowflake developers with all the different things. Building tutorials and sample code and improving the documentation and whatnot. So it'd be. And you can follow my content on Medium, specifically where I write about Snowflake best practices, how to break into data engineering and whatnot. All right, enough about me. Back to Coralong. There are some prerequisites as I mentioned, so let's go over them. The structure of today's session is going to be. We will spend about five minutes, five to 10 minutes on the intro. What is Snowflake? Just a quick Overview and how to get started. And then the rest will be about, I guess, 45 minutes of hands on session where you get to write SQL and Python code to do the data analysis. To do the code along you need to have a bunch of tools which is not too complex. If you already have the PDF handy, you could just go to quickstarts.snowflake.com on Google and once you go there you will see the Quick Starts page. You can search for data engineering and you see a bunch of Quick Starts in here. This is put together by me and folks in my team as well. And for today's session on data analysis, using Snowflake, we're going to use this specific Quick Start. As you see here, and as you see, the Quick Start has multiple steps starting with what is an overview of this entire workflow and how do you set up and config things and then actually get started. To run the data analysis step one, you need to have Git and Python installed. The third step is to have a free trial account. If you already have in Git and Python, no worries, we will anyway go through these steps. I mean if you don't have Git and Python installed, don't worry, we will anyway go through these steps. Once I'm done with the five minute intro of what Snowflake is and how to use it for data analysis. But if you wanted to get it started on the side, while I'm going over the presentation, here is the links to check out Git and Python installations. Okay, so do we have any questions so far on the setup of y'? All? Good. I'm just looking at the chat side by side to see if there's anything we have. Awesome. Okay. All right, Snowflake SQL and Snowpark Python. And as some of you have already started, you know, the course on data analysis with Snowflake, you may have started with SQL because SQL being I guess, the go to tool for any data analysis project. And if you wanted to kind of, you know, go a level above, we also learned Python because Python is again one of the most popular languages, especially in the data space, because of the vast ecosystem of libraries that is available. And if you're already a Python user, you would know Pandas and Numpy and whatnot and all of the libraries that help you analyze the data. Right? So today we're going to use the Snowflake SQL and also touch a bit upon the Snowpark for Python and you know, how do you use both to analyze the data? But meanwhile though, a quick question how many of you here are SQL analysts or how many of you here are Python devs? Would you, you know, write it down in the chat for me so I get an idea of how we're split today? And when I'm talking about Snowflake, SQL and Snowpark Python, there's one thing that I, you know, probably need to tell you, which is when Snowflake started, it started as a SQL based data warehousing solution. So primarily used SQL to do the data analysis. But then if you are a data engineer, a data analyst or a data scientist, you would know that it does not stop with just data analysis. Right? You prepare the data, but then in the end it's getting used. For all the different use cases. It could be a simple BI visualization, it could be a fancy machine learning model, or it could simply be an analytics report. To build on top of this process data, Snowflake added these machine learning capabilities on top of it. When you are thinking about these data science and machine learning and complex data engineering tasks, you also needed the Python support. Which is why although initially Snowflake started with the SQL tool, it has tons of Python capabilities. Now we're going to touch upon both and learn how to use them. Before we had Python, how did it look in the architectural. What you know in the architectural front is that if you have all of your data stored in your Snowflake environment, you would use primarily SQL to query the data from Snowflake. But if you do need these complex open source libraries from Python for data science tasks, you always needed compute from the external modules to be able to run that. So what happens is data goes out of Snowflake, you run some Python jobs for data science or analytics or bi, and then once the data science part is done, the data is written back to Snowflake was not the best of use cases because if you think about it, in a truly big data world, it's not ideal to keep moving the data from one place to another. So what happened is, as I mentioned before, Snowflake introduced this new feature called Snowpark. And what happens with Snowpark is that now you can write not just Python but also so Java, Scholar, any of these languages, you can Write queries and APIs in these languages to analyze the data that is sitting in Snowflake. So by this Snowpark Python support, data does not move anywhere, but your code moves to where the data is, which is one of the biggest changes in paradigms in terms of the architecture. What is Snowpark? Let's just briefly touch upon it before we dive in, Snowpark is just a bunch of libraries and runtimes. What do I mean by a bunch of libraries and runtimes? Say if you are Python dev and you've used Pandas, Pandas is a library. You can think of Snowpark as a similar library as well, which comes with all the more familiar data frame APIs. If you work with Pandas, Data Frames or our Data Frames, very similar to Snowpark Data Frames, it's all making it easy for us to work with data frames. Then it also comes with the runtime. Runtime is nothing but a compute. It could be a simple, you know, warehouse, like a standard warehouse or a Snowpark optimized warehouse, but it's just that it's a set of libraries and runtimes. It's just a bunch of APIs and compute. So that's all it is. And as I mentioned before, to support the complex machine learning and data science use cases, there are tons of other tools as well, ML APIs and container services. But we're not going to touch on all of that. Today we will focus only on the data analysis with Snowflake using SQL and Python. So, you know, the focus would be mostly on the left side of the, you know, architecture here using data frame APIs and stored procedures. And this is a bit about how it works. For example, if you use a data frame API, how is it run internally? It's not necessarily needed for today's workshop, but then I thought if one, some of you want to go dive deep into how it works and what, what is what, feel free to check it out as well. Now again, just to quickly summarize, like I said, today we're going to be focusing on the prepare and transform, you know, phase of this entire data science workflow. But then the quick start that we're going to follow has multiple steps and it has this end to end data science workflow. So some of you here, you might be, you know, focused on the analytics side of it, some of you might be focused on the data engineering, some of you might be on the data science. Depending on wherever your interests may be, you can go check that quick start out. And although today we will only be focusing on step four of the quick start on data engineering, after this code along session, you can go and check the rest of the sections by yourself too. All right, enough talking. Let's get to writing some code today. What is the story today? Right, we are, let's assume there is an example company, it's called Ski Gear company and we're going to analyze the marketing spend and the revenue using Snowflake. So let's get in the first step. Okay, so let's go do this. Do all of you have a Snowflake free trial? Maybe not, right? So let's just go and create one. You can go to signup.snowflake.com all right, I'll probably give a second break here if you know you were not following along. So this. Let's get started. The first step is to go to signup snowflake.com and you can sign up for your 30 day free trial. And when you continue to the next step, it's going to ask you to select a cloud service provider and you can select AWS here because we're going to use the data from AWS S3 to load into Snowflake. I think it takes about probably a few seconds and once you're done, oops, okay, I already have it. So once you are done with creating a quick start, the next once you're done with creating a free trial, the next is to go to the quick start start. Snowflake.com Let me do the with you. Quickstart. Snowflake.com we're going to do the data engineering one. Okay, so we're going to focus on getting started with data engineering and Emma using Snowpark for Python. And today we're going to be focusing on the data analysis part, which is the data engineering part. And as you can see here is the overview and it shows it takes about 70 minutes, but it takes about 70 minutes to run through the entire thing. But we are going to today focus only on the data engineering component of it. All right, so let's quickly go over. I'm zooming in so you can all see what's going on. And as you can see, what is Snowpark? We had a quick overview of that as well. And what are you going to learn today? We will analyze and perform the data engineering tasks a bit. We've already seen this prerequisites. If you don't have Git installed, you can go ahead and install that as well. Depending on what computer using most of us might already have Python installed. If not, feel free to download Python as well. You don't necessarily need the latest one, but then it's cool to have the latest one too. As long as you have 3.9 and above, we should be good. And the third is of course registering for a free trial that we already did. These are some of the prerequisites before we get started. A quick Check. Are we all on track? Do we have any questions in terms of the setup and the configs?

So there was one person querying, just saying the. They haven't got the trial. Let me show that on screen. I'm not sure whether that failed and if you have any advice on what to do in that case.

Interesting. I. You should be able to. Awesome. Okay. You should be able to, like, once you're done with the sign up, you would get an email, whichever email you'd use, with all the details on how to sign into your free trial account. So checking your email would be the next step.

Okay.

Yeah.

If you're getting stuck, then do check your email and hope and let us know in the chat if it still doesn't work. Beyond that, I think. Oh, there was one other question around, just is this something to download or is everything you need in the cloud in the browser?

Yeah, it's in the browser. You cannot download it. It's a software as a Solution. It's a SaaS solution. So it's available in the cloud to your browser, so there's no download. It's not like a traditional desktop software that you would buy and install. It's just a cloud subscription model. Use it in your browser and that's it.

All right, super. With that, I think we're ready to carry on.

Awesome. I see some of you folks are asking if people share the code and why do we need git? The answer to all of that is when you do move on to the next, let's say getting started phase, I have put together all the code and the source code associated with the demo and the Quick start today in Git. If you have git, we can just download it and run with it. That's pretty much why we need get to clone the code into your local and then to run the code. And all of that is available as part of the Quick start. So all you need is this link to the Quick start and you're good. All right, I see that folks are able to catch up. Awesome. Okay, I think I'm moving on to the next one. We're good so far. All right, Once you have the free trial and all the, you know, prerequisites are installed, the next step is, of course, let's log into the free trial account we just created. Right, let's do this. Sign up on snowflake.com. i already have an account, so I'm going to sign into the existing account. Okay. Well, it didn't ask me for the credentials because I already saved it. But when you try to log in it would ask you to enter the email that you used and the password as well. And within Snowflake this is how the UI looks. You would probably have like five different notebooks that comes pre baked with the free trial account. You can see the dashboards here. Currently we don't have any no streamlit apps. So under data you will see there are different databases available. We're going to use one of these databases today and first thing you need to do once you are able to sign in is you should go to admin and then go to Billing and Terms and you would have to enable. I've already enabled it here. But then it would for you it would show it as an enable checkbox. So you need to enable the Anaconda python packages here to be able to use Python as part of Snowflake. And once we have all of that ready, a quick overview. We installed Git, we installed Python and then we also got a free trial like Snowflake free trial. And then once you have the Snowflake free trial, log into your account and go to admin filling and Terms and enable your Anaconda package. Now once you have all of this, let's just go to data or actually worksheets. You can create a new worksheet just to play around or we already have Git installed, why can't we download the code from this git repo and let's run through that existing code. Let me. Okay, so once we have that, I'm going to this from your Quick Start. Your Quick Start is one stop shop for going through everything that we will do in the code along session today. So we are done with the overview. We were able to get our prerequisites and the next step is we also logged into the Snowflake account and the next step is to set up a few things. But before we set up, I want us to go and download this source code from Git as well. It might take a bit of a few minutes. So let's get this done. And once you do go to the git repository, just copy this. Or you could simply copy the commands from here as well. I'm not doing anything new here. Copy the command. I have my terminal here and then I'm just gonna run that as is and it's downloading that. Amazing. Okay, So I downloaded it and I got it in the. I got the source code and my local. Oops. Okay, so what does. Okay, so there's a bunch of files that I have and today we're going to be focusing only on the data engineering component. Like I said, if you're a data science person, if you want to explore a bit of ML and other stuff, you can always check out the other files as well. But today's focus is on the data engineering or the data analysis side of it. So once you have the code locally, what next is you can create a new CONDA environment or a new virtual environment here just for this specific workflow. And again, we have the code and everything ready. Let's just go ahead. And from that as well. We're just going to create a new virtual environment to do this. Yes. Oh no, let's create a new name. I'm just going to keep it D. Awesome. So we were able to create a new environment and the next step is to activate it. But let's. Okay, so it's the same thing. Activate this environment, it's active and you will be able to see if that specific the corner environment that we created here to show that that is the active session currently. And once you have that, the next is to go and install all the relevant packages. And as you can see here, I'm installing Snowflake, Snowpark, Python and I'm also installing ML APIs and I'm installing Pandas, installing Jupyter, Notebook and Cache Tools. And again this all you'll be using all these different packages for today's workshop. Again, we will be only focusing on Snowpark, but we're also installing the rest of the packages because it helps you with the rest of the workflow with ML and more. So I'm going to install this all the dependencies in the package. Okay. It's going to take a few seconds. Yep, let's proceed. And what does the next step look like? Just checking. Okay, so we need to connect update the connection JSON to be able to do this. Perfect. Okay, so everything was done successfully and we're good. And if you see here, there is a connection JSON file that we need to update. So you can do one of two things. Let's say. So this like we need to update all these different parameters for in the connection JSON so we can connect our Jupyter notebook to the Snowflake account. So you can just query from there. Right. So before we do that, I have opened the connection JSON here and by default I have I guess the databases and schemas. This the first three account username and the password needs to be filled by you. So if you had a free trial account, let's say, let's go there, what is it called? You will be able to see on the top, I mean bottom left corner in your snowflake free trial the details of your account. Right. And you can just go and copy the account identifier that will be your account. So I'm going back to my vi. What is my account that I copied from the free trial? I have these identifiers and I need a hyphen instead of period. So it's just part one hyphen, part two, the account identifier you copied. And this username and password are the username that you created and the password, I mean this you would have created when you signed up for the free trial account you created manually just now when you signed up for the free trial. So you can enter those details here as well. And in the Warehouse. I'm just gonna use. Warehouse or let's say dash Large because we're going to use a large warehouse city. Awesome. Okay, so I'm not showing how I'm updating my username and password here, but you can update your username and password here as well. And once you're done. Awesome. We have everything. Let's go back to the quick start. We copy the code, the source code from Git and we were able to create a new CONDA environment, activate it, install all the dependencies and relevant libraries needed. We also updated the connection JSON. So now we're super good to go and do the data analysis. Now let's go to the setup environment. So let's sign into the snow site. All right, let's sign into snow site. Snow site is nothing but snowflakes ui. The current new fancy UI is this one. Awesome. We are in the UI and what are we supposed to do? You just have to run the following SQL commands. Currently what you can do is for example, let me clear the mess. Under worksheets you can go to create a new SQL worksheet because let's start with SQL. For now we can simply copy paste these commands and they would just create all the necessary snowflake objects for us. First what we're doing is we're using the account admin role to create a warehouse. Warehouse is nothing but the compute needed here and the database will just give any database name and schema as well. And if you are giving your own custom names here, you have to remember to use the same custom names for the rest of the workflow as well. But if you want to just keep the names assets, you could simply copy the entire thing and Paste it in your SQL workspace and you can simply run them. As you can see here, there is a run all. Just simply run them. Awesome. We were able to create a new warehouse database and schema. How do we see that? You see under databases there is multiple databases here. If you look into Dash db, which we just created here under dash schema. Well, currently there are no objects because we didn't do anything. So let's go ahead and create some objects. Awesome. Okay, so our data, the marketing spend data is currently present in an AWS S3 bucket. So we're going to read data from S3 and to read that data I need to create a CSV file format because my file is in the form of CSV. So let's just go ahead and run. What I'm doing in this step is we'll copy the data. Not exactly copy. We will load the data from AWS S3 into a snowflake table. So we're going to just create a new table called campaign spend to load the data expend side of the data into snowflake and we first copy the data into a stage. Stage is almost like a pre prod like environment. And then once you copy it into stage, use that data. I mean load that data from stage into your actual snowflake table. I'm just gonna run this one. Let me just paste it. Now we can also run it step by step as you can see. So we were able to use this like we ran until here before. So let's say I want to create a CSV format. I could simply just run that step. Awesome. So it was able to run and let's create a new stage. This stage is where we will load the data. Awesome. That's successful as well. So now let's create a new table. You can see here. Oops. Under dash schema. Under tables we have campaign spend and what does the data look like? Whoops. Okay, we need to select a warehouse because it needs compute to query the data. So I use the large warehouse. Now let's just look at what the data looks like. Oh no, the data is not there. Of course, because we only created the data. The table schema not loaded the data from our stage. Let's go ahead and load the data. Awesome. This is loaded and you see that there are so many rows and almost close to 300k rows and it's all good. Now let's go ahead and. Awesome. This is how our data looks. We were able to load the data from S3 into a snowflake Table. So now that we have data in here, can we just go ahead and query it? We surely can, but before that, let's also pull in the data, which is the revenue side of it. Earlier we had the spend data. Now let's collect the revenue data and then we'll do the analysis. Oops. Okay, I'm copying the going back to my SQL worksheet. Let's paste it here. Monthly revenue. Just want to run this part. It's creating a new stage. Awesome. New table. Yes. Now let's copy the data into this new table. Awesome. So it only had about 100 rows, not too much. And you can see here and under tables we have also the monthly revenue. Great. So this is how the data looks like for every year, every month. What was the revenue? Right, so we have both the tables. Is this the time to go ahead? Oh, yeah. Okay, Just to show you that you don't necessarily need to always load data from an external data source. You can also, just like in a traditional SQL world, insert values into an existing table to create a new table as well. Let's say we're going to create a new table here called Purchase allocations and roi just for the marketing ROI from the past data. And I'm going to insert these values manually to load that table. So let's do that as well. We are creating table number three by manually inserting the values you can see. Let's do this. All right, so the table is created. You will be able to see here. Okay, so there's budget allocations, table, data preview. Well, we only created the schema, we haven't loaded the values into that. So let's do that. Okay. It's only six rows, like a small table. But are we able to see the preview here? Awesome. For every month, what was the ROI from search engine, social media, videos, email and the entire roi. Awesome. We have three tables. We have loaded all the data into the snowflake environment. Now what? Finally for this step, we don't really need it because we're not going to do any of the machine learning side of the stuff. So we don't really need to create a new store procedures or models or UDFS stage. This is just, you know, we're creating these stages here to store our ML models that we train. But in this stage in today's workshop, we're not going to train any ML models. So we can skip this for now. And if you move to the next step, we've already done this. All good. So now let's do the data engineering Stuff, Right. And okay, so I did, I did update my. Okay, so. As you can see here, I had copied all of the code from GitHub earlier and we were also able to update the connection JSON and once you're able to, you know, update your connection JSON as well, you could simply type jupyter notebook and it would spin up a jupyter here like Jupyter in your local. As you can see here. Oops, it opened it there. Awesome. So all the, you know, Python and IPython notebooks that you see here are from the Git repo that we cloned. And I'm going to open the data engineering one. Awesome. So here is how it looks currently it has a bunch of steps. Let's just, you know, go through it step by step, of course. So the first one is nothing but a bunch of import. Like very similar. If you use, let's say Python, for example, Pandas, numpy, you just, you know, import all the dependencies and nothing really complex there. Awesome. We were able to run that in this one. Remember we updated our connection JSON with all these details. We're going to use that connection JSON. We load the connection JSON and use those configs to connect to the Snowflake free trial account from the Jupyter notebook when you run this. Awesome. So it prints you all the user details. Now I'm able to query Snowflake data from this Jupyter notebook. So just to start, very simple, we'll just. We created a campaign spend table in Snowflake, but let's just read it into a data frame here. Data frames. You can imagine data frame abstractions are very similar to just tables. Traditionally, if you work with SQL tables, like relational tables, it's very similar. It just has a bunch of structured data in two dimensions, rows and columns. Very straightforward. We'll just read that. And we were able to read that specific table into a Snowflake DS pending. And once we have that. All right, okay, let's first identify spend per channel. But before I do that, I think I need to go here for example, and we already have the campaign spend data and we know what it looks like correctly. But what I want to do is from my jupyter notebook I want to do the spends per month per channel. But currently it's, it's there by date. So I'm going to create a month column out of it and then group by the month and the channel on this table. And how can I do that? I'm just going to use the date column to get my year, to get my month. There is no complex timestamp functions that will help you create these year and month from the date. Just use the year function and month function. You get it and then you group by your month and channel. So you will get the aggregate right. And we need the aggregate sum here because we are looking at the total cost. We just renaming here as the total cost. And of course we don't want to keep them as messy that we want to change these names, your update into a just year and month and whatnot. So once you run this, you will be. We are also in the end, if you notice, we are sorting it by year and month. So when you do run this, you will see that for every year and every month, for every channel, what is the total cost? And this is not all the data. So when you do show just 10 rows in here, but then you get an idea of what the data looks like. Now what if we could pivot it onto all these different channels? What am I doing? I'm just pivoting these channels. So I will have Search Engine as a new column, video as a new column, and then their associated costs. Let's see how you can do that. Instead of a group by an aggregate, here you're just going to use a pivot function. You're going to pivot on the channel, the channel column. And then what are the values in the channel column? It's just, you know, you give this in a list and you calculate total cost for each of these channels and then sort them by year and month. And then you can also use a select statement to select the year, month and all these different columns together from the data frame. And if you do run this, for example, you will see that for every year and month we have the total cost per channel, but pivoted. Awesome. This is how it looks like currently. What if I wrote this data back into a snowflake table? We have this spend per month, which is the D of spend per month. Now let's write this data back into snowflake table. I'm using an overwrite mode. If there is an already existing table called spend per month, it's going to get overwritten. But I don't think I have any. We only have these three tables, so let's go ahead and run this. Okay. It runs successfully. We're good. So let's go check quickly. Do we have anything here? We have spent per month. Look at the data. Very similar to what we saw there. Amazing. And we could just run through the rest of the steps as well. Oops. Okay, so we wrote the spend per month, so we're done analyzing the spend data now. I'm skipping this automation step for a bit here. Again, if you work with Python, you know the difference between running steps in a jupyter notebook and then putting all these steps together into a python function and running them all together. For example, here I manually, you know, just ran each of these steps individually. But what you can also do is that if you want to think of this entire data analysis as a data pipeline, you're automating multiple transformations together and scheduling them. Say, I want this analysis to be done every day, every hour, every quarter. When you want to do this automation of it, you want to package the existing code into a python function, then you can call this specific function to be run every hour, every day, and more. Only if you want to pipeline the code, you will need to put them into a function and then you can create a task that says, oh, I want to run this specific function on this compute on this warehouse for every three minutes. And you can set these scheduling based on your requirements. It helps you with automating this entire data analysis on a production workload. However, here we're not doing that. We don't need this to be done every day or every hour. But in case you want to, here is how you do it. Awesome. We were able to analyze the spend data and then we were able to write back the spend per month into snowflake. So now let's go analyze the revenue data. So very similar. Monthly revenue is in a snowflake table. We're just going to read it into a snowflake snowpark data frame. And once we have that, we're just going to group by the year and month, aggregate by the sum, and just rename the columns. Very similar. It's just on a different table. Awesome. So this is how it looks currently. Now what we're going to do is we have the revenue for every year and month and we also have the spend by every year, month. So can we just join them together so I can share it with my extended team? Let's say for analytics, for analytics, for visualization, for machine learning, whatever it may be for, let's just combine the spend and the revenue readout one place so your other business teams can access it for their own use cases. How do we combine these two? Currently, as you can see, the common columns being the year and month. And even here, if you remember, there is year and month. So let's just you know, join on those two columns. And how am I running a join here? Oops. Okay, so it's right here. So you can see the spend per month is to be joined with the revenue per month. And I'm using the join columns as year and month and that's pretty much it. Let's see how it looks like. Awesome. So you have for every year, every month how much money we spent on search engine, social media, videos and email and how much revenue in total we earned from that specific month. Awesome. So this is pretty much the analysis that we wanted to do. Now another interesting thing is so far we've used Python data frames to do this data analysis. But you can also say run any sort of SQL queries directly from here as well. Let's do something new here. For example, if I'm a SQL person, if I don't like writing Python for this entire thing, you can do group by and aggregations and just select and join all of these operations that we did in Python. You can also do it in SQL. If you want to run SQL from here you can say Session SQL. Just doing a simple select statement but showing you how you can run your SQL queries for Snowflake as well. Like what is the table that we are looking at? I guess spend per month. So let's see. Okay, so let's just say DF temp and then let's show the DF so we can see. Oops. Okay, awesome. So you can see the spend per month has this data. But the idea is you could just use session SQL and write your entire SQL query here as well. But this is just. Again we're done with the analysis. We have the aggregated spend and the revenue data together. But just like writing any other query, you can always use the explain function to explain to go over what is the execution plan, how is the specific query being run and what happens in each of these stages. And especially, you know, this is very helpful if you're thinking about optimization of queries. So you need to know what's going on. Are you doing an extra table scan that you can avoid? Are you using the right kind of joins and you know, all sorts of diving deep to understand the optimization. But again that this is probably for an advanced level, not the focus for today. But I just wanted to show you how you can do this if you are interested. For sure. So I'm going to write the spend and revenue per month as a table back to Snowflake as well. Awesome. That was successful. So if you see here, I have A spend on revenue per month. This is how it looks. Awesome. Okay, so this we're done with the data analysis part of it. And just to show you another quick thing we had, let's say the setup SQL that we ran through. All of this was in SQL and I showed you how to use Python for the data analysis here as well. And even within Snowflake for example, you could use in worksheets, you can use a Python worksheet to write all the code that we wrote in our Jupyter notebooks. You can do the same thing from here within the Snowflake environment as well. Again, this is very simple just to keep things very clean for your production kind of setting. But I generally prefer notebook style environment. So you can just run every step and see what it looks like. But again, in a production setting you will not be deploying a Jupyter notebook in production. So just need to have a consolidated environment to modularize your code. You can do that here as well. You can have a Python worksheet or a SQL worksheet depending on what you're good with or what you need and then run with it for the rest of analysis once you're done. Very similar. You can also package them into a different function so you can schedule them as you need. As you can see compared to the previous one. In the previous one I said I wanted to run that function every three minutes, but here I'm saying I want to run the function after I process the previous function. So kind of building the dependencies out here. And you can always resume or suspend tasks using the alter command as well. And do you notice how they're using session SQL to run the SQL commands in here as part of the Jupyter notebook as well. So you can do all of this. And that's pretty much I have for today. And let's just quickly go over here back to the Quick Start. This Quick Start is the one stop shop for all things data analysis today. It has a few other steps as well. How do you build a robust data pipeline? Machine learning, building a visualization tool and much more. But today's focus was data engineering. It's just we're loading three tables and analyzing the spend data, analyzing the review, I mean revenue data, just combining them and aggregating them for your different use cases. You can take this combined data and do some ML with it and then build a visualization app and more on top of it. And I guess that's. That brings us to the end of the session and just keeping this one in case you didn't have a link to it previously, I want you to go check out the specific Quick Start that will. That has all the instructions that you need as well. It has a link to the Git repository, you can download your code and everything end to end with the step by step instructions.

All right, super. Thank you so much, Fino. Yeah, that was really interesting. I love the way you can just write your standard either Pandas code or you can write your SQL code and there's not much to change once you've actually connected to that, connected to Snowflake itself. You can just, you know, use your existing skills and then be there. All right, so for anyone who has a question for Vino, we've got a few minutes left. I can see a couple of questions in the audience from the audience already, so let's jump to those. So first question comes from Rahul. So saying if you want to become a data analyst, is it required to learn Snowflake? And basically what are the benefits of this if you're a data analysis?

Gotcha. Okay. If you want to become a data analyst, I think again, just because I work for Snowflake, I don't want to keep telling everyone you need to learn Snowflake. But honestly, if you want to become a data analyst, focus on learning. I would say the foundational tools. What I would do is for example, become super damn good at SQL and then get to beginner to intermediate level in Python for data analysis there is Pandas and Numpy and now we have tons of new libraries as well. Get grid SQL, learn how to write Python code and once you have these two, it's super easy to pick up Snowflake because it's anyway based on SQL and Python so the lift is going to be very less for you. But I would say as any data person, I don't think you should attach yourself to one specific tool, but you just need to focus on the foundations and build it strong. So totally in. Learn SQL, learn Python, then you can pick up Snowflake or any other tool to just, you know, do your analysis.

That does seem like good advice. I suppose if you're going to work for a place where they're using Snowflake, you're going to need to learn a bit of Snowflake at some point or you're not getting access to any data to run your SQL on. So yeah, maybe a few hours of, of experience with Snowflake is going to set you up.

Yeah, for sure. And yeah, sorry, I'm just going to Add one more thing, which is. But Snowflake, for example, there is nothing in terms of the infrastructure or config that needs to set up. You spin up a free trial and then you have the SQL and Python worksheet within and you could just do everything from within Snowflake itself. So if you do want to get started, it's not too hard of a. I would say the entry barrier. So it's super easy to get started as well.

All right, excellent. So next question is one from Joe. So is save as table a Snowflake command? So that was one bit that's not. It doesn't look like standard Pandas. That's something different.

Yeah, so the same as Table is a snowpark, you know, API, like a Snowpark command. And if you. I don't think it is a Pandas one. I think in Pandas you would just have a save, if I remember correctly. But it's just that within Snowflake there are different objects. So there's a standard table, but there is a dynamic table. But there is an iceberg table that's coming up. So there are different types of tables. Whereas in Pandas it's just save as either CSV or a data frame. So it's straightforward. So you needed to have these, I guess, distinctions. You save it as a table or save as a dynamic table and more.

Okay, that seems useful. And then Mario asks. Oh, Hugh. Talking towards the end about copying and pasting code. I think this is scheduling, so I don't know if there's any alternatives. So Mario is saying, can I run the cell I want from a function rather than copying pasting from one notebook to another.

Yeah, I mean you can always select which specific cell that you want to run. And I guess, okay, maybe if they're talking about the first part of it where I copied the code from the quick start and then paste it into the Snowflake environment and run. I just wanted to show that you can write your own code, like your own SQL code from within the environment. But then I had all of the code in my git repository. I just didn't want to just do a run all and then, hey, you know what, it ran successfully. But instead I just wanted to go through step by step and show them how. But yeah, in an ideal environment, in a production setting, you will be able to just run through them all.

Question from an anonymous LinkedIn user. Can you show us again about how you go about connecting the Jupyter notebook to Snowflake Warehouse? I know you showed this before but do you want to just quickly show it in workspace, the connection panel you can talk through? What the.

I'm glad you brought it up. Okay, yes. So two things. One is how do you connect Jupyter to Snowflake, which is, if you remember, we were going over the connection JSON. And you'll have to update this and you will be able to do that. And the second, and the most interesting part is here, if you have a Data Camp account, which I suspect most of you all here already do, in the workspaces, you can create your own workspaces. I see. So under databases, if you go and create a new connection to a database, Google Snowflake. And it's funny, but it's the same set of details that you need. What's the name of the account identifier or the connection name? What's the name of your account? What's the name of the warehouse database and username? And in my case, I guess I already have it. Oh, yeah. Okay. So I created a free trial account, and that would be my name. But say you want to create your own. I'm just gonna keep it a test name. And I use the warehouses. L. Where was the database? It was also. Okay, I can't really type my username and the password here. Then you type your username and password and it will let you connect as well. But it's funny because it's literally the same set of details that you will need. Name of the account, warehouse, database, specific username and password to connect it. And I was already able to connect it and I'm able to see what are the different databases that are part of my Snowflake environment. And I can even write queries to run it from my Data Camp workspace as well.

It's just slightly easier said. You have to edit a JSON file. You got the nice point to click interface.

Yep, very much.

All right, so couple more questions before we finish up. So this one's from saying, what are your main competitors and what makes you different to them? So, yeah. Do you want to talk about how Snowflake compares to other platforms?

Yeah. I think one of the biggest differentiators when it comes to Snowflake is what I talked about, say, the usability and the developer experience of it. Right. If you want to create a new free trial account, if you want to run something with Snowflake, go spin up a free trial and you have the account ready within less than 30 seconds. Just takes literally a couple of seconds and you have it all to run. Anything that you need within Snowflake. But then when you think about the other setups, for example, in the past when I had to work with the simple MySQL for example, I need to have a bunch of installations going and that's only for the personal project. If I'm thinking about a production, setting the amount of infrastructure that I need to maintain, keep updating and then also do a lot of, I guess, configs in here. I just gave a name for Warehouse, just created the database and schema, then ran with it. There was no concept of what is the right parameter and right value for this specific configuration. Otherwise I'm going to end up running this query for the longest of time. There is no configuration involved at all. It's making it super easy for data people to focus on the data instead of all the infrastructure and the configurations and the setup. So you don't want to, you know, end up spending all your time on that. You want to spend more, most of your time on data. So Snowflake being a SaaS solution like keeps everything away and keeps the abstraction to just the data components.

All right. Yeah. So you're spending less time worrying about managing your data warehouse and more time just focusing on the analysis, I suppose. Or actually we'll do two more questions. I think they're fairly quick ones. So this one's another question from Joe saying what happens if you've got got a JSON database like mongodb? Is that supported in Snowflake?

Okay, I don't know about MongoDB as much, so I cannot speak to Mongo. But then if you do have your data in your JSON, you will be able to work with Snowflake. So Snowflake does support the semi structured data formats, so for sure, yes. All right, so I don't know what like MongoDB part is about, but Snowflake can work with Jason. Sure.

All right, excellent. And finally, for people who want to learn more, George is asking like, where can I find more Snowpack Python documentation?

Okay. Oh, if you want to explore the different use cases like quickstarts.snowflake.com is your go to place and it has. I'm glad. Okay, so I have this screen. Maybe you know, Richie, you can share it. It's quickstarts.snowflake.com and then you can also see that for every quick start that is here there is an associated git repository where all the source code is available. So you can check your the source code here. And there is also virtual hands on labs where you can sign up and then an instructor will help you run through things. And then, of course, besides all this, if you're looking for a specific API reference or anything, you can always check out docs.snowflake.com for any documentation.

All right, super. Thank you. Now, before you all dash off, we've got two more webinars this week, so tomorrow we've got a session on AI in robotics. So if you're interested in robots, then, or the robotics industry, then please do come back tomorrow. And then Thursday we've got a session on best practices for putting LLMs into production, so. So you can avoid your family, who are all the Thanksgiving, by joining us for the webinar. All right, brilliant. Thank you again, Vino. That was really cool. Yeah, it's nice to see, like, how straightforward it is to get started with Snowflake. So.

Yeah.

Thank you again. Thank you to Rhys for moderating. Thank you to everyone who asked a question. Thank you to everyone who showed up today. I hope to see you all again in future webinars. All right, bye.

Bye. Thank you.