Real-Time Data Warehousing: Streaming Tables in Databricks SQL

0:00yeah I think it's a su what's really cool about streaming tables it's the semantics of it under the covers just writing a bunch of queries the managed State like so you add files in the volume R files roon all right guys thanks for listening today I'm with Franco uh Franco is the lead product specialist at Dar bricks is our expert on data warehousing and SQL uh thanks so much for taking the time with us today Franco um do you want to present

0:23yourself maybe quickly yeah hey everybody uh my name is Franco patano uh I've been working at data breaks for about 5 years now helping the world of warehousing understand this whole Lakehouse concept and now accelerate into Data intelligence uh and I'd love to help you all uh see how we've improved SQL over the years awesome yeah SQL is a big new thing for the air

0:45brecks I think I remember when I joined four years ago it was mostly data engineering plus a bunch of notebooks so you know just kind of the spark open source version with some optimization um plus notebooks to run the AI in the ML and since then a lot of things changed like we added SQL and a full Warehouse support uh maybe Franco we can start with a like a recap of what happened over the last few years at a bricks

1:08absolutely uh I tell this story every now and then so I've kind of got uh you know a couple slides on on the matter happy to walk you all through this uh so back in uh 2018 the founders kind of noticed something they made SQL available uh to users and they were using it more and more uh and then along

1:31the line along 2018 Delta Lake came out back then it was just Delta and it exposed to do DML or data manipulation with the SQL language think like insert update delete and merge on the data lake with SQL and we noticed that customers loved that and they started using it more and more but they kept asking us we have all our data on the data Lake we have to copy it to the warehouse to get

1:55fast bi serving can't you give us the performance we like out of the warehouse on the lake so that we don't have to have both and so that's where Lakehouse came from we were like sure we can essentially build a performance of a warehouse right here on Databricks so that you could have the best of both worlds and that's kind of where that thought leadership came out in 2020 um and essentially since then now

2:17everyone else is joining the badwagon and they're saying no we have a lake house but really these all seem like fake houses more like uh warehouses that are putting on a new coat of paint um but what we've done over the past uh 3 years that that we've been uh bringing datab break SQL to the market is we've been hiring an amazing teams of

2:39Engineers across the globe to help us build this system to build Lakehouse and now data intelligence platform uh and it's been amazing it's been quite the journey when I first joined Databricks I was helping customers build these you know SQL computes out of all purpose I've I've written blogs I've done talks uh I've kind of helped customers Stitch

3:02these things together and then in 2020 uh I think 2021 we broke the world record in uh data warehousing query serving and that was with our classic data warehouse DB SQL um and our customers kept asking us for more they were like yeah that's great but I still have to manage the compute and the storage and I still have to do all these

3:25configs can you make it simpler and then we came out with serverless in 2022 and customers were just very happy with that we're getting very good performance there and they don't have to manage as much and now they keep asking for more you know can't this plat you you guys are masters of AI can't you just have ai manage everything give us great performance make the usability awesome

3:48and that's where we're going is we want to build kind of the self-driving version of the platform so just to give you an idea of what was built over the past three years or what the impact of it um in 2020 uh you could see here performance was not great compared to the best one of the good data warehouses on the market and in three years we were able to forx that performance now leap frogging the

4:11competition and throughput uh and this is price performance so we're not spending money to get better performance this is cost effective but then not just on Big Data but another concern customers had was has is like how about as the user scale up because what they've noticed on these other their vendors with these warehouses is as they start exceeding about a hundred users

4:35the performance is not great as it was when there were low users and they kind of had to keep pouring more money into the warehouse in order to get good performance uh and whereas our Engineers took a look at this and we solved the problem a little more elegantly uh and we kind of get the same performance as the number of user scale so this was very important uh to customers how

4:59did we do that you know there is a significant long tale of projects that this amazing team uh engineering teams that we've hired over the years have done so I don't want to diminish all of the hundreds upon thousands of things that were done but three big ones uh first off you know I've spent I've written blogs and done talks about how you tune your files about how do you tune Delta Lake

5:21performance the first thing they did was like can't we do autotuning can't we just have ai manage that stuff absolutely we now call it predictive option ization uh and it it just does itself the second one was uh how does the warehouse manage all of the different types of workloads that are coming in uh you know you used to have to size it manually I actually have one

