Databricks Lakehouse: Delta Lake & SQL Warehouse Guide

0:01hello my name is JF I'm a solution architect at data breaks I joined the company two years ago in this session I will give an overview of the lake housee forbi and how the best data house is lake house and then I will show a demo but first let's introduce quickly data breakes data brakes is the inventor and

0:25Pioneer of the data lake house they popularize the concept of a lake house which combines the Bas of data warehouses and data L data BR has been recognized as a gner recognized leader in both database management systems and data science and machine learning platforms this recognition demonstrates datab braas leadership in providing a

0:48comprehensive cloud-based data platform that enables organization to unify analytics data science and machine learning workflows with the focus on scalability performance and e of fuse this is helping organization across Industries to accelerate their data driven digital transformation and datri is also the creator of a highly successful OSS data

1:12project such as Delta Lake Apache spark and ml4 and in the last couple years we noticed that every or is thinking about how to became more data forward but why do you want to be data forward more than ever leaders want to leverage data analytics and AI to

1:34transform every aspect of the business for customer engagement you can bring relevance and Hyper personalization to every touch point for Workforce experience unlock and productivity through automation Ser service and access to realtime insights and for business operation optimizing efficiency both internally and across partner

1:56ecosystems and Supply chains to maximize profitability everyone understand this potential and the urgency behind the data driven transformation and to deliver this project team have historically been working as SOS each team is been in charge of one of technical assets one team is working on the ingestion the

2:18other one on the building dashboards this creates slos duplicate work limits Innovation and slows down initiative and creat risks if data becames inconsistent across activities we've seen the market evolving from thiso Team operating vertically on their own domain of expertise to what we call data produ

2:40team working together to drive a business use case and deliver value faster now let's introduce the databased Lakehouse platform The Lakehouse platform greatly simplifies data architecture by combining the data management and performance typically found in data warehouses with the low cost flexible

3:03object Source offered by datal it's built on open source and open sards to maximize flexibility and lets you store all your data structured semi-structured and unstructured in your existing data Lake while still getting the data quality performance security and all governance you'd expect from a data warehouse data only needs to exist once

3:26support all of your data Ai and bi workloads on one common platform establishing one source of Truth and finally the L house platform provides still or collaborative experience so data Engineers data scientist and data analyst can work together on one common platform across the entire data life cycle from ingestion to consumption and serving

3:49data prod products and of course innovate faster let's look at how with the right data structure and data management capabilities in place we can deliver data warehouse and analytics capabilities on your Lakehouse and that's where data break SQL comes in DP SQL or databas SQL is a serverless

4:11data warehouse on The Lakehouse platform that let you run all your SQL NBI application at scale with up to 12 time better price performance with a unified governance model open formats and apis and course tool of your choice with no look so for the best price performance

4:33we lower cost gets word class performance and eliminate the needs to manage configure or scale Cloud infrastructure with serverless for the governance we're establishing one single copy for all your data using Open Standards and one unified governance layer across all data teams using standard SQL we have a very

4:57rich ecosystem you can use SQL and any tool like f Tran DBT PBI or Tableau along with data brakes to ingest transform and query all your data in place and last but not least a unified SQL experience we have a fully integrated SQL experience from ETL to analytics and ml enables every analyst

5:20to easily access the most complete and freshes data and uncover new insights and in fact DBC now holds the new record in 100 terab TPC DS the G standard performance Benchmark for data housing and it all start with your data stored on your data L with an open

5:43format and here we're talking about Delta this data is governed by unity catalog and then you can run your your orchestration with databas workflows and of course all this data can be accessed by data Engineers ml Engineers dat data scientist and business analyst when it comes to data brakes SQL and of course

6:06we have Perks of using DB SQL as I have mentioned so it's simple powerful data warehouse SQL Warehouse has instant start with serverless we have built-in queries dashboards and alerts and you can plug any external bi tool as mentioned like 4bi d and so on and we have also one Central catalog

6:28and a governance layer so Delta lake is the foundation for an open and secure data sharing and governance on the lake house it underpin the datab briak unity catalog which provides fine grained governance across clouds data and ml assets among the benefits of the unity catalog it allows you to discover audit and govern data

6:50Assets in one place with a userfriendly interface data lineage role based security policies table or column level TXS and Central auditing capabilities which makes it easy for data seers to discover govern and manage data you can also Grant and manage permissions using SQL so Unity catalog brings fine grain

7:14centralized governance to data asset across clouds through the open s s SQL and then finally centrally manage and audit shared data across organizations and during the last data n Summit we announce so many new features including streaming table which is a special type of table for ingesting and

7:36processes streaming data on the lake house you can use them to support realtime analytics machine learning and operational use cases requiring streaming data more efficiently process High volumes of data incrementally versus in batches and enable all your data practitioners not just data streaming expert with a simple SQL

7:58syntax in just data continuously arriving in a cloud storage and message cues and the second feature is or are materialized views which are a special type of views that pre-computes and store the results of SQL query and automatically keeps them fresh over time you can use them to accelerate bi dashboards because it's much faster to

8:21query data that's pre-compute versus querying the massive data volumes in a base Tails reduce data processing because because they are fresh incrementally which avoids the need to build or rebuild The View when new data arrives which lowering overall the compute resources and then improving data access controls by more tightly

8:44governing who can see the underlying uh base table materialized fuse and streaming tables can be used together to create a full data pipelines enabled by project enzyme you can process data incrementally across the entire Tire pipeline unlocking many real-time use cases and the SQL Warehouse are powered

9:08by the photon engine which is the next Generation query engine that has been Rewritten in C++ and leveraging vectorized CPU Premier chips which brings uh three to eight time faster interactive workflows lowering the cost with a 30% average TCO saving and as I mentioned data breaks

9:30Lake housee provides the best CCO because you have one single platform working together not having to stitch multiple system orchestration ml ATL bi Etc your oper and this help you reduce your operational cost of course and the lak house is not let's say also more than a technical platform offering

9:53the best performance because it's cover your data projects from end to end it provides a complete framework to onboard and build your data projects with the best data practices the louse is declarative start with your business objective and the platform will automatically aligned to execute towards this go not the other way around don't

10:16learn by failing leverage best practices to build and deliver your projects over the long term this will ensure successful execution have and have the greater impact not only your operational cost goes done but your projects are delivered faster and successfully bringing higher value and revenue

10:38sooner and now let's move to the platform we will start first by having a look at the data Explorer and then we'll focus on the SQL section which is dedicated to data breaks or data analyst let's have a look at the data

11:00Explorer as you can see over here you have a list of cataloges you can look at them you can also browse for example like case I will look for Global sales once I click on global sales you will see the owner of this catalog you can add some tags to simplify the search of cataloges while using this search bar

11:22you can add some comments to explain what's what's the role of this catalog to help to your team is like building your doation inside uh data BRS you can view all the different cataloges or schemas you have there you can get some details when it was created the met your ID you can also of course Grant permissions for groups to groups or uh

11:44users whether it's like a use catalog use schema execute the function and so on they can also of course access the database and you'll have the same thing you can add some tags comments and you will also view the models link to the specific database you can view the different functions you have build chain

12:06and of course as usual the functions uh I mean details and of course the permissions and then you'll be able to view all the different uh tables uh you have there let's select one single table maybe this daily sales so you'll be able to view the different columns again you can add some comments

12:29you can view a sample of uh this specific table you can view details whether this table uh which what's typ of this specific table as you can see over here this one is a materialized fuse you can see permissions uh lineage if it's the case so you see over here that we have used

12:51orders s to create daily sales of course you can click over here on the lineage you'll see which notebook is reference which table uh has been used to create this specific uh materialized fuse you can see everything can see in which pipeline this table is called you can see that it's called on this specific pipeline I have some volumes linked to

13:15it and I can also see that I have two uh queries linked to this specific table so we'll have really uh the big picture about everything and it will help you quickly the uh the issue you have over there and of course you can inest data easily and quickly in data SQL using our

13:41large variety of Partners through of course partner connect so for data ingestion you have F Tran Arion heo Rivery for example you can get started quickly with f Tran you just need to select the SQL Warehouse which is the power compute you will use to ingest a specific data you will select the catalog which means where we going to send this data and then once you click

14:04next you will uh be pop up to insert your five Tran credentials and start getting the data from from other uh Partners from from for example from Salesforce and so on and of course you have several others categories you have bi in visualization which means you can for example use SQL warehouses to uh get

14:28access to your tables using pobi to build your dashboard but not only pobi you have Tableau hex pret click Sigma t spot and you add other partners handling machine learning security data governance and reverse ETL and and of course you can also inest data manually if you want let's say by

14:51for example uploading uh your CSV your GSM but you can also use the different name native integration we have whether using gcp AWS but also uh Asia and of course the UI is very easy to use just need to go for example

15:13select this specific CSV and then you'll just need to select to in which catalog you want to send your uh data select of course the database and the name of the table and then you can create this table and of course you will need to have a SQL Warehouse on to be able to upload your data to your uh data link and the

15:38third option and that's the one we show you it's creating your tables using uh SQL command but first let's have a look at the SQL SQL warehouses so the SQL warehouses are the power compute that you're going to use to run your queries and so on and it's very easy to get started just need to go select for example going to call

