PET QA and Pricing

Overview

The patient_estimate_tool_qa DAG runs a series of validation checks on custom SSP pricing data before generating the final pricing output.

DAG Configuration

Parameter	Default	Description
`trino_conn_id`	`trino_default`	Trino connection to use
`org_id`	`649`	Organization ID for the PET customer
`schema`	`tq_dev.internal_dev_mmalhotra_pet`	Schema for output tables
`customer_name`	`cameron`	Customer name
`excluded_ssps`	`["CO001", "CO002", "CO003", "CO004", "BE001", "RA003"]`	SSPs excluded from processing
`charge_multiplier`	`0.62`	Charge multiplier

DAG Workflow

start_dag
    → get_max_versions
    → qa_validation_checks (parallel)
    → create_custom_ssp_pricing
    → post_creation_checks (parallel)
    → end_dag

Source Tables

Table
`tq_dev.custom_ssp_pricing.custom_subpackage_line_item`
`tq_dev.custom_ssp_pricing.line_item_price`
`tq_dev.standard_service_packages.ssps`
`tq_production.hospital_data.hospital_rates`
`tq_dev.custom_ssp_pricing.pet_org_to_provider`
`tq_production.consumer_prices.pricing_service_ssp`
`tq_production.consumer_prices.pricing_service_rates`

Pre-Processing Validation Tests

These tests run before the main pricing table is created.

Test 1: Error in Notes Field

Checks for records containing "error" in the notes field of line_item_price table.

Action if failed: Review the notes field for error messages and flag to @Evelyn Ting.

SQL

-- Test 1: Validation test to check for errors in the notes field of line_item_price table
SELECT
    subpackage_id,
    subpackage_line_item_id,
    notes,
    ssp_version_date
FROM {{ line_item_price }}
WHERE org_id = {{ org_id }}
AND cardinality(
    filter(notes, x -> LOWER(x) LIKE '%error%')
) > 0

Test 2: Hospital Rates Plans Missing

Checks for payer/plan builds in hospital_rates that are missing from line_item_price.

Action if failed: Review and flag to @Gillian Halpin.

SQL

-- Test 2: Check for payer/plan builds in hospital_rates that are missing from line_item_price
WITH provider_ids_cte AS (
    SELECT provider_id
    FROM {{ pet_org_to_provider }}
    WHERE org_id = {{ org_id }}
),

hospital_rates_plans AS (
    SELECT DISTINCT
        raw_payer_name,
        plan_name
    FROM {{ hospital_rates }}
    WHERE provider_id in (SELECT provider_id FROM provider_ids_cte)
),

line_item_price_plans AS (
    SELECT DISTINCT
        UPPER(payer_name) AS payer_name,
        UPPER(plan_group_name) AS plan_group_name
    FROM {{ line_item_price }}
    WHERE org_id = {{ org_id }}
)
SELECT
    hr.raw_payer_name,
    hr.plan_name
FROM hospital_rates_plans hr
LEFT JOIN line_item_price_plans lip
    ON hr.raw_payer_name = lip.payer_name
    AND hr.plan_name = lip.plan_group_name
WHERE lip.payer_name IS NULL

Test 3: CSLI Subpackage Line Items Not Priced

Verifies all subpackage_line_item_id values from custom_subpackage_line_item exist in line_item_price.

Action if failed: Review and flag to @Evelyn Ting.

SQL

-- Test 3: Check that all subpackage_line_items were priced (exist in line_item_price)
WITH line_items_to_check AS (
    SELECT
        csli.id,
        csli.subpackage_id,
        csli.ssp_id,
        csli.provider_id,
        csli.fee_type,
        csli.billing_code,
        csli.org_id,
        csli.version_date
    FROM {{ custom_subpackage_line_item }} csli
    WHERE csli.org_id = {{ org_id }}
        AND csli.version_date = DATE '{{ max_version_csli }}'
        AND csli.fee_type IN ('Facility Fee', 'Optional Fee')
        AND csli.billing_code IS NOT NULL
)
SELECT
    li.id,
    li.subpackage_id,
    li.ssp_id,
    li.provider_id,
    li.fee_type,
    li.billing_code,
    li.org_id,
    li.version_date
FROM line_items_to_check li
LEFT JOIN {{ line_item_price }} lip
    ON li.id = lip.subpackage_line_item_id
    AND li.org_id = lip.org_id
