Schema Reference

Full table-by-table reference for the CMS Prior Authorization Compliance Intelligence Dataset. All tables are exposed through the DWV schema on Snowflake and the cms_prior_auth_compliance_dwv schema on Databricks. Column-level descriptions for every table are also available in the DATA_DICTIONARY table.

Informational reference compiled from public Federal Register, eCFR, and CMS sources. Not legal advice; consult counsel for compliance decisions.

Universal column blocks

Every curated reference table (the nine tables in the first group below) carries three standard column blocks in addition to its own columns. They are documented once here and referenced by each table.

Provenance block — every curated row traces to a source

Column	Type	Description
source_doc_id	VARCHAR	FK to RULE_DOCUMENTS — which document asserts this row
fr_citation	VARCHAR	Federal Register citation, e.g. 89 FR 8758
cfr_citation	VARCHAR	CFR citation, e.g. 42 CFR 422.122 (NULL where genuinely not applicable)
source_pinpoint	VARCHAR	Federal Register full-text section reference, e.g. II.B.2.a — a URL alone is not a pinpoint
source_url	VARCHAR	Deep link to the source
verbatim_excerpt	VARCHAR(300)	Verbatim rule-text quote (public domain, 17 USC 105)
source_document_date	DATE	Publication date of the source
retrieved_at	TIMESTAMP_NTZ	When the source was pulled

Interpretation block — how strong is the assertion

Column	Type	Description
assertion_type	VARCHAR	FACT | INTERPRETATION | DERIVED
interpretation_confidence	VARCHAR	HIGH | MEDIUM | LOW
interpretation_notes	VARCHAR	Why we read it this way (NULL when a plain FACT)
needs_verification	BOOLEAN	TRUE quarantines the row — excluded from COMPLIANCE_GOLD and all derived views
reviewed_by	VARCHAR	Editorial sign-off identity (Dataplex Editorial) — every production row is individually human-reviewed before it ships
review_date	DATE	Review date

Lifecycle block — append-only versioning

Column	Type	Description
row_version	INT	Monotonic version per natural key
row_status	VARCHAR	ACTIVE | SUPERSEDED | PENDING_FINALIZATION
is_current	BOOLEAN	Exactly one TRUE per natural key
superseded_by_row_id	VARCHAR	Forward pointer to the superseding row
superseding_doc_id	VARCHAR	FK to RULE_DOCUMENTS — the document that caused supersession
curation_valid_from	TIMESTAMP_NTZ	Curation-time validity start (named distinctly from compliance dates)
curation_valid_to	TIMESTAMP_NTZ	Curation-time validity end (NULL while current)

Dataset provenance variant (payer tables)

The three payer tables (PAYER_DIRECTORY, PAYER_METRIC_SOURCES, PAYER_METRICS) are web/dataset-derived facts, not rule text, so they carry a dataset provenance block in place of the Federal Register one — the interpretation and lifecycle blocks above are identical. The rule-text provenance columns (fr_citation, cfr_citation, source_document_date) do not apply. PAYER_METRICS still carries source_pinpoint and verbatim_excerpt as value-evidence columns on the table itself.