16:00it USF okay just USF M and then choosing the size of the Sous is as easier as you can see just like choosing cluster size so you can see d x small X small small medium large like choose as I mentioned AER CH my case I'm going to choose x large of course whether you need to

16:23specify for how long this sareh housee will be on after it's turned off if it's not used for example for 10 minutes 5 minutes and the auto or the scaling in case you have multiple uh uh data analyst using uh the same SQL Warehouse of course for for to handle concurrency and of course we have different types Zess Pro and classic of

16:47course we do recommend to use serverless to reduce the uh total cost of ownership of course and you can see the CLE Warehouse starts almost instantly so this this one is on you have over here the connection details in order to use those uh details to link them either to connect your data L with forbi w and you

17:11can also see over here the monitoring to make sure that uh to see uh the or to track the usage of your SQL uh SQL warehouse and now let's go to this SQL uh SQL editor so once you're here so I already created the queries over here but you can just click here create a new query and then you'll be able for

17:34example to select as you can see have the other competion select star from and then just need to go here for example sales Global sales uh Europe okay and then I'll be able for example to query my first uh to run my first SQL query of course

17:56you'll see the results uh instantly so have bunch of and of course if you click on this plus you will be able to build visualization with within this specific query so we have a bunch of types line bar area five histogram hit map scatter bubble box combo so really a bunch of visualization that you can build and of

18:20course it will be a save s SC and of course you can you can uh make sure to uh to rename your specific uh your specific query for example just make sure save it first query just need then to save it and it'll be good to go so over here I will just select my uh

18:46SQL orh house and then the first thing I will do over here is run in my first query the purpose is to create a catalog called sales yourself inside this catalog I will create a database called tph and then I will create my first data table and this table and after creating this table I will insert data uh using this specific path to get

19:12for example ORD data I'm just specifying I'm going to inest a CSV 5 I don't have any header so and I will infer the schema and that's the delimer so you see you can see that over here I have my first uh table of course I will do the same thing for to create order stable line item and of course create the uh

19:35customer uh customer Bron Sable and all those Sables will be used of course to uh do some joins and and populate and get uh data in order to build uh to build the dashboard and of course since I already created uh the my catalog sales I can just come here and say sales

19:57uh youf GPH and you see I have my tables over here so the orders has already been created and populated the line item is under creation and the customer uh the customer table bronze again of course while uh so you see that the customer

20:19run Sable has already been created and I did run uh and of course if you want you can like you have some number of affected grows in such should some statistics that are uh generated uh for you and in the in and of course something very very important you have uh something called query history over

20:42here so let's open for query history show you how this query story is very very useful so over here you will see all the queries that was run on your environment of course you can select by user so we select by user yourself and you can select also if you want the SQL Warehouse so for my case I'm going to select HFM which is my SQL warehouse and

21:06you see over here you can see the different queries I did run and you can see for how long I did run when of course you can also filter by SES cued running finished see for example uh to have those kind of details of course if I go to this one you have you will see the query profiler give you some statistics you it's and this one will

21:29help you for example to debug uh your queries to make sure and to make them uh uh optimize here you can see the execution plan we have Scan road to columnar filter of course you can see this by time span by grow by pick up memory and you'll have different cues you can use grab view but also the three view can also copy uh those

21:52results to share them with other teams to in order to help you debug the your uh your issue and of course yeah and but that's pretty much everything about uh this uh the query history uh at the same time let's go yeah so you see now everything has been uh created for me so even the line item

22:14so now I will keep going I will be building some some kpis so I will use uh I will create a new table called customer silver and within this table I will select uh I will I will I mean this specific table will be filled with data coming from the customer PRS and I will

22:36select only few columns and I will remove the where the I will get rid of all the data where the customer key is null and of course where the nation key is different from 21 and here you see I have uh the distinct Uh custom key so what I will do I just click on plus over here visualization and what I will do I'll just create say my first chart

22:59counter I'll collect number of customers and I can also rename the name of uh rename this chart and call it number number of customers I just need to save it over

23:22here and you see I've created my first uh visualization that I can bring later into my uh specific dashboard but you can also download this result CSV tsv Exel you can preview you can add it to a dashboard from here but you can also do it from another uh another uh another UI you can use mine in second step I will

23:45also build another silver table so uh it's called order silver I will just make sure to get only uh data where the order key is not now and where the order uh date is higher than the specific date and I will also select again the distinct uh orders with did uh the

24:07customer uh did order so again I will just got to go to sorry counter number of orders number of orders call it here orders okay I can save it again and I can also create a l line as item silver

