Adding a Payer

Adding a payer requires 3 changes in payer.sql and network configuration.

Steps

1

Confirm MRF data

Does the payer have data in core_rates? Contact the Derivative Assets team if the payer is not labeled in the MRF registry.

→ core_rates

2

Edit payer.sql (3 changes)

Add to payer_ids list, add to the definitive_payer_id CASE block, and add to the blue_payer IN clause (if BCBS).

→ tmp_spines_payer

3

Add network mappings

Register the new payer in network_mappings.py and add keyword/network entries in variables_XXX.sql.

4

(Optional) payer_chunks.sql

Only needed if the payer requires a custom chunk assignment. Most payers use the default chunking logic.

→ tmp_ref_payer_chunks

3 Changes in payer.sql

Change 1 — payer_ids list

Add the new payer as a quoted string in the Jinja2 payer_ids set.

payer.sql — Change 1: payer_ids list

{% set payer_ids = ['169', '42', '101', '643', ..., '999'] %}
--                                                       ^^^
-- Add new payer_id as a quoted string

Change 2 — definitive_payer_id CASE block

Map the new payer to its Definitive Healthcare entity IDs. Use NULL if the payer has no Definitive Healthcare record.

payer.sql — Change 2: definitive_payer_id CASE block

CASE
  WHEN payer_payer_id = 643  THEN ARRAY[100001, 100002]  -- UHC
  WHEN payer_payer_id = 42   THEN ARRAY[200001]           -- Aetna
  ...
  WHEN payer_payer_id = 999  THEN ARRAY[123456]           -- New payer (or NULL if none)
  ELSE NULL
END AS definitive_payer_id

Change 3 — blue_payer IN clause

Only required if the payer is a BCBS plan. Add the payer_id to the IN clause that sets blue_payer = True.

payer.sql — Change 3: blue_payer (BCBS only)

CASE
  WHEN payer_payer_id IN (
      169, 170, 171, ..., 999   -- Add here ONLY if this is a BCBS plan
  ) THEN True
  ELSE False
END AS blue_payer

warning

Payer without networks does nothing. A payer added to payer.sql but without network entries will have no ROIDs — and therefore no rates in the output. Always follow up with network configuration.

warning

Definitive IDs can be NULL. Not all payers have Definitive Healthcare records. A NULL definitive_payer_id doesn't break anything — it just means the payer won't join to Definitive-sourced data.

note

Hospital-MRF-only payers: Some payers (e.g., Exchange plans) only appear in hospital MRF files, not in payer MRF files. They still require a payer.sql entry plus network configuration in network_mappings.py and variables_XXX.sql.