Stage 7: Rate Selection
By the time rate selection runs, each ROID has dozens of candidate rates across raw, transformed, and imputed columns — all scored 0–7. Rate selection picks the one winner per ROID and writes it to canonical_rate.
How All Sources Come Together
Rate selection reads from tmp_int_accuracy_brit — every rate column and its _validation_score are present on a single wide row per ROID. Rate selection does not join anything new. All the work of scoring happened upstream in the accuracy stages.
1
BRIT Accuracy Table
One row per ROID with every rate column and its corresponding _validation_score. Input to rate selection — no additional joins needed.
→ tmp_int_accuracy_brit
2
Build Rate Arrays
Collect all non-NULL rate columns into parallel arrays: values, scores, sources, types, and methodologies. Array position is consistent across all arrays — position i in rate_array corresponds to position i in rate_score_array.
3
Select Canonical Rate
array_max(rate_score_array) finds the highest score. ARRAY_POSITION extracts the index. All canonical_* fields are populated from that index across the parallel arrays.
→ tmp_int_combined_no_whisp
4
Add Whispers
Optional enrichment step. Dimensional summary statistics are computed and joined in, adding context without affecting canonical rate selection.
→ tmp_int_combined
Canonical Rate Score Translation
| Canonical Score | Internal Score | Meaning |
|---|---|---|
| 5 | 7.x | Payer + hospital MRF independently agreed within ±20% |
| 4 | 6.x | Single MRF source, within outlier bounds |
| 3 | 4.x or 5.x | Within bounds, no counterparty validation |
| 2 | 2.x or 3.x | Imputed estimate |
| 1 | 1.x | Outlier — outside all acceptable bounds |
| 0 | 0 | No rate — ROID has no usable data |
Output Columns
| Column | Meaning | Example |
|---|---|---|
canonical_rate | The selected dollar amount | 18500.00 |
canonical_rate_score | 1–5 confidence | 5 |
canonical_rate_source | Which source won | "payer_hospital" |
canonical_rate_type | Exact rate column with prefix | "raw: payer_negotiated_rate" |
canonical_contract_methodology | Hospital's contract methodology | "Fee Schedule" |
canonical_rate_class | "Raw", "Transform", or "Impute" | "Raw" |
canonical_gross_charge_type | GC source for enhanced rates | "mrf_gross_charge_cbsa_median" |
Common Filters
| Filter | SQL | Use Case |
|---|---|---|
| Non-outlier rates | canonical_rate_score > 1 | Primary analysis |
| Any rate exists | canonical_rate_score > 0 | Coverage counts |
| No rate | canonical_rate_score = 0 | Gap analysis |
| Validated only | canonical_rate_score = 5 | Highest confidence |
| Raw rates only | canonical_rate_class = 'Raw' | Exclude imputations |
QA tip
Always filter on canonical_rate_score > 1 to exclude outliers, or canonical_rate_score > 0 to exclude empty ROIDs. A canonical_rate_score = 1 row has a rate value, but it failed outlier bounds and should not be used for analysis without careful review.