Parse FHIR Healthcare Data with VARIANT & Lakeflow in Databricks

0:05Hey folks, today I have the pleasure to host for the first time Matthew Jalia. He's a solution architect based in the US and today he going to demo a very interesting feature that will help a lot of people in the healthcare vertical. Hey Matthew, how are you doing? >> Good. How you doing? Good to see you.

0:28>> Good to see you too. I'm very happy to have you here. We discussed about with us we discussed about making this video a few months ago and he's like built a massive massive or awesome demo that will show about how to leverage a fire format using Databricks technology such as lakeflow and much more. So can you

0:50Matthew tell us a bit more about this fire format and how we can leverage it >> in healthcare uh where I you know I get to work here at Databricks a lot of experience as a customer before that working for health insurance here in the United States you have to be able to share data with all these different uh components of the healthare system right so providers have to send data to payers or to other providers payers of course

1:13are trying to you know potentially co-manage the members for things like valuebased payment or us risk adjustment all these sort of things that we have to do whether it's a mandate you know that's coming from the government or it's just you know better for patient outcome if you know folks can share data so there's been many attempts to do this in a easy way over the years and there's

1:35a group called uh HL7 that comes out with these specifications for folks to use in order to uh to share this data we call this interoperability on the healthcare space and there's been a I would call them like almost like false starts sort of in a way. uh you know HL7 V2 I don't even remember what a V1 looked like to be candid but V2 you know

1:59sort of uh a transactionbased sort of um within those transactions you know multiple lines if you can kind of picture that it would be both like a delimiter within a delimter so like pipe delimited with you know also commas and tildas it was very complicated to use those in reality and then you know people switch to XML uh and all of these are still in use by the way because once you start with one of these formats it's hard to break away you know people build

2:22applications on top of them. XML is notoriously difficult to work with as many people know. So, kind of the most recent one that has really uh kind of taken off, you know, caught fire, so to speak, is the uh fast healthcare interoperability resources, and that is FHIR, fire, and that's a JSON format. Um, which makes it a lot easier to be able to share this data. Now, if you are

2:46in the healthcare space, um, you know, here, especially here in the United States, but really worldwide, being able to receive clinical data in a way that you can ingest that and make sense of it, it really opens up all of the different use cases that a healthcare organization cares about, such as, you know, being able to look at clinical notes and use those for maybe a vector store and then something like agent bricks later to extract value out of

3:09those and, you know, identify patterns there. So predicting adverse events, understanding your patient population, quality measures, all the sort of things that uh that the folks in the healthcare space really care about. It's all really locked away in this data. So these JSON files is what we know what the fire format is. It's already better than XML, but they're they're extremely nested.

3:31They're actually also very large and no two of them really have the same format. There are certain rules for how you put them together. I'll show you one in a second just to kind of scare you kind of what we deal with here in the healthcare space. But they they've been they've actually also been sort of notoriously difficult to uh to take part as well to use for analytics. They're great for,

3:53you know, JSON's always been great for sending information someplace else, but they're not really great for turning it into a data model so that you can do all the kind of things that, you know, folks actually want to use this data for. So, you know, let me share my screen. Let me kind of show you the motivation here for uh what we ended up doing. And to be candid, previously before we had some new advancements in Databricks and even

4:15doing this in spark previously would have required uh either accelerators like dbign Ignite which we had produced um you know here in Databricks in our industry solutions uh or other specialized software in order to take these uh these fire bundles apart. Uh you're going to hear me say file JSON file bundle. When we talk about fire, uh, often we call it a bundle because

4:38it's essentially packaging together, uh, these different what we call resources. And a resource is going to be, uh, information that's going to tie together. It's essentially like a its own data model within this JSON that all relates to a patient or an encounter. An encounter might be you go to the, you know, you go to the office, uh, have an office visit with a doctor. That's an

5:00encounter. Um or it might be an observation. You have a you go and have a lab uh blood draw of some sort and you know your glucose value is going to be an observation let's say uh or a diagnostic report. There's going to be these clinical notes all of this kind of great stuff. And there's about 187 or so about these different resources. Here's what one looks like. Right? So you can see this one's a synthetic data by the

5:24way that we'll be able to share with the with the customer. And we got the resource type bundle here. It's a transaction type and then all the magic happens in this entry array and this is what kind of makes it difficult. In the entry array you have something called the full URL and that's basically the primary key for that particular resource in the array. Uh it happens to

5:48live next to the actual data that you need it. This is synthetic data and when you use synthetic data often you make it a little bit nicer to work with. So you can see that there is an ID here and that you know happens to match part of this full URL which is really nice in the real world that's not a requirement even just capturing the full URL next to the actual real data can be a challenge

