Hospital System Hierarchy (parent / ultimate parent / ownership)

What it is

1. 1A dimension table of US hospital health SYSTEMS (the parent corporate/ownership entity that operates one or more hospitals/facilities), used as the system-level grouping layer above individual hospitals so cost data (chargemaster prices, negotiated rates) can be rolled up and compared by owning system, ownership type, size, and geography.

Grain

How to read it

1. 1Roll 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

1. 1system_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.
2. 2Half 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.
3. 3The 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.
4. 4facility_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.
5. 5No 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.
6. 6Join 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'.

Columns (35)

Query it

describe_data_source(source_fqn="vlada_curated.dim_hospital_system")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.dim_hospital_system WHERE system_id = '<value>' LIMIT 100")