# HRSA 340B Covered Entity Registry (OPAIS)

`drug_silver.dim_hrsa_covered_entity` · Provider identity · status live · 92,814 rows

The HRSA OPAIS registry of 340B covered entities — hospitals/clinics eligible to buy outpatient drugs at 340B ceiling prices, with entity type, name/sub-name, participation status + start date, grant number, address, and contacts. Identifies 340B-eligible providers (the spread between 340B acquisition and billed price is a known overpayment vector).

**Grain.** One row per 340B-registered entity SITE, identified by its 340B ID (id_340b). 92,814 rows = 92,814 distinct id_340b. The internal HRSA key ce_id is 1:1 with id_340b (also 92,814 distinct), so either alone is a valid minimal key — id_340b is the canonical business key. This is a single point-in-time snapshot (observed_period = 2026-05-15), so no date column is needed in the key.

**How to use it.** Count active 340B sites by state and entity type: SELECT street_address_state, entity_type, count(*) FROM drug_silver.dim_hrsa_covered_entity WHERE participating = true GROUP BY 1,2 ORDER BY 3 DESC. Answers 'how many currently-active 340B-eligible provider sites of each type are in each state' — i.e. which providers get 340B discounted drug acquisition cost (vs WAC/ASP), the basis for 340B carve-out and duplicate-discount analysis. To roll up to organizations, group on regexp_replace(id_340b,'-[0-9A-Za-z]+$','') instead.

**Traps.**
- NOT an active-providers list: 31% of rows (28,352) are participating=false (terminated/decertified or pending). You MUST filter participating=true to count or join only active 340B sites; the table deliberately retains inactive entities.
- id_340b is the SITE grain, not the organization grain. One parent organization registers many child sites via the -NN suffix (73,861 parents → 92,814 sites; Cleveland Clinic alone = 498 sites). To analyze at the org level, strip the trailing -[alnum]+ suffix (regexp_replace(id_340b,'-[0-9A-Za-z]+$','')) and aggregate. Counting distinct id_340b counts sites, not organizations.
- Single static snapshot only (observed_period/asserted_at/effective_period_start/effective_period_end/sha256_content_hash/bronze_object_key all = one value, 2026-05-15). All the temporal, vintage, and provenance columns are constants here — they exist for the cross-table snapshot/versioning framework, not for time-series analysis within this table. is_current=true on every row, so it currently de-dupes nothing.
- effective_period_end EQUALS effective_period_start (both 2026-05-15) — this is a point-in-time observation, NOT an SCD-2 validity interval. Do not use [effective_period_start, effective_period_end] as a date range.
- Nulls carry meaning and code-conditional presence: grant_number is null for ~all DSH/RRC/CAH/SCH/PED hospital types (they qualify via the DSH adjustment, not a grant) and populated for grantee types (CH/FP/TB/STD/RW*) — its presence is driven by entity_type. certified_decertified_date is null for 8,550 entities that never had a certify/decertify event (null ≠ inactive). sub_name is null for 9,164 sites with no DBA. drug_source_version_id is 100% null.
- ce_id looks like a tidy key but is a sparse internal HRSA sequence (1–117,822 with gaps) — use id_340b as the durable business key for joins; ce_id can be reassigned/non-portable across HRSA vintages. Also: name is free-text and inconsistently cased (UPPER vs Title case for the same org) — normalize before name-based matching; the leading letters of id_340b usually but not always match entity_type (4,243 mismatches).