6:11just using like normal normal tools and you can see here you know this is a patient resource type you know it's JSON so you know the order is nice here because it's synthetic and again in the real world the order could be anything and there's these things called extensions which you can This makes it very extensible of course, but that also produces a challenge often because you can see you can have an extension of an

6:34extension and that can go very deep and nested. So even if you try to make a schema for these types of things, often it it breaks after a while and you just end up with some string values at the very end because no one really is ever going to know for a particular, you know, transaction how many nested of these extensions you might have. And then additionally, you know, because the

6:58this array, this entry array starts with patient, but then you could have all these different encounters. You can see how gigantic this is here. all these different encounters, right? You and an encounter has providers, there could be more than one locations, all these different things. The structure of any two of these could be wildly different. So, there's really not like a universal

7:20schema for all of these. There's sometimes just a universal schema for potentially a resource in the middle. And folks have come up with kind of interesting ways to parse these such as like cutting the files up and everything. But, you know, these full URLs, which are basically the primary keys and foreign keys to join these things together, they're really just very unique to this file itself. And by splitting it up, you may actually be

7:43causing like a data quality issue. But being able to ingest these easily, usually in a streaming or real time kind of way is, you know, critically important because this is, you know, this is the kind of information that could be used for that real-time decision-m that maybe really would affect a patient's care. So, that's sort of the motivation here, uh, is that they're difficult to work with. great for sending hard to actually get value. Use them for

8:06analytics, machine learning, all the kind of great stuff that we want to use it. >> Be curious to know how how people used to leverage this in the past. In the past, uh, you know, really the only thing the really the only game in town was actually something we had also produced at Databricks in the past called DBignite. Uh, that's still a great industry solution uh, package. It's available completely open source for all of our customers on GitHub. Uh

8:29it actually so you can use it and a lot of folks do use it in production today in order to parse these and that required some very sophisticated uh custom spark functions in order to actually uh parse this and make sense of it. But depending on the real world situation you might be in something like those full URLs if they were required

8:51some of those other types of these packages even ours like didn't necessarily know to you know keep that along for the ride and bring that in you know it also you know to be candid you know having come out a few years ago it you know relies on things like RDDDs you have to have a classic compute cluster those sort of things and you know I really want to make sure that our customers are able to do things as easy as they possibly can and sometimes

9:14that just means that the great innovation that we do here at Databricks allows us to make things a lot simpler. though two things that have really happened that have made this something where you don't need a a specialized software or even an accelerator from us is lakeflow declarative pipelines especially being able to do those with SQL on serverless SQL right so even for folks that maybe

9:37even the the pipeline interface itself is maybe a little too scary but they know how to write SQL and they're connected to a serverless SQL warehouse you can actually you know generate those right now using the SQL syntax so I you know I typically show that and then also just just variant right so the data type variant which is you know as of speaking right now with you is technically public preview in both Delta

9:59and iceberg previously you would have had to use a you know specialized software including an accelerated from data bicks DB Ignite which is available you know open source on on our GitHub and uh Databricks industry solutions still widely used uh in production by many organizations and it does more than just ingest uh fire JSONs. I'll mention it's also fantastic with a low code sort

10:23of mapping for actually writing uh JSON bundles because ultimately let's say you make a machine learning model that prob you know predicts the probability that somebody may uh have developed sepsis or you know may not see a behavioral health specialist you know upon discharge from a a BH uh inpatient admission. You want to get that information over to somebody who can act on it as soon as possible.

10:45And often the way that you would send that into an electronic medical record system like an Epic or a Cerner is by, you know, generating a fire bundle and sending it over. So DB Ignite is still kind of one of the only things that can do that really easily from your lakehouse data using Spark. But, you know, you don't technically need it any longer with ingestion because we continue to innovate here at data bicks. It's really exciting. So two things

11:08really really happened that allowed us to make this easier than ever before, right? such that any SQL analyst can can actually pick this up and understand this. Right? So something that used to be really hard accelerators or specialized software some of which are actually leaving the industry if you follow this I won't say who but uh you know now a SQL analyst can do this. So,

11:32so let me kind of explain what how that happened, right? So, so two things. One is that with lakeflow declarative pipelines, things that used to be really hard like streaming, creating streaming tables, incremental materialized views, those sort of things, you can now write them with the with the SQL language and you can actually execute them directly against the serverless SQL warehouses even if something like the lakeflow declarative pipelines, you know,

