CareLens Facility Quality

Description

CMS Care Compare facility quality and star-rating data for 5 facility types: hospitals, skilled nursing facilities (SNF), home health agencies, hospice providers, and dialysis facilities. Each facility type ships as its own table rather than being unioned into one — the measure sets, ownership categories, and consumer-facing fields differ enough between facility types that a union would drop nearly every column. ccn is the canonical join key across all five and back to HCRIS Hospital Financials (for the hospital table specifically).

• Source: CMS Care Compare (data.cms.gov), one dataset per facility type.
• Refresh cadence: quarterly per CMS's own Care Compare release schedule.
• Row count: 47,127 across 5 files (2026-08-29 export).
• Vintage: 2026-07-31 for SNF, home health, hospice and dialysis; 2026-05-21 for hospitals (last CMS refresh observed per table at export time).

Table: facility_quality_hospitals

Snowflake: HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_HOSPITALS — 5,432 rows

Table: facility_quality_snf

Snowflake: HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_SNF — 14,715 rows

Table: facility_quality_home_health

Snowflake: HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_HOME_HEALTH — 12,559 rows

Table: facility_quality_hospice

Snowflake: HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_HOSPICE — 6,852 rows

Table: facility_quality_dialysis

Snowflake: HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_DIALYSIS — 7,569 rows

Example queries (Snowflake)

-- 1. Top-rated SNFs in a state with no recent abuse flag.
SELECT "ccn", "facility_name", "city", "overall_rating", "staffing_rating", "number_of_certified_beds"
FROM HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_SNF
WHERE "state" = 'FL' AND "abuse_icon" = FALSE
ORDER BY "overall_rating" DESC, "staffing_rating" DESC
LIMIT 25;

-- 2. Hospitals CMS rates below the national average on the safety measure
--    group, worst-rated first.
SELECT "ccn", "facility_name", "state", "overall_rating", "safety_national_comparison"
FROM HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_HOSPITALS
WHERE "safety_national_comparison" = 'Below the national average'
ORDER BY "overall_rating" ASC NULLS LAST
LIMIT 25;

-- 3. Dialysis facility count and average star rating by chain.
SELECT "chain_organization", count(*) AS facilities, avg("star_rating") AS avg_star_rating
FROM HEALTHPARSE_DATA.FACILITY_QUALITY.FACILITY_QUALITY_DIALYSIS
WHERE "chain_organization" IS NOT NULL
GROUP BY "chain_organization"
ORDER BY facilities DESC
LIMIT 10;