VladaOmega
Sign inStart free
Provider identity

NUCC Healthcare Provider Taxonomy Code Set

drug_silver.dim_nucc_taxonomy·live·dim·static·NUCC Healthcare Provider Taxonomy Code Set
Rows
N/A
Columns
23
History
2026-05-16 (first ingestion of NUCC taxonomy v25.1) to 2026-05-18 (current ingestion; is_current=true). Both snapshots are the SAME NUCC release v251 — same sha256 content hash — so there is no real time series, just two load dates.
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1The NUCC Health Care Provider Taxonomy code set — the official US reference dictionary that maps each 10-character provider-taxonomy code (the specialty codes carried on NPIs and 837 claims, e.g.
  2. 2207L00000X = Anesthesiology) to its human-readable grouping / classification / specialization hierarchy, display name, and definition.
  3. 3It is a small static lookup dimension used to label/translate provider specialties when joining NPI, claims, hospital, or drug-dispenser data; it holds no prices itself.

Grain

KeyGrain
codeis_current
  1. 1One row per NUCC taxonomy code per ingestion snapshot.
  2. 2The full table is 1,766 rows = 883 distinct taxonomy codes × 2 byte-identical re-ingestion snapshots of the same NUCC v25.1 file.
  3. 3The decision-grade slice is is_current=true, which is exactly one row per code: 883 rows, with `code` alone verified unique.
  4. 4Across all rows the minimal key is (code, observed_period) or equivalently (code, is_current) = 1,766;
  5. 5`code` alone is NOT unique over the full table (each code appears twice).

How to read it

  1. 1Resolve a provider's specialty code to a readable label and hierarchy.
  2. 2Example: `SELECT code, \"grouping\", classification, specialization, display_name, definition FROM drug_silver.dim_nucc_taxonomy WHERE is_current = true AND code = '207L00000X'` returns the single row 'Anesthesiology Physician' under grouping 'Allopathic & Osteopathic Physicians'.
  3. 3Typical real use is a join — e.g. take the taxonomy code attached to an NPI in a claims/provider table and join to this dim (always with is_current=true) to add the grouping/classification/specialization and display_name for grouping spend or specialty-level cost analysis.

Traps

  1. 1DUPLICATE SNAPSHOTS: the table has 1,766 rows but only 883 real codes — every code appears twice because two ingestion snapshots (observed_period 2026-05-16 and 2026-05-18) of the SAME file are stored. `code` alone is NOT a unique key over the full table. ALWAYS filter is_current=true (gives 883 rows, code unique). The two snapshots are byte-identical (single sha256_content_hash), so the 'history' is a re-ingestion artifact, not a content change or real time series.
  2. 2RESERVED WORD: the column `grouping` is a Trino/Athena reserved keyword (the GROUPING() function). Unquoted use in SELECT/GROUP BY/CASE throws a cryptic 'InvalidRequestException: Queries of this type are not supported' at start_query_execution time (not a normal SQL error). Always quote it as "grouping".
  3. 3specialization NULL is MEANINGFUL: NULL (241 codes) = a base/parent taxonomy code at the classification level with no sub-specialty; non-NULL = a leaf sub-specialty. Don't treat NULL as missing data. Use display_name for a fully-resolved human label since it already incorporates the specialization when present.
  4. 4SCD columns are placeholders, not real history: effective_period_end is the sentinel '2099-12-31' for every code (never closed), effective_period_start/observed_period just equal the load date, and cms_vintage_sequence/parse_revision are constant 1. The profiler's 'daily' periodicity is an artifact of two load dates 2 days apart; treat this as STATIC reference data (NUCC publishes ~semi-annually; only release v25.1 is loaded).
  5. 5This is a code-translation dimension, not a cost or provider table: it contains zero prices, zero NPIs, and zero providers — only 883 taxonomy-code definitions. To label real providers you join this dim's `code` to the taxonomy code on NPPES/NPI or claims data. Naming caveat: drug_source_version_id and the drug_silver database prefix do NOT mean drug data — '251' is the NUCC version and source_id='nucc_taxonomy'.

Columns (23)

