Snowflake Native App Framework to build and deploy data apps that ingest and parse healthcare data formats (HL7) for analytics
The U.S. healthcare industry is a network of various entities such as health insurance firms, physician offices, hospital systems, medical device manufacturers, pharmaceuticals, community-based organizations, social care service organizations, and so on, collaborating to serve the health needs of a patient and deliver successful health outcomes. In the context of healthcare service providers or hospitals, data exchange between healthcare professionals is recognized as a catalyst to ensure care continuum, improve patient outcomes, reduce hospital length of stay (LOS) and hospitalization costs, and higher patient satisfaction.
To enable and promote data exchange between different healthcare entities, the Office of the National Coordinator for Health Information Technology (ONC) has published standards and formats. Health Level 7 (HL7) v2 is the most widely used data interchange standard that allows hospital information systems to communicate clinical and administrative data to one another. HL7 Consolidated Clinical Document Architecture (C-CDA) is another XML-based data format. Recently, ONC introduced the Fast Healthcare Interoperability Resources (FHIR) format and organizations are rapidly transitioning to it.
Healthcare organizations onboard data received in these formats not just to integrate with clinical systems (systems of records) for clinical decisioning but also to integrate into their data platform for analytics purposes, which provides valuable holistic insights about patients. Data in these formats often needs to be ingested, parsed to extract health data elements, and stored in a way that is conducive for analytics. There is a great opportunity to templatize this parsing and publish it as a “Health Data Adapter” (aka data application) that can then be installed and configured for a given healthcare organization’s need.
The Snowflake Native App Framework enables healthcare organizations or healthcare technology firms to build data applications leveraging native Snowflake technologies. In this blog, we will talk about how to create such data applications for healthcare organizations using Snowflake Native App Framework with HL7 v2 data format processing as an example.
Before we begin talking about specifics of how to create HL7 v2 data file format parsing as a native app, we’ll start with the generic building blocks for a Snowflake Native Application.
Steps to Build and Deploy a Snowflake Native App
Develop & Test
Organizationally, publishers who want to develop and publish Snowflake native apps will start with a development DB. The development DB will ideally contain at least two schemas; one to hold the application’s logic, SQL scripts and other resources, and another to house the unit tests for the application. The application logic will be core data app functionality that caters to a particular business use case.
Package & Version
When this application code is built and tested, it is moved to a code stage DB to prepare for conversion into an application package. The code stage DB can be thought of purely as a staging DB for code that will eventually be turned into an application package. This code stage should contain only the artifacts pertinent to the application itself. The application package is created from the code stage DB. Publishers will include version and patch information to the app pack definition. This model supports the rollout of application upgrades (and rollbacks) by the publisher to all consumers using the application. Publishers can test the application package by creating a local application from it directly.
Publish
Once the application package is prepared, the publisher is ready to offer it as a listing in Snowflake Marketplace (either publicly or privately) with optional support for monetization. Previous steps can all be accomplished through SQL scripts, however, the publication of a “listing” is a manual process within the Snowflake UI. Developers navigate to the “Publishers Studio” within the Snowsight UI to complete the listing process. At that point, your target audience would see your listing in the Marketplace.
Deploy
Depending on whether the application listing was defined as a public Marketplace listing or a private listing to a specific customer account, the consumer will find the offering in the Marketplace or apps sub menu in the Snowflake UI. Locate the application, name it, and select the warehouse you want to install the application. Once the installation is complete, the functionality (UDFs, stored procs, shared data sources, etc.) is accessible as any other first-class object within your Snowflake account.
Creating, Publishing and consuming a sample Snowflake Native App — HL7 v2 Health Data Adapter app
Let us take HL7 v2 data format parsing as an example and use the above building blocks to create, publish, and consume HL7 v2 file format parsing as a native app. We will take a Python library for parsing HL7 v2 files, wrap it as a user defined table function, and turn it into a native application we could publish for other Snowflake users. The published application is available for installation in the consumer account under “HL7ParserApp.”
Let’s assume we already have our development DB in place. We will build all of the artifacts out of the PUBLIC schema, however, you can use a custom schema if you prefer. We need to create a STAGE to house the artifacts that will be used by this application.
--add a stage for application artifacts
create or replace stage lib_stg;
--create a TEST schema & stage to house our test files
create or replace schema test
create or replace stage test_stg;We then add the artifacts to each respective stage. We have the Python third-party library that contains the parsing logic (hl7apy-1.3.4.tar.gz; please refer to https://pypi.org/project/hl7apy/ for additional details) and custom Python script that we have developed to act as the interface to this library (hl7parserUDTF.py). We will also upload a manifest and a setup script as required by the native app framework to create an application. The manifest contains information about the characteristics that define the app and the path to the setup script that will execute on the consumer side when the app is installed.
-add the artifacts to the lib stage
put file://./src/native_app/scripts/hl7_setup.sql @public.lib_stg
overwrite=true auto_compress=false;
put file://./src/python/hl7pyparserUDTF.py @public.lib_stg
overwrite=true auto_compress=false;
put file://./src/python/libs/hl7apy-1.3.4.tar.gz @public.lib_stg
overwrite=true auto_compress=false;
put file://./src/native_app/manifest.yml @public.lib_stg
overwrite=true auto_compress=false;
put file://./src/native_app/readme.md @public.lib_stg
overwrite=true auto_compress=false;
–add the test file to test schema
put file://./data/hl7_2–3_samples.txt @test.data_stg
overwrite=true auto_compress=false;Create a setup script that contains SQL commands to run when the app is installed. The contents of the example setup script is as follows
create application role hl7_app_public;
create or alter versioned schema public;
grant usage on schema public to application role hl7_app_public;
--create a HL7 message parsing udtf function
CREATE OR REPLACE FUNCTION public.hl7parserUDTF(p_stagefile string)
RETURNS TABLE (seqno int ,data_file varchar ,raw_msg varchar ,parsed_status boolean ,parsed_msg variant)
LANGUAGE python
runtime_version = 3.8
packages = ('snowflake-snowpark-python')
imports=('/hl7apy-1.3.4.tar.gz', '/hl7pyparserUDTF.py')
HANDLER = 'hl7pyparserUDTF.HL7FlParser'
COMMENT = 'Python based UDTF hl7v2 message parser';
GRANT USAGE ON FUNCTION public.hl7parserUDTF(string) TO application ROLE hl7_app_public;An application role is specifically designed for use with applications. These roles have privileges so the admin in the consumer account can assign ROLEs or USERs in their account.
Once the code is unit tested and ready to package as an application, it’s a best practice to recreate our dev DB as a staging DB (see the conceptual steps above) prior to creating the app package (stage_DB recreation steps not shown as they are mostly copies of the above). Once this is complete, we can create the app package from this DB.
Create application package hl7app_pkg;
--add the version information and resources to the app package
Alter application package hl7app_pkg add version v_0_0_0_1A using '@stage_db.public.lib_stg';
Alter application package hl7app_pkg set default release directive version = v_0_0_0_1A patch = 0;The app package is now complete.
Before we create a listing to share this application with other accounts, let’s test it locally by creating the application from the app package.
Create application hl7app from application package hl7app_pkg using version v_0_0_0_1A debug_mode=true;Running this will create a new application artifact in the UI and will show up as a DB (with a different icon to indicate it’s an app). The function defined as part of the startup script will have created the UDTF definition pointing to the resources loaded in our lib_stg.
The function is now retrievable by its fully qualified path name and we can run necessary tests to validate the parsing logic in the local DB.
Let’s now create a listing. In the Snowsight UI, navigate to Data -> Provider Studio then select Listings from the top of the screen on the right. You will need to be in the accountadmin role or one that has been granted CREATE DATA EXCHANGE LISTING permission to create the listing. Click the + Listing button to start the process. Name the listing and specify whether it will be shared in the Marketplace across the entire Snowflake customer base or with only a specific customer account. For this example, we will go with the direct share. The Marketplace option requires details about yourself, your organization, and about the app being offered. It also kicks off an automated security review of your offering before the listing is approved. (For more details on security reviews, please refer to Running the Automated Security Scan.) Choose whether this listing will be free or require payment for use. The paid version requires app providers to create a provider profile, so for our demo, we will choose free. Next, we specify the HL7_app_pkg we previously created and then specify the customer(s) we will offer this to by using their account identifier. Click on publish to complete the process. Your app now appears as live. You can add/remove customers from this listing or unpublish it to completely disable it.
The customer account you have shared the app with will now be able to find the offering in their Snowsight UI by navigating to the Apps menu item. From there, they will see the listing to add to their account.
Select the Get button to start the process.
Consumers can set the name of the application as they want it, and the app will appear as a database in their account.
The parser function is now callable from within the consumer account using its fully qualified path name hl7parser.public.hl7parserudtf(<filepath>)
With this native app, consumers can drop HL7 message files into cloud storage buckets and have the above native app function parse and load messages into Snowflake for further analytics, greatly simplifying the effort to build and deploy parsers for health data formats.
Key Benefits
Creating and deploying repeatable industry-specific business functionalities as Snowflake’s Native App comes with plenty of benefits, including the following:
- Native applications bring processing logic to data, making it efficient and simplifying the tech stack
- Logic is more repeatable and helps jump-start implementation of business use cases
- Native app developers can confidently deploy the logic of their proprietary/intellectual property without exposing details of the logic
- Sensitive data need not be shared across organizational entities
- Data app creators are empowered to monetize their IP using Snowflake Marketplace
Conclusion
As different healthcare organizations or healthcare technology firms implement the Snowflake Native App Framework to build and deploy data applications, we foresee the formation of a data app mesh that will open up data app economies of scale. The Health Data Adapter mentioned in this blog is one such example. Opportunities are endless!
Learn more about Snowflake’s Native App framework. At the time of this blog release, please note that Snowflake Native App functionality is in public preview and currently only available in AWS.
Special thanks to Ryan Templeton (https://www.linkedin.com/in/ryantempleton/) for co-authoring this blog with me and for his valuable contributions in building this demo.
Want to read more, here are some more useful resources:
- Blog: https://medium.com/snowflake/hl7-ingestion-and-processing-architectural-patterns-with-snowflake-3703b8c08ea4
- Blog: https://medium.com/snowflake/processing-hl7-v2-x-messages-using-snowflake-571441ed3b23
- Snowflake Quickstart: https://quickstarts.snowflake.com/guide/processing_hl7_v2_messages_with_snowflake/index.html?index=..%2F..index#0