11:54multi-file editors, you know, too complicated for whatever reason. >> And they can also correct >> either. >> Go ahead. Maybe in the future they can also use lakeflow designer where they can just do the track and it's part of what you mentioned like democratizing access data to everyone. So what used to be hard to process now it's way easier.

12:16Yeah 100% 100%. And and the second thing to be candid is that you know in Delta Lake you know technically in public preview right now uh the new variant data type which allows you to take things like these JSON files have no knowledge of the schema and start to immediately parse them and you know we call it variant because sometimes you

12:38just don't have control over you know what the schema might be right that you know typically uh we like to think about JSON coming back from APIs for example and today maybe it's one schema that you know the API provider decides and tomorrow it's a different one right and you know in order to be able to allow that to not break our our system you know even though we do offer things like schema evolution and and lay flow

13:00declarative pipelines it allows you to really easily you know take that ingest it use it um and I'll show a few different uh few different things here that we're able to do with with the trip parse JSON function which lets us turn something that's complicated like this into something we can write SQL against. So, let's go ahead and do that. So, I'm going to switch over here to Databricks, of course. And what I've got

13:23here is I've loaded several of these JSONs. Again, this is all synthetic data we generated from Synthe. There's a a great repo that I have on my GitHub that allows you to run syntheta directly on Databricks. So, we'll we'll pass that along later for folks. So, if you're somebody that wants to play with this, um you'll be able to either, you know, use a few files that we'll provide or

13:45you can actually generate your own data, which really, you know, makes it uh makes it even more real, which is really cool. So, you can see that a lot of this I've generated several months ago, but I do do this demo all the time. In uh in the volume itself, we can actually click and see these. So, here's one of those exact, you know, JSON files. Of course, it is gigantic. Um and uh you know, and that is what we're going to load.

14:08So we have a workflow that I often uh kick off doing during my demos. First time I did this was actually for a webinar back in early January. Every, you know, every few, you know, few weeks or every few days or whatever it might be, somebody will ask me, can you do this demo live? And we'll often uh generate new files directly to that volume. And you can see that, you know,

14:30I've got a file arrival trigger here, you know, for example, that will kick this workflow off. And what's really cool about this workflow, and I actually just ran it manually, I think uh uh just a few minutes ago here, just to kind of do this, is these are actually SQL notebook task, SQL SQL notebooks that are executing against a serverless SQL warehouse. So again just for that

14:53you know kind of uh democratization you know the we were sort of talking about a few minutes ago you know if I was to put this in production in you know reality I probably would add this to you know maybe a lakeflow declarative pipeline with the multi-file editor and you know kind of add some additional bells and whistles that we can get with that. um from just like a teaching perspective, I really like using SQL notebooks and uh

15:17being able to show people that like hey you can do really interesting things just with uh serverless SQL right so even without even using other types of compute so you know I declare some variables and things like that that you can see here a lot of folks sometimes don't even know that you can do this that you can declare variables directly in SQL and you know and then for things like the schema or the catalog you know you you can use the identifier function

15:40here and that will take things that are you know in the name space in Unity catalog and allow them to be uh executed as variables in your code which this makes it you know completely able to be used in an asset bundle or something like that for CI/CD and of course you probably have a catalog or a schema for the different environment variables and things like that. And one of the things I like to do is declare statements that I maybe I want to execute by sort of

16:03building them together and as as a string and then you can execute those immediately. I mean this is something that actually has been in data brick SQL for over a year now. You did not need to wait for the stored procedures preview for this to even happen. This is something that we've actually had for quite a while. >> And just for youration we will put the documentation for all the features that Matthew will mention the identifier the

16:28executive edit so you know why to use them and also when to use them. And in addition all the code that Matthew will be showing will be available in the description of this video. It will be in GitHub and then you can deploy it this kind this this pipeline on your own platform or even datab bricks free edition. That's right. So let's get down

16:50to it. Right. So you know here's the files again. We're just you know I just did a list statement uh to show that they're here. Right. of course uh you know so the only the only thing that is really of interest here that I had to create you know sort of a statement that's going to be executed is that you know the volume path is going to be different depending on what catalog or schema maybe I've chosen

17:12otherwise this code you know I could have I could have just hardcoded this if I knew it but exactly but again I always like to think of things for deployable in your environment also so you know you get to choose your catalog and your schema and then this volume path will automatically be there. But if we look at the SQL create a refresh streaming table again not pi spark not scala you know uh not spark structured streaming