| column | type | meaning |
|---|---|---|
| id_340b | string | Canonical 340B identification number for the SITE and the table's grain key (92,814 distinct = row_count). Structured: a parent-entity prefix (e.g. CHC00018, RRC360180) plus a -NN child-site suffix; the same parent organization registers many sites (73,861 distinct parents, avg 1.26 sites each, max 498 = Cleveland Clinic). Strip the trailing -[alnum]+ to group sites under their parent. The leading letters usually equal entity_type but not always (4,243 rows differ, e.g. entity_type CH carries a CHC prefix). |
| ce_id | int | HRSA-internal numeric covered-entity ID. 1:1 with id_340b (92,814 distinct), so it is an alternate minimal key. Sparse sequence ranging 1–117,822 with gaps — it is an assigned ID, NOT a dense row index, and is not stable/meaningful across HRSA snapshots the way id_340b is. |
| entity_type | string | 340B eligibility category code (23 values). Largest: DSH=42,550 (Disproportionate Share Hospitals), CH=16,737 (Community Health Center / FQHC grantees), STD=6,289, RRC=6,077 (Rural Referral Center), CAH=5,588 (Critical Access Hospital), FP=4,022 (Family Planning), SCH=2,835 (Sole Community Hospital), PED=2,773 (Children's), TB=2,453, plus FQHCLA, RWI/RWII (Ryan White), HV (Hemophilia), CAN (Cancer/PCC), HM (Homeless), UI, NH, SPNS. Determines the legal eligibility pathway and roughly whether a grant_number applies. |
| name | string | Primary registered organization name (always populated, 0 nulls). Free-text, inconsistently cased across rows (e.g. 'STONE MOUNTAIN HEALTH SERVICES' vs 'Stone Mountain Health Services') — normalize before joining on name. |
| sub_name | string | Site-level / sub-organization 'doing business as' name (e.g. a specific clinic under the parent). NULL for 9,164 rows where the site has no distinct sub-name. |
| participating | boolean | Boolean: is the site CURRENTLY active in 340B. true=64,462 (~69%), false=28,352 (~31%). CRITICAL: the registry retains terminated/decertified AND not-yet-active entities, so this table is NOT a list of only active 340B sites — you MUST filter participating=true to count active 340B providers. Cannot be derived from dates alone: 1,228 participating=false rows have a FUTURE participating_start_date (approved, pending go-live). |
| participating_start_date | date | Date the site's 340B participation began/begins. Ranges 1992-12-01 (program inception era) to 2026-07-01 (future-dated pending starts). Never null. Not unique (many share dates) — not a key. |
| grant_number | string | Federal grant award number for grant-based eligibility (e.g. H37RH00048), 3,452 distinct, NOT unique (a grant covers many sites). Present only for grant-funded types (CH, FP, TB, STD, FQHCLA, RW*, HV, HM all 100% have it); NULL for hospital types that qualify via DSH adjustment (DSH, RRC, CAH, SCH, PED, CAN, etc. are ~all null). |
| street_address_line_1 | string | Site street address line 1. Fully populated (0 nulls). |
| street_address_city | string | Site city. Fully populated. |
| street_address_state | string | Two-letter state/territory of the site. Fully populated; 56 distinct = 50 states + DC + 5 territories (PR=191, VI=14, AS=12, MP=11, GU=7). Top: CA 7,350, TX 5,627, NY 5,448, OH 5,126. |
| street_address_zip | string | Site ZIP code (string). Fully populated. 5-digit ZIP as observed. |
| authorizing_official_name | string | Name of the entity's authorizing official (the legally responsible signer). Fully populated. Note double-spaces between first/last in source data. |
| authorizing_official_title | string | Title of the authorizing official (e.g. CEO, Executive Director). |
| primary_contact_name | string | Day-to-day 340B contact name. 1 null only. |
| primary_contact_title | string | Title of the primary contact (e.g. COO, Director of Pharmacy, 340B Coordinator). |
| certified_decertified_date | date | Date of the most recent annual recertification or decertification event. NULL for 8,550 rows (entities that have never been through a certify/decertify event). Range 2014-08-08 to 2026-05-14. A null here does NOT mean inactive — read participating for status. |
| edit_date | date | Date the entity record was last edited in HRSA OPAIS. Never null; range 2015-08-24 to 2026-05-14. Use as the per-row freshness/last-modified signal. |
| observed_period | date | Snapshot date the data was captured = 2026-05-15 for ALL rows (single value). The de-facto 'as-of' date of the table. |
| asserted_at | date | Provenance: when this fact was asserted by the pipeline. Constant 2026-05-15 across all rows in this snapshot. |
| effective_period_start | date | Start of the effective interval for the record; constant 2026-05-15. Profiler picks this as the time column but it carries no within-table variance. |
| effective_period_end | date | End of the effective interval; constant 2026-05-15 — EQUAL to effective_period_start. This is a point-in-time snapshot, NOT an open-ended SCD-2 interval; do not treat it as a validity range. |
| cms_vintage_sequence | int | Vintage/ordering sequence from the shared drug-source framework. Constant 1 (only one vintage loaded). |
| parse_revision | int | Parser revision number. Constant 1. |
| parser_artifact_hash | string | SHA-256 of the parser artifact used (provenance/reproducibility). Constant across the snapshot. |
| source_url | string | Upstream source URL — https://340bopais.hrsa.gov/Reports (HRSA 340B OPAIS public reports). Constant. |
| sha256_content_hash | string | SHA-256 of the ingested bronze source file. Single value (one source file) — confirms this is exactly one snapshot. |
| source_id | string | Logical source identifier = 'hrsa_opais_ce_registry'. Constant; identifies the HRSA OPAIS covered-entity feed. |
| is_current | boolean | SCD current-version flag. TRUE for all 92,814 rows (no superseded versions retained). Because every row is current, it does NOT de-duplicate anything in this table today, but keep the is_current=true filter in queries for forward-compatibility when future snapshots land. |
| bronze_object_key | string | S3 key of the raw bronze object (drug/bronze/hrsa_opais_ce_registry/2026-05-15/...340B_CoveredEntityJson_Daily_2026-05-15.zip). Single value = one ingested file. |
| s3_version_id | string | S3 object version id of the bronze file. Single value. Lineage only. |
| vintage_tag | string | Vintage label = literal string 'none' for all rows (no meaningful vintage tag applied). Do not treat 'none' as a missing/null sentinel to act on. |
| drug_source_version_id | string | Version id linking to the drug-source-version registry. 100% NULL in this table — not populated for this source; ignore for joins. |

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