VladaOmega
Sign inStart free
Provider identity

HRSA 340B Covered Entity → NPI bridge

drug_silver.fact_hrsa_ce_npi·live·silver·static·HRSA 340B Covered Entities
Rows
N/A
Columns
18
History
2026-05-15 (single snapshot) to 2026-05-15 (single snapshot)
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A point-in-time snapshot of the HRSA 340B OPAIS Covered Entity registry, mapping each 340B-enrolled covered entity (hospitals, FQHCs, Ryan White HIV clinics, family-planning sites, etc.) to the provider/pharmacy NPIs associated with it and the state(s) those NPIs serve.
  2. 2It is the 340B-eligibility / drug-discount-program identity bridge used to tie 340B covered entities to NPIs (and through NPIs, to drug-cost and claims data).

Grain

KeyGrain
id_340bnpi_numbernpi_state
  1. 1One row per (340B covered-entity ID x NPI x NPI-state).
  2. 2730,707 rows = exactly the count of distinct (id_340b, npi_number, npi_state) triples (verified: max duplicate on the triple = 1, zero duplicate triples).
  3. 3It 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.
  4. 4The same (entity, NPI) pair legitimately repeats once per state it serves.

How to read it

  1. 1List 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.
  2. 2To flag whether a provider participates in 340B (for drug-cost analysis), test EXISTS against npi_number.
  3. 3To 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.
  4. 4Always dedup/group on (id_340b, npi_number, npi_state) and never assume id_340b or npi_number alone is unique.

Traps

  1. 1The 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.
  2. 2Many-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.
  3. 3npi_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.
  4. 4Single 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.
  5. 5id_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.

Columns (18)

ColumnTypeMeaning
id_340bstring340B 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_numberstring10-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_statestringTwo-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_perioddateDate the source file was observed/captured. CONSTANT = 2026-05-15 for every row (single snapshot). No analytical use here.
asserted_atdateDate the assertion was recorded. CONSTANT = 2026-05-15. No analytical use here.
effective_period_startdateStart 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_enddateEnd of validity window. CONSTANT = 2026-05-15. Equal to start; no real interval.
cms_vintage_sequenceintInherited drug-silver schema field. CONSTANT = 1. Meaningless for this HRSA (non-CMS) source; boilerplate.
parse_revisionintParser revision number. CONSTANT = 1. Boilerplate provenance.
parser_artifact_hashstringSHA-256 of the parser code artifact that produced the rows. CONSTANT (single value) across the table. Provenance/lineage only.
source_urlstringOrigin URL. CONSTANT = https://340bopais.hrsa.gov/Reports (HRSA 340B OPAIS public reports portal). Provenance.
sha256_content_hashstringSHA-256 of the source bronze object content. CONSTANT (single value) — one source file. Provenance/integrity.
source_idstringLogical source identifier. CONSTANT = 'hrsa_opais_ce_registry'. Identifies the dataset lineage.
is_currentbooleanCurrent-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_keystringS3 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_idstringS3 object version id of the bronze file. CONSTANT (single value). Provenance.
vintage_tagstringVintage label. CONSTANT = the literal string 'none' (not NULL). No meaning here; boilerplate.
drug_source_version_idstringDrug-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

describe_data_source(source_fqn="drug_silver.fact_hrsa_ce_npi")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_hrsa_ce_npi WHERE id_340b = '<value>' AND npi_number = '<value>' AND npi_state = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keynpi_number
Joinhow_to_join(source_fqn="drug_silver.fact_hrsa_ce_npi")
Raw doorAthena (read-only via query_substrate): drug_silver.fact_hrsa_ce_npi

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