Lineage & Reproducibility

Every number in the Nursing Home Compliance Intelligence dataset traces back to a specific CMS release file, and every derived row carries the pointers needed to walk that trail yourself. This page explains the lineage columns, how the monthly snapshot calendar works, and the exact steps to reproduce any value you see — a derived row, a flag, or an aggregate — from the federal source.

The audit trail

Ask "where did this row come from?" and the answer is always a chain of typed pointers, not a verbal assurance. Every derived table carries the same lineage columns:

Column	What it is
feed_id	Stable surrogate key for the derived feed (FK to FEEDS.id). Identifies which derived table this row belongs to.
feeds_files_id	The derived feed's synthetic batch key (FK to FEEDS_FILES.id). One row per derived feed in FEEDS_FILES is the resolvable FK target.
anchor_source_file_id	The audit pointer. The real upstream CMS load this derived row was anchored to — resolves to a real FEEDS_FILES row, which names the CMS release file.
source_row_ids	On event and interval tables (ownership, enforcement, closure): an array of the upstream loads that contributed to the row.
rownum	Deterministic ordering within the feed (also drives the trial-cohort slice).
created_at	The anchor batch's load time.
updated_at	When the table was last rebuilt.

The reproducibility guarantee is: anchor_source_file_id (and source_row_ids) always resolve to real FEEDS_FILES rows, and each FEEDS_FILES row names the CMS release file, its batch, row count, and load time. There are no dangling pointers — an automated build-time test asserts every derived feeds_files_id resolves, and the reconciliation tests below assert the numbers hold.

Walking the trail (derived row → CMS file)

derived row (e.g. FACILITY_MONTH for a CCN and observed_month)
      │  anchor_source_file_id
      ▼
FEEDS_FILES row  (id = that pointer)
      │  names the batch: source_name, source_file, data_period, row_count, loaded_at
      ▼
CMS release file  (the provider-information snapshot published for that month)

For event and interval tables the same walk starts from source_row_ids, which lists every contributing snapshot — so a disputed ownership interval or enforcement event points at all the monthly snapshots it was observed in, not just one.

Standalone metadata: how derived feeds are tracked

This is a standalone product with its own database (CMS_NH_COMPLIANCE_DW), so it owns its FEEDS / FEEDS_FILES per the standard metadata contract. There are two kinds of rows in FEEDS_FILES:

• Raw-feed batches — one row per successful load of the source extracts (Payroll-Based Journal, NH Chain Performance, Provider of Services), keyed by the real batch identifier.

• Derived feeds — one synthetic row per derived table (FACILITY_MASTER, FACILITY_MONTH, OWNERSHIP_HISTORY, and so on), flagged is_derived_feed = TRUE. Derived tables have no batch load of their own, so this synthetic row is the FK target their feeds_files_id resolves to. The real upstream provenance for a derived row lives in its anchor_source_file_id.

Because the reused CMS nursing-home feeds (provider-information, ownership, deficiencies, penalties, and so on) are loaded upstream, the FEEDS catalog computes each derived feed's freshness (last_loaded_at) as the greatest load time across all the contributing source feeds. That is what tells a freshness check that a derived table has new data even though the derived table itself never runs an extract.

How monthly snapshots work

CMS publishes the nursing-home provider-information and ownership files as monthly snapshots. This product treats each published snapshot as an observed month and never does month arithmetic.

The snapshot calendar is the source of truth

SNAPSHOT_CALENDAR holds one row per (feed_family, observed_month) — the months a given feed family was actually observed. Its key column, prev_available_vintage_month, is the previous month that actually exists for that family (a lag over observed months), never "this month minus one." Each feed family has its own gap set:

• provider-information is missing 2026-01;

• Care Compare ownership is missing every December 2019–2024, plus 2025-01 and 2025-08;

• the PECOS intersection calendar (owners ∩ enrollments) differs again.

So prev_available_vintage_month for provider-information in 2026-02 points at 2025-12, not 2026-01, and prev_vintage_spans_gap = TRUE flags that the step skipped a missing month. Every downstream comparison — ownership intervals, chain-membership intervals, closure-signal disappearances — reads its previous vintage from this calendar. This is why a missing upstream month can never manufacture a phantom event or a phantom disappearance.

Observed months only — gap months emit nothing

FACILITY_MONTH is keyed on CCN × observed month. There are no ghost rows for months CMS did not publish. Its row count reconciles exactly to the sum of per-snapshot facility counts (an automated test enforces this). If a facility was not in a given month's snapshot, there is no row for it that month — not a row of nulls or zeros.

A signal that would depend on a gap (for example, a facility "disappearing" across a missing month) is not emitted, because the disappearance is unattributable when the months are not adjacent.

How to reproduce any number

A facility-month row or its flags

1. Take the row's anchor_source_file_id and look it up in FEEDS_FILES to get the CMS provider-information snapshot for that observed_month.

2. For a staffing-related flag, join the facility's observed_quarter to STAFFING_COMPLIANCE; that row's inputs (pbj_total_nurse_hprd, case_mix_expected_total_nurse_hprd, pbj_submission_status) come from the PBJ data and the provider-information snapshot inside the quarter. Re-apply the published formula and the seed threshold — see the flag methodology.

3. The flag's <flag>_reason and <flag>_inputs_coverage columns tell you which branch of the formula produced the value, so you can confirm the arithmetic rather than guess it.

4. method_version on the row ties the result to the exact formula + threshold version used.

An ownership interval

1. source_row_ids lists every PECOS vintage the interval was observed in; anchor_source_file_id is the latest. Resolve them in FEEDS_FILES.

2. valid_from_observed is the first vintage the (CCN, enrollment, associate, role) key appeared in — a fact you can confirm by finding the key in that vintage and confirming it was absent in the previous available vintage from SNAPSHOT_CALENDAR. valid_from_reported is the self-reported association date, carried verbatim and never used as the effective date.

3. is_current is true when the interval reaches the latest PECOS vintage.

An enforcement event

1. source_row_ids lists every monthly snapshot that reported the event. The event is deduped to one row on its conforming key, so first_reported_month / last_reported_month bound the window it appeared in the federal files.

2. occurrence_index is reproducible by ordering the facility's same-tag events by date.

A chain aggregate

1. CHAIN_MONTH rollups are computed from FACILITY_MONTH for the chain's member CCNs that month; coverage_ratio and aggregates_partial tell you what fraction of members had the underlying measure.

2. The CMS_REPORTED_* columns are CMS's own chain-file numbers, and the reconciliation test holds our aggregates to within ±5% of them — so you can check our rollup against CMS's rollup directly.

Reconciliation guardrails

The build refuses to ship numbers that do not reconcile to the source:

• FACILITY_MONTH row count = the exact sum of per-snapshot facility counts.

• ENFORCEMENT_EVENTS counts and fine totals reconcile against the CMS survey summary: a warning above a 2% difference, a hard error above 10%.

• CHAIN_MONTH aggregates reconcile to the CMS_REPORTED_* columns within ±5%.

• Ownership intervals are mutually exclusive per key, exactly one is_current per active key, and zero intervals exist before Feb 2023.

• Freshness and pin-coherence tests assert the PECOS ownership inputs are current and the pinned vintage exists upstream.

Every number in this product is a deterministic function of published CMS files plus a versioned threshold set. Given the same source files and method_version, the build reproduces the same result.