5:43of our very early customers has a Databricks job that resizes the warehouse every part of the day depending upon their demand uh they're like you know why did we have to build all this can't you just have ai do it and we did we had we built intelligent workload management to basically meet the demand that's currently coming against the warehouse where it is like bring the compute up to match that uh to make it scale better

6:07and then you know having to manage how you get good query performance is a very complex problem in other warehouses you have to do like indexing or clustering and this very complex things and you have to pay for that every time you try to do like an index or a clustering or search optimization you the vendor C charges you money to do that can be

6:30quite expensive our team looked at can we do a suite of techniques that enables the same type of performance without having to have the customer pay for it and that's predictive IO and customers really appreciate that we're able to get very similar performance that other vendors that they charge you for to get there by rewriting your data we're able to get really close to that without having to do all

6:52that and not just there some of the things one thing that we're going to talk about today is streaming tables and materialized views in order to build what I call streaming warehouses uh you know just traditionally warehouses was batch overnight processing it's kind of where it came from because the amount of compute that it took 20 years ago to process all this data was pretty intensive and during the business day that compute needed to be used to serve

7:15the data to the business so they couldn't have it running then so they had this batch overnight processing but the business needs data right now they can't be using yesterday's data the the competition is using real-time data and so streaming tables helps you do that and work that's the demo I'm going to show you today is how can you build streaming tables uh in data rck SQL and then also how do you get good

7:37performance on your bi reports and this is uh materialized views a lot of other warehouses have this ours has some Secret Sauce under the covers to make it really efficient incremental computation uh so that you're not having to pay a lot of money to keep these materialized views up to date and both of these concepts are natively supported within DBT and so typically people used to

8:00think that there was you know Delta live tables and DBT compete they absolutely do not you can use streaming tables and materialize use inside of DBT to make your pipelines uh more durable and in real time because if you've ever tried to do state management or incremental consumption in DBT it's really really difficult and now we make it super duper simple so that's a little bit about uh the capabilities of the platform

8:22that we've been we've been working on kind of the story of where we are what do you think Quinton yeah I think it's aome and the so the the streaming tables uh like it's incremental but you don't have to like it doesn't have to be up and running every day you can do every hour like you can you can change the needle based on kind of price and freshness is that correct yeah uh so what's really cool about streaming

8:44tables it's the it's the semantics of it under the covers the uh manage State like one of the hardest problems in data warehousing is where where did this pipeline leave off where do I pick up and start processing from here with streaming tables you call what uh ultimate what I call Ultimate State Management for free basically the streaming checkpointing automatically keeps track of where you are and Delta's transaction log knows like where

9:07everything is and you get automatic State Management like out of the box which is I mean for a data warehousing person it's like a huge relief off of them that they don't have to even think about this because usually they were doing like last process date uh and doing like really weird things with date and times and it's very complicated so I think uh streaming tables and as well as materialized views it actually brings really awesome capabilities efficiently

9:31and brings it to the analyst Community or the warehousing community in a very cost- effective way all right and the so the material you will use so they will do incremental recompetition when they can like I think a simple example would be you create the average uh then the state will keep for you uh like the sum and the counts and we will just do like an incremental update and we do that with a bunch of techniques to

9:53make sure that uh we avoid any expensive Rec competition is that is that correct yeah the overall project is called enzyme uh but essentially uh we there's all these different ways that you can update data depending upon the incoming data and the logic but in order to do that you need to understand the pipeline the end to end of what's happening on this in order to determine what is the best mechanism to do that is it

10:16partition Rec compute full reload uh all all of these different capabilities and essentially enzyme is built inside of the underlying mechanism that's that uh drives streaming TBL materialized views which is Delta Li tables it does all that computation and hard math and difficult things to understand what is the cheapest way to incrementally process this this materialize View and

10:38it does it that way so it's it's actually really efficient uh what I like to call fast and Fast and cheap which are two very important Dimensions that customers appreciate yeah it feels like their bricks is filling all the gaps do you how do you think the a brick SQL is doing compared to the maybe the top Warehouse on the market there out there I'm cheating a little bit because the they just had their public uh uh quarterly I think yearly results just

