Version: 2.2

Quality Tests

Analyze Report Simulations

Using tq_intermediate.cld_utils.utilization_equal_code_utilization, simulate Analyze reports. The JSON below contains test configurations and the expected canonical_rate_percent_of_state_avg_medicare by bill type.

The actual and expected values should be within 10% of each other.

Click to expand

ANALYZE_REPRODUCE_CONFIGS = [
    {
        "org": "common_spirit_health",
        "provider_id": {
            "6174": {"Inpatient": 1.94901, "Outpatient": 3.08684},
            "6112": {"Inpatient": 1.81451, "Outpatient": 2.59592},
            "6111": {"Inpatient": 2.77823, "Outpatient": 5.08977},
            "6098": {"Inpatient": 2.37351, "Outpatient": 3.80751},
            "6187": {"Inpatient": 2.55923, "Outpatient": 4.19127},
            "6108": {"Inpatient": 2.56108, "Outpatient": 4.22434},
            "6156": {"Inpatient": 2.77823, "Outpatient": 5.05504},
            "6190": {"Inpatient": 2.56540, "Outpatient": 4.52576},
            "6200": {"Inpatient": 2.55334, "Outpatient": 4.10748}
        },
        "network_id": [
            -2162498661799343237
        ],
        "payer_id": [
            "7"
        ]
    },
    {
        "org": "mass_general_brigham",
        "provider_id": {
            "635": {"Inpatient": 1.86204, "Outpatient": 2.00788},
            "662": {"Inpatient": 1.9949, "Outpatient": 2.69132},
            "632": {"Inpatient": 1.93987, "Outpatient": 2.44948},
            "700": {"Inpatient": 1.68831, "Outpatient": 2.01227},
            "658": {"Inpatient": 2.39201, "Outpatient": 3.13266},
            "646": {"Inpatient": 2.44425, "Outpatient": 3.14717}
        },
        "network_id": [
            -1595830227154592941,
            707343854139029255,
            -2162498661799343237,
            -3776001016975145508,
            8361580493441765265,
            1242940224003211760,
            6352083177318679706
        ],
        "payer_id": [
            "392",
            "770",
            "317",
            "720",
            "7",
            "76",
            "643"
        ]
    },
    {
    "org": "providence",
    "provider_id": {
        "6112": {"Inpatient": 2.12712, "Outpatient": 3.03372},
        "6159": {"Inpatient": 1.72539, "Outpatient": 2.98499},
        "6190": {"Inpatient": 2.31158, "Outpatient": 3.06147},
        "6099": {"Inpatient": 3.135, "Outpatient": 2.672}, 
        "6139": {"Inpatient": 1.64293, "Outpatient": 3.54422},
        "6101": {"Inpatient": 2.76118, "Outpatient": 3.89086},
        "6135": {"Inpatient": 1.67793, "Outpatient": 2.72855},
        "6186": {"Inpatient": 2.4575, "Outpatient": 4.06134},
        "6172": {"Inpatient": 2.47732, "Outpatient": 3.61131},
        "6171": {"Inpatient": 2.04881, "Outpatient": 3.53791},
        "6203": {"Inpatient": 1.86151, "Outpatient": 2.6935}
    },
    "network_id": [
        -8393574886119207149
    ],
    "payer_id": [
        "61"
    ]
    },
    {
        "org": "intermountain",
        "provider_id": {
            "5754": {"Inpatient": 2.82384, "Outpatient": 5.05428},
            "5756": {"Inpatient": 2.41067, "Outpatient": 5.05384},
            "5712": {"Inpatient": 2.41067, "Outpatient": 5.05384},
            "5750": {"Inpatient": 1.38009, "Outpatient": 4.86936},
            "5720": {"Inpatient": 2.41067, "Outpatient": 5.05384},
            "5733": {"Inpatient": 2.37691, "Outpatient": 4.59098},
            "5759": {"Inpatient": 2.22233, "Outpatient": 3.65383},
            "5722": {"Inpatient": 1.39982, "Outpatient": 5.46988},
            "5782": {"Inpatient": 3.24916, "Outpatient": 3.05812},
            "5755": {"Inpatient": 3.2493, "Outpatient": 3.12444}
        },
        "network_id": [
            -7695283351826393948
        ],
        "payer_id": [
            "42"
        ]
    }
]

