Scalable Python and SQL Data Engineering without Migraines

Speaker 1 [00:07]

My name is Dirk Jung. I'm a senior sales engineer at Snowflake. So senior solution engineer is a new name. I forget it sometimes, but it's the same. Yes, today I will show you how you can create in Snowflake with Python your own pipeline with a scalable, with regards in compute power, what you are needing. And for me, I'm very happy to be here on the stage. I'm an old guy. A couple of years ago, so in the 90s, I studied computer science here in Darmstadt. And as I started here computer science, it was not a place for the Damm Stadium where the conference is today It was a parking place for the students. So unfortunately, I have no less parking place But we have this nice building here to present many concerts or conferences as well Yes, not sure if everyone knows What is Snowflake? Snowflake is a company who is a there's a cloud data platform It's built for the cloud and we have two founders. We was founded 2012 by two French guys Terry and Benoit both passionate winter Sporting people they like it to snowing and all these things and this is the reason why we give they give the company the name snowflake it's It's one of the funny things and the second is it's where the snow comes down from the cloud so it's a little distance to see it comes from the cloud and And I started 2020 at Snowflake during the corona pandemic and as I started I was employee number 2,000, 2,500 not sure and our first revenue was for going public in September 2020 and our first revenue for the first year was 500 million in 21 and the last year, financial year was 25. we have a revenue of 3.5 billion that's amazing but what's more amazing it's for me you have now 8 000 people who are working for snowflake and from 8 000 people 2000 or 2200 working alone working in the states the research and development department i work in the sales department so we have a really powerful workforce in snowflake to create new features to launch new features develop many more stuff to make this our platform more stable and bring new function who we see in the market requested by our customer yes so as Snowflake was founded we starting with a pure approach to say Snowflake is a cloud native data platform and what we see in the past you see it here on the timeline as companies starting to collect data and store data in different silos and they call it AIS it was the enterprise information system then they store the data in the cloud and again more data come in and have more silos created and snowflake is a one plate of data platform when you can store all of your data semi structured data structured data and of course also unstructured data and then you have your single source of true we see it we're running now in the multi cloud space we have a lot of AI topics so when you take a look in your daily business maybe you use already chat CBT or other Jenny I things like mistrial or clothed and we have also this new Jenny I features included in snowflake but what is snowflake so we talk about it's not We are a data and AI platform or data and AI platform cloud. What we think about with the cloud is we can connect to every Snowflake account. You have not to move the data. So you can connect between different accounts and then you can get access to this data when it's allowed. And we have four of the typical workloads which we covered. The first one is of course analytical work pace. The next one is AI. Then we have also the data engineering path. and it's also possible in Snowflake to create your own application and share this application so that you can also share data. What is the benefit of the some of the USPs of Snowflake? We are very easy so we have a Snowsight UI you take a look afterwards so I will also show it how it works in Snowflake and we are scalable so it's it doesn't matter if you have 10 terabyte in Snowflake or 100 petabyte if you need one compute resource or 500 compute resource the cloud make it possible so we take this resource as the customer needed and you pay only for the time where you use the resources we call these compute resources by the way which are warehouses today we will take a look how we bring the data into snowflake so we have pipelines how we can load the data into snowflake when you store the data on a specific place maybe on a s3 bucket or in a in a plop storage Google storage is the same one and then you have to transform the data and the right shapeness that you were in the end have a table or a few what you can say okay this I will use to report this data or create my own models to make forecasts anomaly detection and so forth and so forth but before you can do this you have to load the data in this snowflake and as I started in snowflake we do this in sequel sequel is old language I I learned it years before. Then I saw that this Python is much more easier. But with Python, you have to create your data frame. So a mix of both languages is a good choice from my point of view. And how we do this to create this pipeline, it's quite easy. So we have a base table where we load the data into it. Then we create with our task a new table and load the data in it. And then in the end, we have a stable pipeline where new data are loaded into the state, for example. Then we have maybe a notification, this notification triggers the task, the task said okay, I load the delta into Snowflake and then I will start a transformation job till the end and in the representative finalized layer I can use the data for my workloads like to build a model or run a model on it or to report the data or make deeper analytical workloads. Yes, and as I mentioned, we have a strong workforce in research and development, so in the past it was possible to connect you with different tools. The Snowflake Jupyter notebook is a very popular one, so we introduced our own notebook. The benefit is you have not to think about it, how I can connect to Snowflake, what I have to think about the data governance and so far and of course it's all included in our notebooks and you can use notebooks to write python code and also of course sql code and it's based on our snowpark feature snowpark also allows you to code in java and then scale it directly into snowflake and to see how it works i use this picture this is a quick start in snowflake we have many quick When you go to your browser and search for Snowflake and Quickstart, you will find maybe 50, 60 of these Quickstarts to different topics. Could be how I load data into Snowflake, how I use it, open AI model, how I use it, maybe create with Snowflake AI my own forecast pipeline, and so far. And every of these Quickstarts have a guideline, and it walks you through step by step how you can use it. You see the link here. This is for this specific Quickstart. Quick start, you can, if you want, try it by yourself to check how it works. What we will do, we see it here, we're starting here with the data stored in the S3 layer. This is a typical layer where we get the data. For us, we create a stage on top of this layer. And the idea is, we have here a franchise company. This franchise company has food trucks all over the globe, so in many countries, many cities. and we see some positioning information like the countries franchise location and menu we will load all this information into one layer this is our raw layer where we load the raw data into it but we will also know what is happening when the weather condition will change or will analyze it in the first step so we have to connect weather data with this and we have not to load this into Snowflake we can connect directly with Snowflake the weather data so if they refresh the weather data you see it immediately you have not to run again an import job to see what weather data we have tomorrow or in the next couple of hours. And the typical way when you take a look on a warehouse then you will of course harmonize the data, calculate some things, so the raw data which we receive here have some small pitfalls, the decreases in Fahrenheit when I see 65 Fahrenheit I think it's very warm but it's cold it's okay for to know it's 20 degrees or 15 degrees and the second thing is of course Inches is not so in my mind as millimeter when we see how many rains comes down. Is it two millimeters or 0.3 inches? For me, it's different to understand it in my mind and calculate it in the right size. I'm a metric man. And of course, it makes sense to have objects in Snowflake which make this task. So they will automatically, when you load data into Snowflake, we call it UDF. We will also walk through to it to calculate my weather from Fahrenheit into degrees Celsius. the same one from inches into millimeter. And in the end, you see it here on the point seven, we will have one table where we see the daily orders per city and per country to make better analytics and understand where is the best places. Maybe we can also arrange, go a bit deeper when we have more places, which places are better in different work days. So this is the idea behind this example. So to see how it works I prepared a demo so I feel it's almost better when it has some slides but I can also see how the software is working. So let me step here out. And let me step in here into this is Snowflake SnowSci. This is our UI which you can use in Snowflake, it's quite easy. You have some areas here for different projects, and as mentioned, we want to do this notebook, so I start this notebook and take a couple of seconds. So we see it. This is the original notebook, which you also can use in this quick start, what I've mentioned. And I get some explanation here about it. You see here which kind of task we will be doing. was the picture again, what I showed beforehand. You can get here some information what is in it. So we have the Markdown cells here, we have also SQL cells, or you can work on it. And first thing what we have to do is we have to prepare the scene to say okay which role I will use, Snowflake had an integrated role-based access model, so we will create a new role and this role should have all access for the specific task, not to say okay maybe I've also in Snowflake included credit card information this is not a data which would see every member or every employees of my company the same as a social security number these are sensitive data and this data have to be carefully and secure only people who's allowed to see this data should see this data this is the reason for a role that access model which is based on objects and only with roles. Nobody, no user can have rights on any of the objects in Snowflake. It's only the roles. So I hope the Internet is fast enough. It looks like so here that we can walk through on it. The next thing is what we now do. We create a database. We create a dedicated compute. We call it, as I mentioned, virtual warehouse. So this is isolated. Only for this workflow I'll use one compute and when our computes running in trouble maybe queuing or so set now it has no impact on my workflow here so this workflow runs dedicated with isolated compute in this case so let me do this so it's quite easy so we create a database with different schemas you see it here we have external schema this is the connection to my data where the data stored so we create in this case a stage then we will load this data into my raw position here, we have the base tables with the countries, with the orders and so far and so forth and then we harmonize this data and in the end we will create this analytical table where we can make our own analysis with our visualization tools. Yes and let me step here, so this is the first step so we will prepare in this session how we can to load the data with Python, so I write here some code with DataFrames. I'm not the Python expert but it works. I do it many times and we will now import some information here for Snowflake utility functions and in the next one we will also the active session get so that we have the right positioning for the data which we will load into Snowflake and now we starting loading the data for every table into Snowflake. This takes roughly two and a half minutes but it's okay so I can explain what's behind Snowflake, what makes Snowflake so unique. So as I mentioned when you have 10 terabytes you can work with 10 terabytes and you can work with 20 terabytes or 100 terabytes. In Snowflake compute and storage is separated so if you need more space you get a space you have not to say okay I need more space I need new hard disk, so in the cloud you load once a day 10 terabytes and next day you can load 15 terabytes, it's not a problem for Snowflake. The second thing is the compute is separate from the storage and the compute is separate from the compute. So when you create a new virtual warehouse you assign this warehouse to a workload. In my case I assigned this warehouse for my data loading workload with my pipeline. So this next one will be maybe a warehouse for data science purposes and the other one will be for the reporting purposes and I have a tool like Power BI or other tool visualize this data. The good thing is so when I see I have a problem maybe I have a huge workload and it takes too many time I can't stop this task and increase my compute in less than one second. What will this mean? In Snowflake we have different types of warehouse sizes. We're starting t-shirt size with XS, this is one server, till 6X large with our 512 servers. It's much more faster as one server of course and it takes me one second to scale out from XS to for example 4XL and it takes me one second to decrease it from 4XL to S. That makes Snowflake so unique that when you need more performance you get the performance. You click on it, you can you plug on it and you can do it also here in a Python script that you say okay I know it in the next step maybe this one I have not changed the house this is the reason why we need roughly two and a half minutes I need a bigger one I have a run at the moment so I guess with a warehouse size s but makes more sense for this load to use maybe a large warehouse and then I'm much more faster than two minutes so this is easy to use in snowflake you only have to do it with one click and said okay give me a bigger warehouse and And then you can use it. So now we have loaded the data. It was faster than last time. We need only two minutes. And when you take a look on the data, we will look. This is the right one. And next thing what we will do is we see here we have a schema detection. We load Parquet files into Snowflake so we can automatically transform this data into a schema so to load it in a standard table in Snowflake without any other workloads or doing, by the way, that you said, OK, I have to do it outside of Snowflake. No, Snowflake had integrated this schema inference. So we detect the schema and can load it directly in the table and copy it into Snowflake. So now we have the base table stored in Snowflake, but I need also the weather table or the weather data. So to combine it together in the end in my final table. So for this reason, I have to load wetter data or link, not to load, link wetter data to my data here. And I can see it also here in the database. We have already created, even doing my speak, this new database. This is the hands-on lab with the schemas, with analytics, external harmonize, and so forth. But I don't see here on frostbite wetter. So you see here what frostbite weather source and we'll do it is now less than a minute we go to data products, this is our marketplace and Search for it or it's highlighted. I often use it. That's the reason so You see here. Some of them are free Some of them you have to pay for it. So it's by request so you can monetize your data So you see we have so many data products for different And yes, business, this could be, I'm waiting for this one, it's not so fast as a tech, so GSP is for financial information or e-commerce, but for example today we will only load the weather data or assign the weather data to my account. The only thing I have to do is I will get the data, then I need a name which I will use for the database. in this case I have to use this name otherwise my script will crash and I have to assign a role to this database who get access that's it so it takes 20 seconds I'm finished so if now the provider of the data will update his data I see it immediately so I see when he updated his data to update also in my account in Snowflake so when we go back and we might have a refresh here so we see here the frostbite wetter source data so it's it was only click and we see here the data is different with the fuse we have forecast for days for hours and so far and postal code of course we have to assign it to the right postal code that's it I bring data from outside into Snowflake without loading this data only link this data into a snowflake that's perfect so and then of course I make some privileges setting here to see if it works if I have to write one it's only a check here yes we see we have here a postal code for England this is my first check and in the next one we can create with with a Python statement a few maybe you can do this again take a look on the watch that in the time so So you see, when you write this in SQL, it costs you more time to write maybe much more line of code. In Python, when you use Python libraries, Python function, it makes it faster for you. Especially when you calculate, for example, KPIs. For KPIs in SQL, to calculate is harder than when you have a library in Python. other scientists to use this library is much more fast and comfortable to use this here in my notebook. And in the end we will then set a session to this, create this one and then it's finished. So we have our view, the view we can work on it and the next idea is how I can load the data continuously into my database. So I float once a time the data in Snowflake, the next thing is to load it continuously I will have a pipeline and for this pipeline I need CDC so I will avoid that I have duplication the data from the base table and for this one we have streams in snowflake stream streams is an object directly on the table and when you load in the first time the data into a table maybe you have 20,000 records in the table then it is finished then you set up on this table a stream and you load 5,000 records these 5,000 records will be in summary 25,000 records in the table but in the stream I see the 5,000 new tables 5,000 new records and I can see is it an insert is an update or delete and then I know how I can to have to handle with this data and it's takes true that I can in the end only data which I need load into snowflake node application. Okay, so the next one what we're doing here is we will also create a user-defined function. What is a user-defined function? This is an object in Snowflake where you can use from different pointers, you can also create a function and invoke it from SQL, also in Python and with this user-defined function I will calculate Fahrenheit into degrees Celsius the reason is people from Europe again cannot understand so good what is 65 degree Fahrenheit to understand this is hot or warm and this will be also add to my pipeline so every time when I load new data the transformations steps my pipeline generate also a new column or the column already exists then it's calculated values and said okay 65 or 63 Fahrenheit is this degree Celsius okay so we saw it's a lot of things how we can load data into Snowflake so we create our own database only with Python code and let me jump in one of my other accounts this is the same what we did but to take a look on the data so here in this account already I have run all the scripts from the notebook and we take a look here also to the database and we see it here again this is my database and we see here where external data comes from so it's from a stage I give the stage the name frostbite raw stage okay I will enable the table directory the reason is I will see which data are included here on my stage so it's the direct access to the S3 bucket with the data governance of Snowflake so if I have not a right to see this stage I cannot see what is in the S3 bucket only when I have the right to see could take a look on the stage then I can see which data are stored in this S3 bucket. Not sure which was the most funny one so I guess this one so we can take a look in the in the years and we see there's a lot of data which we loaded and in the step before when we go back to the order details we see this is 4 gigabytes 2 gigabytes there's a lot of data and then I guess so we loaded string to loading job in the notebook roughly 20 20 gigabyte into snowflake the next thing is I have here my not this one raw position tables based from my s3 bucket I loaded this data into my country and to my menu table I can also say okay show me where comes the data from I see it comes from this stage so in this example it's easy to see it but when you have a bigger warehouse a large warehouse with many pipelines you will see what's the source of my data in this case the source of the data is the stage sometimes maybe comes directly from an ETL tool or you can also stream the data into snowflake that's also possible so it doesn't matter so in the end yeah so and we also create here let me go to my harmonized schema tasks so So when I click on this task, there's only two tasks. When we run this on a real production environment by customer, when we see this, there's more than two tasks here. This task will detect if new data will be stored, or the files will be stored on my S3 bucket and get a notification, and they start the pipeline job to load this data first in the raw data, then into the view and then harmonize it and in the end it creates this analytical table. So this one, this is the product what we want to see in the end. So we can also do here lineage but it depends on the orders, makes sense, we cannot see the impact from the beginning of the source but we will also add more features here on our lineage. And you can take a look in the preview to see what data are in it so they see here the different names of the cities I see the country I see the number of sales for a daily have so 79,000 food pieces that's a lot you see here the temperature and Fahrenheit we see it what it is in decrease Celsius and the same we see it also with inches and millimeters about the rain was comes down okay take the time so I saw how I can load all the data into snowflake and now I will analyze the data so with snowflake so it's seamless so all all things what they're doing now is it's based on the platform I've not to load data from everywhere the different areas into Snowflake and say okay I need some more information and comes from our system so we can all do this in Snowflake and I will analyze this data and for this one we have streamlit in Snowflake so I'm not a good designer but I create a small analyze part here to see which food I have sold in different cities. I have to wait a couple of seconds till it's finished. So now it's finished. Give me a second, I think it looks like nicer when I have the dark mode. Yes, better one. now I can so this was not it was too fast step back to my stream it take a couple of seconds again sorry but it was mistake from my side so I will select a date for example here from the 1st of January 2019 so if only data till 2019 here okay that's it and do this maybe for the first quarter so the first quarter will be in March ending on the 30th

