Snowflake-Labs/sfguide-price-transparency-files-ingestion-framework-with-openflow

Snowflake-Labs/sfguide-price-transparency-files-ingestion-framework-with-openflow

Language: Python

License: Apache-2.0

Stars: 0

Forks: 0

Open issues: 0

Created: 2026-02-24T18:14:07Z

Pushed: 2026-03-30T17:42:15Z

Default branch: main

Fork: no

Archived: no

README:

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,…