WHERE lip.subpackage_line_item_id IS NULL

Test 4: CSLI Subpackages Not Priced

Verifies all subpackage_id values from custom_subpackage_line_item exist in line_item_price.

Action if failed: Review and flag to @Evelyn Ting.

SQL

-- Test 4: Check that all subpackages from custom_subpackage_line_item are priced in line_item_price
WITH expected_subpackages AS (
    SELECT
        subpackage_id,
        ssp_id,
        provider_id,
        fee_type,
        COUNT(*) AS line_item_count
    FROM {{ custom_subpackage_line_item }} csli
    WHERE csli.org_id = {{ org_id }}
        AND csli.version_date = DATE '{{ max_version_csli }}'
        AND csli.fee_type IN ('Facility Fee', 'Optional Fee')
    GROUP BY subpackage_id, ssp_id, provider_id, fee_type
),
priced_subpackage_ids AS (
    SELECT DISTINCT subpackage_id
    FROM {{ line_item_price }}
    WHERE org_id = {{ org_id }}
)
SELECT
    expected.subpackage_id,
    expected.ssp_id,
    expected.provider_id,
    expected.fee_type,
    expected.line_item_count
FROM expected_subpackages expected
LEFT JOIN priced_subpackage_ids priced
    ON expected.subpackage_id = priced.subpackage_id
WHERE priced.subpackage_id IS NULL

Test 5: Duplicate Active Contracts

Checks for duplicate active contracts (same payer/plan/provider combo with multiple builds).

Action if failed: Review and flag to @Evelyn Ting for script issues, @Gillian Halpin for Clear Contracts build issues.

SQL

-- Test 5: Check for duplicate active contracts (same payer/plan/provider combo with multiple builds)
WITH distinct_combos AS (
    SELECT DISTINCT
        build_id,
        build_name,
        provider_id,
        payer_name,
        plan_group_name
    FROM {{ line_item_price }} lip
    WHERE lip.org_id = {{ org_id }}
)
SELECT
    provider_id,
    payer_name,
    plan_group_name,
    COUNT(*) AS num_builds,
    ARRAY_AGG(build_id) AS build_ids,
    ARRAY_AGG(build_name) AS build_names
FROM distinct_combos
GROUP BY provider_id, payer_name, plan_group_name
HAVING COUNT(*) > 1

Test 6: No Match in Hospital Rates

Checks for line items with no match in hospital_rates.

Action if failed: This is informational - review but may not be blocking.

SQL

-- Test 6: Check for line items with no match in hospital_rates
SELECT
    lip.subpackage_line_item_id,
    lip.subpackage_id,
    lip.provider_id,
    lip.billing_code,
    lip.revenue_code,
    lip.payer_name,
    lip.plan_group_name,
    lip.build_name,
    lip.ssp_version_date
FROM {{ line_item_price }} lip
LEFT JOIN {{ hospital_rates }} hr
    ON lip.provider_id = hr.provider_id
    AND lip.billing_code = hr.billing_code
    AND lip.revenue_code = hr.revenue_code
    AND UPPER(lip.payer_name) = hr.raw_payer_name
    AND UPPER(lip.plan_group_name) = hr.plan_name
WHERE lip.org_id = {{ org_id }}
    AND lip.billing_code IS NOT NULL
    AND hr.id IS NULL

Test 7: Multiple Matches in Hospital Rates

Checks for line items with more than one match in hospital_rates.

Action if failed: This is informational - review but may not be blocking.

SQL

-- Test 7: Check for line items with more than one match in hospital_rates
WITH total_line_items AS (
    SELECT COUNT(DISTINCT subpackage_line_item_id) AS total_count
    FROM {{ line_item_price }}
    WHERE org_id = {{ org_id }}
),
multi_match AS (
    SELECT
        lip.subpackage_line_item_id,
        lip.provider_id,
        lip.billing_code,
        lip.revenue_code,
        lip.payer_name,
        lip.plan_group_name,
        COUNT(*) AS match_count
    FROM {{ line_item_price }} lip
    JOIN {{ hospital_rates }} hr
        ON lip.provider_id = hr.provider_id
        AND lip.billing_code = hr.billing_code
        AND lip.revenue_code = hr.revenue_code
        AND UPPER(lip.payer_name) = hr.raw_payer_name
        AND UPPER(lip.plan_group_name) = hr.plan_name
    WHERE lip.org_id = {{ org_id }}
        AND lip.billing_code IS NOT NULL
    GROUP BY
        lip.subpackage_line_item_id,
        lip.provider_id,
        lip.billing_code,
        lip.revenue_code,
        lip.payer_name,
        lip.plan_group_name
    HAVING COUNT(*) > 1
)
SELECT
    mm.subpackage_line_item_id,
    mm.provider_id,
    mm.billing_code,
    mm.revenue_code,
    mm.payer_name,
    mm.plan_group_name,
    mm.match_count,
    ROUND(100.0 * COUNT(*) OVER () / t.total_count, 2) AS pct_of_total
