# CMS Facility Affiliation (Doctors & Clinicians) — NPI→CCN bridge

`drug_silver.dim_facility_affiliation` · Provider identity · status live

The CMS "Facility Affiliation" file from the Doctors and Clinicians / Provider Data Catalog — maps each clinician NPI to the facilities they are affiliated with (Hospital, Home health agency, Hospice, SNF, etc.), carrying the facility CCN. The standing NPI→CCN affiliation bridge used for provider substrate lookups and RBP duplicate-discount screening. Partitioned by facility_type so a single category scans without a full-table read.

**Grain.** One row per individual clinician-facility affiliation: exactly one row per (ind_pac_id, facility_ccn) pair. Verified — distinct (ind_pac_id, facility_ccn) = 2,247,604 = full row_count. Each row says "this clinician is affiliated with this facility." A clinician appears in multiple rows (one per affiliated facility; avg 2.39 facilities/provider) and a facility appears in many rows (avg 54.7 providers/facility). This is a single 2026-05-19 snapshot, so there is no time/version dimension in the grain.

**How to use it.** To list every clinician affiliated with a given hospital: SELECT npi, ind_pac_id, provider_first_name, provider_last_name FROM drug_silver.dim_facility_affiliation WHERE facility_ccn = '090012'. To roll affiliations up by setting: SELECT facility_type, COUNT(DISTINCT ind_pac_id) FROM drug_silver.dim_facility_affiliation GROUP BY facility_type. To attribute a provider-level cost/utilization metric to facilities, join your provider table to this on npi (or ind_pac_id) to fan it out to facility_ccn, then join facility_ccn to a facility/CCN dimension — using COALESCE(facility_type_ccn, facility_ccn) when you need the canonical numeric CCN.

