Provisions

Type	Description	Key Distinction	Source
Medical IP	Inpatient base rates, per diem amounts, and percentage-of-charges rates	Claim-level; applies to entire inpatient stay	MRF / Clear Rates
Medical OP	Outpatient surgical group (OPG) base rates and percentages	Procedure-group-level; organized by OPG code	MRF / Clear Rates
Drug Carveouts	High-cost pharmaceutical reimbursement rates	Line-level; triggered by revenue code 636	MRF / Core Rates
Implant Carveouts	High-cost implant rates	Line-level; triggered by revenue codes 275–278	MRF / Core Rates
Stoploss	Clauses that trigger a different reimbursement method when a claim exceeds a threshold	Claim-level; activates only above a dollar or LOS threshold	MRF notes + Claims remits

Architecture

All provision types are computed independently and unioned into a single final table, enriched with provider, payer, and network metadata from spines.

SOURCE SYSTEMS
├─ Hospital Rates (MRF)         glue.hospital_data.hospital_rates
├─ Core Rates                   tq_production.public_*.core_rates
├─ Claims Remits (Komodo)       tq_intermediate.external_komodo.remits
└─ Spines                       tq_production.spines.*

        ↓

PROVISION STREAMS
├─ Medical IP          →  provisions_medical_inpatient_{subversion}
├─ Medical OP          →  provisions_medical_outpatient_base_rates_{subversion}
├─ Drug Carveouts      →  provisions_drugs_combined_{subversion}
├─ Implant Carveouts   →  provisions_implants_combined_{subversion}
└─ Stoploss            →  provisions_stoploss_combined_{subversion}

        ↓

FINAL UNION
└─ provisions_final_{subversion}

Final Output Table

provisions_final_{subversion} — one row per provision instance.

provision_type values:

Value	Stream
IP Base Rate	Medical IP
IP Per Diem	Medical IP
IP Percentage	Medical IP
OP Surg Group	Medical OP
OP Percentage	Medical OP
High Cost Drugs	Drug Carveouts
High Cost Implants	Implant Carveouts
Stoploss Threshold	Stoploss
Stoploss Percentage	Stoploss
Stoploss Per Diem	Stoploss

Full schema

Column	Type	Description
payer_id	INT
payer_name	VARCHAR
provider_id	VARCHAR
provider_name	VARCHAR
network_id	BIGINT
network_name	VARCHAR
network_type	VARCHAR
setting	VARCHAR	'Inpatient' or 'Outpatient'
unique_id	VARCHAR	Composite key for this provision row
contract_id	VARCHAR	provider_id-payer_id-network_id
provision_type	VARCHAR	One of the 10 values above
provision_subtype	VARCHAR	OPG code for OP Surg Group; NULL otherwise
provision_value	DOUBLE	Numeric value of the provision
traceability_id	BIGINT	Source rate ID

DAG

The full pipeline runs as a single Airflow DAG: clear_rates_provisions (manually triggered).

Task groups run in this order:

1. medical_inpatient_group — inpatient base, per diem, and percentage provisions
2. medical_outpatient_group — outpatient OPG and percentage provisions
3. stoploss_group — 21-task stoploss pipeline (MRF extraction + remits + inference)
4. carveout_groups — drugs and implants carveout pipelines (parallel)
5. final_provisions — union of all streams into provisions_final

DAG parameters

Parameter	Description	Example
clear_rates_schema	Source CLD schema	tq_dev.internal_dev_csong_cld_v2_3_2
target_schema	Output schema	tq_dev.internal_dev_mmalhotra_provisions
clear_rates_subversion	Date suffix for CLD source tables	2025_10
core_rates_month	Month suffix for core rates source	2025_10
subversion	Version suffix for output tables	2025_12
remits_subversion	Version suffix for remits source	2026_01