Speaker 2 [28:24]

Sorry, we're a little bit behind schedule already. Yes. We have a lot of questions in the slide. That's it.

Speaker 1 [28:31]

That's it, so only one thing you see, it works, so I have to hear the data in Canadian from Montreal or I can switch over to Toronto in seconds and only to show it to you, this is the code, it ends here with line 36, I need only 25 roughly lines to create this small analytical part. Thank you very much.

Speaker 2 [28:57]

Thank you. So we have a lot of questions. I will just try to take some few. Say, does Snowflakes keep partial result of computations when changing scaling configuration for an interrupted task? Do you want me to take it again? Does Snowflakes keep partial result of computation when changing scaling configuration for an interrupted task? Thank you.

Speaker 1 [29:30]

I don't understand it.

Speaker 2 [29:31]

I think he's talking about...

Speaker 1 [29:33]

Can I read it?

Speaker 2 [29:33]

Can I read it? okay yeah which one is the first one i think it's talking about scaling and

Speaker 1 [29:34]

Okay. So when you interrupt a job with running, then you lose it, of course. You will scale up, you can run it again, but you cannot, through the process, when you run a query, change the size of the warehouse. This is not possible. But if you run maybe since 10 minutes is too long, then you stop it, increase the size to a bigger warehouse, and you can run it again to see if it runs then through in two or five minutes with the result.

Speaker 2 [30:11]

All right, thank you. There's a lot of question about scaling. He said, is there an auto scaling function to scale up and down the warehouse?

Speaker 1 [30:20]

No, there's no auto scaling. You can do it by program, of course. First thing, so it makes no sense. So otherwise, you're running out of costs. If you said, OK, I'm the owner of this platform and I'm responsible for the cost of the platform, I will not allow anybody to say, OK, I will scale up a warehouse from XS to 6X large. The cost is much more higher, 500 times, and run only a simple SELECT statement. that makes no sense so you can only do it if you have the ability so to have the rights to scale a warehouse and automatically it's not possible but we have multi cluster warehouse if you have a lot of queries doing maybe in the morning when many people's run their reports you can spit out spin up a second a third fraud up to ten of this warehouses so that the loading is load balance is to keep the same one you expected to see the result in ten seconds and not in two minutes when queuing this warehouse.

Speaker 2 [31:17]

Yeah, thank you. I will just take this last one. Maybe I will send the rest to you on Discord. How does version control work within Snowflakes? Sorry. How does version control work within Snowflakes?

Speaker 1 [31:29]

No flex. Warehouse control?

Speaker 2 [31:30]

Control? Version control.

Speaker 1 [31:31]

version control sorry version control this was my um you can use github you saw it yeah you can

Speaker 2 [31:31]

Version control.

Speaker 1 [31:37]

connect to github for your version controlling in snowflake you can also we have a smaller controlling on the site where we have our worksheets you see the different versions but for the version control use github it's the easiest way we have a connector to github

Speaker 2 [31:52]

All right, I think you will take the rest of the questions on Discord. Thank you very much for listening.