Spines

Infusion Centers Spine

Inclusion Criteria

The Infusion Centers spine was created based on common taxonomy codes associated with Infusion Center NPIs. The Life Sciences team has been using these in their products via the provider_profiles table, which we then validated in claims data by looking at the taxonomies that commonly bill infusion procedure codes, plus made it refreshable.

TAXONOMY_SPECS = [
    {'code': '207RH0003X'},
    {'code': '207RH0000X'},
    {'code': '207RR0500X'},
    {'code': '207RX0202X'},
    {'code': '207VX0201X'},
    {'code': '2080P0207X'},
    {'code': '2084N0400X'},
    {'code': '2085R0001X'},
    {'code': '2086X0206X'},
    {'code': '261QI0500X'},
    {'code': '261QX0200X'},
    {'code': '261QX0203X'}

These taxonomy codes are looped through the 15 taxonomy switch columns in tq_production.reference_legacy.ref_cms_nppes_npi, searching for the first Y primary taxonomy value per type 2 NPI. If none are reported, it falls back on the first non-null taxonomy found. Taxonomy details come from tq_production.reference_legacy.ref_nucc_taxonomy_code.

nppes_with_selected_taxonomy AS (
    SELECT
        CAST(npi AS VARCHAR) AS npi,
        {% set num_codes = 15 %}
        CASE
            {% for i in range(1, num_codes + 1) %}
            WHEN healthcare_provider_primary_taxonomy_switch_{{ i }} = 'Y'
                AND healthcare_provider_taxonomy_code_{{ i }} IS NOT NULL
                THEN healthcare_provider_taxonomy_code_{{ i }}
            {% endfor %}
            ELSE COALESCE(
                {% for i in range(1, num_codes + 1) %}
                healthcare_provider_taxonomy_code_{{ i }}{{ "," if i < num_codes else "" }}
                {% endfor %}
            )
        END AS selected_taxonomy_code
    FROM {{ ref_cms_nppes_npi }}
    WHERE entity_type_code = '2'
),
infusion_centers AS (
    SELECT npi
    FROM nppes_with_selected_taxonomy
    WHERE selected_taxonomy_code IN (
        {% for spec in taxonomy_specs %}
        '{{ spec.code }}'{% if not loop.last %},{% endif %}
        {% endfor %}
    )
)

Search for these NPIs in the compressed schema in order to return an EIN. Requires over 3 payers to report the association to increase confidence in the relationship.

SELECT
    tin,
    npi,
    count(distinct payer_id) as n_payers
FROM {{ compressed_providers }} cp
WHERE EXISTS (
    SELECT 1
    FROM infusion_centers ic
    WHERE ic.npi = cp.npi
)
GROUP BY
    tin,
    npi
HAVING count(distinct payer_id) > 3

Structure

Infusion Centers follows a similar structure to the existing spines table, by splitting the NPIs into two tables that can be traced through via provider_id.
The tables are tq_production.spines.spines_provider_infusion_centers and tq_production.spines.spines_provider_infusion_centers_additional_npis.
The primary NPI for a provider_id that will be used in the main table is determined by the lowest numerical NPI value.

ROW_NUMBER() OVER (PARTITION BY provider_id ORDER BY npi ASC) as npi_rank

provider_id is created by hashing the primary NPI. Since Infusion Centers can be Physician Groups or ASCs, the ID is pulled directly from those spines when applicable. If an Infusion Center is independent and not reflected in the Physician Group or ASC spine, it will only have one associated NPI and no records will appear for that ID in the additional table.

    COALESCE(
        asc.provider_id,
        pg.provider_id,
        CAST(from_big_endian_64(xxhash64(CAST(
            npi
            AS varbinary
        ))) AS VARCHAR)
    ) as provider_id,

Infusion Centers Spine​

Inclusion Criteria​

Structure​

On this page:

Infusion Centers Spine

Inclusion Criteria

Structure