Skip to main content

Spines

Dialysis Centers Spine​

DAG ID: spines_dialysis_center

Source: PR #5133

Inclusion Criteria​

  • The Dialysis Centers spine is built from NPPES data filtered by the following taxonomy code:
TAXONOMY_SPECS = [
    # Ambulatory Health Care Facilities > Clinic/Center > End-Stage Renal Disease (ESRD) Treatment
    {'code': '261QE0700X'},
]
  • 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.
CREATE OR REPLACE TABLE {{ schema }}.tmp_nppes_taxonomy_dialysis_center_{{ sub_version }} 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'
  • Search for these NPIs in the compressed schema to return an EIN. Requires over 3 payers to report the association.

National Providers​

NATIONAL_PROVIDERS = {
    "DAVITA": "DaVita",
    "FRESENIUS": "Fresenius",
}

Structure​

  • Dialysis Centers follows a similar structure to the existing spines tables, by splitting the NPIs into two tables that can be traced through via provider_id.
  • The tables are tq_dev.spines.spines_provider_dialysis_center and tq_dev.spines.spines_provider_dialysis_center_additional_npis.
  • The primary NPI for a provider_id is determined by the lowest numerical NPI value, partitioned by canonical_provider_name and provider_state.
    ROW_NUMBER() OVER (PARTITION BY dp.canonical_provider_name, bp.provider_state ORDER BY bp.npi ASC) as primary_rank,
    MIN(bp.provider_id) OVER (PARTITION BY dp.canonical_provider_name, bp.provider_state) as group_provider_id
  • provider_id is created by hashing the NPI:
    CAST(from_big_endian_64(xxhash64(CAST(
        npi
        AS varbinary
    ))) AS VARCHAR) as provider_id,
  • The national_payer_coverage field is set to TRUE for national providers (DaVita, Fresenius) with more than 5 NPIs.

Pipeline​

start_dag
    → build_tmp_nppes_taxonomy
    → build_tmp_nppes_base
    → build_tmp_nppes_primary
    → [build_dialysis_center_spine, build_additional_npis]
    → cleanup_intermediate_tables
    → [build_spines_provider_dialysis_center, build_spines_provider_dialysis_center_additional_npis]
    → end_dag
On this page: