Clear Rates

DME in Clear Rates

Source: PR #5150

Steps in provider.sql:

• Array associated NPIs from the two spines tables to the provider_id level.

dme_array_npi as (
    SELECT
        provider_id,
        ARRAY_SORT(ARRAY_AGG(DISTINCT cast(npi as varchar))) as npi
    FROM (
        SELECT provider_id, npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_DME.value }}
        UNION
        SELECT provider_id, npi
        FROM {{ cld_params.Tables.SPINES_PROVIDER_DME_ADDITIONAL_NPIS.value }}
    )
    WHERE npi IS NOT NULL
    GROUP BY provider_id
),

• The dme CTE reads directly from the spines table and joins arrayed NPIs, geo lookups (state, CBSA), and NUCC taxonomy. All spines providers are included (no Komodo encounter filtering).

dme AS (
    SELECT
        p.provider_id,
        CASE
            WHEN npis.npi[1] IS NULL THEN NULL
            ELSE npis.npi
        END as npi,
        p.provider_name as provider_name,
        p.provider_city AS city,
        p.provider_state AS state,
        p.provider_cbsa_code AS cbsa,
        p.provider_parent_system as health_system_name,
        p.provider_type as provider_type,
        p.taxonomy_code,
        p.national_payer_coverage
        -- ...
    FROM {{ cld_params.Tables.SPINES_PROVIDER_DME.value }} p
    LEFT JOIN dme_array_npi as npis ON p.provider_id = npis.provider_id
    LEFT JOIN nucc ON p.taxonomy_code = nucc.taxonomy_code
    LEFT JOIN geo_state ON p.provider_state = geo_state.state_abbreviation
    LEFT JOIN geo_cbsa ON p.provider_cbsa_code = geo_cbsa.cbsa_id
),

• The result is unioned into the final df CTE alongside hospitals, ASCs, labs, PGs, dialysis centers, infusion centers, etc.
• In lookback runs, DME is excluded from the state filter (same as ASCs, PGs, and Dialysis Centers).

Codeset (Rate Object Space)

• A dedicated dme_codeset table defines which billing codes are plausible for DME suppliers. Codes are sourced from the DME reference pricing table, filtered to:

  • Modifiers: NU (new), RR (rental), UE (used)
  • HCPCS prefixes: E, K, L, A, V
  • Rate > 0

• In the rate object space, DME codes are filtered to bill_type = 'Professional' and matched against the codeset:

SELECT DISTINCT
    cs.bill_type,
    cs.billing_code_type,
    cs.billing_code,
    cs.facility,
    'DME' as provider_type,
    NULL as provider_subtype,
    NULL as provider_id,
    '' as billing_code_modifier
FROM {{ schema_name }}.tmp_ref_code_spine_{{ sub_version }} cs
WHERE cs.bill_type = 'Professional'
    AND cs.billing_code IN (
        SELECT billing_code
        FROM {{ cld_params.Tables.DME_CODESET.value }}
    )

Medicare Benchmarks

• DME has its own Medicare reference pricing source (tq_production.reference_external.dme_reference_pricing), separate from MPFS.
• The medicare_rate for DME is set to: COALESCE(dme_geographic_avg.state_avg_rate, mpfs_geographic_avg.state_avg_rate) — DME Fee Schedule first, MPFS as fallback.
• State-level rates are computed as the median rate per HCPCS code per state, filtered to modifier = 'NU'.
• The medicare_reference_source is labeled 'DME Fee Schedule' or 'MPFS' depending on which source matched.

Accuracy Scoring

• DME has its own dedicated accuracy scoring method (accuracy_score_dme), separate from the standard medical method.
• Outlier bounds: rate must be between 0.5x and 5.5x Medicare rate.
• Scoring hierarchy (from best to worst):
  1. Score 7 — has_consistent_pct_of_medicare_rate = true AND rate is between 0.9x and 3.5x Medicare
  2. Score 6 — has_consistent_pct_of_medicare_rate = true AND rate is between 0.5x and 5.5x Medicare
  3. Score 5 — rate is between 0.9x and 3.5x Medicare (without consistency check)
  4. Score 4 — within outlier bounds but doesn't meet higher tiers
• Note: unlike Urgent Care, DME does not have a "validated" tier (no cross-column comparison).
• QA thresholds: minimum 3.0% of providers must have rates.