Provider identity

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

drug_silver.dim_facility_affiliation·live·dim·other·Provider Enrollment & Ownership (PECOS)

Rows

N/A

Columns

History

2026-05-19 (single snapshot; static) to 2026-05-19

Card

yesgrain, traps, filters

Typed tools

What it is

1A CMS-sourced bridge table linking individual Medicare-enrolled clinicians (by NPI / individual PAC ID) to the healthcare facilities they are affiliated with (by CMS Certification Number / CCN).
2It is the verbatim CMS "Facility Affiliation" file from the Provider Data Catalog (Doctors & Clinicians / Care Compare), loaded into the drug-cost silver layer as a provider-to-facility affiliation spine.
3Used to attribute clinicians to hospitals/SNFs/dialysis/etc. and to join provider-level cost/utilization data up to the facility (and facility type) level.

Grain

Key Grain

Key	Grain
`ind_pac_idfacility_ccn`	1One row per individual clinician-facility affiliation: exactly one row per (ind_pac_id, facility_ccn) pair. 2Verified — distinct (ind_pac_id, facility_ccn) = 2,247,604 = full row_count. 3Each 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). 4This is a single 2026-05-19 snapshot, so there is no time/version dimension in the grain.

ind_pac_idfacility_ccn

1One row per individual clinician-facility affiliation: exactly one row per (ind_pac_id, facility_ccn) pair.
2Verified — distinct (ind_pac_id, facility_ccn) = 2,247,604 = full row_count.
3Each 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).
4This is a single 2026-05-19 snapshot, so there is no time/version dimension in the grain.

How to read it

1To 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'.
2To roll affiliations up by setting: SELECT facility_type, COUNT(DISTINCT ind_pac_id) FROM drug_silver.dim_facility_affiliation GROUP BY facility_type.
3To 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

1Grain 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.
2facility_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.
3facility_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.
4This 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.
5This 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.

Columns (25)

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

describe_data_source(source_fqn="drug_silver.dim_facility_affiliation")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.dim_facility_affiliation WHERE ind_pac_id = '<value>' AND facility_ccn = '<value>' LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`npi`
Join	`how_to_join(source_fqn="drug_silver.dim_facility_affiliation")`
Raw door	Athena (read-only via query_substrate): drug_silver.dim_facility_affiliation
Note	Iceberg table drug_silver.dim_facility_affiliation (physically in drug_silver for M-2 envelope sharing; conceptually a provider-identity bridge). Runner: pipelines/drug/ingest/facility_affiliation_runner.py.

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