# HRSA 340B Covered Entity → NPI bridge

`drug_silver.fact_hrsa_ce_npi` · Provider identity · status live

Bridge from each HRSA 340B covered entity (id_340b) to its associated NPI(s), with state. Lets a claim's provider NPI be checked for 340B eligibility and joined to dim_hrsa_covered_entity.

**Grain.** One row per (340B covered-entity ID x NPI x NPI-state). 730,707 rows = exactly the count of distinct (id_340b, npi_number, npi_state) triples (verified: max duplicate on the triple = 1, zero duplicate triples). It is a many-to-many bridge: a single covered entity has many NPIs across many states, and a single NPI (e.g. a contract/chain pharmacy) is shared by up to 665 different covered entities. The same (entity, NPI) pair legitimately repeats once per state it serves.

**How to use it.** List every NPI tied to a 340B Disproportionate Share Hospital and where it operates: SELECT id_340b, npi_number, npi_state FROM drug_silver.fact_hrsa_ce_npi WHERE id_340b LIKE 'DSH%' AND is_current. To flag whether a provider participates in 340B (for drug-cost analysis), test EXISTS against npi_number. To count 340B reach per state: SELECT npi_state, COUNT(DISTINCT npi_number) FROM drug_silver.fact_hrsa_ce_npi WHERE npi_state <> '' GROUP BY 1. Always dedup/group on (id_340b, npi_number, npi_state) and never assume id_340b or npi_number alone is unique.

**Traps.**
- The profiler's 7-column 'verified unique' grain is a misleading superset. The TRUE minimal grain is just (id_340b, npi_number, npi_state) = 730,707 distinct = total. Every other column it listed (source_id, bronze_object_key, s3_version_id, drug_source_version_id) is constant or all-NULL, so it adds nothing. Group/dedup on the triple only.
- Many-to-many, NOT a clean lookup. id_340b is NOT unique (66,761 distinct), npi_number is NOT unique (21,504 distinct), and neither is any pair. One NPI maps to a median of 2 and up to 665 covered entities (contract/chain pharmacies); one entity has up to 339 NPIs across up to 59 states. Joining this to NPI-level cost data will fan out unless you aggregate first.
- npi_state is part of the grain and its empty string '' (10,257 rows) is a meaningful missing value ('state unknown'), not blank padding. It never co-exists with a stated value for the same entity+NPI pair, so do not COALESCE it away or treat '' as a dropable null without changing row counts.
- Single static snapshot only: every date column (observed_period, asserted_at, effective_period_start/end) = 2026-05-15 and is_current is true everywhere. There is no history and no effective-dating despite the column names suggesting otherwise. cms_vintage_sequence, parse_revision, vintage_tag='none', and drug_source_version_id=NULL are inherited drug-silver boilerplate with no signal here.
- id_340b prefixes are NOT a clean normalized enum. HRSA's raw casing/variants are preserved: e.g. 'dsH260137U', 'DSh450184EP' (DSH variants), 'CHO...', 'FQ89' (truncated FQHC), 'RWIIR...'. To classify by entity type, lowercase/normalize the prefix and account for these variants; do not assume a fixed clean code list.

| column | type | meaning |
|---|---|---|
| id_340b | string | 340B program ID of the covered entity (the clean primary identifier of the entity). Format = entity-type prefix + state/sequence digits + optional trailing letter(s) that denote child/associated sites (e.g. BL030740 vs BL03074C are sibling sites of the same parent). 66,761 distinct entities. The leading alpha prefix encodes the 340B eligibility/grant TYPE: DSH=Disproportionate Share Hospital (dominant: 616K rows / 37.5K entities), CH=Children's Hospital, RRC=Rural Referral Center, PED=Pediatric, CHC/CH/FQHC/FQHCLA=Community/Federally-Qualified Health Center, CAH=Critical Access Hospital, SCH=Sole Community Hospital, STD/TB/HV/HM/RWI/RWII/RW=Ryan White HIV programs, FP=Family Planning, CAN=Hemophilia/Cancer, URB/UI=Urban Indian, BL=Black Lung clinic. NEVER NULL. |
| npi_number | string | 10-digit National Provider Identifier of a provider/pharmacy/site associated with the covered entity. 100% valid 10-digit numeric (all 730,707 rows). 21,504 distinct NPIs. Stored as string. NEVER NULL/empty. This is the join key to NPI-keyed cost/claims data. |
| npi_state | string | Two-letter state/territory the NPI serves under this entity. 60 values = 50 states + DC + PR + VI + AS + GU + MP + FM + PW + MH (US territories + Pacific freely-associated states). PART OF THE GRAIN: the same (entity, NPI) pair appears once per state served. EMPTY STRING '' (10,257 rows) is a meaningful missing-value = 'state not recorded for this NPI', not noise (and never co-occurs with a stated value for the same entity+NPI pair). Note SD (659) and NH (1,017) rows are anomalously low vs neighbors. |
| observed_period | date | Date the source file was observed/captured. CONSTANT = 2026-05-15 for every row (single snapshot). No analytical use here. |
| asserted_at | date | Date the assertion was recorded. CONSTANT = 2026-05-15. No analytical use here. |
| effective_period_start | date | Start of validity window. CONSTANT = 2026-05-15. The table's nominal time column, but it is a single point, not a real effective-dating range. |
| effective_period_end | date | End of validity window. CONSTANT = 2026-05-15. Equal to start; no real interval. |
| cms_vintage_sequence | int | Inherited drug-silver schema field. CONSTANT = 1. Meaningless for this HRSA (non-CMS) source; boilerplate. |
| parse_revision | int | Parser revision number. CONSTANT = 1. Boilerplate provenance. |
| parser_artifact_hash | string | SHA-256 of the parser code artifact that produced the rows. CONSTANT (single value) across the table. Provenance/lineage only. |
| source_url | string | Origin URL. CONSTANT = https://340bopais.hrsa.gov/Reports (HRSA 340B OPAIS public reports portal). Provenance. |
| sha256_content_hash | string | SHA-256 of the source bronze object content. CONSTANT (single value) — one source file. Provenance/integrity. |
| source_id | string | Logical source identifier. CONSTANT = 'hrsa_opais_ce_registry'. Identifies the dataset lineage. |
| is_current | boolean | Current-snapshot flag. CONSTANT = true for all 730,707 rows. Because only one snapshot exists, this does NOT dedup anything yet, but a query filtering this table should still apply is_current=true to stay correct once future snapshots land. |
| bronze_object_key | string | S3 key of the source bronze file. CONSTANT (single value): drug/bronze/hrsa_opais_ce_registry/2026-05-15/...-340B_CoveredEntityJson_Daily_2026-05-15.zip — confirms the source is the daily 340B Covered Entity JSON export. Provenance. |
| s3_version_id | string | S3 object version id of the bronze file. CONSTANT (single value). Provenance. |
| vintage_tag | string | Vintage label. CONSTANT = the literal string 'none' (not NULL). No meaning here; boilerplate. |
| drug_source_version_id | string | Drug-source version linkage. 100% NULL across all 730,707 rows (cardinality 0). Carries no information in this table; do not join on it. |

Query it: `query_substrate` with `SELECT ... FROM drug_silver.fact_hrsa_ce_npi WHERE ... LIMIT 100`; read `describe_data_source("drug_silver.fact_hrsa_ce_npi")` first.