Making Data More Accessible for a Large Healthcare System
One of the largest not-for-profit healthcare systems in the country — with more than 50 hospitals and almost 50,000 employees — needed help implementing Snowflake to make their data more accessible and intelligible, and to create a single source of truth for their metrics. As a Snowflake Elite Services partner, Brooklyn Data Co. was the perfect agency to help them do that.
Challenge
To best serve their patients and create operational efficiencies, this healthcare network selected Snowflake to store consolidated Epic EHR data and provide a single source of truth for their organizational metrics. The client's data was highly normalized across thousands of tables, making it difficult to query without significant knowledge of the data system’s architecture. The specialized knowledge required to access and interpret the data caused stakeholders to rely on the centralized data team for their reporting. This overwhelmed the team with requests, so they knew that to harness their data’s full potential they had to make it more accessible and reduce the steps required to make it understandable.
Although many healthcare organizations rely on Epic’s suite of products for reporting on clinical and operational data, our client desired a higher level of flexibility necessary to generate custom metrics and reports from transformed data. Our proposed solution was to use dbt to transform and integrate raw data into easily understood models (e.g., Patient and Encounter) stored in Snowflake and accessed by users organization-wide. This would allow departments across the business to access and innovate on consistent, trustworthy data that we could continuously enhance by collaborating with them to create new features that provide better insights.
As a Snowflake Elite Services partner, we were the logical choice to help this client implement Snowflake. Additionally, the client turned to us for the dbt and data modeling expertise needed to transform their data so their analytics team could build essential dashboards serving key metrics for their decision-makers on customer behavior and operational performance.
The client’s challenges included:
- Establishing a single platform to store data for their organizational metrics
- Making their data more accessible and easier to query
- Implementing Snowflake and manipulating data from Epic to generate essential dashboards for tracking organizational success
Approach
This client needed help implementing Snowflake as a data warehouse, but after talking with them to better understand their data needs, we proposed building centralized data models in Snowflake with a medallion architecture using transformations performed in dbt to ensure their data could be leveraged as a strategic asset.
This required translating raw data with a highly normalized star schema design pattern into common data models that are easy to consume for end users. Source data was translated to the target database with schema and field-level data mapping including definitions for table joins, column renaming, data type transformation, calculations, and added ETL metadata fields. After we established the process for these data sources it was rolled out to additional sources including digital activity, marketing campaigns, customer surveys, and financial data.
Source system data was transformed into the common data models using dbt with testing to ensure data integrity. The data models were materialized in silver-grade tables in a Snowflake database with pipelines built in Azure DevOps to perform daily tests of development work and migrate features to production.
Our teams relied on our data expertise, Epic’s documentation to help understand how to navigate its data, and our Snowflake experience to determine how we could help this client best leverage their data in Snowflake while maintaining strict data privacy standards.
We worked with the client to understand the data required to generate their vital metrics. Next, we consolidated the client’s data into one place, developed a structured data engineering process, and built a metrics layer using dbt. From there, we developed an automated methodology to calculate metrics based on shared data models with storage centralized in Snowflake. Calculating these metrics and storing them in a central place has allowed this client to easily query and analyze the information they needed from Epic with more robust dashboards.
Our approach included:
- Implementing Snowflake and building centralized data models in it
- Learning the best ways to transform Epic data into simple data models
- Understanding the data required to generate the client’s vital metrics
- Consolidating the client’s data in one place and building a metrics layer using dbt
- Calculating metrics based on shared data models with storage centralized in Snowflake
Solution
With Snowflake’s shared data models powered by a dbt metrics layer, the client can leverage information from Epic in valuable dashboards. Their customer analytics team has used data from Snowflake to track customer journeys, initially focusing on campaigns to encourage people to have mammograms. With this approach proving effective their team performed similar, valuable analyses for other campaigns, creating more applications from the original work. Now that they’ve seen success with Snowflake and the systems we’ve created for them, this client is considering Brooklyn Data Co. for additional work in the upcoming fiscal year, which includes migrating additional metrics to Snowflake and increasing their digital footprint.
Results:
- A Snowflake data warehouse that provides a source of truth for their organizational metrics
- Data from Epic that’s easier to understand and query for users
- Valuable dashboards that allow stakeholders to make data-informed decisions
- Tracking and analyses that help the client conduct more successful marketing and communications