Column	Type	Description
source_dataset	VARCHAR	The dataset the row was derived from (e.g. a CMS enrollment file, the payer's own website)
source_url	VARCHAR	Deep link to the source
retrieved_at	TIMESTAMP_NTZ	When the source was pulled

The lifecycle block is the same, except superseding_doc_id is a plain nullable column (a payer refresh is a data update, not a rule document).

---

Curated reference tables

RULE_DOCUMENTS

Grain: one Federal Register document. PK: document_id.

Column	Type	Description
document_id	VARCHAR	Human-readable slug — CMS-0057-F, CMS-0062-P, CMS-0053-F, IPPS-FY2026
fr_doc_number	VARCHAR	Federal Register document number, e.g. 2024-00895
citation	VARCHAR	e.g. 89 FR 8758
rin	VARCHAR	Regulatory Identifier Number
regulations_gov_docket	VARCHAR	Docket to poll for finalization, e.g. CMS-2026-1255 on CMS-0062-P
document_type	VARCHAR	RULE | PRORULE
publication_date	DATE	Federal Register publication date
effective_date	DATE	Rule effective date (NULL for proposed rules)
comment_close_date	DATE	Comment period close (proposed rules)
comment_count	INT	Number of comments received
document_status	VARCHAR	PROPOSED | FINAL | FINALIZED_AS_OTHER | WITHDRAWN | SUPERSEDED
finalized_as_document_id	VARCHAR	FK to RULE_DOCUMENTS — set when a proposed rule is finalized (the live watch column; NULL on CMS-0062-P)
pa_relevance	VARCHAR	CORE | ADJACENT
cfr_parts	VARCHAR	Affected CFR parts
full_text_xml_url	VARCHAR	Federal Register full-text XML URL
fact_sheet_url	VARCHAR	CMS fact-sheet URL
pdf_url	VARCHAR	Rule PDF URL
+ universal blocks		Provenance, interpretation, lifecycle

RULE_OBLIGATIONS

Grain: one obligation per document. PK: obligation_id.

Column	Type	Description
obligation_id	VARCHAR	Stable slug, e.g. CMS-0057-F:PA-DECISION-TIMEFRAMES
document_id	VARCHAR	FK to RULE_DOCUMENTS
obligation_category	VARCHAR	API | OPERATIONAL | REPORTING | DENIAL_NOTICE | DOCUMENTATION | MEASURE
obligation_name	VARCHAR	Human-readable obligation name
requirement_text_verbatim	VARCHAR	Verbatim requirement text from the rule
requirement_summary	VARCHAR	Plain-language summary
is_pa_scope	BOOLEAN	FALSE on every CMS-0053-F adjacency row
applies_regardless_of_api_build	BOOLEAN	TRUE for the three January-1-2026 operational obligations
related_obligation_id	VARCHAR	FK to RULE_OBLIGATIONS — links related obligations across documents
binding_status	VARCHAR	FINAL_BINDING | PROPOSED_NOT_BINDING (every CMS-0062-P row the latter)
audit_relevance	VARCHAR	Why this obligation matters for an audit
penalty_enforcement_notes	VARCHAR	Enforcement / penalty context
+ universal blocks

ENTITY_TYPES

Grain: one entity/program type, including explicit out-of-scope rows. PK: entity_type_id.

Column	Type	Description
entity_type_id	VARCHAR	e.g. MA_ORG, MEDICAID_FFS, QHP_FFE, MIPS_CLINICIAN, HIPAA_COVERED_ENTITY
entity_type_name	VARCHAR	Full program name
in_scope	BOOLEAN	FALSE for explicit out-of-scope rows (MEDICARE_FFS, PART_D, QHP_SBE, STANDALONE_DENTAL, COMMERCIAL_ERISA)
scope_basis	VARCHAR	Verbatim citation for inclusion or exclusion
date_semantics_default	VARCHAR	LITERAL | RATING_PERIOD_ON_OR_AFTER | PLAN_YEAR_ON_OR_AFTER | PERFORMANCE_PERIOD (NULL for out-of-scope rows)
approx_entity_count	INT	Approximate population (from research, not rule text)
program_notes	VARCHAR	Program-specific notes
+ universal blocks

OBLIGATION_APPLICABILITY

Grain: (obligation × entity type × version) — the central fact, append-only SCD2. Natural key: (obligation_id, entity_type_id); unique_key is the list ['obligation_id','entity_type_id','row_version'].

Column	Type	Description
obligation_id	VARCHAR	FK to RULE_OBLIGATIONS
entity_type_id	VARCHAR	FK to ENTITY_TYPES
applicability	VARCHAR	REQUIRED | EXEMPT | OPTIONAL | EXTENSION_AVAILABLE | NOT_APPLICABLE — exemptions are explicit rows
compliance_date_anchor	DATE	Verbatim on-or-after anchor. Never a resolved per-payer due date; never fabricated (a dateless row ships quarantined)
compliance_date_type	VARCHAR	LITERAL_DATE | RATING_PERIOD_ON_OR_AFTER | PLAN_YEAR_ON_OR_AFTER | PERFORMANCE_PERIOD | RELATIVE_TO_FINALIZATION
compliance_date_display	VARCHAR	Human phrasing of the anchor
date_certainty	VARCHAR	FINAL | PROPOSED
first_deliverable_due	DATE	Actual literal deadline where the rule states one, e.g. 2026-03-31
recurrence	VARCHAR	e.g. ANNUAL
timeframe_detail	VARCHAR	e.g. 72h expedited / 7 calendar days standard
extension_exemption_notes	VARCHAR	Extension paths and exemption bases
+ universal blocks

STANDARDS_REGISTRY

Grain: one standard or implementation-guide version. PK: standard_id.

Column	Type	Description
standard_id	VARCHAR	e.g. hl7.fhir.us.davinci-pas#2.2.1, X12N-275-006020X314
standard_family	VARCHAR	HL7_FHIR_IG | X12 | NCPDP | HL7_CDA
standard_name	VARCHAR	Standard / implementation-guide name
version	VARCHAR	Version identifier
canonical_url	VARCHAR	Canonical URL (facts only)
npm_package	VARCHAR	HL7 NPM package identifier where applicable
version_status	VARCHAR	CURRENT | EXPIRING | EXPIRED
version_expiry_date	DATE	e.g. STU-2 stack 2028-01-01 (proposed)
baseline_note	VARCHAR	Baseline-stack note (NULL for PROPRIETARY_IDENTIFIER_ONLY rows)
license_class	VARCHAR	PUBLIC_DOMAIN | HL7_CC_FACTS_ONLY | PROPRIETARY_IDENTIFIER_ONLY
+ universal blocks

Licensing firewall. Where license_class = PROPRIETARY_IDENTIFIER_ONLY (X12, NCPDP), all prose columns are NULL by construction — only the identifier, family, and version are shipped. The fact of a standard's adoption is public domain (it is in the Federal Register); the proprietary specification text is never redistributed.

OBLIGATION_STANDARDS

Grain: (obligation × standard) bridge. Natural key: (obligation_id, standard_id).

Column	Type	Description
obligation_id	VARCHAR	FK to RULE_OBLIGATIONS
standard_id	VARCHAR	FK to STANDARDS_REGISTRY
binding_strength	VARCHAR	REQUIRED | NAMED | ENCOURAGED | PROPOSED
+ universal blocks

REPORTING_REQUIREMENTS

Grain: one reporting obligation and required content element. PK: reporting_requirement_id.

Column	Type	Description
reporting_requirement_id	VARCHAR	e.g. CMS-0057-F:RPT-WEB-02
obligation_id	VARCHAR	FK to RULE_OBLIGATIONS
destination	VARCHAR	PAYER_WEBSITE | CMS
cadence	VARCHAR	e.g. ANNUAL
due_rule	VARCHAR	e.g. March 31 for prior CY
first_due_date	DATE	e.g. 2026-03-31 (verified for website metrics)
content_element	VARCHAR	The required metric / content element
metric_id	VARCHAR	FK to METRIC_DEFINITIONS
+ universal blocks

DENIAL_NOTICE_ELEMENTS

Grain: one required denial-notice element. PK: element_id. (Element-grain by design — not a denial-reason code taxonomy.)

Column	Type	Description
element_id	VARCHAR	e.g. CMS-0057-F:DNE-01-PROVIDER-SPECIFIC-REASON
obligation_id	VARCHAR	FK to RULE_OBLIGATIONS
element_name	VARCHAR	Element name
element_description_verbatim	VARCHAR	Verbatim rule-text description
channel_scope	VARCHAR	e.g. ANY_CHANNEL, PRIOR_AUTH_API, ENROLLEE_WRITTEN_NOTICE, PROVIDER_NOTICE
+ universal blocks

METRIC_DEFINITIONS

Grain: one operational metric. PK: metric_id. The stable FK spine for PA-metric definitions and benchmarking.

Column	Type	Description
metric_id	VARCHAR	e.g. PA_APPROVAL_RATE_STD, API_USAGE_PATIENT_ACCESS
metric_name	VARCHAR	Operational metric name
numerator_text	VARCHAR	Numerator definition
denominator_text	VARCHAR	Denominator definition
breakdown_dimensions	VARCHAR	Reporting breakdowns
source_obligation_id	VARCHAR	FK to RULE_OBLIGATIONS
benchmarkable	BOOLEAN	TRUE for payer-comparable rate metrics
+ universal blocks

PAYER_DIRECTORY

Grain: one payer parent organization. PK: payer_id. Ungated. Uses the dataset provenance variant.

Column	Type	Description
payer_id	VARCHAR	Kebab-case slug PK, e.g. unitedhealth-group
payer_name	VARCHAR	Payer parent organization name
payer_kind	VARCHAR	MA_PARENT_ORG | MEDICAID_MCO_PARENT | STATE_MEDICAID_AGENCY
entity_type_ids	VARCHAR	Comma-separated FKs into ENTITY_TYPES — every token resolves to an entity type
state	VARCHAR(2)	Two-letter state (state Medicaid agencies; NULL for multi-state parents)
states_operating	VARCHAR	Comma-separated operating states (non-FK)
approx_enrollment	INT	Approximate enrollment
enrollment_as_of	VARCHAR	Enrollment period text, e.g. 2026-01
cms_contract_ids	VARCHAR	Comma-separated CMS contract IDs (Medicare Advantage only)
+ dataset provenance variant + interpretation + lifecycle blocks

PAYER_METRIC_SOURCES

Grain: one posting location per payer. PK: source_id. Gated (a small payer cohort is trial-visible). Uses the dataset provenance variant. Contains no metric values — it is the registry of where each payer posts its CMS-required PA metrics, not the metrics themselves.

Column	Type	Description
source_id	VARCHAR	Posting-location id
payer_id	VARCHAR	FK to PAYER_DIRECTORY
posting_url	VARCHAR	URL where the payer posts its public PA metrics (NULL when not located)
posting_format	VARCHAR	HTML | PDF | DASHBOARD | OTHER | NOT_FOUND
covers_lobs	VARCHAR	Comma-separated lines of business the posting covers (non-FK)
cy_covered	VARCHAR	Calendar year covered, e.g. CY2025
found	BOOLEAN	TRUE when a posting location was located and verified; FALSE recorded explicitly when not
first_verified_at	DATE	First verification date
last_verified_at	DATE	Most recent verification date
verification_method	VARCHAR	How the location was confirmed (e.g. dual-verified)
search_notes	VARCHAR	Notes on the search / verification
+ dataset provenance variant + interpretation + lifecycle blocks

PAYER_METRICS

Grain: one posted metric value per (payer × metric × line of business × period). PK: (payer_id, metric_id, lob, period). Gated (a small payer cohort is trial-visible). Uses the dataset provenance variant. The values table: what each covered payer actually posted under its CMS public-reporting obligation, keyed to METRIC_DEFINITIONS so the same metric is comparable across payers. Only human-reviewed values appear — a value that hasn't been individually reviewed never reaches this view, and every promotion is recorded as a VERIFIED entry in RULE_AUDIT_LOG.

Column	Type	Description
payer_id	VARCHAR	FK to PAYER_DIRECTORY
source_id	VARCHAR	FK to PAYER_METRIC_SOURCES — the posting this value was read from
metric_id	VARCHAR	FK to METRIC_DEFINITIONS — which operational PA metric this is
lob	VARCHAR	Line of business / contract as the payer scoped it (e.g. Medicaid, Medicare Advantage)
period	VARCHAR	Reporting period as posted (e.g. CY2025)
value_text	VARCHAR	The value exactly as printed in the posting
value_numeric	FLOAT	Numeric form when the value is a single number; NULL for list/compound values
unit	VARCHAR	percent | hours | days | count | other
snapshot_sha256	VARCHAR	SHA-256 of the archived snapshot of the posting the value was read from
source_pinpoint	VARCHAR	Page / heading in the posting where the value appears
verbatim_excerpt	VARCHAR	The exact quote from the posting that contains the value
harvest_run_id	VARCHAR	Provenance: which extraction run produced the row
prompt_version	VARCHAR	Provenance: extraction prompt version
extraction_method	VARCHAR	How the value was extracted (e.g. dual-pass, reconciled)
quarantine_reason	VARCHAR	NULL on every visible row (a quarantined value never reaches this view)
quarantine_note	VARCHAR	NULL on every visible row
+ dataset provenance variant + interpretation + lifecycle blocks

---

Derived analytics tables

COMPLIANCE_GOLD

Grain: (entity type × obligation), is_current rows only. Derived — excludes every needs_verification = TRUE row; surfaces (never hides) the certainty and confidence flags.

Column	Type	Description
entity_type_id	VARCHAR	The entity type
obligation_id	VARCHAR	The obligation
obligation_category	VARCHAR	Obligation category
applicability	VARCHAR	REQUIRED | EXEMPT | ...
compliance_date_anchor	DATE	Resolved anchor for this entity + obligation
compliance_date_display	VARCHAR	Human phrasing
date_certainty	VARCHAR	FINAL | PROPOSED
binding_status	VARCHAR	FINAL_BINDING | PROPOSED_NOT_BINDING
interpretation_confidence	VARCHAR	HIGH | MEDIUM | LOW

Additional joined context (standards summary, reporting summary, timeframe detail) is projected through from the underlying facts; see the DATA_DICTIONARY for the full column list.

COMPLIANCE_DEADLINES

Grain: one deadline. A pure UNION projection over applicability anchors, recurring reports, document milestones, and implementation-guide expiries. A deadline can never contradict its source fact by construction.

Column	Type	Description
deadline_type	VARCHAR	COMPLIANCE | FIRST_REPORT | RECURRING_ANNUAL | COMMENT_CLOSE | IG_EXPIRY
entity_type_id	VARCHAR	Entity context (where applicable)
obligation_id	VARCHAR	Obligation context (where applicable)
compliance_date_anchor	DATE	The anchor date
compliance_date_type	VARCHAR	Date-semantics type
compliance_date_display	VARCHAR	Human phrasing
date_certainty	VARCHAR	FINAL | PROPOSED

RULE_AUDIT_LOG

Grain: one field-level editorial change. Customer-visible (trial-visible with value masking). Materialized from explicit changelog entries in the curation store, never from git-history parsing.

Column	Type	Description
audit_id	VARCHAR	Change id
table_name	VARCHAR	Table changed
row_pk	VARCHAR	Natural key of the changed row
change_type	VARCHAR	CREATED | SUPERSEDED | STATUS_CHANGE | CORRECTION | VERIFIED
field_changed	VARCHAR	Column changed
old_value	VARCHAR	Prior value (masked to [full access] for gated rows on the trial)
new_value	VARCHAR	New value (masked to [full access] for gated rows on the trial)
change_reason	VARCHAR	Customer-facing reason for the change (always visible)
triggering_document_id	VARCHAR	Document that caused the change
changed_by	VARCHAR	Editorial identity that made the change (Dataplex Editorial)
commit_hash	VARCHAR	Commit that introduced the change
pr_number	VARCHAR	Pull request number
changed_at	TIMESTAMP_NTZ	When the change was made

On the free trial, the change events — table, field, change type, reason, timestamp, triggering document — are always visible; only the old_value / new_value content of gated rows is masked to [full access]. The event stream is the proof the reference is maintained.

CURATION_STATUS

The completeness & quarantine ledger — so "where is my row?" is a query, not a support ticket. One row per currently quarantined row (held back from COMPLIANCE_GOLD pending verification) and per row verified in the last 180 days. row_pk joins back to the named table; table_rows_current is the per-table denominator, so a per-table quarantine percentage is one GROUP BY away. Always fully visible — no trial filter — so an evaluator can see exactly what is and isn't in the compliance set.

Grain: one quarantined-or-recently-verified row. PK: (table_name, row_pk, status).

Column	Type	Description
table_name	VARCHAR	The DWV share table this row belongs to (e.g. OBLIGATION_APPLICABILITY)
row_pk	VARCHAR	Natural key of the row within its table; composite keys are joined with |
status	VARCHAR	QUARANTINED (held back from COMPLIANCE_GOLD pending verification) or VERIFIED (independently confirmed, promoted within the last 180 days)
quarantine_reason	VARCHAR	Why a quarantined row is held: AWAITING_ADVERSARIAL_PASS, AWAITING_FINALIZATION, SOURCE_AMBIGUOUS, or PENDING_SOURCE_VERIFICATION. NULL for verified rows
blocked_on	VARCHAR	For an AWAITING_FINALIZATION quarantine, the document whose finalization would resolve it (e.g. CMS-0062-P) — event-bound, not age-bound. NULL otherwise
interpretation_confidence	VARCHAR	Confidence in the source reading for a quarantined row (HIGH, MEDIUM, LOW). NULL for verified rows
review_date	DATE	When the quarantined row was last human-reviewed (the age clock). NULL for verified rows
days_in_quarantine	INT	Days since review_date, computed live. NULL for verified rows; event-bound rows are reported but exempt from age alarms
verified_at	TIMESTAMP_NTZ	For a verified row, when it was promoted. NULL for quarantined rows
customer_note	VARCHAR	Plain-language note: why a row is held, or what was confirmed on verification
table_rows_current	INT	Total current rows in table_name — the denominator for a per-table completeness percentage
updated_at	TIMESTAMP_NTZ	When dbt last rebuilt this view

---

Semantic view: COMPLIANCE_ANALYST

A Snowflake semantic view included in the share — the semantic model that powers natural-language querying with Cortex Analyst and structured querying with the SQL SEMANTIC_VIEW() construct. It is a lens over the tables above (its relationships mirror the entity-relationship diagram), not a separate copy of the data; the trial and liability gates on the underlying views apply unchanged.

Logical tables (SCD2 dimensions are pre-filtered to current rows):

Logical table	Over	Role
obligations	COMPLIANCE_GOLD	Fact: one row per (obligation × entity type)
entity_types	ENTITY_TYPES (current)	Dimension: the regulated programs
payer_metrics	PAYER_METRICS	Fact: posted PA metric values
payers	PAYER_DIRECTORY (current)	Dimension: payer parent organizations
metric_defs	METRIC_DEFINITIONS (current)	Dimension: which PA metric

Dimensions: entity_type_id, entity_type_name, obligation_id, obligation_category, applicability, compliance_date_anchor, date_certainty, binding_status, payer_name, payer_kind, metric_name, line_of_business, reporting_period, unit — each with synonyms so Cortex Analyst understands "insurer", "deadline", "program", etc.

Metrics: obligation_count, avg_metric_value, min_metric_value, max_metric_value, value_count.

Use it two ways:

• Cortex Analyst (natural language): in Snowsight, AI & ML → Cortex Analyst → select DWV.COMPLIANCE_ANALYST, then ask questions in plain English.

• SQL via SEMANTIC_VIEW() — see the cookbook examples.

---

Metadata tables

FEEDS

Dataset catalog. last_updated_at is the greatest RULE_AUDIT_LOG.changed_at per table — curation-driven and honest, never CURRENT_TIMESTAMP().

Key columns: id (PK), table_name, description, last_updated_at, total_rows_extracted, total_batches.

FEEDS_FILES

Curation-release history — one row per published curation release (e.g. R1.0.0), with an is_latest flag on the newest release. Unlike Dataplex's file-based products, rows here represent curation releases, not source-file loads; created_at is the release date and moves only when a release ships.

Key columns: id (PK), release_id (e.g. R1.3.0), release_date, title, description, is_latest, created_at, updated_at.

CHANGELOG

Change log for curation releases, schema changes, and corrections.

Key columns: event_id (PK), event_date, event_type, table_name, description.

DATA_DICTIONARY

Column-level descriptions for every table.

Key columns: entry_id (PK), table_name, column_name, data_type, column_description.

---

Informational reference compiled from public Federal Register, eCFR, and CMS sources. Not legal advice; consult counsel for compliance decisions.