Payer MRF (core_rates)

Payer-reported negotiated rates from Transparency in Coverage MRFs. Standard rates go through one path; Physician Group (PG) rates take a separate 3-step path.

Standard Path

1

Source Table

tq_production.public_{yyyy_mm}.core_rates — one row per payer × network × provider × billing code combination as filed in the MRF.

→ core_rates

2

NPI → provider_id

UNNEST provider_spine.npi array, join to core_rates.provider_npi. One NPI can resolve to multiple providers.

→ —

3

Bill Type Logic

Determine Inpatient / Outpatient / Professional from billing_code_type + billing_class + drug/lab flags.

→ —

4

Normalization

Percentage rates < 1.0 multiplied by 100. Modifier counting computed for dedup priority.

→ —

5

Code Crosswalks

APR-DRG → MS-DRG (averaged across SOI), APC → HCPCS (OPPS Addendum B), EAPG → HCPCS.

→ tmp_ref_crosswalks

6

Output

6 payer chunks run in parallel, then unioned into tmp_raw_mrf_payer_rates.

→ tmp_raw_mrf_payer_rates

Physician Group Rates — 3-Step Path

PG rates use group NPI or EIN rather than individual provider NPI. They require a separate build path before joining the standard schema.

1

build_pg_rates_prebuild()

Resolves group EIN/NPI → Clear Rates provider_id. Normalizes modifiers, derives pos_designation, deduplicates. Also handles PG rates posted under hospital provider_ids (singly-affiliated PGs only).

→ tmp_raw_pg_prebuild

2

build_payer_pg_mrf()

Converts prebuild output to common schema. Joins to rate_object_space (Professional bill type only). Deduplicates by jaccard → provider_npi_count → negotiated_rate.

→ tmp_raw_mrf_payer_pg_rates

3

apply_custom_pg_logic()

Payer-specific business rules for PGs that report by specialty.

→ tmp_raw_mrf_payer_rates (merged)

Provider ID Resolution (in prebuild)

EIN-based join  (takes priority)
    COALESCE
NPI-based join

Records where both joins return NULL are dropped.

Modifier Normalization

Modifiers are standardized to three values: '26', 'TC', or '' (global). All other modifier values are filtered out entirely.

pos_designation (Facility vs Non-Facility)

service_code contains	pos_designation	Meaning
'11' only	Non-Facility	Office/clinic
'21' / '22' / '23'	Facility	Hospital/inpatient
Both '11' and '21'/'22'	Global	All settings
None/other	Global	Default
'11' and payer_id=643 (UHC)	Global	UHC special case

Deduplication

Dedup runs in two separate passes — one for Facility rates and one for Non-Facility rates — then the winners are unioned. This allows a single (provider, code, modifier) combination to carry both a Facility rate and a Non-Facility rate.

Supplemental Path: PG Rates Under Hospital Provider IDs

Some PGs post rates under a hospital's provider_id rather than their own. The prebuild identifies these as singly-affiliated PGs (HAVING COUNT(DISTINCT health_system_id) = 1) and remaps them.

Why singly-affiliated PGs only?

If a PG works with multiple health systems, remapping hospital rates to the PG would be ambiguous. Only 1:1 affiliations are safe to remap.

Multiple Rates per ROID — Dedup Priority

When a ROID has more than one candidate rate from the payer MRF, the pipeline selects one winner per ROID using this priority order:

Priority	Column	Direction	Reason
1	drug_billing_class	ASC	Prefer Outpatient over Professional for drug codes
2	lab_billing_class	DESC	Prefer Professional over Outpatient for lab codes
3	modifier_count	DESC	More specific modifiers preferred; modifier_count = -1 for '26', 1e9 for 'TC' or no modifier
4	negotiated_rate	DESC	Tiebreaker

Key Gotchas

NPI array join

Provider spine NPI array is UNNESTed, then joined on equality to core_rates.provider_npi. One NPI can resolve to multiple providers — expect fan-out if the same NPI is shared across provider records.

Capitation filtered out

negotiation_arrangement = 'capitation' records are filtered out. Capitated rates are per-member-per-month, not fee-for-service, and cannot be compared on a per-service basis.

Percentage normalization

Some payers report 1.25 meaning 125%. The pipeline multiplies by 100 when rate < 1.0 and negotiated_type = 'percentage'.

APR-DRG averaging

When crosswalking APR-DRG → MS-DRG, rates are AVERAGED across severity of illness levels. This introduces a known upward bias vs native MS-DRG rates.