Stage 4: Transformations

Raw rates arrive in different units and can't be compared directly. Transformations convert them to a common unit — dollars per service — while preserving all original values.

The Comparability Problem

Why raw rates can't be compared directly

Hospital A: "125% of gross charges" for code 99213 — meaningless without knowing the gross charge.

Hospital B: "\$2,500 per day" for MS-DRG 470 — can't compare to a case rate without length of stay.

Hospital C: "\$500 per 40mg vial" for drug J0135 — different dosage unit than what the payer's rate covers.

Payer: "\$200 fee schedule rate" — already in dollars, no transformation needed.

Transformations normalize the first three to dollars. The payer's $200 passes through unchanged.

Raw Rate Types and Transformations

Rate type	Example	What's needed	Transformation
% of billed charges	Hospital reports 130% for CPT 27447	Gross charge for that code at that provider	0.01 × pct × gross_charge (6 GC sources = 36 output columns)
Per diem	$2,500/day for MS-DRG 470	Geometric mean LOS (GLOS) from CMS	GLOS attached as reference; per diem carried as-is for scoring
Drug dosage	$500 per 40mg vial of J0135	Standard ASP dosage quantity	(rate / source_quantity) × asp_quantity (three methods)
Anesthesia	Rate per base unit or per case	Anesthesia base units; payer-specific convention	Payer-specific formula

Additive, Not Destructive

Transformations add new columns — they do not overwrite raw values. tmp_int_transformations contains:

• 36 pct-to-dollar columns (6 rate types × 6 gross charge sources)
• GLOS/ALOS reference columns
• Drug dosage standardized columns (3 methods × dollar + percentage variants × per contract_methodology)
• Anesthesia conversion columns per negotiated_type + a conversion_method string

NULL transformation column ≠ missing rate

A NULL in a transformation column usually means the raw rate is already usable as-is (e.g., a fee schedule dollar rate needs no pct-to-dollar conversion). It does not mean the ROID has no rate.

Before/After Example

Pct-to-dollar transformation

Input: payer_id=42, provider P123, code 27447, Outpatient. hospital_pct_of_total_billed_charges_pct = 130.

Gross charge lookup: Provider-level GC (gc_hosp) = \$18,000. State-level GC (gc_hosp_state) = \$17,500.

Transform (gc_hosp): 0.01 × 130 × 18,000 = \$23,400

Transform (gc_hosp_state): 0.01 × 130 × 17,500 = \$22,750

Two new columns are written. The original 130% and the $18,000 gross charge are preserved in their own columns.