# Hospital System Hierarchy (parent / ultimate parent / ownership)

`vlada_curated.dim_hospital_system` · Provider identity · status live

The hospital-system hierarchy: normalized system name, parent_system_id and ultimate_parent_id, ownership_level + ownership_type, EIN, and HQ. A PARTIAL ownership-rollup (system → parent → ultimate parent) — the closest thing we have to ownership intelligence until the PECOS "All Owners" datasets are ingested.

**Grain.** One row per hospital system, identified by system_id (an HSI-prefixed surrogate key). Verified: 646 rows = 646 distinct system_id. It is a single static snapshot (no versioning), so system_id alone is the full key. 639 rows come from AHRQ 2023 Compendium data; 7 are manually-added national specialty chains (LTACH/rehab/behavioral).

**How to use it.** Roll hospital cost data up to the owning system: join dim_hospital_system.system_id = dim_facility.system_id, then dim_facility to the chargemaster/rate facts, to answer e.g. 'compare median negotiated price for CPT 99213 across the 5 largest for-profit hospital systems.' Example: SELECT s.system_name, s.ownership_type, s.facility_count, CAST(json_extract_scalar(s.metadata,'$.net_revenue') AS double)/1e9 AS net_rev_billions FROM dim_hospital_system s WHERE s.ownership_type='for_profit' ORDER BY net_rev_billions DESC — returns the for-profit systems ranked by AHRQ-reported net revenue (HCA tops near $52B), with their facility footprint, ready to scope a system-level price comparison.

**Traps.**
- system_id is the ONLY key. system_name is NOT unique (640 distinct of 646; 6 names — Trinity Health, Baptist Health, Methodist Health System, Mercy Medical Center, Memorial Health System, Southeast Health — each map to 2 different system_ids). Never join or dedup on system_name.
- Half the schema is structurally empty. 100% NULL columns: system_name_normalized, common_name, legal_name, ein, parent_system_id, ultimate_parent_id, hq_geo_id, hq_address, hq_zip, total_employees, annual_revenue_millions, shared_mrf_url, mrf_url_pattern, investor_relations_url, status_reason, effective_date. ownership_level and status are constant (1 / 'active'). Don't build logic on these.
- The real economics live in the metadata JSON, NOT in the look-alike columns. Revenue = metadata.net_revenue (raw $, up to $52.2B for HCA), physicians = metadata.total_physicians, discharges = metadata.annual_discharges, teaching/multistate flags = metadata.* — while the annual_revenue_millions and total_employees COLUMNS are 100% NULL. The 7 MANUAL_SPECIALTY_CHAIN rows have metadata '{}' and NULL beds/revenue/ownership, so any metadata-based metric silently excludes them.
- facility_count is AHRQ's self-reported total and is systematically LARGER than the facilities actually linked in dim_facility (e.g. HCA 185 vs 165 linked, Trinity 105 vs 94). It is a source attribute, not a count of platform rows — don't use it as ground truth for how many hospitals you can actually join.
- No history / no is_current: effective_date is entirely NULL and _synced_at is one constant timestamp, so this is a single 2023-vintage static snapshot. parent_system_id/ultimate_parent_id are NULL so NO system hierarchy is encoded — every row is flat at ownership_level 1; you cannot roll sub-systems into ultimate parents from this table.
- Join path to cost data is via dim_facility.system_id (FK), then facility→chargemaster; 644/646 systems have at least one linked facility (the 2 without are facility_count=0 manual chains), but dim_facility ALSO has 1,935 rows with NULL system_id (independent/unaffiliated hospitals) that belong to no system here. ownership_type='government_state' means government-OWNED (AHRQ code 3, e.g. Valleywise, Alameda Health), not 'state of incorporation'.

| column | type | meaning |
|---|---|---|
| system_id | string | PK. Surrogate system key, format HSI + 8 digits (e.g. HSI00000012, range HSI00000008..HSI00001595). 646 distinct = the only true key. This is the FK that dim_facility.system_id and dim_hospital_linkage_audit.system_id join back to. |
| system_name | string | Display name of the system (e.g. 'HCA Healthcare','Ascension Health'). 100% populated but NOT unique — 640 distinct over 646 rows; 6 names are shared by 2 different system_ids (Trinity Health, Baptist Health, Methodist Health System, Mercy Medical Center, Memorial Health System, Southeast Health). Do not use as a join key. |
| system_name_normalized | string | Intended normalized/match name. 100% NULL — unused. |
| common_name | string | Intended common/dba name. 100% NULL — unused. |
| legal_name | string | Intended legal entity name. 100% NULL — unused. |
| ein | string | Intended employer ID number (tax id), the bridge to Form 5500 / IRS data. 100% NULL — not yet populated. A known coverage gap, not a real value here. |
| parent_system_id | string | Intended self-referential FK to an immediate parent system. 100% NULL — no hierarchy is modeled; every row is a top-level system. |
| ultimate_parent_id | string | Intended self-referential FK to the ultimate parent. 100% NULL — unused. |
| ownership_level | bigint | Depth in the ownership tree. Constant = 1 for every row (all are top-level). Carries no information in current data. |
| ownership_type | string | Profit/control class, derived from AHRQ ownership code: 'nonprofit' (439, code 1), 'government_state' (141, code 3 = state/local government-OWNED e.g. Valleywise, Alameda Health — means govt-owned, NOT state-of-incorporation), 'for_profit' (40, code 2), NULL (26 = code 5 'other'/unmapped or the 7 manual rows). Populated for 620/646. |
| system_type | string | Specialty classification. Almost entirely NULL (639). Only the 7 manual rows are tagged: specialty_ltach (4), specialty_behavioral (2), specialty_rehab (1). AHRQ rows are general acute systems and are left NULL here. |
| hq_geo_id | string | Intended FK to a geography dim. 100% NULL — unused. |
| hq_address | string | HQ street address. 100% NULL — unused. |
| hq_city | string | HQ city (e.g. 'Nashville','Gaithersburg'). Populated 644/646. |
| hq_state | string | HQ state 2-letter code. 100% populated. Concentrated in CA(58),NY(45),PA(34),OH(32),FL/TX(26). This is system HQ location, not where its facilities operate (systems can be multi-state — see metadata.is_multistate). |
| hq_zip | string | HQ ZIP. 100% NULL — unused. |
| facility_count | bigint | AHRQ-REPORTED number of facilities in the system (int, range 0..185; HCA=185, UHS=158, CommonSpirit=145). 100% populated. GOTCHA: this is the source's declared count and is consistently HIGHER than the number of facilities actually linked in dim_facility (HCA 185 declared vs 165 linked) — it is NOT a live count of platform rows. The 2 manual behavioral chains have facility_count=0. |
| total_beds | double | Total staffed beds across the system (double, 13..37,395; HCA=37,395). Populated for 635/646 (AHRQ only; all 7 manual rows are NULL). |
| total_employees | string | Intended employee count. 100% NULL in the column — unused (note: physician count lives inside metadata.total_physicians instead). |
| annual_revenue_millions | string | Intended revenue (string). 100% NULL in the COLUMN. The real revenue figure lives in metadata.net_revenue (raw dollars, populated 634/646, $28.7M..$52.2B, HCA top). Use metadata, not this column. |
| shared_mrf_url | string | Intended shared machine-readable-file URL for the system's negotiated-rate transparency file. 100% NULL — unused. |
| mrf_url_pattern | string | Intended MRF URL pattern. 100% NULL — unused. |
| website_url | string | System website (e.g. https://www.adventisthealthcare.com). Populated 640/646 — the best-populated descriptive URL field. |
| investor_relations_url | string | Intended IR page (for public for-profit systems). 100% NULL — unused. |
| status | string | Lifecycle status. Constant = 'active' for all 646 rows. Carries no information currently. |
| status_reason | string | Reason for status. 100% NULL — unused. |
| effective_date | string | Intended effective/as-of date for SCD-style history. 100% NULL — there is NO effective dating; treat the table as one static snapshot. (Despite the profiler picking this as the 'time column', it has zero values.) |
| data_source | string | Provenance of the row: 'AHRQ' (639, AHRQ Compendium of US Health Systems 2023) or 'MANUAL_SPECIALTY_CHAIN' (7, hand-added national specialty operators). This split governs which other fields are populated. |
| source_version | string | Source vintage. '2023' for the 639 AHRQ rows; NULL for the 7 manual rows. |
| created_at | timestamp | Row creation timestamp (2026-04-04..2026-04-07). Loading metadata, not business history. |
| updated_at | timestamp | Row last-update timestamp (same April-2026 window). Equals/near created_at — no ongoing updates. |
| created_by | string | Creator. Constant 'system'. |
| updated_by | string | Updater. Constant 'system'. |
| metadata | string | JSON blob — the RICHEST part of the table for AHRQ rows. Empty '{}' for all 7 manual rows. Keys (AHRQ): net_revenue (raw $, 634), annual_discharges (635), total_physicians (639), is_multistate bool (639), has_major_teaching bool (639), acute_hospital_count (639), ahrq_ownership_code (1/2/3/5, 638), onekey_id (IQVIA OneKey id, 638 — near-unique, 638 distinct), aha_sysid (AHA system id, only 310 distinct over 639 — many NULL/shared, NOT a key), website_extraction (rare). To get system financials/discharges/teaching status you MUST parse this JSON; the dedicated columns are NULL. |
| _synced_at | timestamp | ETL sync-to-lake timestamp. Constant 2026-04-17 18:45:59 UTC for every row — confirms a single bulk sync (no incremental snapshots). |

Tools that read it: resolve_provider

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