Concepts /
NPI (National Provider Identifier)
The 10-digit ID every US health care provider uses on claims.
An NPI is a unique identification number for covered health care providers. It is a HIPAA Administrative Simplification standard, so providers, health plans and clearinghouses must use it in standard administrative and financial transactions such as claims.
The NPI is a 10-digit number that is "intelligence-free": the digits don't tell you anything about the provider, such as their state or specialty. To learn those things you join the NPI to other data.
Individual clinicians get a Type 1 NPI. Organizations such as hospitals, clinics, group practices, labs and pharmacies get a Type 2 NPI, and a large organization can get separate NPIs for its parts (subparts).
In the tables
| Column | Tables | Meaning |
|---|---|---|
npi | providers_hi, provider_services_hi | 10-digit National Provider Identifier; join key between the two tables. |
entity_type | providers_hi | CMS PUF coding: I = individual (Type 1 NPI), O = organization (Type 2 NPI). |
Formats
- Always treat NPI as text (VARCHAR), never as a number — leading zeros and exact 10-character matching matter in joins.
- Intelligence-free: the digits alone do not encode state or specialty; join to other columns/files for those.
Common pitfalls
- Casting NPI to INTEGER / DOUBLE drops leading zeros and breaks joins.
- One NPI ≠ one person forever in every file grain: this cut is one row per NPI in providers_hi, and many rows per NPI in provider_services_hi (one per HCPCS × place-of-service).
- Type 1 vs Type 2: filtering individuals vs orgs needs entity_type, not the NPI digits.
Beginner queries
DuckDB SQL against this site's Parquet tables. Illustrative — the graded set is on /learn/.
Count individual vs organization NPIs in Hawaii
DuckDB SQL
SELECT entity_type, COUNT(*) AS npi_count FROM providers_hi GROUP BY entity_type ORDER BY entity_type;Illustrative DuckDB SQL against this site's Parquet tables. Try the graded set on /learn/.
Join services to providers on NPI
DuckDB SQL
SELECT p.npi, p.specialty, s.hcpcs_code, s.place_of_service, s.services FROM providers_hi p JOIN provider_services_hi s USING (npi) ORDER BY s.services DESC NULLS LAST LIMIT 10;Lists a few Level II rows with specialty. Illustrative — graded exercises live on /learn/.
Where this shows up in the claim cycle: How a Claim Gets Paid, module 1: Who's who: payers and providers
Used in: Filter by specialty · Top 10 providers by beneficiaries · Which specialties bill drug J codes? · Flu shot administration by specialty