FROM multi_match mm
CROSS JOIN total_line_items t

Test 11: Pricing Flags

Flags claims with pricing anomalies:

ZERO_PRICE: $0 priced_rate
UNDER_5_PCT: priced_rate < 5% of charge_amount
UNDER_10_PCT: priced_rate 5-10% of charge_amount
OVER_200_PCT: priced_rate > 200% of charge_amount

Action if failed: Review flagged items for accuracy.

SQL

-- Test 11: Flag claims with $0 pricing or extreme pricing ratios (priced_rate vs charge_amount)
-- Flags: $0 priced_rate, <5% of charge, <10% of charge, >200% of charge
SELECT
    subpackage_id,
    subpackage_line_item_id,
    priced_rate,
    charge_amount,
    CASE
        WHEN charge_amount > 0 THEN ROUND((priced_rate / charge_amount) * 100, 2)
        ELSE NULL
    END AS pct_of_charge,
    CASE
        WHEN priced_rate = 0 THEN 'ZERO_PRICE'
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) < 0.05 THEN 'UNDER_5_PCT'
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) < 0.10 THEN 'UNDER_10_PCT'
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) > 2.00 THEN 'OVER_200_PCT'
        ELSE NULL
    END AS pricing_flag,
    provider_id,
    payer_name,
    plan_group_name,
    build_name,
    billing_code,
    revenue_code
FROM {{ line_item_price }}
WHERE org_id = {{ org_id }}
    AND (
        priced_rate = 0
        OR (charge_amount > 0 AND (priced_rate / charge_amount) < 0.10)
        OR (charge_amount > 0 AND (priced_rate / charge_amount) > 2.00)
    )
ORDER BY
    CASE
        WHEN priced_rate = 0 THEN 1
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) < 0.05 THEN 2
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) < 0.10 THEN 3
        WHEN charge_amount > 0 AND (priced_rate / charge_amount) > 2.00 THEN 4
    END,
    subpackage_id,
    subpackage_line_item_id

Post-Processing Validation Tests

These tests run after the final pricing table is created.

Test 8: Zero Prices in Final Table

Checks that no subpackages in the final output table have zero price.

Action if failed: Investigate why pricing resulted in $0.

SQL

-- Test 8: Check that no subpackages in the final table have zero price
SELECT
    ssp_id,
    ssp_name,
    from_subpackage_id,
    provider_id,
    contract_build_payer_name,
    contract_build_plan_group,
    subpackage_price,
    payer_id,
    payer_name,
    plan_alias
FROM {{ schema }}.pricing_service_custom_ssp_{{ customer_name }}
WHERE subpackage_price = 0

Test 9: Benchmark vs Production SSPs

Checks for SSPs with >25% price variation from production pricing_service_ssp.

Action if failed: Review large price deviations to ensure they are expected.

SQL

