Adding a Network

network_mappings.py is the single source of truth for the network spine. ppo_payer_keywords in variables_XXX.sql controls hospital MRF matching and must be updated separately.

Step 1 — network_mappings.py

Path: airflow_dags/.../core_licensable_data_sub_dag/utils/network_mappings.py

Each network is registered as a tuple:

network_mappings.py — tuple format

(payer_id, [mrf_labels], network_name, [states])
#  --------  -----------  ------------  --------
#  string    list of      string hashed  list of state
#            exact        → network_id   abbreviations;
#            plan_name                   [None] = national
#            strings

Field reference:

Field	Type	Notes
payer_id	string	Must match payer_id in payer.sql
mrf_labels	list of strings	Exact plan_name strings from payer MRF. Case-sensitive. Use [] for Exchange/MA.
network_name	string	Human-readable; deterministically hashed → network_id. Renaming changes the network_id and invalidates historical ROIDs.
states	list	State abbreviations the network covers. [None] for national networks.

Variable → network type mapping:

List variable	Network type	SQL template
PPO_NETWORK_MAPPINGS	PPO	network_base.sql
HMO_NETWORK_MAPPINGS	HMO	network_hmo.sql
NARROW_NETWORK_MAPPINGS	Narrow	network_narrow.sql
EXCHANGE_NETWORK_MAPPINGS	Exchange	network_exchange.sql
MEDICARE_ADVANTAGE_NETWORK_MAPPINGS	MA	network_medicare_advantage.sql

Step 2 — variables_XXX.sql

variables_XXX.sql controls how hospital MRF data is mapped to networks. It has four relevant sections:

1. ppo_payer_keywords (critical)

A dict of payer_id → keyword list. Hospital MRF plan_name values are normalized before matching:

REPLACE(REPLACE(UPPER(plan_name), '_', ' '), ' ', '')

This produces all-caps strings with no spaces. Write keywords in that normalized form.

Keyword matching — how hospital rates get attributed

Raw plan_name: "Choice Plus" (from UHC hospital MRF file)

Normalization: UPPER("Choice Plus") → "CHOICE PLUS" → remove spaces → "CHOICEPLUS"

Keyword lookup: payer 643's keyword list contains "CHOICEPLUS" → match found

Result: The rate is attributed to payer 643 (UHC) with the matching network name

Always write keywords in normalized form: ALLCAPS, no spaces, no underscores.

2. ppo_product_network_mapping (critical)

A dict of payer_id → network_name. The network_name value here must match network_mappings.py exactly — same case, same spaces.

Pattern	Example	When to use
Single string	'643': 'UHC Choice Plus PPO'	Payer has one primary network
List of strings	'643': ['UHC Choice Plus PPO', 'UHC Navigate PPO']	Multiple networks for the same payer
State-keyed dict	'643': {'IL': 'UHC Choice Plus IL', 'TX': 'UHC Choice Plus TX'}	State-specific network names

3. ppo_exclusions / ppo_exclusion_exact_matches

Global exclusion keyword lists applied to all payers. Common exclusions include terms like MEDICARE, MEDICAID, OON (out-of-network), and similar. These filter out plan_name values that should never be mapped to any in-network rate.

4. ppo_payer_exclusions

A dict of (payer_id, state) → exclusion keywords. Use when a keyword should only be excluded for a specific payer-state combination — not globally.

warning

Silent failure mode. If keywords don't match the normalized plan_name, no error is raised and no rates are attributed. Always verify after adding a network by querying hospital_rates.plan_name for the payer and confirming your keywords match normalized values.

warning

Multi-network payers (e.g., Kaiser). Each network needs its own entry in network_mappings.py with a distinct network_name and distinct mrf_labels. Reusing mrf_labels across entries causes ambiguous attribution.

warning

Empty mrf_labels for Exchange/MA. Exchange and Medicare Advantage networks don't use payer MRF data. Set mrf_labels=[] for these entries in network_mappings.py.

warning

Payer_id must be present even with an empty list. If a payer_id is absent from ppo_payer_keywords, the plan bridge skips that payer entirely. An empty list [] means 'match via general/network keywords only.'

note

After running the sub-DAG, check tmp_ref_plan_bridge_{sub_version} for rows where network_name matches your new network. If the result is empty, your keywords didn't match any hospital MRF plan_name values.