> For the complete documentation index, see [llms.txt](https://docs.dataplex-consulting.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.dataplex-consulting.com/data-catalog/cms-nh-compliance-dataset.md). # Nursing Home Compliance Intelligence Dataset ### About the Dataset The **Nursing Home Compliance Intelligence Dataset** is an analytics-ready intelligence layer over U.S. nursing home data from the **Centers for Medicare & Medicaid Services (CMS)** — a CCN-spined facility master, 7+ years of monthly facility history, effective-dated **PECOS** ownership with self-reported private-equity (PE) and real estate investment trust (REIT) flags, chain intelligence reconciled to CMS's own chain file, **Payroll-Based Journal (PBJ)** staffing compliance, conformed enforcement and deficiency history, and observed closure signals. It joins **Care Compare** (Nursing Home Compare / Five-Star), PECOS ownership, PBJ staffing, and enforcement feeds onto a single stable facility spine so operators, investors, lenders, and researchers can query nursing home ownership, staffing, and compliance without building the pipeline themselves. {% hint style="info" %} **Get Full Access** | [Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRT1QB) | [Free Trial](https://trial.dataplex-consulting.com) {% endhint %} {% hint style="warning" %} **Data coverage:** > Effective-dated ownership intervals: Feb 2023–present (PECOS). Labeled owner-name observations: Jan 2019–present (Care Compare). > {% endhint %} ### Quick Access **Tables**: FACILITY\_MASTER, FACILITY\_MONTH, OWNERSHIP\_HISTORY, OWNERSHIP\_EVENTS, CHAIN\_MEMBERSHIP\_HISTORY, STAFFING\_COMPLIANCE, ENFORCEMENT\_EVENTS, CLOSURE\_SIGNALS, CHAIN\_MONTH, SNAPSHOT\_CALENDAR\ **Sources**: CMS Care Compare (Nursing Home Compare / Five-Star), PECOS ownership, Payroll-Based Journal (PBJ), NH Chain Performance Measures, Provider of Services (POS)\ **Coverage**: 16K+ certified nursing homes, Jan 2019–present (7+ years of monthly snapshots)\ **Update Frequency**: Monthly — pipeline monitors CMS for new releases and loads within days of publication\ **Geography**: United States (all states and territories) {% hint style="info" %} **Signal, not prediction.** Every flag in this dataset is a published, deterministic, seed-versioned formula computed over CMS's own data. Flags are `0/1/NULL` — `NULL` means *not evaluable*, never a silent `0` — and each carries a reason code and an inputs-coverage column. There is no machine learning, no predictive scoring, and no name-based inference. See the [flag methodology](/data-catalog/cms-nh-compliance-dataset/methodology-flags.md). {% endhint %} ## Overview The Nursing Home Compliance Intelligence Dataset provides analytics-ready access to CMS nursing home compliance data including: * **FACILITY\_MASTER** — One row per nursing home (CCN): current identity, location, certification, Five-Star ratings, chain, and observed-history bookends * **FACILITY\_MONTH** — CCN × observed month history (1.2M+ rows) with monthly snapshot state, PBJ staffing, ID-based ownership summary, and the five computable compliance flags plus the operational-stress composite * **OWNERSHIP\_HISTORY** — Effective-dated PECOS ownership intervals (Feb 2023–present) with PE/REIT/holding-company typing, plus labeled Care Compare owner-name observations (Jan 2019–present) * **OWNERSHIP\_EVENTS** — Detected ownership-change events and official change-of-ownership (CHOW) confirmations * **CHAIN\_MEMBERSHIP\_HISTORY** — CMS-reported facility chain-membership intervals * **STAFFING\_COMPLIANCE** — CCN × quarter Payroll-Based Journal staffing with case-mix expectation and Five-Star staffing passthrough * **ENFORCEMENT\_EVENTS** — Conformed, de-duplicated deficiency citations and monetary penalties with true-repeat occurrence indexing * **CLOSURE\_SIGNALS** — Observed-only closure-type signals (snapshot disappearance, POS termination, beds-to-zero) * **CHAIN\_MONTH** — Chain × observed month rollups reconciled to CMS's own chain performance file (within ±5%) * **SNAPSHOT\_CALENDAR** — Per-feed-family observed-month calendar that anchors every vintage comparison ### Metadata Tables Every Dataplex data product includes these standard metadata tables: | Table | Purpose | | ----------------- | -------------------------------------------------------------- | | `FEEDS` | Dataset catalog — available tables, descriptions, update dates | | `FEEDS_FILES` | Batch load history with `is_latest` flag for data freshness | | `CHANGELOG` | Change log — data loads, schema changes, corrections | | `DATA_DICTIONARY` | Column descriptions for all tables | ## Ask it questions — Cortex Analyst and the semantic view The share includes the **`FACILITY_ANALYST` semantic view** — a semantic model over the facility master, the monthly history, and PBJ staffing, with relationships, synonyms, and business definitions built in. Point **Cortex Analyst** at it (in Snowsight: AI & ML → Cortex Analyst → select `DWV.FACILITY_ANALYST`) and ask questions in plain English: * *"Which states have the highest average nurse staffing hours per resident day?"* * *"Compare occupancy by ownership type."* * *"How many facilities are in each county in Ohio?"* New to these features? See Snowflake's documentation on [Cortex Analyst](https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst) and [semantic views](https://docs.snowflake.com/en/user-guide/views-semantic/overview). The same semantic model is queryable directly in SQL with the `SEMANTIC_VIEW()` construct: ```sql -- Average total nurse hours per resident day, by state SELECT * FROM SEMANTIC_VIEW( DWV.FACILITY_ANALYST DIMENSIONS state METRICS avg_total_nurse_hprd, facility_count ) ORDER BY avg_total_nurse_hprd DESC NULLS LAST; ``` ```sql -- Occupancy by ownership type — the semantic view resolves the -- facility joins for you SELECT * FROM SEMANTIC_VIEW( DWV.FACILITY_ANALYST DIMENSIONS ownership_type METRICS avg_occupancy, facility_count ) ORDER BY avg_occupancy DESC NULLS LAST; ``` ## Entity Relationship Diagram
Nursing Home Compliance Intelligence Entity Relationship Diagram

Facility-centric model: FACILITY_MASTER is the CCN spine; facility-keyed tables join on ccn, chain rollups join on chain_id, and every data table carries feed_id / feeds_files_id lineage back to FEEDS and FEEDS_FILES

The **CCN** (CMS Certification Number) is the spine of the product: `FACILITY_MASTER` holds one row per facility, and `FACILITY_MONTH`, `OWNERSHIP_HISTORY`, `STAFFING_COMPLIANCE`, `ENFORCEMENT_EVENTS`, and `CLOSURE_SIGNALS` all join back to it on `ccn`. Chain rollups in `CHAIN_MONTH` join on `chain_id`. Every data row also carries `feeds_files_id` (FK to `FEEDS_FILES`) so you can trace it to the exact batch that loaded it. ## Facility Tables ### Facility Master (FACILITY\_MASTER) One row per nursing home (CCN) — current-state identity, location, certification, Five-Star ratings, chain, and observed-history bookends. There is no SCD2 history and no `is_closed`; longitudinal state lives in `FACILITY_MONTH`. **Key Features:** * 16K+ certified nursing homes on a stable CCN spine * Current provider name, legal business name, address, county, and SSA county code * Certified beds, average residents per day, and current Five-Star ratings * `ccn_reuse_suspect` and `successor_ccn_hint` — observed-only heuristics for likely certification-number reuse (never name-based inference) #### Column Reference | Column | Type | Description | | ---------------------------------------- | --------- | -------------------------------------------------------------------- | | ccn | VARCHAR | 6-char CMS Certification Number — the product spine | | provider\_name | VARCHAR | Current provider name (latest snapshot) | | legal\_business\_name | VARCHAR | Current legal business name | | provider\_address | VARCHAR | Street address | | city | VARCHAR | City | | state | VARCHAR | State | | zip\_code | VARCHAR | ZIP code | | county | VARCHAR | County / parish | | ssa\_county\_code | VARCHAR | SSA county code | | ownership\_type | VARCHAR | CMS ownership type (for-profit, non-profit, government) | | number\_of\_certified\_beds | NUMBER | Certified beds | | average\_number\_of\_residents\_per\_day | NUMBER | Average daily resident census | | current\_chain\_id | VARCHAR | Current CMS-reported chain id | | current\_chain\_name | VARCHAR | Current CMS-reported chain name | | special\_focus\_status | VARCHAR | Special Focus Facility (SFF) / SFF-candidate status | | abuse\_icon | VARCHAR | Care Compare abuse icon flag | | overall\_rating | NUMBER | Five-Star overall rating | | health\_inspection\_rating | NUMBER | Five-Star health inspection rating | | staffing\_rating | NUMBER | Five-Star staffing rating | | qm\_rating | NUMBER | Five-Star quality measure rating | | first\_observed\_month | DATE | Earliest month this CCN appears | | last\_observed\_month | DATE | Latest month this CCN appears | | present\_in\_latest\_snapshot | BOOLEAN | TRUE if in the most recent snapshot month | | ccn\_reuse\_suspect | BOOLEAN | Observed-only reuse heuristic (state or name changed across history) | | successor\_ccn\_hint | VARCHAR | Address-match successor CCN hint for a disappeared CCN | | feed\_id | VARCHAR | FK to FEEDS | | feeds\_files\_id | VARCHAR | FK to FEEDS\_FILES | | created\_at | TIMESTAMP | When the source data was loaded | | updated\_at | TIMESTAMP | When the table was last rebuilt | ### Facility Month (FACILITY\_MONTH) The gold table: one row per CCN × **observed** month (1.2M+ rows). Monthly snapshot state, the PBJ staffing quarter, an ID-based ownership summary, and the five computable compliance flags plus the operational-stress composite. Keyed on observed months only — there are no ghost rows for months CMS did not publish. Flag formulas and thresholds are documented in the [flag methodology](/data-catalog/cms-nh-compliance-dataset/methodology-flags.md). **Key Features:** * 1.2M+ facility-months, Jan 2019–present * Five 0/1/NULL compliance flags, each with a `_reason` code and an `_inputs_coverage` boolean * `operational_stress_indicator_count` — a 0–5 count of fired components (NULL unless at least 4 of 5 are evaluable) * `method_version` stamped on every row for reproducibility #### Column Reference | Column | Type | Description | | -------------------------------------------- | --------- | -------------------------------------------------------------- | | ccn | VARCHAR | CMS Certification Number | | observed\_month | DATE | Published snapshot month | | observed\_quarter | DATE | Calendar quarter (PBJ staffing join key) | | provider\_name | VARCHAR | Provider name in this snapshot | | city, state, zip\_code, county | VARCHAR | Location in this snapshot | | ownership\_type | VARCHAR | Ownership type in this snapshot | | number\_of\_certified\_beds | NUMBER | Certified beds | | average\_number\_of\_residents\_per\_day | NUMBER | Average daily census | | occupancy\_ratio | NUMBER | Residents per day ÷ certified beds | | overall\_rating | NUMBER | Five-Star overall rating | | health\_inspection\_rating | NUMBER | Five-Star health inspection rating | | staffing\_rating | NUMBER | Five-Star staffing rating | | qm\_rating | NUMBER | Five-Star quality measure rating | | special\_focus\_status | VARCHAR | SFF / SFF-candidate status | | abuse\_icon | VARCHAR | Abuse icon flag | | chain\_id, chain\_name | VARCHAR | CMS-reported chain for the month | | pbj\_submission\_status | VARCHAR | `reported_full` / `partial` / `missing` | | pbj\_total\_nurse\_hprd | NUMBER | Total nurse hours per resident day (quarter) | | case\_mix\_expected\_total\_nurse\_hprd | NUMBER | CMS case-mix expected total nurse HPRD | | pbj\_avg\_daily\_census | NUMBER | PBJ average daily census | | in\_pecos\_coverage | BOOLEAN | TRUE when this month is inside PECOS ownership coverage | | disclosed\_owner\_count | NUMBER | Distinct disclosed owners this PECOS vintage | | pe\_owner\_count | NUMBER | Distinct PE-flagged owners | | reit\_owner\_count | NUMBER | Distinct REIT-flagged owners | | has\_undisclosed\_upstream\_owner | BOOLEAN | Current-state dead-end disclosure measure | | staffing\_shortfall\_flag | NUMBER | 0/1/NULL — reported HPRD below case-mix expectation | | staffing\_shortfall\_reason | VARCHAR | Reason code | | staffing\_shortfall\_inputs\_coverage | BOOLEAN | Were the inputs present | | repeat\_deficiency\_flag | NUMBER | 0/1/NULL — deficiency tag recurred across standard surveys | | repeat\_deficiency\_reason | VARCHAR | Reason code | | repeat\_deficiency\_inputs\_coverage | BOOLEAN | Were the inputs present | | ownership\_disclosure\_gap\_flag | NUMBER | 0/1/NULL — ownership disclosure gap in-coverage | | ownership\_disclosure\_gap\_reason | VARCHAR | Reason code | | ownership\_disclosure\_gap\_inputs\_coverage | BOOLEAN | Were the inputs present | | enforcement\_escalation\_flag | NUMBER | 0/1/NULL — annual fine total escalated | | enforcement\_escalation\_reason | VARCHAR | Reason code | | enforcement\_escalation\_inputs\_coverage | BOOLEAN | Were the inputs present | | deactivation\_signal\_flag | NUMBER | 0/1/NULL — observed closure signal fired | | deactivation\_signal\_reason | VARCHAR | Reason code | | deactivation\_signal\_inputs\_coverage | BOOLEAN | Always TRUE — always evaluable | | sff\_or\_abuse\_icon | NUMBER | Composite component: SFF status or abuse icon | | pbj\_census\_decline\_gt20pct\_yoy | NUMBER | Composite component: census down >20% YoY | | chow\_within\_12m | NUMBER | Composite component: confirmed CHOW within 12 months | | stress\_indicators\_evaluated | NUMBER | 0–5 count of evaluable components (the denominator) | | operational\_stress\_indicator\_count | NUMBER | 0–5 count of fired components (NULL unless ≥4 evaluable) | | stress\_indicator\_reason | VARCHAR | Which components fired, or `insufficient_evaluable_components` | | method\_version | VARCHAR | Methodology version stamped on the row | | anchor\_source\_file\_id | VARCHAR | Upstream CMS load pointer (resolves in FEEDS\_FILES) | | feed\_id | VARCHAR | FK to FEEDS | | feeds\_files\_id | VARCHAR | FK to FEEDS\_FILES | | created\_at | TIMESTAMP | When the source data was loaded | | updated\_at | TIMESTAMP | When the table was last rebuilt | ### Ownership History (OWNERSHIP\_HISTORY) Effective-dated, ID-keyed PECOS ownership intervals (Feb 2023–present) with self-reported PE/REIT/holding-company typing, plus labeled Care Compare owner-name observations (Jan 2019–present). PECOS rows (`source = 'pecos'`) are the only branch that carries interval math, `is_current`, and ID-based counts; Care Compare rows (`source = 'care_compare_name'`) are name-grain observations, never intervals. **Key Features:** * 13M+ ownership rows across both sources on a shared CCN spine * ID identity via `owner_associate_id` (PECOS ASSOCIATE\_ID) — names churn, IDs do not * Dual dating: `valid_from_observed` (fact, used for interval math) vs `valid_from_reported` (self-reported claim, carried verbatim) * Self-reported PE, REIT, holding-company, investment-firm, and chain-home-office typing #### Column Reference | Column | Type | Description | | ------------------------------------ | --------- | -------------------------------------------------------------------------------------------------- | | ccn | VARCHAR | Facility CMS Certification Number | | source | VARCHAR | `pecos` (ID-keyed interval) or `care_compare_name` (labeled name observation) | | enrollment\_id | VARCHAR | PECOS enrollment id | | owner\_associate\_id | VARCHAR | PECOS ASSOCIATE\_ID (identity for pecos rows) | | role | VARCHAR | Ownership / control role | | owner\_display\_name | VARCHAR | Owner display name | | owner\_kind | VARCHAR | Organization vs individual | | name\_bridge\_match\_tier | VARCHAR | Care Compare name → ID bridge tier (hint only) | | ownership\_percentage | NUMBER | Reported ownership percentage | | is\_owned\_by\_another\_org\_or\_ind | BOOLEAN | Owner is owned by another org/individual | | has\_undisclosed\_upstream\_owner | BOOLEAN | Curated current-state dead-end measure (pecos rows) | | is\_private\_equity | BOOLEAN | CMS self-reported PE flag | | is\_reit | BOOLEAN | CMS self-reported REIT flag | | is\_holding\_company | BOOLEAN | Holding-company flag | | is\_investment\_firm | BOOLEAN | Investment-firm flag | | is\_chain\_home\_office | BOOLEAN | Chain home-office flag | | is\_for\_profit / is\_non\_profit | BOOLEAN | Profit-status flags | | valid\_from\_observed | DATE | First vintage the interval appears (interval math + is\_current) | | valid\_from\_reported | DATE | Self-reported association date (verbatim; never effective\_from) | | last\_observed\_vintage | DATE | Latest vintage the interval reaches | | prev\_available\_vintage\_month | DATE | Previous available vintage (from SNAPSHOT\_CALENDAR) | | is\_current | BOOLEAN | TRUE when the interval reaches the latest vintage | | date\_confidence | VARCHAR | `observed_only` / `reported_precedes_coverage` / `reported_within_coverage` / `reported_anomalous` | | end\_reason | VARCHAR | `ongoing` / `not_observed_after` / `chow` / `label_observation` | | anchor\_source\_file\_id | VARCHAR | Latest contributing upstream CMS load | | source\_row\_ids | ARRAY | All upstream loads that contributed to the interval | | feed\_id / feeds\_files\_id | VARCHAR | Lineage FKs | | created\_at / updated\_at | TIMESTAMP | Load and rebuild timestamps | ### Ownership Events (OWNERSHIP\_EVENTS) Detected ID-keyed ownership-change events plus unmatched official CHOW rows. Useful for change monitoring and diligence. **Key columns:** `event_id`, `ccn`, `event_type` (owner\_appears / owner\_gone / ownership\_percentage\_change / facility\_enrollment\_appears / facility\_enrollment\_gone / official\_chow), `ccn_join_confidence` (matched / unmatched), `alert_tier` (chow\_signal / watchlist / disclosure), `owner_associate_id`, `owner_name`, `old_ownership_percentage`, `new_ownership_percentage`, `is_private_equity`, `is_reit`, `is_holding_company`, `chow_confirmed`, `chow_type_text`, `chow_effective_date`, plus lineage columns. ### Chain Membership History (CHAIN\_MEMBERSHIP\_HISTORY) CMS-reported facility chain-membership intervals from the provider-information snapshots. CMS-reported only — no inferred chains. `no_chain_reported` is an explicit interval value. **Key columns:** `ccn`, `chain_id`, `chain_name`, `no_chain_reported`, `valid_from_observed`, `last_observed_month`, `observed_month_count`, `is_current`, plus lineage columns. ### Staffing Compliance (STAFFING\_COMPLIANCE) CCN × quarter Payroll-Based Journal staffing with case-mix expectation and Five-Star staffing passthrough. `pbj_submission_status` is explicit; suppressed values stay NULL (never a fabricated zero). #### Column Reference | Column | Type | Description | | --------------------------------------- | --------- | --------------------------------------- | | ccn | VARCHAR | Facility CMS Certification Number | | staffing\_quarter | DATE | First day of the PBJ calendar quarter | | cy\_qtr | VARCHAR | Calendar-year quarter label | | pbj\_submission\_status | VARCHAR | `reported_full` / `partial` / `missing` | | pbj\_days\_reported | NUMBER | Days present in PBJ for the quarter | | pbj\_expected\_days | NUMBER | Days expected in the quarter | | pbj\_avg\_daily\_census | NUMBER | Average daily resident census | | pbj\_total\_nurse\_hprd | NUMBER | Total nurse hours per resident day | | pbj\_rn\_hprd | NUMBER | RN hours per resident day | | pbj\_lpn\_hprd | NUMBER | LPN hours per resident day | | pbj\_cna\_hprd | NUMBER | CNA hours per resident day | | pbj\_contract\_staff\_share | NUMBER | Share of hours from contract staff | | five\_star\_staffing\_rating | NUMBER | Five-Star staffing rating passthrough | | reported\_total\_nurse\_hprd | NUMBER | Care Compare reported total nurse HPRD | | case\_mix\_expected\_total\_nurse\_hprd | NUMBER | CMS case-mix expected total nurse HPRD | | feed\_id / feeds\_files\_id | VARCHAR | Lineage FKs | | created\_at / updated\_at | TIMESTAMP | Load and rebuild timestamps | ### Enforcement Events (ENFORCEMENT\_EVENTS) Conformed, de-duplicated union of deficiency citations and monetary penalties (1.9M+ events), one row per event with `occurrence_index` for true repeats of the same tag. **Key columns:** `event_id`, `ccn`, `event_type` (health\_deficiency / fire\_safety\_deficiency / penalty), `event_date`, `tag`, `scope_severity_code`, `category`, `description`, `standard_deficiency`, `complaint_deficiency`, `corrected`, `correction_date`, `fine_amount`, `payment_denial_start_date`, `payment_denial_length_days`, `occurrence_index`, `first_reported_month`, `last_reported_month`, `source_row_ids`, plus lineage columns. ### Closure Signals (CLOSURE\_SIGNALS) Observed-only closure-type signals. Gap months emit nothing; a signal inside CMS's official change-of-ownership publication lag is held at `pending_confirmation`. **Key columns:** `ccn`, `signal_type` (snapshot\_disappearance / pos\_termination / bed\_count\_zero), `signal_month`, `signal_tier` (top / corroborating), `detail`, `explained_by_official_chow`, `signal_status` (observed / pending\_confirmation), `source_row_ids`, plus lineage columns. ### Chain Month (CHAIN\_MONTH) Chain × observed month rollups computed from `FACILITY_MONTH` for each chain's member CCNs, with a `coverage_ratio`, an `aggregates_partial` flag (set when under 90% of members had the underlying measure), and `CMS_REPORTED_*` columns from CMS's own chain performance file for reconciliation (within ±5%). **Key columns:** `chain_id`, `chain_name`, `observed_month`, `member_count`, `avg_total_nurse_hprd`, `avg_overall_rating`, `avg_health_inspection_rating`, `avg_staffing_rating`, `avg_qm_rating`, `avg_operational_stress_indicator_count`, `coverage_ratio`, `aggregates_partial`, `cms_reported_facility_count`, `cms_reported_avg_overall_rating`, `cms_reported_avg_total_nurse_hprd`, plus lineage columns. ### Snapshot Calendar (SNAPSHOT\_CALENDAR) Per-feed-family observed-month calendar — the source of `prev_available_vintage_month` everywhere, so no query ever does month arithmetic across a gap. **Key columns:** `feed_family`, `observed_month`, `prev_available_vintage_month`, `months_since_prev_vintage`, `prev_vintage_spans_gap`, `vintage_seq`, `snapshot_row_count`. Full column-level descriptions for every table are available in the `DATA_DICTIONARY` table. ## Data Quality ### Standardization * All columns use UPPERCASE naming consistent with Snowflake conventions * CCN is normalized to a 6-character certification number and used as the join key across every table * Boolean flags are native `BOOLEAN`; compliance flags are `SMALLINT` `0/1/NULL` (never a silent 0) * Every vintage comparison reads the previous **available** month from `SNAPSHOT_CALENDAR` — never a naive month-minus-one across a gap * PBJ staffing measures are `NULL` when suppressed or unreported, never fabricated as zero * Ownership identity is always the CMS-assigned `owner_associate_id`; owner-name strings are display data, not identity ### Data Freshness Check when data was last updated: {% tabs %} {% tab title="Snowflake" %} ```sql SELECT source_name, data_period, row_count, created_at, is_latest FROM DWV.FEEDS_FILES WHERE is_latest = TRUE; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT source_name, data_period, row_count, created_at, is_latest FROM cms_nh_compliance_dwv.feeds_files WHERE is_latest = TRUE; ``` {% endtab %} {% endtabs %} ## How to Query Nursing Home Compliance Data ### Platform Schema Reference This dataset is available on both Snowflake and Databricks. Queries use schema-only references — the database is already set by the share or catalog context: | Platform | Schema | Example | | -------------- | ----------------------- | -------------------------------------- | | **Snowflake** | `DWV` | `DWV.FACILITY_MONTH` | | **Databricks** | `cms_nh_compliance_dwv` | `cms_nh_compliance_dwv.facility_month` | ### Discover Available Data Start with the `FEEDS` table to see what's available, and `FEEDS_FILES` to understand data freshness and load history. {% tabs %} {% tab title="Snowflake" %} ```sql -- Browse the data catalog SELECT table_name, description, last_loaded_at, total_rows_extracted, total_batches FROM DWV.FEEDS ORDER BY table_name; ``` {% endtab %} {% tab title="Databricks" %} ```sql -- Browse the data catalog SELECT table_name, description, last_loaded_at, total_rows_extracted, total_batches FROM cms_nh_compliance_dwv.feeds ORDER BY table_name; ``` {% endtab %} {% endtabs %} ### Working with Data Lineage Every data row links to `FEEDS_FILES` via `feeds_files_id`, which tells you exactly which batch loaded that data. Use this to filter to the current data version or trace any row back to its source load. {% tabs %} {% tab title="Snowflake" %} ```sql -- Current facility-month rows with batch lineage SELECT fm.ccn, fm.observed_month, fm.provider_name, fm.overall_rating, ff.data_period, ff.created_at AS data_loaded_at FROM DWV.FACILITY_MONTH fm JOIN DWV.FEEDS_FILES ff ON fm.feeds_files_id = ff.id WHERE ff.is_latest = TRUE LIMIT 20; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT fm.ccn, fm.observed_month, fm.provider_name, fm.overall_rating, ff.data_period, ff.created_at AS data_loaded_at FROM cms_nh_compliance_dwv.facility_month fm JOIN cms_nh_compliance_dwv.feeds_files ff ON fm.feeds_files_id = ff.id WHERE ff.is_latest = TRUE LIMIT 20; ``` {% endtab %} {% endtabs %} ### Staffing-Shortfall Screening Screen facilities whose payroll-based reported nurse staffing fell below CMS's case-mix expectation. The flag is only set where PBJ was fully reported — `NULL` (not evaluable) is excluded automatically by `= 1`. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT fm.ccn, fm.provider_name, fm.state, fm.observed_month, fm.pbj_total_nurse_hprd, fm.case_mix_expected_total_nurse_hprd, fm.staffing_shortfall_reason FROM DWV.FACILITY_MONTH fm JOIN DWV.FEEDS_FILES ff ON fm.feeds_files_id = ff.id AND ff.is_latest = TRUE WHERE fm.staffing_shortfall_flag = 1 ORDER BY fm.observed_month DESC, fm.state LIMIT 100; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT fm.ccn, fm.provider_name, fm.state, fm.observed_month, fm.pbj_total_nurse_hprd, fm.case_mix_expected_total_nurse_hprd, fm.staffing_shortfall_reason FROM cms_nh_compliance_dwv.facility_month fm JOIN cms_nh_compliance_dwv.feeds_files ff ON fm.feeds_files_id = ff.id AND ff.is_latest = TRUE WHERE fm.staffing_shortfall_flag = 1 ORDER BY fm.observed_month DESC, fm.state LIMIT 100; ``` {% endtab %} {% endtabs %} ### Effective-Dated Ownership with PE/REIT for a Facility Trace the effective-dated PECOS ownership of a single facility, including private-equity and REIT typing and effective-dating confidence. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT ccn, owner_display_name, role, ownership_percentage, is_private_equity, is_reit, is_holding_company, valid_from_observed, valid_from_reported, date_confidence, is_current, end_reason FROM DWV.OWNERSHIP_HISTORY WHERE ccn = '345003' AND source = 'pecos' AND is_current = TRUE ORDER BY is_reit DESC, owner_display_name; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT ccn, owner_display_name, role, ownership_percentage, is_private_equity, is_reit, is_holding_company, valid_from_observed, valid_from_reported, date_confidence, is_current, end_reason FROM cms_nh_compliance_dwv.ownership_history WHERE ccn = '345003' AND source = 'pecos' AND is_current = TRUE ORDER BY is_reit DESC, owner_display_name; ``` {% endtab %} {% endtabs %} ### Chain-Month Rollup vs CMS-Reported Compare our chain rollups to CMS's own chain performance file. `coverage_ratio` and `aggregates_partial` tell you how much of the chain the rollup covers. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT chain_id, chain_name, observed_month, member_count, avg_total_nurse_hprd, avg_overall_rating, coverage_ratio, aggregates_partial, cms_reported_facility_count, cms_reported_avg_total_nurse_hprd FROM DWV.CHAIN_MONTH WHERE cms_reported_facility_count IS NOT NULL ORDER BY observed_month DESC, member_count DESC LIMIT 100; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT chain_id, chain_name, observed_month, member_count, avg_total_nurse_hprd, avg_overall_rating, coverage_ratio, aggregates_partial, cms_reported_facility_count, cms_reported_avg_total_nurse_hprd FROM cms_nh_compliance_dwv.chain_month WHERE cms_reported_facility_count IS NOT NULL ORDER BY observed_month DESC, member_count DESC LIMIT 100; ``` {% endtab %} {% endtabs %} ### Enforcement Escalation + Repeat Deficiencies for Diligence Screen facilities whose latest snapshot shows both an escalating penalty pattern and a recurring deficiency, with the count of repeat tags and the maximum tag recurrence — a one-query diligence shortlist. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT fm.ccn, fm.provider_name, fm.state, fm.observed_month, fm.enforcement_escalation_flag, fm.repeat_deficiency_flag, COUNT(DISTINCT e.tag) AS distinct_repeat_tags, MAX(e.occurrence_index) AS max_tag_occurrences FROM DWV.FACILITY_MONTH fm JOIN DWV.ENFORCEMENT_EVENTS e ON e.ccn = fm.ccn AND e.event_type = 'health_deficiency' AND e.occurrence_index >= 3 WHERE fm.observed_month = (SELECT MAX(observed_month) FROM DWV.FACILITY_MONTH) AND fm.enforcement_escalation_flag = 1 AND fm.repeat_deficiency_flag = 1 GROUP BY ALL ORDER BY max_tag_occurrences DESC LIMIT 100; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT fm.ccn, fm.provider_name, fm.state, fm.observed_month, fm.enforcement_escalation_flag, fm.repeat_deficiency_flag, COUNT(DISTINCT e.tag) AS distinct_repeat_tags, MAX(e.occurrence_index) AS max_tag_occurrences FROM cms_nh_compliance_dwv.facility_month fm JOIN cms_nh_compliance_dwv.enforcement_events e ON e.ccn = fm.ccn AND e.event_type = 'health_deficiency' AND e.occurrence_index >= 3 WHERE fm.observed_month = (SELECT MAX(observed_month) FROM cms_nh_compliance_dwv.facility_month) AND fm.enforcement_escalation_flag = 1 AND fm.repeat_deficiency_flag = 1 GROUP BY ALL ORDER BY max_tag_occurrences DESC LIMIT 100; ``` {% endtab %} {% endtabs %} ### Closure Signals with Corroboration Find facilities where a top-tier snapshot disappearance is corroborated by a second observed signal (a POS certification termination or a beds-to-zero transition). Remember: a signal is a change in the federal record, not a statement that a building's doors have closed. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT cs.ccn, m.provider_name, m.state, cs.signal_type, cs.signal_tier, cs.signal_month, cs.signal_status, cs.explained_by_official_chow, cs.detail FROM DWV.CLOSURE_SIGNALS cs JOIN DWV.FACILITY_MASTER m ON m.ccn = cs.ccn WHERE cs.ccn IN ( SELECT ccn FROM DWV.CLOSURE_SIGNALS GROUP BY ccn HAVING COUNT(DISTINCT signal_type) > 1 ) ORDER BY cs.ccn, cs.signal_month LIMIT 100; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT cs.ccn, m.provider_name, m.state, cs.signal_type, cs.signal_tier, cs.signal_month, cs.signal_status, cs.explained_by_official_chow, cs.detail FROM cms_nh_compliance_dwv.closure_signals cs JOIN cms_nh_compliance_dwv.facility_master m ON m.ccn = cs.ccn WHERE cs.ccn IN ( SELECT ccn FROM cms_nh_compliance_dwv.closure_signals GROUP BY ccn HAVING COUNT(DISTINCT signal_type) > 1 ) ORDER BY cs.ccn, cs.signal_month LIMIT 100; ``` {% endtab %} {% endtabs %} ### Tracking Data Changes Over Time `FEEDS_FILES` records every batch load with `row_count_delta` showing what changed. Use this to monitor source data updates. {% tabs %} {% tab title="Snowflake" %} ```sql SELECT source_name, data_period, row_count, row_count_delta, created_at AS loaded_at, is_latest FROM DWV.FEEDS_FILES WHERE source_name = 'pbj_daily_nurse_staffing' ORDER BY created_at; ``` {% endtab %} {% tab title="Databricks" %} ```sql SELECT source_name, data_period, row_count, row_count_delta, created_at AS loaded_at, is_latest FROM cms_nh_compliance_dwv.feeds_files WHERE source_name = 'pbj_daily_nurse_staffing' ORDER BY created_at; ``` {% endtab %} {% endtabs %} ## Methodology, Lineage & Caveats This dataset is built to be cited in diligence, underwriting, and compliance work. Three companion pages document exactly how it behaves: * [**Flag Methodology**](/data-catalog/cms-nh-compliance-dataset/methodology-flags.md) — the exact formula, thresholds, evaluability rules, and worked examples for each of the five compliance flags and the operational-stress composite. * [**Lineage & Reproducibility**](/data-catalog/cms-nh-compliance-dataset/lineage-reproducibility.md) — the lineage columns, how the monthly snapshot calendar works, and the steps to reproduce any value from the CMS source files. * [**Caveats & Coverage**](/data-catalog/cms-nh-compliance-dataset/caveats.md) — the data-coverage statement and the source-data properties you must reason about before drawing a conclusion. ## Who Uses This Data ### Common Use Cases * **Private-equity and REIT ownership diligence** — Trace effective-dated, ID-keyed ownership intervals with CMS self-reported PE, REIT, and holding-company flags for investor and lender diligence on chains and facilities * **Staffing compliance monitoring** — Monitor payroll-based (PBJ) nurse staffing against CMS's case-mix expectation by facility and quarter, with explicit coverage flags where PBJ is missing, partial, or suppressed * **Enforcement and survey diligence** — Query conformed, de-duplicated deficiency citations and penalties with occurrence indexing to surface true repeat deficiencies across a facility's full survey history * **Chain intelligence and benchmarking** — Analyze chain membership and chain-month rollups reconciled to CMS's own chain performance file, with explicit partial-coverage flags * **Longitudinal facility research** — Study 7+ years of facility history on a stable CCN spine — staffing, quality, enforcement, ownership, and observed closure signals — for researchers, journalists, and analytics teams * **Change-of-ownership monitoring** — Track detected ownership-change events and official CHOW confirmations for portfolio and market surveillance ### Related Datasets and Research This dataset pairs well with: * [**FacilityGuard — Facility Ownership Intelligence**](/data-catalog/facilityguard-app.md) — The interactive app built on the same curated CMS PECOS ownership model; `has_undisclosed_upstream_owner` here is the same figure FacilityGuard reports * [**CMS Data Feeds Dataset**](/data-catalog/cms-data-feeds-dataset.md) — The broader CMS nursing home Care Compare feeds this product joins together, plus Medicare provider spending for cross-program analysis * [**CMS NPPES Provider Dataset**](/data-catalog/cms-nppes-provider-dataset.md) — NPI provider attributes for enriching owners and administrators with taxonomy and practice-location detail * [**HRSA Healthcare Resources**](/data-catalog/hrsa-dataset.md) — County-level workforce and shortage-area data for correlating staffing patterns with local provider supply ### Frequently Asked Questions **What facilities are included?** All Medicare/Medicaid-certified U.S. nursing homes (skilled nursing facilities and nursing facilities) that appear in the CMS Care Compare provider-information snapshots — 16K+ facilities on a stable CCN spine, spanning Jan 2019–present. **How often is the data updated?** Our pipeline automatically monitors CMS for new releases and loads updated data within days of publication — no manual intervention needed. CMS publishes the nursing home provider-information, ownership, deficiency, and penalty files monthly; PBJ staffing is published quarterly and arrives roughly two quarters behind. Coverage grows with each CMS release. **Why do the ownership tables start in Feb 2023 but the facility history goes back to 2019?** Effective-dated, ID-keyed ownership intervals begin in February 2023, when CMS's PECOS ownership data becomes usable as dated intervals. Ownership before that date ships only as labeled owner-name observations from the Care Compare ownership feed, back to January 2019 — carried as name-grain rows, never as intervals, and never counted in any ID-based figure. **What does a `NULL` flag mean?** `NULL` means *not evaluable* — the inputs needed to decide were missing, suppressed, or outside coverage. It is never a silent `0`. A facility that never reported payroll staffing for a quarter gets `NULL` on the staffing flag, not `0`, because "we could not evaluate this" is a different statement from "we evaluated this and found no shortfall." Each flag carries a `_reason` code and an `_inputs_coverage` boolean so you always know which branch produced the value. **Are these flags predictions of closure or care quality?** No. Every flag is a published, deterministic formula over CMS's own data — signal, not prediction. There is no machine learning, no predictive scoring, and no name-based inference. The composite is named `operational_stress_indicator_count` because it is a count of fired indicators, not a distress score or a closure probability. See the [flag methodology](/data-catalog/cms-nh-compliance-dataset/methodology-flags.md). **What's the difference between this and downloading from CMS directly?** CMS publishes nursing home data across many separate monthly files — Care Compare, PECOS ownership, PBJ staffing, deficiencies, penalties, chain performance — each with its own keys, gaps, and quirks. This product joins them onto one stable CCN spine, resolves ownership identity to CMS-assigned IDs, dates ownership intervals, conforms and de-duplicates enforcement events, and adds published compliance flags with full batch lineage — analytics-ready in Snowflake and Databricks, updated automatically, with every row traceable to its CMS source file. {% hint style="success" %} **Ready to access Nursing Home Compliance Intelligence data?** Questions? [Contact our team](mailto:support@dataplex-consulting.com) for a walkthrough. {% endhint %} | Platform | Action | | -------------- | --------------------------------------------------------------------------------------------------- | | **Snowflake** | [View listing on Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZT1Z7QRT1QB) | | **Databricks** | [Free Trial](https://trial.dataplex-consulting.com) | --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.dataplex-consulting.com/data-catalog/cms-nh-compliance-dataset.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.