11:02published um and they said the best data warehouse on the market is trying to build capabilities like Databricks and what they call Snow Park which is a snarky take on spark uh but essentially uh their business is less than 100 million on on them trying to come out

11:25into our territory uh I think the last published number that Ali gave publicly that I can publicly say our warehouse hit 250 million last year uh I believe was the last number he publicly stated so the number as of today is larger than the last publicly referenceable number uh and I can safely say that we are more

11:47than 3x into into the leading cloud data warehouses Market than they are in our market so if that's any gauge of how well we're doing we're 3x better than what they're doing in our on our tur yeah so your take is their books is moving much faster into SQL and Warehouse than they would be moving into um maybe data engineering and and AI is

12:11that is that's what you're seeing that's what I'm seeing and I like to sum it up like this to best like to best understand it data warehousing is a solved problem set we have solved data warehousing there are papers academic papers researchers we've solved all the problems of data warehousing up until today uh so it's all like what I like to call solved problems we just have to implement them the best way possible on

12:34Databricks M data science machine learning at AI is not a solved problem it's a very very hard problem to go after and you need really really smart engineers in leadership in order to do it and datax has been executing on the hardest thing the unsolved problem for a very long time to Really add on solved problems to a platform that

12:57delivers the complex of AI is Trivial in order for a warehouse company to add this unsolved problem of data science machine learning AI to their stack just seems it just I don't it's because they're gonna have a harder time doing it right it's just we're we're basically adding on solved problems uh to the platform as fast as possible I just

13:19think uh just think about that for a second right it makes sense all right so SQL is easy and AI is okay sounds good let's let's see what we can do with the SQL house then do you want to do you want to start a quick demo to show us how we can create the streaming table and the material View and and maybe run some some exploration on top of that absolutely so uh here I have

13:41data breaks Unity catalog pulled up uh this is the One-Stop shop to what you need in data braks right so whatever you need it usually it comes from Unity catalog and I think you can't do data warehousing without Unity anyone that tells you otherwise is wrong so here what I've done is we've set up uh raw Landing of this data that comes in

14:03from the system where the device data lands uh in a volume and essentially in that volume is where all of uh the data goes so what I'm going to do is I'm going to ingest the data from the volume here into a streaming table see there just a folder full of Json files right and then I'm going to ingest that data into my ingestion schema into

14:28two tables I have my iot devices stream and so this is those Json files that are coming in for these devices what are they think like wearable uh exercise things like your uh Fitbit Apple watch type things they are users and it's tracking all of their calories burnt step swed and stuff like

14:50that then uh I'm going to ingest data about the users so I have various metadata about these users uh their gender age and other metrics about them uh to understand who they are right and so I have all of this data being ingested and I want to be able to use it so uh off the bat I just want to kind of show some really cool things about Databricks while we're here I've kind of had

15:14some of the uh comments about what this data is it's a streaming table for iot devices but then if I want to define the columns I can click this AI generate button here and it will essentially uh give me the definitions of these columns by itself so now I have really well defined metadata and it was super duper

15:36simple then one of the other biggest problems that happens is lineage and here I'd like to show you just what that looks like on Databricks uh here I have endtoend lineage so my data came from that volume where I had all my files landed and then my streaming table here uh where all my data gets ingested to and then it's going to my my

15:58actual materialized view that that this is where my data is going to end up where I'm going to be clearing it for consumption what is that uh and so just to kind of go over what this stuff looks like let's just take a look at the code real quick before we dive into the data build a quick dashboard from that um essentially this is the SQL Syntax

16:20for streaming tables uh you create a refresh streaming table you give it a name uh and then the definition and you can kind of see where we ingest the files uh from that directory and volumes same thing for that user data I'm going to define the table and I'm going to select the data uh as it types from from that from those files that land there

16:42and then what I want to do is I want to create a materialized view so when you're building dashboards it's it's super important to leverage materialize views because you're going to get the best performance on the bi dashboard with that so this is kind of like looking through an end to-end flow and so here our our materialized views we create that but just by create materialized

17:09view and then uh then all of our data is created uh now let's go back into the catalog here and we'll take a look at our database uh and this is where all the data is and now I want to leverage the consumption so here's that materialized view and let's say I wanted to uh have