Example SQL:

SELECT 
    p.provider_id,
    p.provider_name,
    p.bill_type,
    AVG(canonical_rate_percent_of_state_avg_medicare) AS avg_canonical_rate_percent_of_state_avg_medicare
FROM tq_dev.internal_dev_csong_cld_{version}.prod_combined_abridged p
JOIN tq_intermediate.cld_utils.utilization_equal_code_utilization u
    ON p.billing_code_type = u.code_type
    AND p.billing_code = u.code
where provider_id IN ('6174', '6112', '6111', '6098', '6187', '6108', '6156', '6190', '6200')
AND network_id IN (-2162498661799343237)
AND payer_id IN ('7')
AND canonical_rate_score > 1
AND is_drug_code = false
GROUP BY
    1,2,3
ORDER BY
    1,2,3

Sanity Checks

Basic sanity checks to ensure nothing crazy happened:

Median non-outlier canonical rate across ALL ROIDs is between $1000 and $4000.
Median non-outlier percent of state average medicare rate across ALL ROIDs is between 100% and 200%
There are no negative canonical rates
There are no canonical rates greater than $20,000,000
Coverage of non-outlier rates is greater than 30% of ROIDs
Coverage of non-outlier rates by provider type is greater than the thresholds below:

PROVIDER_TYPE_COVERAGE_THRESHOLDS = {
    'ASC': 10.0,
    'Physician Group': 8.0,  
    'Laboratory': 5.0,
    'Childrens Hospital': 12.0,
    'Critical Access Hospital': 35.0,
    'Short Term Acute Care Hospital': 35.0,
    'Rehabilitation Hospital': 4.0,
    'Imaging Center': 3.0,
}

Coverage Checks

Coverage for key payers do not drop below these thresholds:

expected_coverage = {
    '522': {'pct_non_outlier_providers': 0.2983, 'pct_non_outlier_rates': 0.2131},  # Kaiser Permanente
    '388': {'pct_non_outlier_providers': 0.3336, 'pct_non_outlier_rates': 0.1689},  # BCBS Florida
    '76': {'pct_non_outlier_providers': 0.5522, 'pct_non_outlier_rates': 0.2842},   # Cigna
    '403': {'pct_non_outlier_providers': 0.5521, 'pct_non_outlier_rates': 0.2998},  # Blue Shield CA
    '643': {'pct_non_outlier_providers': 0.6354, 'pct_non_outlier_rates': 0.3327},  # United Healthcare
    '42': {'pct_non_outlier_providers': 0.2993, 'pct_non_outlier_rates': 0.1673},   # Anthem
    '7': {'pct_non_outlier_providers': 0.6024, 'pct_non_outlier_rates': 0.2794},    # Aetna
    '169': {'pct_non_outlier_providers': 0.6038, 'pct_non_outlier_rates': 0.3621},  # BCBS Texas
}

Rate Type Checks

For each canonical_rate_type, we take one random sample and use source rate to re-produce the rate.

For example:

for the rate type raw: payer_derived_rate, we would search core rates to make sure the canonical_rate exists
for the rate type transform: payer_gc_komodo_perc_to_dol, we would search hospital_data.hospital_rates for the percentage and re-produce the canonical_rate using the Komodo gross charge amount

Traceability Checks

Check Parameters and Formulas

For each canonical_rate_type, we take one random sample and derive the canonical_rate using traceability fields: canonical_method_params and canonical_method_formula.

Check Best Payer and Hospital Rates Exist