17:36this is just going to automatically take care of this for us using lakeflow declarative pipelines which is really exciting and I can apply DDL just like I might have you know normally when I was you doing something like in a traditional warehouse and I can even do things like uh you know have a default with the current time stamp and this first this first table Here I'm going to use autoloader and I'm going to stream

18:00using the read files function. And notice what I'm doing here is I'm just going to load this in first as text. And that will just create a a string representation of each of those JSON files. And this will allow me to then to, you know, kind of have a couple things here. Going to bring in things like the file metadata so I know exactly, you know, where that file lived originally. That's actually really important, Yseph, from a healthcare

18:22perspective. If I act on some information, you know, maybe from a machine learning model on a Medicare member here in the United States, I might have to in an audit prove like exactly where all of that came from. So like right from where you know the output from the machine learning model to the features to the tables all the way back to the file. So I could say this is exactly where that came from and

18:45this is why I acted on that and I might need to know that for 11 years believe it or not um based on some of the uh requirements that we have here in the United States. Just a few other things here like table properties that I like to set and so forth. And you know I'm building this statement here as you can see. So I always like to print those using a select which is really cool. And then there's that execute immediate which then behind the scenes created that late flow declarative pipeline for

19:08me just for that one streaming table and allows me to you know ingest that in really easily. >> And again that SQL you could you could put that in an actual late flow to curtive pipeline with the multi-file editor and it would it would function exactly the same which is pretty cool. This is what you get. uh I have this kind of opinionated way of doing my bronze tables. Here's my file metadata as a strruct which you know of course is

19:31great. It also is going to be used potentially for um a slowly changing dimension type one or type two later when I might when I want to create my silver tables. I might not have any other information about changes to an encounter or a clinical note or something like that other than this file is newer than a previous file where I received that you know that other information. So something like the file

19:54modification time you know when this file was created those sort of things that that is often going to be pretty important to carry through. So sometimes you need this file metadata even just to kind of make sense of it later downstream. I always like to do the ingest time and things and you know here's just that value the string value representation of this from a mandate perspective in the United States >> times uh people that are like well we

20:16just needed to get it in and usually when I you know show this to folks in the industry it's like oh we met the we met the mandate there it is and usually people laugh because sometimes that's as best as you could have done but now we get to use the power of data brick SQL late flow to car pipelines invariant so right here because this is streaming from a previous bronze table. It's just

20:38straight SQL. I don't have to actually, you know, parse that together in any way. Again, I can add my metadata um or my DDL, I'm sorry, for for this here in the table. And then, you know, temporarily, this is really the only option that is really required to use variant. You actually do not need the pipelines channel preview any longer. This was just I did this a year ago, I

21:00believe, and at the time that was true. But the variant data data type preview in Delta Lake is still uh true as of you know us talking right now. Uh you know I'd love to love to update that. Maybe you could update the group in a few weeks when when that is uh going to be GA. I know a lot of folks are interested in that. So uh in order to just take

21:22something like that string value you saw a second ago and you know I only printed one record because the data is so large I can't possibly show more in a notebook. It's all definitely there. But all you need to do is just say, you know, from stream here. Again, this is another streaming table. And then just try parse JSON. And there's actually two functions for this. There's parse JSON and try parse JSON.

21:46Uh with uh parse JSON, if you get a malformed JSON, uh it will actually fail uh the load because it's expecting, you know, well-formed JSONs, right? The fact that we have to have a term for malformed JSON just means it's, you know, that happens in the real world. Of course. So what try parse JSON will do is it'll if it is a malformed one, it'll just create a null. Now there's maybe

22:09one other reason for folks to potentially know about here is that as of today, the maximum size that you can store it for a variant data value in Delta Lake today, this might change in the future, but for today is approximately 16 megabytes. is actually 16 mi bytes, but you know that's essentially 16 megabytes. So that's

22:33that's per value, not for the whole column of course. Um so if you've got exceptionally large uh you know uh fire bundles for example, which some folks have ones that are much larger than the 16 maybe, then you still can use the trip parse JSON and start to extract additional data out of it like we might if we were using an API call or something like that. But you can't actually it'll show a null if you you

22:57know try to take something that's like really really really large. So that's another reason potentially to you know check things with the file metadata for the synthetic data that we're using uh here. That's not really an issue. So we can you know kind of kind of move on. So here's what it looks like after you're done. Right. So again same sort of metadata that I'm bringing in here for the file. And this is this is one of those files that we just ingested here.

23:21And uh let's see what this one looks like. This one has a meta object here. So I can actually now write SQL against any element of this and uh start to you know kind of take that apart which is sort of amazing. And so that's the meta object. Here's that entry array. This one looks like it's got nine elements in here of course with the here. So this one looks like it is a a message header

23:44and this one is a uh an encounter you know and you can see like the encounter has a subject. This would be the patient's uh you know full URL. So that's the foreign key to join to the patient and those sort of things. You can see the type there. Even if even writing SQL against this you can absolutely do it which is amazing. One thing that I like to show and maybe we'll show it in a couple minutes here

24:06is like the lat the latitude and longitude of the patient's address is often buried several layers deep in uh these types of resources. Let me see if uh this one maybe doesn't have it exactly, but um or maybe could be here in the extension. Let's see if uh well, you can see actually there's some good stuff here in the extension for what we

24:30call NCQA reporting. We often need to know things like, you know, race and ethnicity and those types of things. So, we can report out, you know, make sure that our our our care management programs or whatever don't have, you know, any bias or things like that in it. This could be really important data for HEISS or other types of things. And you know that's in the patient resource but it's you know in these extensions you know for example. So you know being

24:52able to extract this sort of information just using SQL is really cool. >> Let me uh kind of switch over now to um your switching for those who heard Matthew talking about latitude and longitude. Keep in mind that with data brick SQL you can also leverage geospatial data and you can maybe create a map where to locate those patient maybe look for the closest medical

25:16center in addition to as much you showed. So let's uh let's maybe take a look at that right should start here. So here's that same bronze table, right? And the way that you can actually extract data directly from one of these columns, right? Here's that fire metadata is first you, you know, select the column just like you normally would in SQL. And then you use a colon and the colon says like look, this is variant

25:40data. I want to be able to, you know, enter the variant column. Unfortunately, now I'm going to grab the entry array that's part of this. Uh that's where all the meat is. But you know that's the colon allows you to go into into this and then uh you know this is an array and this particular you know one that maybe I'm grabbing here uh happens to be where the patient resource was first. Um

26:02and I happen to know that that you know that's something we'll address in a second here. But uh so I'm going to grab the array position of the zero there. And then if we get into that then we can see that um uh that for example let's go into one of these the resource and then and then you start to use the dot notation like you might have already done

26:24previously with JSON where it's you know it's the resource then the ID and the thing about variant is that what's interesting about it is that it just assumes that everything should just be the variant data type just to make it so that it kind of you know passes or parses easily in order to do things like comparisons for in order to do things like make a map for example which is

26:46what we're going to do in a second you do need to cast it to the actual data type that it should be now we do have some other really cool functions in data brick SQL like schema of variant and schema of variant aggregation which I both love the difference is that with schema of variant a or aggregation you could actually create a universal strruct mapping for a particular variant

27:11column that you may be looking at that will basically look at all of the different grouping variables and say this is this is the kind of structure that will allow for any of the data that you've seen come in because as we know with JSON especially when we're talking about well those API developers they like to nulls they like to just you know miss them or whatever so if I only looked at one value I'd potentially miss other data points that maybe could be

27:33there uh so looking at lots of the data points and doing that that schema v schema of variant aggregation, you know, allows me to kind of get that that universal sort of schema. In this case, a lot of folks don't know this, but uh you can actually cast really nicely in SQL with just the colon colon. I love this. It's just so much cleaner, especially when you're using this kind of data. It's like look, I want to grab

27:56this uh this this resource ID here. When it's the patient resource, that's the patient ID. Going to cast that as a string. And then right here in the extension, the extension, that's a double. I'm going to cast that as the latitude and the longitude and uh and then you know what you end up with is essentially you know something that looks like this. Right? So I was able to get down to that really easily. So let me let me just kind of I'll just add a

28:19little wear statement here for the first uh SQL up here just so we can see what one of those looks like. Let's run that again. [snorts] And here is the just that resource type. Let me bring this down. So, you know, in reality, how did I, you know, how did I know that? Like, this is the patient resource. Here's the address. We're just

28:42going to assume that the first address in the array is the primary address. Really, no other way to know that. And then we can get into the extension and then the extension and then the extension. You can see that at some point that might have been string even if you got a schema from the HL7 website. And then here's the latitude and longitude, right? So, you know, first part of the array is latitude, second part of the array is longitude.

29:05Once you have that you you know you can essentially write that SQL you saw and you can make a map. So we'll go ahead and do this. We'll click on visualization and then I will find uh map markers. Um this actually uses leaflet behind the scenes. I used to be a a very proficient R programmer, Yseph, back uh when I was a data scientist as a customer and uh we

