How to Turn SQL Database changes into Real-Time Intelligence with Fabric Eventstreams | Data Exposed

Key Takeaways

Learn how to turn SQL database changes into real-time intelligence with Fabric and Delta Flow this week on Data Exposed. Hi, I'm Anna Hoffman and welcome back to Data Exposed. Today, I'm joined by Arindam. Thanks so much for joining us today. Can you tell us a little bit about yourself and what you work on? Hi, Anna. I'm Arindam Chatterjee. I'm a principal product manager on the Fabric real-time intelligence team. My particular area of focus is Fabric event streams and some of our eventing and stream analytics services on Azure. Awesome. It's great to have you on the show and this is an area that I know is getting a lot of excitement. And so, I'm curious to learn how it plays with SQL databases. Now, just to set the the stage, like SQL developers have been working with databases for a long time. But, we don't necessarily see everybody using real-time capabilities. What's changing and you know, what are you seeing with your customers and the scenarios you're enabling? Absolutely. So, you know, what when we talk to SQL developers, you know, we often wonder why isn't every single SQL application real-time yet? And the and because the the fact is that every single insert, update, delete operations on your database is a business event that's waiting to be acted upon in real-time. So, imagine an e-commerce application, your customers, your users are submitting orders, canceling orders, inventories are piling up or and getting fulfilled. All of this can be acted upon in real-time. But, what's the what's the challenge? What's the problem today? So, the reality is that when you're actually trying to build real-time applications on on SQL, you do have to stitch together CDC connectors, get message brokers involved, stream processors. You may have to learn Debezium semantics, parse a bunch of nested JSON, write a whole lot of custom transformation code. And last but not the least, manually manage destination tables through continuous source schema changes that may be happening in your application. So, all of that poses a big challenge. SQL developers really should not have to worry about all that when they're trying to build real-time applications. Cool. Yeah, no, it makes a ton of sense. And for folks who haven't worked with Fabric maybe a lot or just getting started and don't know about real-time intelligence, can you tell us a little bit about what it is? Sure. So, you some of you have may have heard about Fabric. We just had our Fab Con presentation. It is a set of integrated services or workloads that all come together and enable you the user to put together data platforms that meet your data, your streaming data requirements, real-time requirements, batch requirements, and analytics that you might be running you know, for your businesses. So, real-time intelligence is a is a part of that. And to go a little bit deeper into real-time intelligence itself is the product and service that I I work on. So, we are about Fabric event stream. Fabric event stream is the way to do ingestion, processing, and routing of events all in real-time. On the left are a whole bunch of sources that that our event stream supports. This lets you get to get data from over 30 different sources, you know, from other clouds, from other on-prem databases and services and Fabric events. All of those data is getting ingested. That's your ingestion layer. Then, you get to process the data as it is streaming in through our no-code experiences. So, this is just UI that you can drag and drop your different operators and put together a query. Or actually, you can use SQL. This is something that we had a past you know, session with you Anna at Data Exposed. We went to a little bit more detail there. And after you have processed all of those events that are coming in, you can send it out to Event House, which is our high high scale performance time series database. You can of course send it to Lakehouse, run your Spark jobs and other things on it. But, you can also build real-time dashboards and build your own custom applications on top of it. Um And then, rules and alerts. You can get all the data, you can get all the insights, but you want to be able to act on them in real-time. And that's what our rules and actions through Activator is all about. And last but not the least, I wanted to talk about Spark. A lot of our users have told us that while their data might be coming in from various sources, including CDC sources, they want to be able to write Spark jobs, do some real-time AI applications, build those. And that's what we have enabled with Spark notebooks as a destination for event streams. Awesome. Great. I love it. This is a great explanation and gives me an idea of, you know, why I might use this and when I might use this or when I really should think about using this. Now, one of the things that's coming a lot up often in this space is this idea of Delta Flow. I actually have no idea what that means or what it is. I can make some guesses, but I'd love to get your take on like, what is Delta Flow and how does it help SQL developers? Sure. Um And so, Delta Flow is actually our our solution for SQL developers, for database application developers to get database changes. That's what Delta in Delta Flow stands for. To flow those changes from their source databases into real-time application, real-time intelligence in Fabric through event streams. So, what does it do? First off, it is about transformation of the raw Debezium feeds into analytics-ready events. You don't have to worry about CDC, the Debezium semantics, you don't have to worry about the JSON processing. What Delta Flow does for you is transform all of that into a set of events, into rows that look very like your source tables because we are aware of the schema of your source tables. Because this is a schema-aware system, we are actually able to also dynamically adapt. So, if you go and change your source table schemas, add a add a new table, add a new column, delete columns from your from your tables, we are able to detect that and automatically adapt to it. The schema registrations happens for you automatically as well. And then, the problem I referred to earlier where users can have hundreds if not thousands of tables sometimes in their source databases. And managing those on the destination side becomes a becomes a challenge. So, we realized that what we have done is actually because we have access and are able to track the source changes, the schema changes, we automatically create the destination tables and manage them for you as your schema is evolving. And so, put all together, what it enables our developers, the database developers to do is to focus on building their applications. You know, not on on all the plumbing, the CDC plumbing that was previously required. They're able to focus on what they want to do. They're able to use our no-code experiences to you know, to process the data, analyze the data as it's flowing through and then use Activator or real-time dashboards and Event House to build their end-to-end applications. Awesome. That makes a ton of sense and I I love the last point about letting developers focus on building their apps. I guess, you know, like I've seen this little gift, but like I'd love if we could walk through an example or a demo. Yeah, absolutely. I would love to do that. So, um So, this is you know, I'm going to start actually in the in a Fabric workspace. This is showing various components of a system that we were actually building ourselves just to kind of test out test out everything. What you are what you're seeing here is a stadium operations. So, imagine a stadium with 100,000 attendees, you know, coming in to the stadium on game day. And you're building the task is to actually build an operations a system which is getting data from entrances, from the concession stands, for security events that might be happening. All of that flowing in from various sources, including from Azure SQL. That's the database part of it. Into an into a database which is our Event House, the stadium operations database. And then, you build some visualization and alerting and reporting applications on top of it. So, what I'm going to focus on, I'm going to start playing the video. What I'm going to focus on is actually the concessions event stream that is going to receive all those database changes. So, let's get started. All right. So, we'll start with the concessions. Let's go ahead and you know, add an item there. It lets me you know, I go ahead and create an event stream. Um I give it a name which is my concessions tracking event stream. I create. Then, this is our get data experience where I'm going to choose the Azure SQL CDC connector. Uh some of you might have already used it. Uh you can of course set up your secure networking there if it happens to be, you know, in a VNet. In this case, I'm going to choose all tables and this is where I can still keep the old behavior and and get the raw CDC events, but then I'm going to use Delta flow here. Uh so I'm going to choose the analytics ready events and auto updated schema option. So that which is the recommended option for moving forward. Uh I'm going to set where the schemas are going to be stored and that's it. Just a few clicks. Uh and I start connecting. It creates the event stream and now you see an event stream with a source. And you see the data actually flowing in and already you can see that it's it's flattened out. There's no Debezium per se and you see the schemas on the left. And so now I'm going to set up the the destination because I need all of these to be in an event house. And you can see that we have already detected that there are six or seven schemas here. Uh six actually. All the tables that were on the on the SQL database. Uh I'm going to say that okay, for each of those just create separate tables. Uh and just a few more things. I'm going to just say that just give me the payload not so much of the metadata that's coming in and I hit save. And that's it. So again, regardless of how many tables there were on your on your source database, you're able to with a few clicks start ingesting the events from there including the snapshots. The initial snapshots start to flow into your event house. And you can see that there are you know, you do preserve some of the Debezium metadata in case you need that in your application. But you also get your your application columns. You know, that that you need. Awesome. It's great to see this and I love how easy it is to actually go through setting it up, confirming it works, giving the previews, giving walk-throughs of the various choices that need to be made. So I love this. One question I had as you were going through it is you know, what if something changes in the database? For example, like there's a new column, maybe a new table. How how will this kind of handle that change? Yeah, absolutely great great question. So let's actually, you know, talk about handling schema source schema changes. So it all works in four steps here. So there are source DB changes. So the two scenarios that we cover are new tables getting created or in existing tables new columns getting added and I probably should have added here about columns getting deleted or renamed and things like that. Anything that causes a schema change, right? Uh we are able to detect. So Delta flow is able to detect these changes through the DDL events that we get from the Debezium feed. Uh particular to SQL, we do have a lot of information in the Debezium feed. There are some differences with other other databases, but that's something that we take care of instead of passing it on to you. Uh the user. Uh so once we detect these changes, we register a new schema if it's a new table or we will register a new version of the existing table schema in the fabric schema registry. Uh and as soon as we do that, then all the destinations you know, right now it's event event house, but very soon we will add support for lakehouse and other destinations. Uh all of those destinations they adapt. In the case of event house, uh new tables get created or altered. All of this without any downtime from an application standpoint. Awesome. That's great. I'd love to take a look if you have something. Yeah, absolutely. I mean, we have something very very quick. So I'm going to go back to that same you know, same application. This time I'm going to focus on I'm still going to be in the event house. Uh and what you will see here is actually let me start playing. Yeah. So we have a menu items V1. And so this is original table. It has a bunch of columns about what the menus are in the concession stand. Menu items are what the prices are which stand there in and so and so forth. Uh and what you will notice is that while there is a price column, you know, something interesting can be done if you want to do promotions. And so all the a developer has to do is go to the source table, add a column. So this is just a simple alter table column and add a promo price to that you know, to that table. We are able to detect that and you automatically get you know, within seconds a minute or two at most, you will get a menu items table that has the promo price column automatically added. And so the from a user standpoint, nothing else to do. Yeah, awesome. That's great to see. This has been great. I've learned a lot. I think there's a lot for customers to go look at and I know from both sides, both from your team and from the SQL side, there are things that are in the works. Things like change event streaming which is in public preview in many of the Microsoft SQL options and then some integrations we're working on with your team. So there's a lot of things that are in the works that we're excited about, but as we close out, any final tips or tricks for folks who are getting started? Absolutely. So so I know I mean, you know, what I showed over the last few minutes was really from database changes to real-time insights. I mean, that's our goal to get that you know, to enable our users to get that within minutes. Uh and so hopefully what you noticed is while the reality today without Delta flow was all those problems that I had called out before. Uh but now with Delta flow, one managed pipeline, right? I mean, I just I walked you through the UI. Very simple point at the source database. We will do a you know, all the heavy lifting to to get all the schemas, transform them, make it ready into an analytics ready shape, schema registration and the schema evolution part. Uh so all of these is available today in preview with in fabric and those are the databases Azure SQL, SQL MI, SQL and VM and Postgres. Those are the databases we support. You made an awesome point that there are other technologies like CES, like mirror mirroring that are also in the works. Those are in our road map. You will hear details from from me and my team very shortly on that. Awesome. Great. Well, it seems like we have a reason to bring you back on the show in sometime in the future. So we look forward to that. To our viewers, if you like this episode, go ahead give it a like, leave us a comment and let us know what you think of the new experience or what you're using it for today. We'll put some links in the description for you to learn more and we hope to see you next time on Data Exposed.