WALD: A Modern & Sustainable Analytics Stack

Speaker 1 [00:02]

Hi everyone, today I want to present to you the VALS stack, modern and sustainable analytics stack. And before I start, just a little bit about my background. So my name is Florian, I'm head of data science at InnoVix, I'm a mathematician by profession, I'm interested in everything analytics, personalization, recommendation, organization, also modern data warehousing and have been using Python for about 12 years, so basically all my professional life and I'm also an open source contributor and one of the tools you might know like PyScaffold for setting up Python projects. So what do we do at Innovex? employer I work for. We are an innovation and IT driven project house and we focus on digital transformation so we help you to build all kinds of cool products, cool solutions from infrastructure to back-end front-end development and of course also analytics, data science and data engineering. So that's the part where I work. So let's get started. And before we tackle the WaldStack, a few definitions first, right? I'm a mathematician, so definitions first. I want to talk about the differences between a data warehouse, a data lake, and what ETL and ELT actually means. So just as a recap. So a data lake is a term that was introduced almost 10 years ago. Many of you, if you are a data scientist, data engineers, have worked with it already, I guess. The data structure is quite raw. You kind of pump in from all kinds of data sources everything into a data lake. The purpose of the data, so what you want to use with it, might not even be determined at that point. And yeah, if you're a data scientist, data engineer, then you most likely have worked a lot with data lakes in the past. And it's highly accessible, quickly to update, so there's no schemas and so on. You put everything in there, you just need to make sure that it doesn't become a data swamp over time. Then there's the traditional data warehouses, and the traditional data warehouses, the data is really processed, it's highly structured, it's currently in use, and mostly business professionals use it. And it's more complicated and costly to make changes, because it's highly integrated. So this is for the traditional data warehouses. So now, how to get data into a data warehouse, this is where you normally would do the ETLs, so the extract, transform and load. So on the left hand side we see all kinds of data sources, so you extract the data into maybe some data lake into a staging area where you would do all kinds of transformations, joining the data, filtering, doing data cleansing, maybe even applying some machine learning models to enrich the data and then after the transformation you would load it into a data warehouse where it's then easily accessible for some analytics and visualization dashboarding. So now with ELT, so extract, load, transform, you basically have reduced this one step in between, this one step who needed a different processing engine like Spark, so you completely get rid of this, you directly extract and load your data into the data warehouse, into a modern data warehouse that has those capabilities, like Snowflake, and do the transformations directly in the warehouse. So this reduces drastically the whole complexity, so you only have one processing engine within your data warehouse, it's more integrated, and this gives you also then faster access to new incoming data. So this is then ELT. And there are two big players in the field of the data business, you surely all have heard of Databricks and Snowflake, and Databricks with the really well-known Spark technology, processing engine started off offering solutions for data lakes back then. And those data lakes are the ones we use mostly for ETL pipelines, so you could say that Databricks has more like an ETL background. So on the other hand, we have Snowflake. Snowflake has a data warehouse background, but in a much more modern and advanced way so that it has the capabilities to do ELT and over the years actually the solutions of both providers converged, so nowadays they share many features, Databricks went more and became also warehouse in certain sense, so they call it a lake house, Snowflake offers the data cloud and also has many of the features you would normally expect from a data lake. So we see this unification. And still today the Snowflake is quite ahead in this race as they are offering a more modern and more unified approach to modern data warehousing and ELT. And now I think we have set the stage to enter the WaldStack. So this WaldStack is basically a combination of tools that fit nicely together and make use of ELT in combination with the modern data warehouse that allows ELT. And for all of you who don't know any German, so Wald actually just means forest in German and it's composed of the four letters so the the W just stands for a modern warehouse where you could use Snowflake but also Google BigQuery or Databricks. Then we have Airbyte. Airbyte is a tool that allows you to easily ingest data from hundreds of data sources into a modern data warehouse and it's really easy to use. I'm going to show this. And then we have Lightdash. Lightdash is a self-service BI tool that is a bit like Looker, so you can easily run ad hoc queries, do your charts, dashboarding and so on. And everything is kind of tied together by dbt the data build tool and if you don't know dbt and have any kind of sql scripts that you might be running with airflow somehow then definitely take a look into dbt it's it's more and more like adapted from from many companies it's an easy tool to do the transformations basically, so it brings all the software engineering best practices to your SQL scripts by giving them a nice structure. You have reproducibility, you also have modularity that you can reuse parts of your SQL queries easily, everything is version controlled nicely, you have documentation of your columns, you You have data lineage, you have nice visualizations, so dbt is really a must-have, and check it out if you haven't yet used it. And yeah, the VALSTEC basically is a combination of those four technologies, and as I said, you can run it on different warehouses, so like Google BigQuery, a fully managed enterprise data warehouse, only available on Google Cloud, as you know, and there's a dbt plugin that allows you to interface with BigQuery and also do UDFs with PySpark, then using a Dataproc cluster. It's also possible with Databricks, with the SQL warehouses as computation nodes, as Databricks is also or is available on all major cloud providers and there's also a dbt plug-in to make this possible and of course if you do anything with python then it will use in the end py spark the coolest approach actually is to use snowflake as a modern data warehouse So, as it comes with all the nice features of Snowflake, like all the governance, really easy to use, near zero maintenance and also like spinning up and down your warehouses, so your computational nodes in different sizes in less than a second and so on. So it brings all this and it also runs on all major cloud providers, has tons of great documentations except especially those quick start guides and yeah it's the easiest to use and full featured option to get started with the VAL stack so how does this like stack that I described now look like again in in a ELT kind of fashion so here's the overview again on the left hand side let's say we have some data sources, then the E and L part, the extract and load part, are taken over by Airbyte. So really easy to just put all your data done in the modern data warehouse, which, as I'm going to show later, is going to be Snowflake, and then you use dbt for all the transformations using SQL, but also, and this is like a newer feature that came out in the last six months by DBT, that you can also do those transformations in a combination with Python. And there, implicitly, Snowpack is used. I don't know if you also attended the talk in the morning by Michael about large-scale feature engineering and data science with snow park and python where he explained how to use snow park directly and here in this case it's internally instrumented using dbt and this allows you to to do it in a in a really um yeah in a unified way together with uh with sql and then in the end we can use lightdash to visualize and do ad hoc queries on whatever we have done. So just to showcase this, I made a little tutorial. This tutorial can be found under just valstek.org, which is basically only a redirect to a GitHub repository that has a huge readme on how to download and install all those tools which are all open source. So dbt is open source, airbyte is open source and also lightdash is open source but of course there's also managed services available and if you try it out you can install it and it's basically a dbt project repository and also the links to the data you're going to need. So I'm going to go quickly over it, speed will be quite brisk, but rest assured that you can find all the details in the tutorial. Speed is actually quite a nice keyword because the use case in the data I'm going to use in this tutorial is formula one data and in the tutorial the data is just used and uploaded into Snowflake then dbt is used to run some basic analysis basically a lot of SQL is used but also more machine learning use cases like what if I wanted to predict the future positions of certain drivers in future races and in the end the results can then be visualized. And this is kind of a typical use case that you first kind of ingest your data, you have basic analysis, you have more advanced analysis, so it can also be applied to many other use cases. So to get a rough overview of the data, it's not overly complex, we typically have also here dimension tables like the driver, Michael Schumacher, Sebastian Vettel, I don't know, circuits like Silverstone, Monaco, we have the constructors like Ferrari and certain races like Grand Prixs in certain countries and fact tables like the lap times in those races by certain drivers, the pit stops and results and in the tutorial you can play around with it and all the setup so if you use Snowflake as a warehouse to activate also Python to have the possibility to do the machine learning part. All you have to do is to activate the Anaconda packages, so this is a preview feature but in public preview and create a warehouse which is just a computation node in snowflake speak so that dbt can use it later and that's already it. So the setup is really, really simple. interesting the data with airbyte is also really really simple so airbyte if you have installed it if you run it you basically here's the screenshot you have a connection and i set up like you can just say okay as a source you take the the file that you want to upload like a csv file you have destination snowflake in our case we manually gonna trigger it in any and then you can just launch it and have as many connections as you want in in practical use cases you would of course rather have as a source other databases as three buckets kafka you but you can also have things like Twitter and Trello so Airbyte is at that point really flexible and also works closely with dbt so even at that point you could already define some additional transformations that are directly run in the beginning. Now I'm going to dbt and to install dbt together with Snowflake again all you need are a few additional extensions like for Snowflake then Snowpark that is the data frame API that is like PySpark, then we have the Snowflake connector and then you configure everything in the profiles YAML. So I'm now going to skip the whole SQL part because this is PyCon, right, so you want to see some Python code. So maybe for all of those who don't know dbt, so dbt works in the way that it says a model is a kind of a view or a table, so every select SQL statement that does something that is somehow materialized in a view or in a table is called a model, and with now the possibility to also have Python models, this is why this function is called model, so it does not mean that it has to be some machine learning model, so to avoid this confusion. And what we do here, so this is the function basically trains a model to train on some features, what the future position of a race car could be, and we can, in this dbt context, define the packages we're going to need. So here Python, scikit-learn, and pandas, how it's going to be materialized. Then we create a model stage, so model stage is just some blob storage in Snowflake where we can later save our model. Then we reference another table, a dbt table, so the train test data set, so this is something not shown here, but in the whole tutorials this was done using SQL within dbt, so you reference this table and what you get back is in this case a snow park data frame. So at that point we are now in snow park, but since we want to apply directly scikit-learn and the data is not that huge, so what we do is we make it a pandas data frame, we use a train test split from scikit-learn, a really, really simple model, because that's not the point here, logistic regression, we fit on train and we save the model in the stage. So the main takeaway here is that it's really simple to combine dbt with python and leveraging a snow park and a snowflake to make use of it. So how does the inference look like? Again we have another model, so because we're going to do the inference and save the results in a new table, so we again define the packages we're going to need for this function. We load the model from the stage, we define a user-defined function, it does a prediction given a data frame, in this case only with two columns, let's say I only have two features for the simplicity of it, and returns then a new column with the actual results, what we think, what kind of position the driver is going to have. Here we predict and return it, so it's really simple. Again, and now to apply this UDF, we reference the holdout data set for prediction, select the feature column, and we have a snow park data frame, and this looks like PySpark syntax, but it's actually like snow park because it's really much the same, the API. So we add a new column predicted and call the UDF on using our feature columns. And what Snowpark now does for us is that it does all the conversions into pandas and runs this on the Snowpark data frame. So that's, again, like really, really simple for a simple use case like this. And so we see that DBT, Snowpark, Snowflake can be easily combined and you have the power of Python to use it and not only SQL. So let's go quickly to the last one, the visualization with LightDash. So LightDash also again highly integrated into your DBT project. it will pull all the information from your dbt projects like dimensions and metrics and what are those. So if we now ask a simple analytics question like what are the average lap times split by year in Grand Prix then this is the metric and this is the dimension and you would define those directly in code in your dbt project which is nice because if the metrics become more complicated and you talk to a colleague about a metric, they know how the metric is defined because it's in code. The same with the dimensions and what Lightdash, like kind of an open source looker alternative, does for you is you can run ad hoc queries, create the charts and the dashboards. So how does this look like? So just to show you how simple the main view looks and that you have basically those four just described options and if we now are interested in the average lap time, all we would have to do on the left hand side, we would select the race name and the year and the metric and we can also filter like saying yeah I only want the Italian Grand Prix and the British Grand Prix and we get a chart and here in most cases the average lap time of the British one is a bit a little bit longer maybe because the circuit is longer and just so it's with light dash you can easily kind of provide other like more business intelligence oriented people the like an easy way to take the metrics and the dimensions you define to run ad hoc queries so it's quite nice and of course those charts can then also be saved on different dashboards here it also took the minimum pit stop durations you can arrange them give them to your co-workers to your colleagues, and yeah, it's actually a really neat way that it all plays together nicely with your whole dbt setup. So yeah, that's already the end of the overview of the tutorial, as I said, so everything is on GitHub, try it out, it's way longer. And yeah, maybe one more thing, not all use cases are purely like analytics and I visualize something and run some ad hoc queries quite often, you also want something more interactive and for this you might know the Streamlit library, which is a really nice way to build and share data apps and also this was just acquired by Snowflake, so you could also at that point use this to build a data app. So yeah, to conclude this talk I presented a VALT stack, so basically four powerful technologies combined and they really work well together so and dbt is in the in the middle of it and this stack especially if you want to go for ELT, then this stack is so flexible that it allows you to do use cases from simple analytics but also to full-fledged end-to-end data products. And with this I want to also say a short thank you especially to Hope Watson from our partners at DBT Labs. I built the first iteration and used her Python Snowpark GitHub repository as a starting point. Also Michael and Marco from our partners at Snowflake helped with the details of Snowpark and those awesome slides are not by me, I wish I could. But my colleague Michael Hoffman helped. So thanks a lot. And yeah, we are also having a booth out there, so if you have more questions, if you want to later talk to us, then just meet us at the Snowflake Innovex booth. Thank you. Thank you very much for this beautiful and interesting presentation. Two people ask, how do you create such super fancy slides? Yeah, you have to ask Michael, but it's Keynote, and yeah. Okay, and another question, aren't the Python UDFs in dbt connection to Databricks computationally expensive and slow? No, it's basically, so I would compare it with the same overhead that you also have in PySpark if you, in the UDF, I mean, since it's, it depends, it really depends. If you use Pandas, then of course there's going to be some copying in between the data frames of snow park into pandas and this is why you should avoid using pandas in the UDFs but if you want to use scikit-learn you have to at some point right but for everything else definitely only use the snow park API and then in the morning we learned that there's snow park ML coming right now in private preview. So I'd rather use this. So yeah, you have to be careful whenever you use Pandas. Thank you. Another one. Have you considered using a lightweight open source alternative for the transformation layer? For example, DuckDB? If yes, what are the reasons you are not using it? So, DuckDB is a database, a really nice and fast database, but it doesn't bring, at least as far as I know, I haven't used it, all the capabilities you would want from a full-fledged data warehouse. So I'm not sure if dbt has any connectors so that you can directly use it on ducttp, might be, but yeah, definitely ducttp I would say has other use cases. Thank you, five more questions, maybe you can meet with Florian. Yeah, I'm looking forward to it, so see you. Yeah, thanks a lot.