Microsoft Fabric: Data Warehouse vs Lakehouse vs KQL Database
Vložit
- čas přidán 27. 06. 2024
- In Microsoft Fabric, we have the data warehouse, the lakehouse and the KQL database as options to choose from if we want to store some data, BUT which of these should YOU choose? And how should you begin to decide?
This video steps through the data warehouse, the lakehouse and the KQL database in detail, comparing their features and limitations, so that can make the right decision for your business.
Towards the end of the video, we look at how to combine these data stores into end-to-end architectures (medallion architecture).
Catch up on the Power BI to Microsoft Fabric Transition Guide series here: • Power BI to Fabric Tra...
Timeline
0:00 Intro
1:08 It's all delta parquet
2:30 Lakehouse overview
7:17 Data Warehouse oveview
8:28 KQL Database overview
11:05 Side-by-side comparison
12:07 Data Types
13:08 Languages
15:00 Copilot + low/no code
16:45 Ecosystems
18:40 Security granularity options
20:55 Typical Workloads
22:33 Other unique features (including Git)
23:49 Personas
24:36 Practical applications (end-to-end examples)
#microsoftfabric #powerbi #dataanalytics
Hey Fabricators! If you found this video helpful, please LIKE, SUBSCRIBE and SHARE with colleagues to get the word out - THANK YOU! 🙌🙌👍👍
Like the pragmatic approach. You're walking the tour in a naturally calm way enabling viewers to follow your thinking very easily.
ah thanks for the feedback! were you talking about the first half in Fabric or on the tablet? Just curious to hear which style people prefer, or a mixture of the two? Thanks for watching!
@@LearnMicrosoftFabric I liked the mix of both - the tablet was useful for methodically going through each point, but I also like being able to relate that to seeing the tool on screen.
Good to know, thanks!!
@@LiamMcCauley Agreed. A mix of both was great in this video. But divvying out each section into more digestible sized videos maybe the way to go. As much as i watched this video all the way through. 30 minutes may be too long for others.
This content is pure gold. Uncredible quality!
Thanks, glad you're finding it useful 😊
Great video! The additional data security options for data warehouse is another reason to implement the gold tier using data warehouse instead of data lakehouse, for example if you want to have a unified data warehouse within your organization but you want to control who can access what dimensions/facts based on criteria such as departments, job roles, etc.
Yes exactly! Thanks for watching!! Have you been building things in Fabric yet Brian?
@@LearnMicrosoftFabric not yet but looking at getting the DP-600 cert in the next few months hopefully!
@@brianszeto3418 nice, good luck with that! I will probably be moving to DP-600 specific content after this series (April time) which might be useful for you 👍👍
I've got to say your videos are great and your delivery style is excellent. Remarkably personable.
Keep doing what your doing. Distilling down this huge transition that some of us are going through, in that moving from the traditional Azure data services (ADF, Databricks, Synapse etc) into Fabric. Other CZcamsrs show similar material, but you're diving just that little bit deeper. Nice one.
Matt
Thanks for the comment, appreciate your feedback 🙌
Will, that was a a really great video : nice and comprehensive , and the flow at the end was great : thanks!!!
Glad you liked it! Thanks for watching :))
your content is really straightforward, to the point, well designed and perfectly communicated. thanks a million.
Thanks a lot for your kind words! Glad you're finding it helpful 🙌
Superb Explanations...! and Great Video
Thanks! And thanks for watching 😊
Outstanding. The layout of the content is extremely helpful.
Glad you found it helpful 😊
Great Video! really appreciate the breakdown and the possible outcomes.👍
Thanks a lot Alex! Glad you enjoyed the breakdown! Have you been building with any of these data stores yet?
Will, love the way you explained this much discussed topic . Thank you
Glad it was helpful 🙌
NIce one Will, good to see you dominating the Fabric space. well done on great videos. I'm learning from you :)
Hi Clint, great to see you here :) hope you're doing well, and thanks for the support 🙌
great info! thanks for sharing!
Very detailed explanations. Thank you
Thanks for watching Peter 🙌🏽
Insane, thank you so much Will.
No problem, hope you found it useful 🙌
Very helpful explanation of the topic! Thank you Will!
Great, glad you found it useful!
Great content indeed! It helps me to clear the mist in my head, many thanks for the work you are putting in this series! I am pretty much an on prem DWH architect making my feet wet to build out an ETL architecture in fabric.
That's good to hear! Glad the videos are helping :) hopefully it's making sense for you with your background? Lots of exciting opportunities in Fabric over an on-prem DWH 😊
Very informative, thank you!
Thanks for watching, I'm glad you found it informative 🙌
Awesome video man, didn't know your channel, subscribed! thx a lot
Welcome to the channel, lots of video to catch up on 😉 haha thanks for watching!!
this is a great series, really enjoying the content... very nicely explained without unnecessary jargon or unnecessary complexity - well done sir 🍻
Awesome, glad you're enjoying it!! Next video coming out on Friday 😊
Thanks! Keep up the great work.
very generous, thanks a lot Ramesh 🙌🏽 i will do, just getting started ☺️
Simple amazing! I believe this is the best video that I’ve seen to a end to end solution for fabric.
Hey thanks for watching and for the comment! Glad you enjoyed 🙌
Good Explanation !
Thanks for the feedback, glad you liked it 🙌
Thank you very much Will.
Really useful :)
Thanks for watching, glad you found it useful 🙌🙌
Great great video, thank you
Glad you enjoyed 👍
Good video, so really clear...
Thanks for watching!
Top presentation!
Thanks for watching!
Thank you for the excellent video. I have a question: Which is preferable in terms of Audit Framework and Watermarking? We encountered update conflicts in the warehouse table when attempting to update audit tables while ingest multiple tables simultaneously, which updates watermark and audit values. To circumvent this issue, we've adopted an insert-only approach for both Audit and Watermark frameworks, which has proven effective. There's also discussion about using a KQL database, but we're uncertain about its complexity.
Great video Will. Thoughts on having a lakehouse for gold layer as well but perhaps then loading this to warehouse for SQL specific tasks like masking and object or column security. I just like the flexibility of lakehouse languages offer.
yeh I’m pretty sure you can do that stuff on the Lakehouse SQL endpoint (no need to move over to DWH). just be careful that your audience has no more than Viewer role in the workspace otherwise anything you set on the SQL endpoint can be sidestepped (using Spark). hope that makes sense?
Amazing thanks for the breakdown! I'd love to throw pricing in the comparison, maybe for another video?
The cost for each will be similar. In Fabric, most of ‘cost’ comes from Compute (I.e. how you operate on the data), rather than which data store you use 👍
Hey, Will, I have a question. In the previous video comparing Data pipeline vs Dataflow.., you mentioned that it is not possible to make a shortcut from a Lakehous to a Data Warehouse. However, in the end-to-end architecture example in this video, the third layer is a Data Warehouse that is getting data from the previous layer - a Lakehouse. How can we set up this workflow, then?
Otherwise, I find this video and your content extremely helpful and insightful! Keep up the amazing work!
Hey Daniel, great question, I probably should have clarified that point in one of the videos. Although you can't create a shortcut from a lakehouse into a DWH, you can actually query a lakehouse from a DWH using something like select * from [LakehouseName].[dbo].[TableName] - this makes your 'Silver' layer accessible in your 'Gold' DWH. You can build views from the DWH (from Silver lakehouse data) or if you want to 'materialize' some gold analytical models, you could use stored procedures with an INSERT INTO you gold table. Hope that makes sense? Another option is to use a data pipeline or dataflow to write gold tables into the data warehouse (unfortunately we can write to a data warehouse using a Notebook).
@@LearnMicrosoftFabric Thank you so much for your quick and extensive answer! It definitely answered my question and I have more clarity now. Keep crushing it with the amazing content!
Very clear and informative video of the different options in Fabric. A related question, admittedly not in the scope of this video, is using semantic models vs data warehouses to share data with customers (effectively the Power BI devs). At my org, we're generally planning to create semantic models to give users access to gold data, how do you stack that up against giving access to a gold data warehouse? Are there use cases for both for this task?
Yeh depends on your requirements, and how much access/flexibility you want to give them.
Greetings Will -- fantastic job! You happen to have your hand drawn diagram available in electronic form that you could share?
For me. It is always the Lakehouse because everything else is derived from there.
Another amazing video - thank you. Question: how does one decide on how many lakehouses/warehouses to have? Do you divide by the many functional areas of your business or can you dump all your organization’s data into just a few lakehouses?
Thanks for watching! Really depends on your requirements and preference as a business! Normally the biggest factors are whether you want git integration and separation of data for security reasons
Hi, my two cents. Depends of the method that you use for organization data, if you came from mesh you focus on owner data, if you came form fabric probably y use funtional aproachh.. if you start from scratch yo divede by responsability grousps and security requeriments. but remember you can change in the future.
Hi Will,
Thanks for all the insightful video.
got a question about Power BI DirectLake mode,
can a DirectLake dataset be created based on Data Warehouse as well?
Many thanks!
Yes can be built from both Lakehouse or data warehouse 👍 next video will focus on Direct Lake ☺️
Great video Will. I have few questions.
1. "Materialized views" are supported in warehouse, but not in lakehouse. Please confirm.
2. In my ongoing project of Power BI to Fabric migration, i am thinking of the following pattern. Bronze and Silver layer in lakehouse and Gold layer spread across LH and WH. To be precise, star schema in LH and materialized views in WH. Your opinion about this ?
3. There is one more entity "Data Mart". It has good RLS and OLS features. Any idea when to use this and how Data Mart is different from LH and WH?
1. No, materialised views only in KQL database, not data warehouse. In Lakehouse you can imitate the effect by writing to a different table (which is effectively what a materialised view is)
2. It’s difficult to give opinion on architecture without context, but in general LAKE-Lake-warehouse is a common pattern.
3. Don’t have much experience with data mart so can’t really comment. Look at Radacad’s article on the subject I believe he mentions data marts in his comparison 👍
Preparing for DP-600, my first Microsoft exam, but KQL wasn't covered in the learning material, thanks!
KQL is not assessed in the DP-600 exam
@@LearnMicrosoftFabric strange as its in the practise exam
@@04nimmot oh really? that's surprising to me... I'm 98% sure it's not in the exam. I didn't get any KQL questions in my exam. And it's not mentioned in the Study Guide: learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600
@@LearnMicrosoftFabric you're definitely correct its not in the learning guide, had a question on ALM Toolkit too. Bit of looking up XMLA and should be okay for the exam in two weeks, I think..
Hi, this is Gabee from Brazil. Thank you for this amazing lesson! ⌛ I have a question about RLS in the Lakehouse. Does it apply to Power BI as well?
Currently, no, you will have to define in the semantic model too. In the future, yes (when they roll-out a feature called OneSecurity, possibly by the end of the year).
@LearnMicrosoftFabric do you have the side-by-side comparison as a downloadable file? Thank you
Hi Michael, I've added it to the resources section here: www.skool.com/microsoft-fabric/classroom/c75b239c?md=b89a483c2f8146d0adfc7366f714d3d3
Hey Will, I was reading today that Lakehouse default function is replace and not append any longer. Will these deltas still apply to lakehouses after that announcement?
Hi Paul, not sure I entirely understand your question, but the underlying format for all tables in Lakehouse is delta parquet, so parquet files with a delta log that tracks changes (whether they be through a replace or an append)
I love u
😂 thanks
MARK
✅ Dynamic FilePath
✅ Add Date from Within File (this is good)
✅ Flatten Header Rows with fx (ooo la la, add-in's ftw)
✅ Remove null Columns (Wow!!)
✅ Change Header of Column (I forget to use reference tricks!)
✅ Filter by a List with Merge (hyperspeed!)
✅ Clean data in 1 step (I really really love fewer steps!)
CHANDEEP
✅ Eliminate Promoted Header Step (love fewer steps!)
✅ Create Columns from a Record (I really love fewer steps!!)
✅ Add Total Row to Table (wierd, but cool)
✅ is Number or Structure (weirder, but cool)
✅ Projections (weirdest, but cooler)
✅ Apply a function to all columns of a table
✅ Group Kind. Local to count stints (🤯)
It's a tie! Which mean I win! 🙏Thanks guys!!!
I have one doubt, in this video you are telling we need warehouse for data modelling. can we use warehouse that is automatically created for each lakehouse for data modelling? Instead of creating a new one? Can anyone explain this?
I'm not telling people what I think they need, just talking through the characteristics of each to allow people to make their own decisions 👍 every company is different, with different requirements.
You can use the T-SQL endpoint of a Lakehouse to perform some data modelling, but you will be limited to read-only operations.
@@LearnMicrosoftFabric thank you for your response man. I got it
⏳
Thanks for watching!!