Skip to main content

Remits

clear_rates_remits analyzes Komodo remits data in the context of MRF stoploss provisions. It produces a table joining to TQ spines and remit header and line level tables. Use remits_lines_filtered_* to analyze potential stoploss and remits_lines_by_year_* to see them rolled up by remit year. It also left joins to the provisions table, so the values from any MRF notes that we have parsed for the payer/provider will be arrayed for reference.

current tables:

tq_dev.internal_dev_mmalhotra_provisions.remits_lines_filtered_2026_01 tq_dev.internal_dev_mmalhotra_provisions.remits_lines_by_year_2026_01

Source Tables​

TableDescription
tq_intermediate.external_komodo.remitsRaw Komodo remit lines
tq_intermediate.external_komodo.medical_headersMedical claim headers; used to link encounter_key → kh_plan_id for payer resolution
tq_production.spines.spines_provider_hospitals_additional_npisNPI-level spine: maps payee_npi → provider_id
tq_production.spines.spines_provider_hospitalsProvider-level spine: maps provider_id → provider_name and attributes
tq_dev.claims_benchmarks.payer_mapEncounter-based payer map: kh_plan_id → TQ payer info
tq_dev.internal_dev_lrobbins.remits_payer_mappingsCustom payer map: Komodo payer names → TQ payers (fallback when no encounter_key)
stoploss_provisions_tableMRF-extracted stoploss provisions

Pipeline​

remits_spines_{subversion}
        ↓
remits_stoploss_rollup_{subversion}
        ↓
remits_header_{subversion}
        ↓
remits_lines_{subversion}
        ↓
remits_lines_filtered_{subversion}
        ↓
remits_lines_by_year_{subversion}

Step 1: Spines (remits_spines_{subversion})​

Joins every raw remit line to the hospital and payer spines. Payer resolution uses the claims benchmarks map when an encounter_key is present, falling back to the custom Komodo payer name map.

Key outputs added:

  • provider_id, provider_name — from NPI → spine lookup
  • remit_year — derived from the first non-null date across service_from_date, service_to_date, claim_from_date, claim_to_date, bill_date
  • payer_channel — standardized channel (Commercial, Medicare Advantage, Medicaid, etc.)
  • tq_payer_id, tq_payer_name, tq_payer_type — TQ-canonical payer identifiers

Step 2: Stoploss Rollup (remits_stoploss_rollup_{subversion})​

Rolls up the stoploss_provisions_table (MRF-extracted provisions) to the provider_id × payer_id grain. Dollar thresholds below $50K are excluded from the average.

Key outputs:

  • avg_dollar_threshold — average MRF dollar threshold per provider-payer combo (thresholds > $50K only); used as the header filter threshold with a $150K fallback
  • Array fields (stoploss_types, dollar_thresholds, percentage_reimbursements, per_diem_reimbursements, etc.) — all distinct values across networks, carried for informational use downstream

Step 3: Header (remits_header_{subversion})​

Aggregates to the remit_id grain. Applies the stoploss dollar threshold to scope the dataset to high-acuity claims: a remit is kept only if total_billed_amount >= applied_threshold.

Threshold logic: uses avg_dollar_threshold from the rollup where available; falls back to $150,000 for provider-payer combos with no MRF data.

Key outputs added:

  • total_billed_amount, total_allowed_amount — claim-level totals
  • total_implant_billed/allowed — revenue code 027x subtotals
  • total_drug_billed/allowed — revenue code 0636x subtotals
  • applied_threshold — the threshold value actually used for the HAVING filter
  • All stoploss provision array fields from the rollup

Step 4: Lines (remits_lines_{subversion})​

Line-level table scoped to remit_ids present in the header (claims totals above the threshold). Pulls all columns from spines and attaches applied_threshold and stoploss provision arrays per line.

Step 5: Filtered Lines (remits_lines_filtered_{subversion})​

Applies quality gates and a stoploss signal filter to the lines table. No year filter is applied here — use remit_year to filter downstream.

Filters:

  • billed_amount > 0
  • allowed_amount > 0
  • provider_name IS NOT NULL (matched to provider spines)
  • tq_payer_name IS NOT NULL (matched to payer map)
  • allowed_amount / billed_amount BETWEEN 0.1 AND 0.9 — retains lines in the 10–90% payment ratio range

Step 6: Lines by Year (remits_lines_by_year_{subversion})​

Groups filtered lines by provider_id × tq_payer_id × revenue_code × remit_year × perc_allowed for year-over-year payment trend analysis. Calculates total_remit_lines that can be used as the denominator.

Key outputs:

  • perc_allowed — ROUND(allowed_amount / billed_amount, 2)
  • lines_paid_at_this_percentage — count of lines sharing the same rate bucket
  • total_remit_lines — total lines for that provider-payer-revenue code-year combination
  • All stoploss provision array fields joined from the rollup

Assumptions and Limitations​

  • $150K fallback. Using 150K as the standard threshold (around the median of thresholds we have) as the fallback threshold when there is not an MRF parsed threshold. If there is one in MRFs, it will use the average (network-level is lost) instead of 150K. However, the rollup also drops MRF thresholds ≤ $50K before averaging, so avg_dollar_threshold can be NULL even when dollar_thresholds is non-empty.

  • Unmatched hospital spines and payer maps are dropped. Step 5 drops those that don't have a match.

  • Payment ratio filter (10–90%) excludes edge cases. Step 5 drops lines reimbursed below 10% or above 90% are dropped.

  • Carveout subtotals cover only 027x and 0636x. Other carved-out codes (025x, 0621–0624, 0638) are not broken out in the header.

Output Schema Reference​

Stoploss provision fields (present on both tables, sourced from remits_stoploss_rollup):

ColumnTypeDescription
stoploss_typesARRAY(VARCHAR)Distinct stoploss types across networks (e.g., ['first dollar'])
stoploss_threshold_typesARRAY(VARCHAR)'dollar' or 'per diem'
stoploss_reimbursement_typesARRAY(VARCHAR)'percentage' or 'per diem'
dollar_thresholdsARRAY(DOUBLE)All distinct MRF dollar thresholds
percentage_reimbursementsARRAY(DOUBLE)All distinct MRF percentage reimbursement rates
per_diem_reimbursementsARRAY(DOUBLE)All distinct per diem reimbursement amounts
per_diem_thresholdsARRAY(DOUBLE)All distinct per diem thresholds
per_diem_capsARRAY(DOUBLE)All distinct per diem caps
stoploss_settingsARRAY(VARCHAR)Settings (e.g., ['Inpatient'])
stoploss_network_countBIGINTNumber of distinct networks with a stoploss provision for this provider-payer combo
On this page: