Provider-to-Operator Crosswalk

Description

The entity dimension for the catalogue. Every other product here is keyed on a CMS Certification Number (CCN) or an NPI — a facility. This one resolves a facility to the company that owns it, and where that company is publicly traded, to a ticker. It is what turns facility-level rows into company-level analysis.

Two files:

1. operator_crosswalk — the current mapping. One row per (CCN, operator).
2. operator_crosswalk_history — point-in-time observations, one row per (CCN, operator, monthly CMS snapshot), so a facility can be attributed to the operator that held it in a given month rather than to its present owner.

A facility can legitimately appear more than once. A hospital leased from a REIT and run by an operator maps to both; filter on entity_class to pick a lens.

Provenance — read this before using ticker

The mapping is built in two links, and they do not have the same standing:

match_method and match_confidence are exported so a buyer can separate them. matched_owner_name carries the exact CMS string a pattern hit, so any mapping can be audited back to source.

• Source: CMS Hospital All Owners + Hospital Enrollments (data.cms.gov, monthly); existing SNF/HHA/hospice All Owners rows; CMS HCRIS cost report provider names as a fallback.
• Refresh cadence: monthly, on the CMS ownership file release.
• Row count: 4,412 crosswalk rows covering 4,037 distinct CCNs (2026-08-29 export, rebuilt the same day; the history file holds 113,074 rows).
• Ticker coverage: 2,139 CCNs mapped to a ticker, across 28 tickers (measured on the shipped 2026-09-11 export, after CCN normalisation; Amedisys is no longer a separate listed company after UnitedHealth's acquisition closed, and its facilities carry UNH).
• History: monthly snapshots from 2022-11 onward (42 published pairs). The first_seen/last_seen columns exist in the schema but are empty in this release. All 4,412 rows are null on both. Derive the first and last month a pair appears from operator_crosswalk_history instead.

Coverage by entity class:

Rows by resolution method:

Per-product ticker coverage, since this is what buyers ask:

The price-transparency row is a moving figure — a crawl targeting hospitals owned by listed operators is in progress, so re-measure before quoting it. The backfill plan behind that crawl is available on request.

Table: operator_crosswalk

Snowflake: HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK

Table: operator_crosswalk_history

Snowflake: HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK_HISTORY

Example queries (Snowflake)

-- 1. Hospital financials for one listed operator, current mapping.
SELECT f."ccn", f."provider_name", f."fy_end_year", f."total_revenues", f."net_income"
FROM HEALTHPARSE_DATA.HCRIS_HOSPITAL_FINANCIALS.HCRIS_HOSPITAL_FINANCIALS f
JOIN HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK x
  ON x."ccn" = f."ccn"
WHERE x."ticker" = 'HCA'
  AND x."entity_class" = 'public'   -- exclude a REIT landlord row for the same CCN
  AND f."fy_end_year" >= 2020;

-- 2. Point-in-time: which hospitals did Tenet hold as of June 2024?
SELECT DISTINCT h."ccn"
FROM HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK_HISTORY h
WHERE h."operator_key" = 'tenet_healthcare'
  AND h."snapshot_date" = '2024-06-03';

-- 3. Facilities that left an operator's file. A screening query, not a
--    transaction feed — see the caveat on dropouts below. The crosswalk's own
--    first_seen/last_seen columns are empty in this release, so derive the
--    window from the history table.
SELECT h."ccn", h."operator_key",
       min(h."snapshot_date") AS first_seen,
       max(h."snapshot_date") AS last_seen
FROM HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK_HISTORY h
GROUP BY h."ccn", h."operator_key"
HAVING max(h."snapshot_date") < (SELECT max("snapshot_date")
                                   FROM HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK_HISTORY)
ORDER BY last_seen DESC;

-- 4. Only mappings backed by a CMS ownership filing.
SELECT * FROM HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK
WHERE "match_method" = 'cms_ownership_name';

Known limits

Two structural limits. Read both before writing a join.

1. ccn is not unique — a naive join fans out and double-counts

The file holds 4,412 rows over 4,037 distinct CCNs. 357 CCNs appear more than once, and the duplicates are not all the benign REIT-plus-operator pair described above: 14 CCNs map to two conflicting tickers — CCN 030107 resolves to both SGRY and THC, CCN 109079 to both UNH and AVAH, and twelve more in the same shape.

The duplicate count is higher than the row count alone suggests because normalising short CCNs to six characters merged what had been two separate spellings of the same facility (for example 60014 and 060014) into one CCN, so rows that used to look like two different facilities now sit on one key.

This matters because it is silent. Join HCRIS financials or negotiated rates to this table on ccn alone and a duplicated CCN's facility rows are emitted once per crosswalk row. Every sum() downstream — revenue, beds, capital purchases — is then inflated for exactly those facilities, and the row counts still look plausible. The 14 conflicting-ticker CCNs are worse than inflation: the same hospital's revenue lands in two different companies' totals.

Recommended de-duplication. Reduce to one row per CCN before joining, preferring the better-evidenced mapping:

-- One row per CCN: highest match_confidence first, then a CMS-filed
-- ownership record over a facility-name inference.
SELECT * EXCLUDE (rn) FROM (
  SELECT x.*,
         row_number() OVER (
           PARTITION BY x."ccn"
           ORDER BY CASE x."match_confidence" WHEN 'high' THEN 0 ELSE 1 END,
                    CASE x."match_method"
                      WHEN 'cms_ownership_name'    THEN 0
                      WHEN 'cms_enrollment_parent' THEN 1
                      ELSE 2 END,
                    x."ownership_pct" DESC NULLS LAST,
                    x."operator_key"
         ) AS rn
  FROM HEALTHPARSE_DATA.OPERATOR_CROSSWALK.OPERATOR_CROSSWALK x
) WHERE rn = 1;

The ordering is deliberate: match_confidence is the coarse split (high for the two CMS-filing methods, medium for hcris_provider_name), then cms_ownership_name — an ownership record CMS published — beats cms_enrollment_parent, which beats a facility-brand name match. If you are doing operator roll-ups rather than landlord analysis, add AND "entity_class" <> 'reit' so a REIT landlord row cannot win the CCN. If you want only CMS-asserted mappings, filter match_method <> 'hcris_provider_name' instead of ranking.

Our own derived products already apply this rule: the hospital_capex_a7_by_operator and hospital_capex_a7_operator_calendar files collapse the crosswalk to one row per CCN before joining, so they cannot fan out.

2. ccn formatting was repaired on 2026-09-11

ccn is documented as a 6-character zero-padded string. Until the 2026-09-11 export, 399 of 4,412 rows were not: 374 were 5 characters (a lost leading zero; 134 of them carried a ticker) and 25 were 7–9 characters (a 6-character CCN with a CMS sub-unit suffix appended, e.g. 45003300, 330195004, 07003301).

The 5-character rows were the expensive ones — 371 of the 374 match a live HCRIS CCN once left-padded, so those hospitals silently vanished from every ticker roll-up built on an equality join. The export now normalises: left-pad to 6 below length 6, and take the first 6 characters after restoring any lost leading zero above it. That recovers a live HCRIS CCN for 23 of the 25 long values; the two exceptions (33S19501 → 33S195, 39T204A → 39T204) are alpha sub-unit codes for psych/rehab units that never file their own cost report, so they stay unmatched — but they are now 6 characters, as documented.

Effect on join rates (FY2019+ HCRIS rows): 27.78% → 29.68%; on the A-7 capex panel, 28.56% → 30.51% (10,304 → 11,008 matched rows). HCA picked up CCN 060014 (Presbyterian St Luke's Medical Center, Denver), which had been sitting in the crosswalk as 60014.

Note that normalising collapses duplicate representations of the same CCN, so distinct-CCN counts fall even as match counts rise: 4,299 → 4,037 distinct CCNs, and 2,240 → 2,139 distinct ticker-bearing CCNs. The number that went up is the one that matters — HCRIS rows reaching a ticker: 17,767 → 18,586.

If you hold an export dated before 2026-09-11, left-pad ccn to 6 yourself before joining.

Known limits

Verified against the built table, not assumed.

• Tickers are point-in-time as of 2026-07-28 and are not maintained continuously. The registry flags entries whose corporate status was known to be in flux at authoring — the Concentra/Select Medical separation, PACS Group's listing status, and Amedisys's acquisition status. Treat ticker as a convenience key, not a security master.
• Denormalised ticker on history rows is the current one. A row for snapshot_date 2023-01 carries the operator's 2026 classification. For an operator that IPO'd, went private, or was acquired during the window, this is a look-ahead artefact. Join on operator_key and apply your own security master if that matters.
• Name patterns are a judgement layer and were wrong before they were right. Two false positives were found and corrected during the build: the bare token UHS also matches UHS-PRUITT (PruittHealth, an unrelated nursing-home operator, 94 CCNs), and a bare MERCY HEALTH pattern collided with at least three distinct systems. Both are now separated. Others of the same kind may remain — matched_owner_name exists so a buyer can check.
• Bon Secours Mercy is understated, deliberately. It is matched on BON SECOURS and its Cincinnati entities only, because the broader pattern was unsafe. Its true facility count is higher than the 75 CCNs shown.
• The history file covers hospitals only. All 42 walked snapshots are the Hospital All Owners family, so only hospital CCNs have any point-in-time history at all. SNF, HHA and hospice rows have a current mapping and nothing else. CMS publishes equivalent monthly snapshots for those families and they have not been walked yet.
• first_seen and last_seen ship empty. Both columns are null on all 4,412 rows in this release. They are kept in the schema so the column layout does not change when the backfill runs. Until then, derive the window from operator_crosswalk_history.
• A facility dropping out of an operator's file is a weak divestiture signal. Individual CCNs do drop out and reappear across months as enrolments are re-filed. Verify any single case against Provider Change Events (ownership_change, sourced from the CMS CHOW file) before treating it as a transaction.
• Coverage grows over the history window for reasons that are not all real consolidation. Matched pairs rise from ~2,384 in the earliest 2022 snapshot to ~4,000 in the latest. Some is genuine M&A; some is CMS ownership reporting becoming more complete. Do not read the series as a clean consolidation measure.
• Absence is not evidence of independence. ~6,000 hospital CCNs map to no operator. Most are genuinely independent or small local systems, but some are chain-owned through entity names the registry does not yet cover.
• hcris_provider_name matches are the weakest tier — 134 of 4,412 rows. They exist to catch facilities whose brand carries the operator name where no ownership filing was matched.