17:32comments on it like the AI suggested comments here from the from the generate documentation I can go ahead and accept that and maybe I want to do AI generate for those columns too so I can go ahead and click AI generate and now all my metadata is complete uh and as an analyst this was super easy for me right I really didn't have to do a whole lot of heavy lift just a little bit of SQL

17:55um and then I was clicking through the UI and I'm able to understand lineage and it there other things in ER too if users are using it you'll get insights and then if you build in a data quality monitor you can do that as well and the so thei commands is like it's super powerful because it's enabling the search you could be asking any question and based on the command because the engine understand the data like it's going to be able to find your table and

18:17that's also powering like things like text to visualizations and things like that for example in the l view dashboard is that right yeah let's go straight into it great segue Quinton let's go straight into creating a dashboard out of it this so what I want to do is I want to go ahead and select that that data right

18:39so uh here we go so we have uh the user activities that I want to grab from there so let's go ahead and grab that actually I want to grab so that's our our table and you we'll

19:01see a little preview of that data right here as it's uh generating it so awesome these are our fields we want to go ahead and select that and let's go over to our canvas so what I have here is uh my users table and I have their age risk calories burnt uh how many records steps and Miles walked so I know I want to get some uh some some graphics along those

19:24lines so let's go ahead and go over here to the lake view canvas and so what's really nice here is I can uh just ask it to generate my charts so I want to see show users uh risk level and Miles walked and what uh Lake View is going to do is it's going to understand my question and then it's going to build me

19:46a visualization uh chart user miles walked by risk factor there we go so we see average numbers of w miles walked by their various risk factor I also want to see

20:09uh chart steps taken by age and here it shows the total number of steps taken by age group so I can understand what are the what's the activity levels of my user group and this is synthetic data I just want to call that out real fancy because it's

20:31it's autogen it's kind of synthetically generated data but this is how easy that you can create a dashboard and let's say I want to give it a name so I'm going to say uh activity tracker age and risk factor

20:53analysis and I want to publish my dashboard awesome I published my dashboard and there it is all right awesome so let me let me do a recap of what we like of what you did so you add files in the volume row files Ron uh just writing a bunch of queries SQL queries you can ingest incrementally the data meaning

21:15that every time you have new data you can just rerun it or maybe just based on some trigger on files Aral you rerun the the job which is going to incrementally add the new data refresh the materal as View at the end in your consumption um schema which is what you give to the analyst and then as an analyst it's super easy to build any kind of dashboard that's amazing yeah

21:37and it's not done yet I there's just one more thing I want to show you that I think is one of my favorite things about data breaks so uh not only can you build a lake view dashboard pretty simply right uh but let's go back to the catalog real quick one of my favorite things about this is that if you have uh

22:00let's say a business partner that had an issue with the dashboard right and they just gave they said I I need to understand uh what happened you can actually go into lineage here and you for that table and you can see exactly which dashboards are impacted by this table and so this helps you understand blast radiuses of issues right so if all

22:23of a sudden you have an issue with a pipeline you understand oh oh my gosh how do I understand what am I Downstream impacts of this well you come straight over here and you can see exactly where that is okay well what notebook produced that all right let's go over notebooks this notebook produced that code I'm got to share this tab instead so this notebook produced that code and that pipeline

22:46fed this dashboard so I know exactly what my blast radius is right and and then uh what about all of the queries that are tied to there or the models so with unity C calog you're when you leverage you know the best parts of Databricks together uh trying to do root cause analysis like one of the most contentious things in in business today

23:09is why is this data wrong and how fast can we fix it right and what is the blast radius you know when I was a practitioner in Industry something like this would take uh an FTE like 10 to 12 hours a week just to do root cause analysis I did it in a few seconds right uh I didn't have to track through codee I didn't have to look through different

23:32systems it's all right here in the UI yeah I think it's also super powerful for things like governance if you want to track the potential impact even if you have just pii information that got leaked like it's super easy to do it that way also so that's kind of what I wanted to share with you today Quinton hopefully uh hopefully that helps amazing yeah yeah it was awesome well thank you Franco uh thanks for the demo

23:54it was amazing and yeah let's just get started with The Arc SQL