If canonical_rate_score > 1, best_payer_rate OR best_hospital_rate must exist, unless canonical_rate_source is imputation.
Each bill_type has 6 distinct best_payer_rate_score and best_hospital_rate_score values (0-5).

Consistency Checks

For each provider_type, provider_id, payer_id, network_id, bill_type, and is_drug_code combination, compute the average and median of the following fields:

canonical_rate
canonical_rate_percent_of_state_avg_medicare
canonical_gross_charge
medicare_rate
state_avg_medicare_rate
asp_payment_limit

Then compare the average and median of the new version to the old version.

Check that the median of the percent difference is within +/- 5% and that the 10th and 90th percentiles are within +/- 20%.

SQL Query

WITH
old AS (
    SELECT
        provider_type,
        provider_id,
        payer_id,
        network_type,
        network_id,
        bill_type,
        is_drug_code,
        avg(canonical_rate) as avg_canonical_rate,
        approx_percentile(canonical_rate, 0.5) as median_canonical_rate,
        avg(canonical_rate_percent_of_state_avg_medicare) as avg_canonical_rate_percent_of_state_avg_medicare,
        approx_percentile(canonical_rate_percent_of_state_avg_medicare, 0.5) as median_canonical_rate_percent_of_state_avg_medicare,
        avg(canonical_gross_charge) as avg_gross_charge,
        approx_percentile(canonical_gross_charge, 0.5) as median_gross_charge,
        avg(medicare_rate) as avg_medicare_rate,
        approx_percentile(medicare_rate, 0.5) as median_medicare_rate,
        avg(state_avg_medicare_rate) as avg_state_avg_medicare_rate,
        approx_percentile(state_avg_medicare_rate, 0.5) as median_state_avg_medicare_rate,
        avg(asp_payment_limit) as avg_asp_payment_limit,
        approx_percentile(asp_payment_limit, 0.5) as median_asp_payment_limit
    FROM {{ old_table }}
    WHERE canonical_rate_score > 1
    GROUP BY 1, 2, 3, 4, 5, 6, 7
),
new AS (
    SELECT
        provider_type,
        provider_id,
        payer_id,
        network_type,
        network_id,
        bill_type,
        is_drug_code,
        avg(canonical_rate) as avg_canonical_rate,
        approx_percentile(canonical_rate, 0.5) as median_canonical_rate,
        avg(canonical_rate_percent_of_state_avg_medicare) as avg_canonical_rate_percent_of_state_avg_medicare,
        approx_percentile(canonical_rate_percent_of_state_avg_medicare, 0.5) as median_canonical_rate_percent_of_state_avg_medicare,
        avg(canonical_gross_charge) as avg_gross_charge,
        approx_percentile(canonical_gross_charge, 0.5) as median_gross_charge,
        avg(medicare_rate) as avg_medicare_rate,
        approx_percentile(medicare_rate, 0.5) as median_medicare_rate,
        avg(state_avg_medicare_rate) as avg_state_avg_medicare_rate,
        approx_percentile(state_avg_medicare_rate, 0.5) as median_state_avg_medicare_rate,
        avg(asp_payment_limit) as avg_asp_payment_limit,
        approx_percentile(asp_payment_limit, 0.5) as median_asp_payment_limit
    FROM {{ new_table }}
    WHERE canonical_rate_score > 1
    GROUP BY 1, 2, 3, 4, 5, 6, 7
),
comparisons AS (
    SELECT
        provider_type,
        provider_id,
        payer_id,
        network_type,
        network_id,
        bill_type,
        is_drug_code,

        (v.avg_canonical_rate - q.avg_canonical_rate) / ((v.avg_canonical_rate + q.avg_canonical_rate) / 2) as diff_avg_canonical_rate_pct,
        (v.median_canonical_rate - q.median_canonical_rate) / ((v.median_canonical_rate + q.median_canonical_rate) / 2) as diff_median_canonical_rate_pct,
        (v.avg_gross_charge - q.avg_gross_charge) / ((v.avg_gross_charge + q.avg_gross_charge) / 2) as diff_avg_gross_charge_pct,
        (v.median_gross_charge - q.median_gross_charge) / ((v.median_gross_charge + q.median_gross_charge) / 2) as diff_median_gross_charge_pct,
        (v.median_medicare_rate - q.median_medicare_rate) / ((v.median_medicare_rate + q.median_medicare_rate) / 2) as diff_median_medicare_rate_pct,
        (v.avg_canonical_rate_percent_of_state_avg_medicare - q.avg_canonical_rate_percent_of_state_avg_medicare) / ((v.avg_canonical_rate_percent_of_state_avg_medicare + q.avg_canonical_rate_percent_of_state_avg_medicare) / 2) as diff_avg_canonical_rate_percent_of_state_avg_medicare_pct,
        (v.median_canonical_rate_percent_of_state_avg_medicare - q.median_canonical_rate_percent_of_state_avg_medicare) / ((v.median_canonical_rate_percent_of_state_avg_medicare + q.median_canonical_rate_percent_of_state_avg_medicare) / 2) as diff_median_canonical_rate_percent_of_state_avg_medicare_pct,
        (v.avg_medicare_rate - q.avg_medicare_rate) / ((v.avg_medicare_rate + q.avg_medicare_rate) / 2) as diff_avg_medicare_rate_pct,
        (v.avg_state_avg_medicare_rate - q.avg_state_avg_medicare_rate) / ((v.avg_state_avg_medicare_rate + q.avg_state_avg_medicare_rate) / 2) as diff_avg_state_avg_medicare_rate_pct,
        (v.median_state_avg_medicare_rate - q.median_state_avg_medicare_rate) / ((v.median_state_avg_medicare_rate + q.median_state_avg_medicare_rate) / 2) as diff_median_state_avg_medicare_rate_pct,
        (v.avg_asp_payment_limit - q.avg_asp_payment_limit) / ((v.avg_asp_payment_limit + q.avg_asp_payment_limit) / 2) as diff_avg_asp_payment_limit_pct,
        (v.median_asp_payment_limit - q.median_asp_payment_limit) / ((v.median_asp_payment_limit + q.median_asp_payment_limit) / 2) as diff_median_asp_payment_limit_pct

    FROM old q
    JOIN new v USING (provider_type, provider_id, payer_id, network_type, network_id, bill_type, is_drug_code)
)
SELECT
    APPROX_PERCENTILE(diff_avg_canonical_rate_pct, 0.5) as median_diff_avg_canonical_rate_pct,
    APPROX_PERCENTILE(diff_avg_canonical_rate_pct, 0.1) as p10_diff_avg_canonical_rate_pct,
    APPROX_PERCENTILE(diff_avg_canonical_rate_pct, 0.9) as p90_diff_avg_canonical_rate_pct,
    APPROX_PERCENTILE(diff_median_canonical_rate_pct, 0.5) as median_diff_median_canonical_rate_pct,
    APPROX_PERCENTILE(diff_median_canonical_rate_pct, 0.1) as p10_diff_median_canonical_rate_pct,
    APPROX_PERCENTILE(diff_median_canonical_rate_pct, 0.9) as p90_diff_median_canonical_rate_pct,
    APPROX_PERCENTILE(diff_median_gross_charge_pct, 0.5) as median_diff_median_gross_charge_pct,
    APPROX_PERCENTILE(diff_median_gross_charge_pct, 0.1) as p10_diff_median_gross_charge_pct,
    APPROX_PERCENTILE(diff_median_gross_charge_pct, 0.9) as p90_diff_median_gross_charge_pct
FROM comparisons
WHERE diff_avg_canonical_rate_pct IS NOT NULL

Analyze Report Simulations​

Sanity Checks​

Coverage Checks​

Rate Type Checks​

Traceability Checks​

Check Parameters and Formulas​

Check Best Payer and Hospital Rates Exist​

Consistency Checks​

On this page: