Fabric SQL Database Use Cases Within Analytics Solutions | Data Exposed: MVP Edition

Key Takeaways

Learn how folks are using fabric SQL database in the real world in analytics solutions this week on data exposed MVP edition. Hi, I'm Anna Hoffman and welcome back to Data Exposed MVP edition. Today I'm joined by Paul Andrew and MVP. And Paul, why don't you kick us off by telling us a little bit about yourself and what you do? Sure. Yeah. Hi, Anna. Thanks for having me. So, I've basically been designing and delivering data platform solutions for for customers far and wide now for about 20 years. Uh my career started with SQL Server 2000. I've been using it ever since in every form and yeah, helping customers to implement it. >> Awesome. Cool. Well, that's great to hear. And I know uh not only do you have a big SQL server background which obviously goes back uh a much longer time period uh but also an awesome experience with fabric. And so today's a super interesting example episode for me because we're going to be talking about in your customer scenarios in your line of business like what are you seeing people doing or what are your suggestions for what people do when these two worlds kind of come together and you have fabric uh analytics workloads and platforms and and then now you have SQL database and fabric. So I'd love to just kind of learn from you absorb uh what you're seeing and what you recommend. Yeah, of course. So, fabric fabric plus databases, the uh the green and the blue uh the the green the purple icons um to start us off here. So, to help us with context and kind of just walk through some of these use cases, I I've got a very basic kind of data analytics platform that um we might implement. Uh I'm old enough to call this extract, transform, and load in a data warehouse if you wish, but you know, let's let's bring us up to date with the the cloud native tooling. Uh the unified platform of fabric. So very simplistically here, we've got some source systems. We don't really care what form, what what shape, what they look like at this point, but getting those through a bronze, silver, gold type state to curate that data to to make it accessible for customers to to serve it out. So yeah, nothing nothing too fancy here. Just a starting point for what we can then do to this platform to extend it with some SQL database items if we wish. >> Cool. >> So first off, my my favorite um metadata. So data about data. So what could we do here? Well, if I'm going to extend a customer's platform and and have done here, I might add that SQL database and I then might use it to house some tables around my pipelines, my parameters, whatever dependencies that I might need there. And I might add perhaps a a bootstrap pipeline or you know a parent pipeline here to then go off and and call all of those child bits of of work and activity that I need to then go and run. So having a SQL database within fabric within the same workspaces as those pipelines really helpful to then obviously use that metadata that's in there to drive all those other processes as I get that data from the source over to the reporting. This is my first use case and pattern for you. >> Awesome. Yeah. And I mean just from my experience I'm hearing about this one a ton. like everyone's like, "Oh, it's awesome because the database is right there and I can just like go ahead and use it for this." >> Yeah. And it means as well that, you know, if we think about deployment of of these things from development to test and production, it means that instead of deploying new pipelines, potentially those pipelines can be very dynamic. I can then just use metadata changes in that database to to move through environments as well if I wish. So yeah, that metadata support gives a lot of advantages here in that execution. Awesome. >> Um, so that was my favorites. Um, number two, um, we've used this one for logging and alerting. So across that that platform if we want something custom maybe from our notebooks or or maybe from those pipelines as well thinking about maybe the internals of those pipelines where we've got our activities with failure path success paths you know we all want that to go to a a central and and customized place beyond those out of the box capabilities in fabric. So here in this example, let's have that SQL database capturing all that logging and alerting and and things that we might need from those different artifacts as this is processed. We could then of course expose that in some other PowerBI dashboard as part of that same workspace as well. And uh something that's that's I like in fabric that we didn't have in data factory before is the teams activities and outlook activities within that pipeline. So if I then want to send an alert, I can then do that very easily on the back of some of that logging and alerting information that's captured here. >> Oh, interesting. I didn't realize that from pipeline. So like something happens in your database, you can trigger a pipeline that sends an email or some sort of alert. Yeah, we could do um either triggered or or polling or or whatever we want there to to yeah fire off something if this execution process has not done what it's supposed to do or even if it's just finished. We can post it to the the teams channel for the business users. Maybe the data set's refreshed, go look at your dashboard, include a link in that team's channel post as well. You know, it just makes that execution of of the platform a little nicer for them. >> Awesome. Nice. That's a great use case. Cool. >> Uh that was number two. So uh moving on to to number three. Uh I think probably uh the the bane of most people's lives when it comes to analytics platform, what I call reference data or or old enough to call it MDM, master data management. But every source system will almost certainly have a different version of some lookup data, some reference data that we need to bring in here. So, in my example here, I'm just going to use a SQL database to actually handle that. Maybe I've got three source systems coming in. Maybe they've all got a different value for country. Um, for me, based in the UK, there'll be, you know, U.K., there'll be United Kingdom, there'll be UK without dot, etc. So having somewhere that's relational that's that's easy to access just to rationalize to to harmonize some of those values as they come in. So in this example I might interact directly with the source system to to do that. I obviously might do something from my bronze layer as well. But ultimately I've got that same transactional store as part of the analytics platform just to deal with that harmonization before I maybe go off into some dimensional model in my gold layer. Obviously I need consistency there for my business users and reporting. So one way we could do this we could of course do it in a lakehouse but yeah it's one of those things it depends but yeah in this case why not use a SQL database. And how does it work in the the section where it's going from your bronze into a notebook to your silver? How do you kind of use those things together? >> Um, typically if I was doing this, then I I might just use some JDBC connections to to access that SQL DB to then obviously do that that harmonization through that bronze to silver notebook. Um, that'll be perhaps the the best pattern at the moment. Otherwise, yeah, using some pipelines to to copy that harmonized set of reference values back out maybe into that that silver layer in in some reference tables or something as well. Yeah, depending on how we we want to curate that data as we go through. >> Got it. Got it. Sounds like there's a couple different options you could use here, but a database is useful there. Got it. >> Yeah. Um, so that was number three. So, moving on to number four. So, in this scenario, I've I've got a Power App. Um, of course, doesn't have to be a Power App, but let's just assume we've got some kind of lightweight, you know, small front-end thing that we actually want to include as part of our analytics platform. I implemented this for customers thinking about some um finance use cases where we'd got the data flowing in from the finance system and people in the finance team, they wanted to make some kind of manual adjustments to that data. as it was going through the the solution. So in that sort of scenario, we exposed a power app with a SQL DB as the backend to that power app and then through that analytics platform, you know, it almost becomes another source in the analytics solution. But of course, it's slightly more integrated than that because we've got those record changes in the transactional store. they can be fed from our silver layer maybe and then pushed back into our gold layer as well. It means in this scenario we can have that integration between those dashboards into that power app depending on if it's a canvas app or a model app. There's a few ways to do that. So yeah, just a another potential way that we could use this. Lots of other patterns here as well for for people more familiar with data versse and things like that having mirroring into fabric from other places but yeah in this scenario it was contained for for a use case that we delivered for a customer. >> Got it. Got it. Makes sense. Yeah. I'm also hearing a lot about uh Power Apps and this being an easy database source. We're also hearing about um transitical task flows. I'm curious if you want to comment on kind of how that might play out. Maybe if you just had a dashboard instead of a power app or what's been your experience with customers? >> My experience with customers is you'll you'll deploy and build a very simple power app that's intended to be quite simple for a scenario like this. But then of course you know the the requirements uh evolve you answer one question there's 10 more questions that become spawn from it. Uh we've we've also um maybe to combined the Power App with my previous use case around master data as well. Having customers use the Power App as a front end to like upload sets of post codes and you know just a bulk insert of of a whole bunch of reference data as well. Could be another kind of scenario blend in three and four perhaps. But it it depends maybe is the the consultant's answer as always. >> It's a great answer. It's great. always always correct. [laughter] But no, it it makes sense. Thank you for your experience. Yeah. >> Uh and then I think just to to finish off with a a fifth one that's a bit of a an inflight um workload at the moment. We've we've got a scenario where sensitive data handling very important um for for us and obviously lots of people. I'm in Europe, so often subject to GDPR and other regulatory requirements. In this situation, I've got a a SQL database in there, but the the purpose this time is actually to use it to handle the sensitive values as they come in from the source system. So maybe something that's personally identifiable. It's it's PII. I intercept that at the point of ingestion. We put that into the relational store for a name, date of birth, whatever else we might want to capture. And then from there, we either, you know, encrypt, we hash it, we mask it, we we do something with that um before that data then goes into the downstream analytics solution. Now, as with the others, you know, we we could have used a lakehouse or something else to do that. The design thinking for this one was actually because the customer already had Microsoft purview in the picture that we could actually scrape some of that perview metadata from what had already been captured in the data catalog. Uh thinking about the classifications for some of that ingested data that you know perview already knew about from the source system and actually then use that to then drive some of the ingestion processes. kind of blending that metadata thinking from that first pattern with this privacy store thinking of sensitive data handling as then that flowed through into the downstream reporting solution as well. >> Interesting. Yeah, this is definitely a good use case and I like the way that the things come together and kind of you're using what you already have to try to optimize um the overall architecture. Um cool. I I've learned a lot in this episode, Paul. I'd love to like you know obviously you've worked with fabric a bunch you worked with SQL a bunch also sounds like SQL database and fabric um like any tips or tricks or advice or gotchas or things you think uh would be good for users to know who are trying to explore what use cases to use SQL DB and fabric for >> um I'd say yeah give them all a try and you know the the five or so patterns that I've exposed in this conversation yeah you know they're not exclusive you know bring them together and and start using them together. Um, I would definitely recommend looking at some of the the VS Code extensions and and the tooling for the SQL databases we've got in Fabric now as well. Thinking about your deployment options, using metadata as code and having that backed up in your source control repository as well if you are going to start using some of these capabilities. So, yeah, definitely things to bear in mind there in addition to that kind of data architecture lens. >> Cool. Awesome. Well, Paul, uh, thanks so much for coming on Data Exposed. I learned a bunch. I think it'll be super interesting for our users as well. Uh if you're watching this episode, go ahead, give it a like, leave us a comment, and let us know uh what scenario you're going to use Fabric SQL database for. Uh we'll put some links in the description for you to learn more, and we hope to see you next time on Data Exposed.