ColumnTypeMeaning
codestringThe 10-character NUCC provider taxonomy code (always exactly length 10, alphanumeric, conventionally ending in X), e.g. 207L00000X. This is the join key against the taxonomy code carried on NPI records (NPPES) and 837 claims. Always populated; unique within is_current=true (883 codes).
groupingstringTop level of the NUCC 3-tier hierarchy — the broad provider category, e.g. 'Allopathic & Osteopathic Physicians' (238 codes), 'Ambulatory Health Care Facilities', 'Agencies', 'Hospitals', 'Pharmacy Service Providers'. 29 distinct values in is_current. Always populated. CAVEAT: 'grouping' is a reserved word in Trino/Athena — you MUST quote it as "grouping" in SQL or the query fails with a misleading 'Queries of this type are not supported' error.
classificationstringMiddle tier of the hierarchy — the specialty/provider-type within a grouping, e.g. 'Anesthesiology', 'Allergy & Immunology', 'Home Health', 'Case Management'. Always populated (0 nulls). Together with grouping it defines the base specialty.
specializationstringBottom tier — the sub-specialty within a classification, e.g. 'Allergy', 'Clinical & Laboratory Immunology'. NULL for 241 of 883 codes: NULL means the code is a BASE/parent code (classification-level, no sub-specialty); non-NULL means it is a leaf sub-specialty. Both Individual (157 base / 541 spec) and Non-Individual (84 base / 101 spec) sections contain base codes.
definitionstringFree-text prose definition of the provider type, sourced from NUCC / specialty boards (e.g. ABMS). Populated for 875/883 codes; 8 are NULL (rare codes shipped without a definition). Length varies from one sentence to a full paragraph.
notesstringFree-text NUCC change-log / source annotation for the code, e.g. '[7/1/2003: new]', 'Source: National Uniform Claim Committee, 2022', often with embedded HTML '<br/>' tags. NULL for 282/883 codes (no annotation). Informational only — do not parse the embedded dates as the code's effective date.
display_namestringThe official human-readable label for the code, e.g. 'Anesthesiology Physician', 'Home Health Agency', 'Multi-Specialty Group'. Always populated (0 nulls). This is the canonical string to show users when labeling a taxonomy code; it folds in the specialization when present.
sectionstringTwo-valued partition of the code set: 'Individual' (698 codes — individual practitioners and practitioner groups) vs 'Non-Individual' (185 codes — organizations: facilities, agencies, hospitals, suppliers, labs). Always populated. Mirrors the NUCC entity-type split (NPI Type 1 vs Type 2 alignment).
observed_perioddateSnapshot/ingestion date this row belongs to: 2026-05-16 (older, is_current=false) or 2026-05-18 (current, is_current=true). This is the column that distinguishes the two duplicate snapshots; it is a load date, not a NUCC release date. Constant within each snapshot.
asserted_atdateDate this assertion was recorded by the pipeline; equals observed_period for every row (2026-05-16 or 2026-05-18). Provenance/audit field; carries no independent information here.
effective_period_startdateSCD-style validity-start date; in this table it just equals the snapshot date (2026-05-16 / 2026-05-18), NOT a real NUCC effective date. The profiler keys 'history' off this column and infers a 2-day/'daily' cadence, but that is a re-ingestion artifact, not a release schedule.
effective_period_enddateSCD-style validity-end date. Always the sentinel '2099-12-31' for all 883 codes — meaning 'open / currently in effect'. No code is closed-out; do not treat 2099-12-31 as a real date.
cms_vintage_sequenceintInteger vintage sequence number; constant = 1 across all 1,766 rows (only one logical vintage loaded so far). Placeholder for future multi-release versioning.
parse_revisionintParser revision number for the ingest; constant = 1 across all rows. Audit field.
parser_artifact_hashstringHash identifying the parser run/artifact that produced the row. 2 distinct values across the table — one per ingestion snapshot — so it (along with vintage_tag/observed_period) is what differentiates the two otherwise-identical loads of the same file.
source_urlstringThe upstream NUCC download URL: https://www.nucc.org/images/stories/CSV/nucc_taxonomy_251.csv. Single value across all 1,766 rows. Confirms the source is NUCC taxonomy CSV release 251 (v25.1).
sha256_content_hashstringSHA-256 of the ingested source file content. EXACTLY 1 distinct value across all 1,766 rows — proof that the two snapshots (2026-05-16 and 2026-05-18) are the byte-identical same NUCC file re-ingested, i.e. there was no content change between the two loads.
source_idstringLogical source identifier; constant = 'nucc_taxonomy' for every row. Use this to identify the dataset within the drug_silver provenance model.
is_currentbooleanBoolean snapshot flag. TRUE marks the latest ingestion (2026-05-18, 883 rows) and is the slice you almost always want; FALSE marks the superseded 2026-05-16 snapshot (883 rows). ALWAYS filter is_current=true to get one-row-per-code; without it every code is duplicated.
bronze_object_keystringS3 key of the raw bronze object: drug/bronze/nucc_taxonomy/v251/...-nucc_taxonomy_251.csv. Single value across all rows (both snapshots point at the same bronze file). Lineage pointer to raw storage.
s3_version_idstringS3 object version ID of the bronze file. Single value across all rows. Lineage/provenance field.
vintage_tagstringHuman-readable vintage label, e.g. 'nucc_taxonomy_v251_2026-05-18'. 2 distinct values (one per snapshot/load date). This plus observed_period/parser_artifact_hash are the only columns that differ between the two snapshots.
drug_source_version_idstringSource version identifier; constant = '251' across all rows = NUCC taxonomy release 25.1. Despite the 'drug_' prefix (it lives in the drug_silver provenance schema) this is the NUCC version, not a drug version.

Query it

describe_data_source(source_fqn="drug_silver.dim_nucc_taxonomy")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.dim_nucc_taxonomy WHERE code = '<value>' AND is_current = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycode
Joinhow_to_join(source_fqn="drug_silver.dim_nucc_taxonomy")
Raw doorAthena (read-only via query_substrate): drug_silver.dim_nucc_taxonomy
NoteIceberg table drug_silver.dim_nucc_taxonomy (physically in drug_silver for M-2 envelope sharing; conceptually a provider-identity vocabulary). Runner: pipelines/drug/ingest/nucc_taxonomy_runner.py.

This page as markdown: /docs-md/data/drug_silver_dim_nucc_taxonomy.md · every page in one file: /llms-full.txt