Healthcare Price Transparency Demo with Snowflake Openflow

Process Healthcare Price Transparency Machine-Readable Files (MRFs) using Snowflake Openflow's Apache NiFi engine.

Overview

Under the Transparency in Coverage rule, U.S. healthcare payers must publish monthly MRF files detailing negotiated rates. This demo shows how to ingest these large, deeply-nested JSON files into Snowflake using Openflow.

What this demo does:

• Downloads MRF files from payer websites (e.g., Blue Cross Blue Shield)
• Parses nested JSON structures for negotiated rates and provider information
• Writes data to Snowflake tables using Snowpipe Streaming
• Provides sample analytical queries for price analysis

Prerequisites

• Snowflake Account: Capacity account or on-demand account with credit card (trial accounts cannot use Openflow)
• ACCOUNTADMIN Role: Required for initial setup
• Non-ACCOUNTADMIN Default Role: Users with ACCOUNTADMIN as default role cannot login to Openflow

Quick Start

Step 1: Setup Openflow Admin Role

-- Run sql/01_setup_openflow_admin.sql as ACCOUNTADMIN

Step 2: Create Deployment in Snowsight

1. Navigate to Ingestion → Openflow
2. Click Launch Openflow
3. Click Create a Deployment
4. Select Snowflake as deployment location
5. Wait 15-20 minutes for deployment creation

Step 3: Create Database and Tables

-- Run sql/02_setup_database_and_tables.sql

Step 4: Create Runtime Role

-- Run sql/03_setup_runtime_role.sql

Step 5: Create Network Access

-- Run sql/04_setup_network_access.sql

Step 6: Create Runtime in Openflow UI

1. In Openflow Control Plane, create a new Runtime
2. Size: Large (for optimal processing)
3. Min/Max Nodes: 1 (increase for larger files)
4. Snowflake Role: OPENFLOW_RUNTIME_ROLE_PRICE_TRANSPARENCY
5. External Access Integration: PRICE_TRANSPARENCY_INTEGRATION
6. Wait 5-10 minutes for runtime creation

Step 7: Import Flow Definitions

1. In the Openflow canvas, drag Process Group icon onto canvas
2. Click browse icon and select flows/in_network_processing.json
3. Double-click the process group
4. Right-click canvas → Enable All Controller Services
5. Right-click canvas → Start

Step 8: Process Provider References

1. Return to root canvas (right-click → Leave group)
2. Drag another Process Group and import flows/provider_reference_processing.json
3. Enable controller services and start

Step 9: Run Analytics

-- Run queries/sample_analytics.sql

Project Structure

price-transparency-dev/
├── README.md
├── sql/
│   ├── 01_setup_openflow_admin.sql   # Create OPENFLOW_ADMIN role
│   ├── 02_setup_database_and_tables.sql # Create database and tables
│   ├── 03_setup_runtime_role.sql     # Create runtime role with grants
│   ├── 04_setup_network_access.sql   # Network rules and EAI
│   └── 99_cleanup.sql                # Remove all demo resources
├── flows/
│   ├── in_network_processing.json    # NiFi flow for IN_NETWORK array
│   └── provider_reference_processing.json # NiFi flow for providers
└── queries/
    └── sample_analytics.sql          # Sample analytical queries

Tables Created

HEALTH_PLAN_RATES

Stores negotiated rates from the IN_NETWORK array:

Column	Description
FILE_URL	Source MRF file URL
NAME	Plan name
DESCRIPTION	Service description
NEGOTIATED_TYPE	Type of negotiation (fee schedule, etc.)
NEGOTIATED_RATE	The negotiated price
BILLING_CODE	CPT/HCPCS/DRG code
BILLING_CODE_TYPE	Type of billing code
PROVIDER_REFERENCES	Array of provider reference IDs

PROVIDERS

Stores provider information from PROVIDER_REFERENCE array:

Column	Description
PROVIDER_GROUP_ID	Reference ID (links to HEALTH_PLAN_RATES)
TIN_TYPE	Tax ID type (EIN, NPI)
TIN_VALUE	Tax identification number
NPI	Array of National Provider Identifiers

Processing Other Files

To process different MRF files:

1. Stop the process group
2. Double-click the InvokeHTTP processor
3. Change the HTTP URL to the new file URL
4. Start the process group

Sample MRF URLs

Blue Cross Blue Shield of Illinois (smaller, ~10 min):

https://app0004702110a5prdnc868.blob.core.windows.net/output/2025-07-18_Blue-Cross-and-Blue-Shield-of-Illinois_Blue-Options-or-Blue-Choice-Options_in-network-rates.json.gz

UnitedHealthcare of Washington (larger, ~9 hours with 5 nodes):

https://mrfstorageprod.blob.core.windows.net/public-mrf/2025-11-01/2025-11-01_UnitedHealthcare-of-Washington--Inc-_Insurer_Choice-EPO_561_in-network-rates.json.gz

Scaling

For larger files, increase runtime nodes:

1. Go to Runtime tab
2. Click three dots → Edit
3. Increase Min/Max nodes (5-10 recommended for files >10GB)
4. Click Apply

Cleanup

-- Run sql/99_cleanup.sql

Also suspend/delete runtime in Openflow UI before dropping roles.

Troubleshooting

"Invalid channel" error on PutSnowpipeStreaming

Normal during initial writes. As long as data appears in tables, ignore this error.

Cannot login to Openflow

Your default role is ACCOUNTADMIN. Change it:

ALTER USER YOUR_USERNAME SET DEFAULT_ROLE = OPENFLOW_ADMIN;

Network access errors

Ensure the EAI is attached to the runtime in the Openflow UI (not just created in SQL).

Automated Deployment with Cortex Code

This demo includes a skill file for fully automated deployment using Cortex Code (Snowflake's AI coding assistant).

Prerequisites for Automated Deployment

1. Account Type: NOT a trial account (Openflow requires capacity or on-demand with credit card)
2. Role: ACCOUNTADMIN or role with CREATE ROLE, CREATE COMPUTE POOL, CREATE OPENFLOW INTEGRATION privileges
3. Browser: Snowsight must be accessible for Openflow UI automation

How to Use

Open this project in Cortex Code and simply say:

deploy this demo

Or use any of these trigger phrases:

• "deploy price transparency"
• "install"
• "fresh deployment"
• "start deployment"

What the Skill Automates

The skill handles the entire deployment process:

1. SQL Setup: Creates roles, database, tables, network rules, and integrations
2. Openflow Deployment: Automates browser interactions to create deployment (15-20 min wait)
3. Runtime Creation: Creates and configures the NiFi runtime (5-10 min wait)
4. Flow Import: Uses NiFi REST API to import flow definitions programmatically
5. Controller Services: Enables all controller services via API
6. Start Processing: Starts all processors to begin data ingestion
7. Analytics: Runs sample queries once data is loaded

Teardown Options

Ask Cortex Code to teardown when done:

Level	Command	What it Does
Suspend	"suspend the runtime"	Pauses compute, preserves data
Full	"full teardown"	Deletes everything (runtime, deployment, SQL objects)

Important: Full teardown must delete Openflow components (runtime, deployment) BEFORE dropping SQL objects to avoid orphaned deployments.

References

• Blog Post: Processing Healthcare Price Transparency Files with Snowflake Openflow
• Openflow Documentation
• CMS Price Transparency