SQL — Raw Data
SQL Files
Payer MRF
| File | Description |
|---|---|
raw/payer_mrf/build.sql | Matches payer MRF rates to the ROS; applies HCPCS/APR-DRG crosswalks |
raw/payer_mrf/build_pg.sql | Physician Group variant; applies HCPCS/APR-DRG crosswalks and MPFS RVU lookups |
raw/payer_mrf/prepare_pg.sql | Pre-processes PG payer MRF rates using MPFS RVU conversion factors |
raw/payer_mrf/union.sql | UNIONs HOSP and PG payer MRF chunks into tmp_raw_mrf_payer_rates |
Hospital MRF
| File | Description |
|---|---|
raw/plan_bridge/hosp_build_plan_bridge.sql | Builds HOSP plan bridge; maps hospital_rates.id → network_id via keyword matching |
raw/plan_bridge/hosp_prepare_base.sql | Prepares candidate plan bridge rows for HOSP networks |
raw/plan_bridge/hosp_prepare_plan_bridge_combined.sql | Combines PPO/HMO/RC HOSP plan bridge candidates with confidence scoring |
raw/plan_bridge/pg_build_plan_bridge.sql | Builds PG plan bridge (Professional billing_class routes here) |
raw/plan_bridge/rc_build_plan_bridge.sql | Builds Revenue Code plan bridge for RC-based hospital MRF matching |
raw/hospital_mrf/build.sql | JOINs hospital MRF rates to plan bridge and ROS |
raw/hospital_mrf/build_pg.sql | PG variant; applies HCPCS/APR-DRG crosswalks |
raw/hospital_mrf/union.sql | UNIONs HOSP and PG hospital MRF chunks into tmp_raw_mrf_hospital_rates |
Komodo & Gross Charges
| File | Description |
|---|---|
raw/komodo_allowables.sql | Builds tmp_raw_komodo_allowables from Komodo NPI-payer allowable rates |
raw/gross_charges/build_gc.sql | Builds tmp_raw_gross_charges; merges NPI, CBSA, and state-level gross charge estimates with ASP and cost report relativities |
raw/ndc_derived_hcpcs.sql | Derives HCPCS codes from NDC codes using Medispan WAC pricing |
raw/combine_raw.sql | JOINs all raw sources onto the ROS to produce tmp_int_combined_raw; applies cash price outlier filter |
Input Tables (params.py)
| Variable | Table | Used in |
|---|---|---|
MPFS_RVU | tq_production.reference_legacy.ref_cms_pfs_rvu | payer_mrf/prepare_pg.sql |
HCPCS_EAPG_CROSSWALK_TABLE | tq_intermediate.cld_utils.hcpcs_eapg_crosswalk | payer_mrf/build.sql, hospital_mrf/build_pg.sql |
APR_DRG_XWALK_TABLE | tq_production.reference_legacy.drg_crosswalk | payer_mrf/build.sql, hospital_mrf/build_pg.sql |
ALLOWABLES_NPI_PAYER | tq_intermediate.claims_benchmarks.claims_benchmarks_allowable_npi_payer | komodo_allowables.sql |
GROSS_CHARGES_NPI | tq_intermediate.cld_utils.claims_benchmarks_gross_charges_npi_2025_09 | gross_charges/build_gc.sql |
GROSS_CHARGES_CBSA | tq_intermediate.cld_utils.claims_benchmarks_gross_charges_cbsa_2025_09 | gross_charges/build_gc.sql |
GROSS_CHARGES_STATE | tq_intermediate.cld_utils.claims_benchmarks_gross_charges_state_2025_09 | gross_charges/build_gc.sql |
COST_REPORT_CHARGE_RELATIVITIES | tq_intermediate.cld_utils.cost_report_charge_relativities_2025_04_03 | gross_charges/build_gc.sql |
ASP_PRICING_TABLE | tq_production.reference_internal.asp_reference_pricing | gross_charges/build_gc.sql |
MEDISPAN_TABLE | tq_production.reference_legacy.ref_medispan_wac_rx | ndc_derived_hcpcs.sql |
CASH_OUTLIER_TABLE | tq_intermediate.cld_utils.outlier_bounds_cash_price_2025_11_07 | combine_raw.sql |