**Traps.**
- Grain key is (ind_pac_id, facility_ccn), NOT (npi, facility_ccn). (npi, facility_ccn) is NOT unique: 44 rows collide because 14 clinicians are enrolled under two different ind_pac_ids and appear twice at the same facility (e.g. npi 1609495936 at CCN 100088 under PAC 0042395923 and 7416387147). Join/dedup on ind_pac_id to be safe; npi alone double-counts those people.
- facility_ccn is a STRING, 6 chars, zero-padded, and NOT always numeric. ~10,156 values contain a letter (alpha distinct-part units like '23T146'). Casting to int will drop leading zeros and break on alpha CCNs. Always keep/compare as a left-zero-padded string.
- facility_type_ccn is NULL 99.6% of the time and that NULL is meaningful-by-absence, not missing data. It is populated only for the ~8,702 alpha CCNs that map to a canonical numeric form (e.g. '23T146' -> '230146'). To get a clean all-numeric CCN, use COALESCE(facility_type_ccn, facility_ccn) — but note ~1,454 alpha CCNs still won't normalize.
- This is a SINGLE static snapshot dated 2026-05-19. is_current is true for every row, and effective_period_start / observed_period / asserted_at / vintage all have exactly 1 value — there is no time series and no SCD history yet, despite the bitemporal columns. effective_period_end = 2099-12-31 is the 'open/still valid' sentinel, not a real date. ingested_at and drug_source_version_id are 100% NULL.
- This is an affiliation BRIDGE, not a roster of facilities or of clinicians. It does not contain facility names, addresses, specialties, or any cost/rate amounts. Provider names are attributes of the clinician repeated on every facility row (don't treat name rows as distinct people). Join out to a facility dimension (via facility_ccn) or a clinician dimension (via ind_pac_id/npi) for those attributes.

| column | type | meaning |
|---|---|---|
| npi | string | Individual clinician's 10-digit National Provider Identifier. Always present (0 nulls), always 10 digits. 939,059 distinct. Identifies the person. NOTE: not a clean key here — 14 NPIs map to >1 ind_pac_id, so use ind_pac_id (not npi) as the individual key in joins. |
| facility_ccn | string | The facility's CMS Certification Number — the facility identifier and the second half of the grain. 6-character, zero-padded, MUST stay a string (e.g. '090012'). 41,087 distinct facilities. NOT always numeric: ~10,156 values contain a letter for special distinct-part units (e.g. '23T146' = a 'T'/rehab unit). CCN range encodes facility type (each CCN maps to exactly 1 facility_type). |
| ind_pac_id | string | Individual PECOS Associate Control ID — CMS's 10-digit identifier for the enrolled individual clinician. THE load-bearing individual key: every ind_pac_id maps to exactly 1 npi (0 violations), and (ind_pac_id, facility_ccn) is the unique grain. 939,073 distinct (slightly more than distinct NPIs because 14 people are enrolled under 2 PAC IDs). |
| provider_last_name | string | Clinician last name (free text, uppercase). Always present. Functionally attached to the clinician (ind_pac_id), repeated on every facility row for that clinician. |
| provider_first_name | string | Clinician first name (uppercase). Always present. |
| provider_middle_name | string | Clinician middle name/initial. NULL ~34% (763,234 rows) — sparse-by-nature, not a meaningful flag; simply absent for many people. |
| provider_suffix | string | Name suffix (Jr/Sr/III etc.). NULL ~98% (2,212,006 rows) — sparse; absence just means no suffix, not missing data. |
| facility_type | string | Clean 7-value category of the facility, derived from the CCN range: Hospital (85%, 1.9M), Home health agency, Hospice, Nursing home, Dialysis facility, Inpatient rehabilitation facility, Long-term care hospital. Always present; 1 type per CCN. This is the column to use to filter affiliations by setting. |
| facility_type_ccn | string | GOTCHA COLUMN. NULL in 99.6% of rows (2,238,902). When non-null (8,702 rows) it is the NORMALIZED all-numeric CCN for facilities whose facility_ccn carries an alpha distinct-part suffix — e.g. facility_ccn '23T146' -> facility_type_ccn '230146'. It is a strict subset of alpha CCNs (every non-null here has an alpha facility_ccn; only IRF/Nursing-home 'T'-style units are mapped; ~1,454 other alpha CCNs get no value). NULL just means facility_ccn is already the canonical number — it does NOT mean missing. |
| observed_period | date | Date the source data was observed/loaded. Constant = 2026-05-19 across all rows (single snapshot). |
| asserted_at | date | Date the affiliation fact was asserted. Constant = 2026-05-19. Provenance/bitemporal bookkeeping, not analytically varying here. |
| effective_period_start | date | Bitemporal validity start. Constant = 2026-05-19. (Profiler picked this as the time column, but it has only 1 distinct value — the table is static.) |
| effective_period_end | date | Bitemporal validity end. Constant = 2099-12-31, the open-ended/far-future sentinel meaning 'still in effect'. |
| cms_vintage_sequence | int | CMS source vintage counter. Constant = 1 (first/only vintage loaded). |
| parse_revision | int | Parser revision number. Constant = 1. |
| parser_artifact_hash | string | SHA256 of the parser artifact that produced these rows (lineage). Constant single value across the snapshot. |
| source_url | string | Origin URL of the raw CMS file. Constant — the data.cms.gov Facility_Affiliation.csv from the CMS Provider Data Catalog. Confirms this is verbatim public CMS data. |
| sha256_content_hash | string | SHA256 of the source file content (dedup/integrity). Constant single value (one source file). |
| source_id | string | Logical source name. Constant = 'cms_facility_affiliation'. |
| is_current | boolean | Snapshot currency flag. ALL rows = true (no superseded/historical rows yet). Since the table is a single snapshot, filtering on is_current is currently a no-op — but it exists for future multi-snapshot SCD behavior. |
| vintage_tag | string | Human-readable snapshot tag. Constant = 'facility_affiliation_2026-05-19'. The handle for this load. |
| ingested_at | timestamp | Ingestion timestamp. Entirely NULL (0 distinct) in this table — not populated for this source. |
| bronze_object_key | string | S3 key of the bronze object this silver row came from. Constant = 'drug/bronze/cms_facility_affiliation/2026-05-19/...csv'. Lineage to the raw landing file. |
| drug_source_version_id | string | Drug-pipeline source version id. Entirely NULL (0 distinct) — not used for this CMS source. |
| s3_version_id | string | S3 object version id of the source file. Constant single value (one versioned object). |

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