29:28used to make so many of these leaflet maps that at one time um uh people thought data science meant uh uh the cgraphy team. Um so I can just click save on there and now I can kind of just drill in here and you know have this map really easily. I don't have to code code that anymore. I love that. That's what Lisa few seconds ago. That's amazing. >> Yeah. Yeah. So, I love that. And you

29:51know, the these JSONs can get really complicated. So, I actually had a customer send me one. This is this is going to be a nested bundle. That fire bundle that you saw, those ones that you saw, they're already very complicated, right? So, like making it even more complicated. It just seems mean somehow. Uh what happened was is a a customer um that we work with in healthcare, they they messaged me and said uh or they

30:14messaged their account team who who came to me and said like look like we got a customer who's who's doing some stuff and like none of the software accelerators that they're using work. Can you help them? And I said sure. So without even looking at the file, I just threw it into my workflow that you saw a few minutes ago and it completely parsed everything. And then later I went to look at this using just the uh SQL

30:36editor here. So those fire JSONs, right, they're they're already pretty complicated. Um so that full that full workflow that you saw um that'll actually take all those different resources and turn them into silver tables eventually and uh you know we've got a webinar for that. I actually got to speak at Summit where we got to meet uh Ysef on on this and show that as well. So we'll pass on the Summit talk

30:59uh with a with a great partner of ours called Redox that we also use here. But sometimes people have things that are sort of interesting, right? So a different account team here at data bicks actually contacted me. They said their customer had a really interesting fire bundle that none of the other accelerators or other specialized software were able to use it with and they said they saw you know they saw your summit talk like do you think that you can help them with this file? So I

31:24said sure. So they they sent me the file you know it's it's fake data or synthetic data also. And I just I just threw it in that workflow and just had it go through and just you know have it ingest it into the bronze and all the silver tables. And I I didn't even look I didn't even open the file. I was just so so confident that the workflow would work perfectly. So then the morning of the meeting, I I went and you know started to write some SQL. So I figured I'd switch over to SQL editor just cuz sometimes we focus a lot on

31:47notebooks and SQL editor is really you know pretty awesome too with the new the new SQL editor. And you know I just I thought let me just go ahead and start to explore this. Why do they think it was so hard? Right? So it turned out that this particular JSON is actually a fire bundle that has nested fire bundles. So just take what I just showed you and then make it exponentially worse somehow. So you you can see that most of

32:11the specialized software very likely would not have um you very likely not have even considered that that was the case. And uh when I first saw this I didn't even think it could be possible either. But it turns out that the bundle itself is a resource type. Therefore you can have it be part of the entry array. So this one's actually kind of interesting. So, I'm going to go ahead and again in the SQL editor, as long as

32:34you separate stuff with semicolons, you can declare some variables and, you know, kind of chain some stuff together here. So, I'm just going to go ahead and do that. Make sure I've got the right catalog and schema here using my uh input parameters that you can see with the colons there. And uh let's go ahead and look at this particular one specifically. Uh knowing the file name, I was able to just kind of filter on the

32:56file metadata, of course. So, there's that particular file. And you know here is that here's the value of this one you know just a string and you know you can see it's it's quite complicated including the fact that it's actually a bundle within you know two bundles actually next to each other inside this bundle. So then we can look at it now with variant and of course because I

33:20brought it through my workflow and now we can see in the entry array here's that first one there and here is the second one and they are actually um this itself is another bundle. So we can look here and this is going to then have you know all these different things as well. And and then we've got another

33:44bundle here. And this just gets you know more and more complicated as you go down. So how do we want to extract the information out of here? And this actually is for something they want to do explicitly. I actually happen to really like I wanted to kind of prove that you can go from just the bronze all the way down to the thing that they were looking for. And this also is just kind of my exploratory analysis in a way too.

34:06So you know first is we can you can actually explode these using the lateral variant explode. And what's cool about this is that it will actually group um all of the things that have you have before the variant column such that it'll explode it long without having to uh you know kind of create any views. And I've actually found to be candid that uh SQL is a lot easier for this

34:28than pispark. Um and uh and even when I'm coding lakeflow declarative pipelines with a mix of the languages, I'll often switch to SQL, you know, from the in the from statements here. And you might also notice that I'm a big fan of the pipe syntax SQL which uh you know comes from my R days and using depier and things like that. So you know, so right here I'm going to do a width

34:50statement. So we're going to basically take that there and then we're just going to bury and explode. So I just want to show you what that looks like here. And this is how you start to break these things apart. So now I've got essentially the entry, the ID, the meta, the resource type here, right? And now I can start to, you know, see I start to, you know, write SQL a little bit easier here. I can start to filter that. So

35:12then we could pull out, you know, bundle level details, for example. So that's the same exact, you know, sort of width statements here or CTE. And then, you know, I can I can use this to pivot. So I can pivot on those keys. In the workflow that I show, we actually identify those keys and sort of have it be automatic. Um, so that you can, you know, have it be really generalized, which is cool. And so I'll show you this

35:35one now. So now we've pivoted and now I've got my columns for example for these things, right? So then I can continue to split this up more and more. So I'll click this off here and we'll do the next one. And you can see here, you know, you can just keep going and just get more and more interesting here. And we're going to pull out things like that, those full URLs. We're going to explode the entry,

35:58which is all the really great information. Pull out the full URLs, pull out the things that should be at that kind of the header there. And, you know, again, we're just kind of getting, you know, closer and closer and closer to being able to start to really evaluate, um, you know, what's going on here. So, this one has a full URL. These two don't. And that's because these have the resource types of bundles, right? So now I can see that this this is a bundle

36:21itself and so is the you know so is the other ones. So now now I've got to do a second pass basically you know on here um you know for example. So and then uh you know ultimately this is actually used for what we call electronic clinical quality measures believe it or not. So after I won't go through all the SQL, but you could see kind of keep

36:43exploding and keep making these CTE off of here and you know ultimately I can get down to actually showing you know what this report will look like. And one of the nice things of course of the new SQL editor is you can you know click back and forth on the results. And ultimately we needed all of that you know giant file was to get down to basically this where you have the actual

37:06information you you really care about like uh you know which codes were contained for uh which observations or whatever were used for the heat measure the quality measure that's in here you know what the evaluated resources are. So these can all join back. So you have like uh providence all the way going back and um you know the measure date, the measure string, the period start and ends, the measure report and then you know what the you

37:30know actual subject for that measure is. So, you know, we're able to get down to exactly this one row is what was needed. And then you could see that you could actually use additional things to kind of break this up to know, you know, for the different uh, you know, groups and things like that that that would be here for the population and the scores and all this other kind of good stuff here. Um, that's really the power here, you

37:52know, it's like like you can like literally take anything and start to break it apart. Um, you know, really easily. >> And then I think I'll just show maybe one more thing. And I think you also make make the file let's say more readable and accessible to do the aggregation rather than the first one with the I don't know how many nested columns have I've seen. So it make

38:15things easier and you did start breaking it down step by step like going from one from many files to like to like one file to many files and going down until you're able to explode to exploit the data you have inside those those formats these formats. >> Yeah, it's exactly right. I mean it's these files are these files can be wild.

38:37So um I want to show you know just one more thing. you know, some you're you're going to often create you're often going to be, you know, pulling things against um APIs and one of the things I love about Databricks Unity catalog especially is you can have an HTTPS external location you know so you get lineage and you know access control list for all those kind of things but then

38:59you can take that kind of stuff and you can wrap it in you know SQL udfs to make either you know uh table value functions or you know just regular userdefined functions which then are also in Unity catalog for use for lineage and everything. And anyone who connects to a a SQL warehouse, a serverless SQL warehouse can also take advantage of those functions that people create, including, you know, things that are downstream of Databricks like PowerBI

39:21or something like that, right? So, but you know, when you use these APIs, they often come back as JSON, right? So, how do you turn it into something that's like table usable? So, like in this case, if I just um do this right here, uh let's see if I can, you know, what I'm gonna do here is I'm just gonna create, you know, a post. I'm going to um basically feed some data into a uh a

39:43a SQL UDF that I've created called Redux request here. That's going to need um you know that's going to need the uh you know the method and what I want to grab. In this case I'm going to grab a diagnostic report. I want to search for a certain patient which hopefully is still there and and I'm going to get some sort of response. Right? So this I'm going to feed those things into this function

40:05here redux redux request that will that's going to give me JSON back but I can do this try parse JSON on that and then get variant data back which from there I can start to actually extract just like I would you know normally just like you saw a few minutes ago in order to get things like you know that fully URL the codes the encounters you

40:28know whatever it might be that's associated with this diagnostic report The example here, the use case here, Ysef, is like I'm maybe a care manager or utilization management, you know, person at a payer and I just need to see are there any like recent lab values and instead of going and asking a provider for a fax copy of a PDF, maybe I just

40:50want to get the PDF itself just directly from the EMR, assuming I've got that ability. This is what this particular thing is able to do, you know. So this diagnostic report when it comes back uh it comes back technically as a a base 64 uh string that's just part of the JSON of course cuz you're not going to get you know the full text or whatever it might be. So you know