-- Test 9: Check for SSPs with avg >25% price variation from prod
WITH output_prices AS (
    SELECT
        ssp_id,
        ssp_name,
        AVG(subpackage_price) AS avg_output_price,
        MIN(subpackage_price) AS min_output_price,
        MAX(subpackage_price) AS max_output_price,
        COUNT(*) AS output_row_count
    FROM {{ schema }}.pricing_service_custom_ssp_{{ customer_name }}
    GROUP BY ssp_id, ssp_name
),
production_prices AS (
    SELECT
        ssp_id,
        AVG(facility_fee) AS avg_production_price,
        MIN(facility_fee) AS min_production_price,
        MAX(facility_fee) AS max_production_price,
        COUNT(*) AS production_row_count
    FROM {{ pricing_service_ssp }}
    GROUP BY ssp_id
),
price_comparison AS (
    SELECT
        o.ssp_id,
        o.ssp_name,
        o.avg_output_price,
        o.min_output_price,
        o.max_output_price,
        o.output_row_count,
        p.avg_production_price,
        p.min_production_price,
        p.max_production_price,
        p.production_row_count,
        ABS(o.avg_output_price - p.avg_production_price) / NULLIF(p.avg_production_price, 0) * 100 AS price_variation_pct
    FROM output_prices o
    JOIN production_prices p
        ON o.ssp_id = p.ssp_id
)
SELECT
    ssp_id,
    ssp_name,
    CAST(avg_output_price AS DECIMAL(18, 3)) AS avg_output_price,
    CAST(min_output_price AS DECIMAL(18, 3)) AS min_output_price,
    CAST(max_output_price AS DECIMAL(18, 3)) AS max_output_price,
    output_row_count,
    CAST(avg_production_price AS DECIMAL(18, 3)) AS avg_production_price,
    CAST(min_production_price AS DECIMAL(18, 3)) AS min_production_price,
    CAST(max_production_price AS DECIMAL(18, 3)) AS max_production_price,
    production_row_count,
    CAST(price_variation_pct AS DECIMAL(10, 2)) AS price_variation_pct
FROM price_comparison
WHERE price_variation_pct > 25
ORDER BY price_variation_pct DESC

Test 10: Benchmark vs Cash Prices

Checks for SSPs with >25% price variation from pricing_service_rates (discounted_cash_price).

Action if failed: Review large cash price deviations.

SQL

-- Test 10: Check for SSPs with avg >25% price variation compared to prod discounted_cash_prices
WITH output_prices AS (
    SELECT
        ssp_id,
        ssp_name,
        AVG(subpackage_price) AS avg_output_price,
        MIN(subpackage_price) AS min_output_price,
        MAX(subpackage_price) AS max_output_price,
        COUNT(*) AS output_row_count
    FROM {{ schema }}.pricing_service_custom_ssp_{{ customer_name }}
    GROUP BY ssp_id, ssp_name
),
production_prices AS (
    SELECT
        ssp_id,
        AVG(discounted_cash_rate) AS avg_production_price,
        MIN(discounted_cash_rate) AS min_production_price,
        MAX(discounted_cash_rate) AS max_production_price,
        COUNT(*) AS production_row_count
    FROM {{ pricing_service_rates }}
    WHERE fee_type != 'professional' --hard-coding professional only for now
    GROUP BY ssp_id
),
price_comparison AS (
    SELECT
        o.ssp_id,
        o.ssp_name,
        o.avg_output_price,
        o.min_output_price,
        o.max_output_price,
        o.output_row_count,
        p.avg_production_price,
        p.min_production_price,
        p.max_production_price,
        p.production_row_count,
        ABS(o.avg_output_price - p.avg_production_price) / NULLIF(p.avg_production_price, 0) * 100 AS price_variation_pct
    FROM output_prices o
    JOIN production_prices p
        ON o.ssp_id = p.ssp_id
)
SELECT
    ssp_id,
    ssp_name,
    CAST(avg_output_price AS DECIMAL(18, 3)) AS avg_output_price,
    CAST(min_output_price AS DECIMAL(18, 3)) AS min_output_price,
    CAST(max_output_price AS DECIMAL(18, 3)) AS max_output_price,
    output_row_count,
    CAST(avg_production_price AS DECIMAL(18, 3)) AS avg_production_price,
    CAST(min_production_price AS DECIMAL(18, 3)) AS min_production_price,
    CAST(max_production_price AS DECIMAL(18, 3)) AS max_production_price,
    production_row_count,
    CAST(price_variation_pct AS DECIMAL(10, 2)) AS price_variation_pct
FROM price_comparison
WHERE price_variation_pct > 25
ORDER BY price_variation_pct DESC

Overview​

DAG Configuration​

DAG Workflow​

Source Tables​

Pre-Processing Validation Tests​

Test 1: Error in Notes Field​

Test 2: Hospital Rates Plans Missing​

Test 3: CSLI Subpackage Line Items Not Priced​

Test 4: CSLI Subpackages Not Priced​

Test 5: Duplicate Active Contracts​

Test 6: No Match in Hospital Rates​

Test 7: Multiple Matches in Hospital Rates​

Test 11: Pricing Flags​

Post-Processing Validation Tests​

Test 8: Zero Prices in Final Table​

Test 9: Benchmark vs Production SSPs​

Test 10: Benchmark vs Cash Prices​

On this page: