> 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/lineage-reproducibility.md). # 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. It 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](/data-catalog/cms-nh-compliance-dataset/methodology-flags.md). 3. The flag's `_reason` and `_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. --- # 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/lineage-reproducibility.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.