41:13while it's coming back like it's you know without having to go and look at that JSON again and I could wrap this in a table valued function and use it you know just have it be in Unity catalog. Um I'm going to unbase 64 that string and call it diagnostic report. This is what comes back, right? So, I've got those full URLs that you I was talking about before. Um, you know, these different codings here. So, this is some sort of EEG,

41:36the diagnostic report ID that lives somewhere. If this was associated with an encounter, I'd have that that foreign key for that. You know, this is null for whatever reason. I've got some metadata. And then check out what I've got here. So, this is uh this is test data from a test database someplace. So, it's not going to be real at all. But you'll notice here that there's this text, you know, rich text format. Well, when it's just regular text, you know, now I've

41:59got the text, right? So I this is something I can now extract out of here, throw into a vector store index, you know, do some rag on it potentially, right? And summarize this for whoever might be looking at it, you know, using like AI query for example. But then I love this. This says application PDF here. This is the binary for making the PDF which we could then take this in a

42:22different workflow and just do a spark write binary to a volume and like literally have the PDF that then could be surfaced in a you know a Databricks app or you know whatever it might be um you know such that uh you know somebody actually can view the actual the actual PDF and see exactly what it is. You know, in the United States, I don't know it is like in the rest of the world, but

42:44like fax machines are HIPPA compliant. So, a lot of health care professionals are printing stuff, going to a fax machine, faxing it, and then they're hoping that another healthare professional picks it up in the office and your information is not being shared. I'd much rather have those those PDFs just be, you know, automatically sent in something like a HL7 transaction like these with fire. And then with data

43:08bricks because we're able to do this, you know, we can just render this PDF and now you've got that PDF on your side that you don't have to then take the facts and then scan it so you can get it into your application. This could just be part of your application now. So this is kind of the start of something you know just to kind of show the value. Uh but in production we would of course wrap these with those Unity catalog EDFs like I mentioned and you know do the other parts of the workflow you know for

43:30example that you saw. And then uh you know I I like to do other things with variant too. you know I this is a a Kafka stream that I I'm bringing in here from Red Panda actually you know when I bring it in of course this is of course using our new uh lakeflow declared pipelines forgot that the uh compute turns itself off it is serverless after

43:52all so that makes sense so the you know the bronze table is going to have just a a JSON payload as as the string as as most folks are familiar with when they get Kafka data out and you know with late flow declared pipelines. You can really easily perform slowly changing dimension type one and type two things just by declaring for example a

44:15streaming table or maybe a quarantine table based on some sort of expectation where maybe we want to uh drop that and then you can define a flow. So in this case I'm defining a flow here called uh profiles CDC uh you know so I'm bringing that JSON in, right? I want to be able to parse that. So I can apply my auto CDC and create my silver table. Notice right

44:38here what I'm doing is from the streaming you know change data feed that's coming in from the bronze table. I'm using trip parse JSON and then automatically taking all of those elements out so that I can then easily apply my changes you know using this one happens to be a slowly change dimension type one you know just directly to my silver table. So I use this variant in

45:02many places and you'll notice that here I don't actually store it as variant. So for folks that are you know maybe concerned that it's you know public preview this week while we're still talking uh you don't actually have to store your data as variant to take advantage of using it. In this case I'm never actually storing it in the delta table that way. I'm just using the power of variant in memory in my late flow

45:24declarative pipeline to take that JSON and and parse it and turn it into you know these uh tables that you see down >> that that's amazing. So basically you're leveraging the power of LDP plus of course I can see the SCD uh you can use it easily in like one line of code in Lakeflow declar pipeline rather than having to code it which can

45:47take very it can be time consuming. You leverage the power of variant to explode and make those nested super nested files and make good usage of them to make sure that your non I mean I can't call it semistructured because it's not even structured like transforming those

46:09semistructured data to structured one which can be compute which can be queried by analysts and make sure that they can aggregate the KPI and probably save uh save lives. And this is really the power of I think not only Matt used knowledge but also Databricks features and when you combine the human plus

46:32those awesome features you can only get awesome projects which can and this is really the beauty of using data transform it into actions and by actions here it's very critical saving lives and I think Matthew you're going to share your repo so people can deploy the same code and if they have any question. If you have any question, make sure to

46:55reach out to Matthew. He'll be super happy to help you. >> Yeah, thanks for having me. This is uh it's a real pleasure to get to speak with you. And to all of the folks that, you know, subscribe to NextGenLakehouse. I've been a a huge fan for many years. >> Thank you. Thank you. Thank you, Matthew. Thank you. Have a good rest.