24:29table again I will select only few columns and of course I will get rid of everything where the order key is not null and just shipment date and everything I'll be did over here will help me to uh build my table discounts where I will be joining two tables together order silver and line item silver and I will create a new count

24:52called discount so if the discount is higher than this value then it's discount else it won't be uh it won't be discounted so let's just run the select star PR this uh specific

25:15table to get the uh to get the results see how the how the new uh table looks like and at the same time I will show you the lineage that would have been uh uh populated for me so I have this specific result so I can just go here click on visualization I can build a p start call

25:42discounts okay and you see over here like uh number for example number of discounted for example orders I can if I want I can choose the uh the color I know let's make it green we'll save this I will save of

26:04course uh I will save my query keep it on this GS just make sure to save everything of course yeah I forgot to order silver this is very important okay everything has been uh has been save so first let's go to the data Explorer I will show you the lineage

26:30Sal Us sales us and then discount okay really and then lineage okay so we see over here we have order server and line item silver was used to create this discount if I click on the plus you will see my bronze table

26:52his order bronze uh TPS silver orders so you have those kind of details and of course my apologies I will click over here so if I come see tables and I see the query so you see the query discount as I did run to get the number of discounted results

27:17number of discounted orders and now that I built those specific uh uh those specific queries and some charts I can now go and uh build my first uh my first dashboard so create a dashboard let's call it sales sales

27:41dashboard save it here of course I would require a c Warehouse then I can start for example I can add text uh sales dashboard of course you can have you have several uh several markdowns that uh that you can use to

28:04make your dashboard say uh build a better dashboard for visualization now you just need to go and uh add let's say the uh name of your name of your query so over here I have two options I can either select the chart the Das the little chart I buil or I can display the uh results of

28:26the table so in my case I will use the uh the charts I built I will also bring another visualization I think it's the number of uh I think it's orders yeah this silver order silver yeah again I have this counter that I have over here of course I can

28:50add I think I did choose the wrong uh the wrong query if I'm not I'm not mistaken because I did create build several queries over here that's not that's not a big deal yeah okay it's it's over here of course you can build as many charts as you want you can select them where where to push them you

29:13can uh edit the query edit like edit the visualization to make sure it's not displaying the uh the wrong thing so for example here I'll just call this counts instead of having the uh yeah for example uh discounts I can order everything and then done and here I have several options I can either specify

29:35like the refh the interval for the refresh for example can refresh every hour every two hours since the data is uh still coming I can add some subscribers to receive this to receive this dashboard and also uh also track it and that's not uh everything so I'll

29:57go back to this SQL editor and now I want to show you uh new features that we have uh added recently the first one is are the streaming table the one I have introduced and materialized fuse so for my case what I will do I will just called my uh the uh the specific celles

30:20user uh which is my my specific uh catalog tpch let's make sure that I'm getting the tpch GPC GPC so now what I will do first I'll

30:45just go to the uh the data Explorer I will go to uh sales youf I'll go to gpch and I will create what we call volume there that I will make I will simulate that we have let's say some uh let's say uh streaming uh that I have

31:10streaming data coming from sorry close the browser coming from uh my uh uh from a system just uh okay it's overs yeah okay I will upload it to my specific to the specific path for orders I will just

31:33copy this specific uh link and now I can inest streaming data uh inside uh data seal and it will be it will handle what we call the uh the incremental data so the first thing as you men as you see I've I'll be injesting data I push here

31:56so let's suppose you get everyday CSV files there so we can injest them through the streaming table and this and the data will be pushed to this order uh St so I can I can run uh I can run this specific uh query which will ingest as I mentioned the data and then of course whenever you get

32:18new data it will be incremental unless you want to have a full refresh and it can be done easily just by add this command refresh streaming table and you add a command there and of course that the first thing I will do is creating this streaming table to injust data from the specific data set and then

32:40I will create on the top of uh on the top of this specific uh on the top of this specific uh apologies top of this specific streaming I will get the data from uh from my uh from my Metalized view that I'll be also uh creating now so

33:03first let me just show you this let me just where disable select St [Music] from [Music] order this one so you can see over here the data we

33:28do have so it's the amount the ID of transaction item count so you see how the data looks like so what I will do I will just create just uh paste this code so create materialized view call sales USF tpch daily sales as and I will be getting the data from my uh my streaming table and then of course I can

33:52grant access to other users to access this material as use to avoid each time having this uh this full compute so now I can just start from and I can get the results I have over here okay I will just run this query to show you how this

34:15uh this table looks like so you see the number transaction count and the uh the total amount of transactions we have uh per day so that was everything I want to present the last thing I wanted to mention is if you want to have advanced trainings on data braks make sure to use the data breaks Academy just go to datab

34:38breaks.com learnt trining home thank you