Device Fields

8 new columns added to the combined table:

Field	Type	Description	Source
device_dependent	boolean	Whether the procedure code requires a device	tq_dev.internal_dev_npattison.device_dependent_codes
device_price_inclusive	boolean	Whether device reimbursement is bundled with the procedure rate
device_percentage	double	Implant provision/carveout percentage value	tq_dev.internal_dev_mmalhotra.provisions_final, provisions_implants_combined
device_gross_charge	double	Average device gross charges at procedure/payer/provider level	tq_production.devices.device_primary_rates_latest
device_dollar	double	Dollarized device reimbursement: device_percentage × device_gross_charge	— (calculated)
device_qualifier	varchar	Revenue code used to identify the implant carveout percentage (usually 278)	tq_dev.internal_dev_mmalhotra.provisions_implants_combined
device_source	varchar	How device gross charges were derived (from DevPR or Komodo claims)	—
bundled_rate	double	Combined procedure + device reimbursement. Equals canonical_rate when device is inclusive	— (calculated)

Calculation Logic

• device_dollar = device_gross_charge * (device_percentage / 100) — only when device_dependent = true and both inputs are non-null
• bundled_rate:
  • For carveout providers: canonical_rate + device_dollar
  • For inclusive providers (or when device data is incomplete): canonical_rate
  • NULL when device_dependent = false

Provider Classification

Device-inclusive vs carveout is currently determined by hardcoded provider lists. Next steps include developing logic to systematically determine whether a rate is bundled or unbundled.

Classification	Provider IDs	Behavior
Device Inclusive	3968, 1951, 6861, 4689, 3896	Device cost already included in procedure rate. device_price_inclusive = true, bundled_rate = canonical_rate
Device Carveout	6433, 6329, 1815, 2014, 6527	Device cost billed separately. device_price_inclusive = false, bundled_rate = canonical_rate + device_dollar
Unknown	All others	device_price_inclusive = NULL

Device Gross Charges

Data Sources

Device gross charges are derived from two sources:

1. DevPR (Device Primary Rates) — labeled implant charges from device_primary_rates_latest
2. Claims — charges for revenue code 0278, from claims medical service lines

Geographic Hierarchy

Each source is aggregated at four levels of granularity:

1. Provider
2. Health System
3. CBSA
4. State

Fallback Cascade

The final device_gross_charge is selected using an 8-step priority waterfall:

Priority	Source	Level
1	DevPR	Provider
2	DevPR	Health System
3	Claims	Provider
4	Claims	Health System
5	DevPR	CBSA
6	Claims	CBSA
7	DevPR	State
8	Claims	State

The device_source field records which level was used (e.g., "DevPR: Provider Level", "Claims: Healthsystem Level").

Device Source Distribution

Device Source	ROIDs	Percent
Claims: State Level	7,540,912	64%
Claims: CBSA Level	2,965,555	25%
Claims: Healthsystem Level	665,308	6%
DevPR: CBSA Level	194,442	2%
DevPR: State Level	171,046	1%
Claims: Provider Level	107,479	1%
DevPR: Healthsystem Level	91,407	1%
DevPR: Provider Level	79,765	1%
Total	11,815,914

The update increased gross charges coverage from 3,991,815 to 7,405,704 ROIDs (86% increase).

Claims Processing

Claims-based gross charges follow this pipeline:

1. Filter to revenue code 0278 line items with line_charge > 0 and units > 0, within the last 12 months
2. Standardize by units: line_charge / units
3. Roll up to encounter level: SUM(line_charge_std) per encounter
4. Join to device-dependent procedure codes and provider spine
5. Only institutional claims (claim_type_code = 'I'), inpatient (11%) or outpatient (13%)
6. Require 10+ encounters per group for statistical reliability
7. Average across encounters per geographic level

SQL

build_device_gc.sql

-- Build device gross charges
-- Table: {{ schema_name }}.tmp_raw_device_gross_charges_{{ sub_version }}

CREATE OR REPLACE TABLE {{ schema_name }}.tmp_raw_device_gross_charges_{{ sub_version }}
AS
WITH

{%- set granularity = [
    {'name': 'provider', 'col': 'provider_id'},
    {'name': 'system', 'col': 'health_system_id'},
    {'name': 'cbsa', 'col': 'cbsa'},
    {'name': 'state', 'col': 'state'},
] %}

-- DevPR Gross Charges
devpr_base AS (
    SELECT
        SUBSTRING(procedure_billing_code_full, 7, 5) AS billing_code,
        provider_id,
        health_system_id,
        cbsa,
        state,
        implant_gross_charge
    FROM {{ cld_params.Tables.DEVICE_PRIMARY_RATES.value }}
    WHERE implant_gross_charge > 0
),

{% for g in granularity %}
devpr_{{ g.name }}_level AS (
    SELECT
        billing_code,
        {{ g.col }},
        AVG(implant_gross_charge) AS avg_implant_charge
    FROM devpr_base
    WHERE {{ g.col }} IS NOT NULL
    GROUP BY 1, 2
),
{% endfor %}

-- Claims Gross Charges
claims_rc_line_charges AS (
    SELECT
        encounter_key,
        line_charge,
        units,
        line_charge / units AS line_charge_std
    FROM {{ cld_params.Tables.CLAIMS_MEDICAL_SERVICE_LINES.value }}
    WHERE revenue_code = '0278'
        AND line_charge > 0
        AND units > 0
        AND service_from > CURRENT_DATE - INTERVAL '12' MONTH
),

claims_rc_charges AS (
    SELECT
        encounter_key,
        SUM(line_charge_std) AS rc_charge
    FROM claims_rc_line_charges
    GROUP BY 1
),

claims_base AS (
    SELECT
        ps.provider_id,
        ps.health_system_id,
        ps.cbsa,
        ps.state,
        msl.procedure_code AS billing_code,
        CASE WHEN mh.bill_type_code LIKE '11%' THEN 'Inpatient'
            WHEN mh.bill_type_code LIKE '13%' THEN 'Outpatient'
            ELSE mh.bill_type_code END AS bill_type,
        r.rc_charge,
        msl.encounter_key
    FROM {{ cld_params.Tables.CLAIMS_MEDICAL_SERVICE_LINES.value }} msl
    JOIN {{ cld_params.Tables.CLAIMS_MEDICAL_HEADERS.value }} mh
        ON msl.encounter_key = mh.encounter_key
    JOIN claims_rc_charges r
        ON msl.encounter_key = r.encounter_key
    JOIN {{ cld_params.Tables.DEVICE_DEPENDENT_CODES.value }} ddc
        ON msl.procedure_code = ddc.billing_code
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
        CROSS JOIN UNNEST(ps.npi) AS n(npi)
        ON msl.hco_s_1_npi = n.npi
    WHERE msl.units > 0
        AND msl.hco_s_1_npi IS NOT NULL
        AND mh.claim_type_code = 'I'
        AND (mh.bill_type_code LIKE '11%' OR mh.bill_type_code LIKE '13%')
        AND ps.provider_type = 'Hospital'
),

{% for g in granularity %}
claims_{{ g.name }}_level AS (
    SELECT
        {{ g.col }},
        billing_code,
        bill_type,
        AVG(rc_charge) AS avg_rc_charge
    FROM claims_base
    WHERE {{ g.col }} IS NOT NULL
    GROUP BY 1, 2, 3
    HAVING COUNT(DISTINCT encounter_key) > 10
),
{% endfor %}

devpr_claims_combined AS (
    SELECT
        ros.provider_id, ros.bill_type, ros.billing_code,
        dpl.avg_implant_charge AS device_gc_provider,
        dsyl.avg_implant_charge AS device_gc_system,
        dcl.avg_implant_charge AS device_gc_cbsa,
        dsl.avg_implant_charge AS device_gc_state,
        'DevPR' AS gross_charge_source
    FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps
        ON ros.provider_id = ps.provider_id
    LEFT JOIN devpr_provider_level dpl ON ros.billing_code = dpl.billing_code AND ros.provider_id = dpl.provider_id
    LEFT JOIN devpr_system_level dsyl ON ros.billing_code = dsyl.billing_code AND ps.health_system_id = dsyl.health_system_id
    LEFT JOIN devpr_cbsa_level dcl ON ros.billing_code = dcl.billing_code AND ps.cbsa = dcl.cbsa
    LEFT JOIN devpr_state_level dsl ON ros.billing_code = dsl.billing_code AND ps.state = dsl.state
    WHERE ros.bill_type = 'Outpatient'
    AND (dpl.avg_implant_charge IS NOT NULL OR dsyl.avg_implant_charge IS NOT NULL
         OR dcl.avg_implant_charge IS NOT NULL OR dsl.avg_implant_charge IS NOT NULL)
    UNION ALL
    SELECT
        ros.provider_id, ros.bill_type, ros.billing_code,
        cpl.avg_rc_charge AS device_gc_provider,
        csyl.avg_rc_charge AS device_gc_system,
        ccl.avg_rc_charge AS device_gc_cbsa,
        csl.avg_rc_charge AS device_gc_state,
        'Claims' AS gross_charge_source
    FROM {{ schema_name }}.tmp_rate_object_space_{{ sub_version }} ros
    JOIN {{ schema_name }}.tmp_ref_provider_spine_{{ sub_version }} ps ON ros.provider_id = ps.provider_id
    LEFT JOIN claims_provider_level cpl ON ros.billing_code = cpl.billing_code AND ros.provider_id = cpl.provider_id AND ros.bill_type = cpl.bill_type
    LEFT JOIN claims_system_level csyl ON ros.billing_code = csyl.billing_code AND ps.health_system_id = csyl.health_system_id AND ros.bill_type = csyl.bill_type
    LEFT JOIN claims_cbsa_level ccl ON ros.billing_code = ccl.billing_code AND ps.cbsa = ccl.cbsa AND ros.bill_type = ccl.bill_type
    LEFT JOIN claims_state_level csl ON ros.billing_code = csl.billing_code AND ps.state = csl.state AND ros.bill_type = csl.bill_type
    WHERE (cpl.avg_rc_charge IS NOT NULL OR csyl.avg_rc_charge IS NOT NULL
           OR ccl.avg_rc_charge IS NOT NULL OR csl.avg_rc_charge IS NOT NULL)
)
SELECT
    provider_id, bill_type, billing_code,
    COALESCE(
        MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_provider END),
        MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_system END),
        MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_provider END),
        MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_system END),
        MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_cbsa END),
        MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_cbsa END),
        MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_state END),
        MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_state END)
    ) AS device_gross_charge,
    CASE
        WHEN MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_provider END) IS NOT NULL THEN 'DevPR: Provider Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_system END) IS NOT NULL THEN 'DevPR: Healthsystem Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_provider END) IS NOT NULL THEN 'Claims: Provider Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_system END) IS NOT NULL THEN 'Claims: Healthsystem Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_cbsa END) IS NOT NULL THEN 'DevPR: CBSA Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_cbsa END) IS NOT NULL THEN 'Claims: CBSA Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'DevPR' THEN device_gc_state END) IS NOT NULL THEN 'DevPR: State Level'
        WHEN MAX(CASE WHEN gross_charge_source = 'Claims' THEN device_gc_state END) IS NOT NULL THEN 'Claims: State Level'
    END AS device_source
FROM devpr_claims_combined
GROUP BY 1, 2, 3

combined_main.sql

-- Device Fields (added to the final SELECT of combined_main.sql)

{% set PROVIDERS_DEVICE_INCLUSIVE = ['3968', '1951', '6861', '4689', '3896'] %}
{% set PROVIDERS_DEVICE_CARVEOUT = ['6433', '6329', '1815', '2014', '6527'] %}

code_spine.is_device_dependent as device_dependent,

CASE
    WHEN code_spine.is_device_dependent = False THEN NULL
    WHEN c.provider_id IN ('3968', '1951', '6861', '4689', '3896') THEN TRUE
    WHEN c.provider_id IN ('6433', '6329', '1815', '2014', '6527') THEN FALSE
    ELSE NULL
END as device_price_inclusive,

c.device_percentage,
c.device_gross_charge,

CASE
    WHEN code_spine.is_device_dependent = True
         AND c.device_percentage IS NOT NULL
         AND c.device_gross_charge IS NOT NULL
    THEN c.device_gross_charge * (c.device_percentage / 100.0)
    ELSE NULL
END as device_dollar,

c.device_qualifier,
c.device_source,

CASE
    WHEN code_spine.is_device_dependent = True
         AND c.provider_id IN ('6433', '6329', '1815', '2014', '6527')
         AND rate_array[best_idx] IS NOT NULL
         AND c.device_gross_charge IS NOT NULL
         AND c.device_percentage IS NOT NULL
    THEN rate_array[best_idx] + (c.device_gross_charge * (c.device_percentage / 100.0))
    WHEN code_spine.is_device_dependent = True
         AND rate_array[best_idx] IS NOT NULL
    THEN rate_array[best_idx]
    ELSE NULL
END as bundled_rate