Skip to main content

Spines

DME Spine​

DAG ID: spines_dme

Source: PR #5133

Inclusion Criteria​

  • The DME spine is built from NPPES data filtered by the following taxonomy codes:
TAXONOMY_SPECS = [
    # Durable Medical Equipment & Medical Supplies
    {'code': '332B00000X'},   # DME & Medical Supplies (general)
    {'code': '332BC3200X'},   # Customized Equipment
    {'code': '332BD1200X'},   # Dialysis Equipment & Supplies
    {'code': '332BN1400X'},   # Nursing Facility Supplies
    {'code': '332BX2000X'},   # Oxygen Equipment & Supplies
    {'code': '332BP3500X'},   # Parenteral & Enteral Nutrition
    # Other Suppliers
    {'code': '332H00000X'},   # Eyewear Supplier
    {'code': '332S00000X'},   # Hearing Aid Equipment
    {'code': '335V00000X'},   # Portable X-ray and/or Other Portable Diagnostic Imaging Supplier
    {'code': '335E00000X'},   # Prosthetic/Orthotic Supplier
]

All codes fall under taxonomy_grouping = 'Suppliers'.

  • 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_dme_{{ 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'

National Providers​

  • No national providers defined yet (placeholder only).

Structure​

  • DME 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_dme and tq_dev.spines.spines_provider_dme_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,

Pipeline​

start_dag
    → build_tmp_nppes_taxonomy
    → build_tmp_nppes_base
    → build_tmp_nppes_primary
    → [build_dme_spine, build_additional_npis]
    → cleanup_intermediate_tables
    → [build_spines_provider_dme, build_spines_provider_dme_additional_npis]
    → end_dag
On this page: