> 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-prior-auth-compliance-dataset/compliance-questions-cookbook.md).

# Compliance Questions Cookbook

The questions a payer compliance, regulatory-affairs, or product team actually asks about the CMS prior-authorization rulemaking wave, each answered by **one** query. Questions 1–10 cover the obligations, deadlines, standards, and reporting duties; 11–13 cover the payer directory, the metrics-posting registry, and the posted values; 14–18 cover tracking changes over time. Queries use the `DWV` schema on Snowflake. Several of these are the dataset's Snowflake Marketplace example queries (marked ★).

{% hint style="warning" %}
Informational reference compiled from public Federal Register, eCFR, and CMS sources. Not legal advice; consult counsel for compliance decisions.
{% endhint %}

## 1. What is my full compliance calendar? ★

Every prior-authorization compliance deadline that applies to a given entity type, with the date semantics and how certain each date is.

```sql
SELECT
    entity_type_id,
    obligation_id,
    deadline_type,
    compliance_date_anchor,
    compliance_date_display,
    date_certainty
FROM DWV.COMPLIANCE_DEADLINES
WHERE entity_type_id = 'MA_ORG'
ORDER BY compliance_date_anchor NULLS LAST;
```

## 2. Everything that applies to me, in one row per obligation

The gold answer table: what applies to my entity type, by which anchor date under which date semantics, at what certainty and binding status.

```sql
SELECT
    obligation_id,
    obligation_category,
    applicability,
    compliance_date_anchor,
    compliance_date_display,
    date_certainty,
    binding_status,
    interpretation_confidence
FROM DWV.COMPLIANCE_GOLD
WHERE entity_type_id = 'MEDICAID_MC'
ORDER BY compliance_date_anchor, obligation_category;
```

## 3. What must I post publicly on my website, and by when? ★

The annual public prior-authorization metrics payers must post, the required content elements, and the recurring due rule and first due date.

```sql
SELECT
    rr.reporting_requirement_id,
    rr.content_element,
    rr.cadence,
    rr.due_rule,
    rr.first_due_date,
    md.numerator_text,
    md.denominator_text
FROM DWV.REPORTING_REQUIREMENTS rr
LEFT JOIN DWV.METRIC_DEFINITIONS md
    ON md.metric_id = rr.metric_id
   AND md.is_current = TRUE
WHERE rr.destination = 'PAYER_WEBSITE'
  AND rr.is_current = TRUE
ORDER BY rr.first_due_date, rr.content_element;
```

## 4. What must I report to CMS, and when?

The usage metrics reported directly to CMS (as opposed to posted on the payer website), with their cadence and first due date.

```sql
SELECT
    reporting_requirement_id,
    obligation_id,
    content_element,
    cadence,
    due_rule,
    first_due_date
FROM DWV.REPORTING_REQUIREMENTS
WHERE destination = 'CMS'
  AND is_current = TRUE
ORDER BY first_due_date, content_element;
```

## 5. What must a denial notice contain? ★

The required content elements of a prior-authorization denial notice and the channels each applies to.

```sql
SELECT
    element_id,
    obligation_id,
    element_name,
    element_description_verbatim,
    channel_scope
FROM DWV.DENIAL_NOTICE_ELEMENTS
WHERE is_current = TRUE
ORDER BY channel_scope, element_id;
```

## 6. Who is subject to the decision timeframes, and who is exempt? ★

Decision-timeframe applicability across entity types for the PA decision-clock obligation, surfacing the explicit exemption (QHP issuers on the FFEs) as its own row rather than a missing one.

```sql
SELECT
    entity_type_id,
    applicability,
    compliance_date_display,
    timeframe_detail,
    extension_exemption_notes
FROM DWV.OBLIGATION_APPLICABILITY
WHERE obligation_id = 'CMS-0057-F:PA-DECISION-TIMEFRAMES'
  AND is_current = TRUE
ORDER BY applicability, entity_type_id;
```

## 7. Which FHIR / Da Vinci implementation guides does each API obligation require?

Each API obligation mapped to its named standards and implementation-guide versions, with the binding strength (required vs named vs proposed).

```sql
SELECT
    o.obligation_id,
    o.obligation_name,
    os.binding_strength,
    sr.standard_family,
    sr.standard_name,
    sr.version,
    sr.version_status
FROM DWV.RULE_OBLIGATIONS o
JOIN DWV.OBLIGATION_STANDARDS os
    ON os.obligation_id = o.obligation_id
   AND os.is_current = TRUE
JOIN DWV.STANDARDS_REGISTRY sr
    ON sr.standard_id = os.standard_id
   AND sr.is_current = TRUE
WHERE o.obligation_category = 'API'
  AND o.is_current = TRUE
ORDER BY o.obligation_id, os.binding_strength;
```

## 8. What is explicitly out of scope, and on what basis?

The program and plan types CMS did **not** include, each with the verbatim citation basis for exclusion, so you can prove a scope determination.

```sql
SELECT
    entity_type_id,
    entity_type_name,
    scope_basis,
    program_notes
FROM DWV.ENTITY_TYPES
WHERE in_scope = FALSE
  AND is_current = TRUE
ORDER BY entity_type_id;
```

## 9. What is coming that is not yet binding? ★

The forward radar: proposed obligations flagged as not binding (for example CMS-0062-P drug prior-authorization) so roadmaps anticipate the next requirement.

```sql
SELECT
    entity_type_id,
    obligation_id,
    obligation_category,
    compliance_date_display,
    binding_status,
    date_certainty
FROM DWV.COMPLIANCE_GOLD
WHERE binding_status = 'PROPOSED_NOT_BINDING'
ORDER BY compliance_date_anchor;
```

## 10. Which implementation-guide versions are expiring?

The standard and IG versions with a status or expiry date on the horizon (for example the STU-2-era stack proposed to expire January 1, 2028) so upgrade planning starts early.

```sql
SELECT
    standard_id,
    standard_family,
    standard_name,
    version,
    version_status,
    version_expiry_date
FROM DWV.STANDARDS_REGISTRY
WHERE (version_status IN ('EXPIRING', 'EXPIRED')
   OR version_expiry_date IS NOT NULL)
  AND is_current = TRUE
ORDER BY version_expiry_date NULLS LAST, standard_family;
```

## 11. Which payers am I benchmarking against?

The payer parent organizations in a segment (for example the Medicare Advantage parents, ranked by enrollment) from the payer directory. Filter `payer_kind` and `state` / `states_operating` for the segment you care about.

```sql
SELECT
    payer_id,
    payer_name,
    payer_kind,
    states_operating,
    approx_enrollment,
    enrollment_as_of
FROM DWV.PAYER_DIRECTORY
WHERE payer_kind = 'MA_PARENT_ORG'
  AND is_current = TRUE
ORDER BY approx_enrollment DESC NULLS LAST, payer_name;
```

## 12. Where does each major payer post its PA metrics? ★

The verified posting locations for the top payers' CMS-required annual public PA metrics, joined to the directory (only rows where a location was found). The registry gives the posting location; the posted values themselves are in `PAYER_METRICS` (next question).

```sql
SELECT
    d.payer_name,
    d.payer_kind,
    s.cy_covered,
    s.posting_format,
    s.posting_url,
    s.covers_lobs,
    s.last_verified_at
FROM DWV.PAYER_METRIC_SOURCES s
JOIN DWV.PAYER_DIRECTORY d
    ON d.payer_id = s.payer_id
WHERE s.found = TRUE
  AND s.is_current = TRUE
ORDER BY d.payer_name, s.cy_covered;
```

## 13. What PA metric values did each payer post? ★

The posted values themselves (approval and denial rates, decision times, appeals-overturn, request volumes), joined to the metric definitions and payer directory. Every value is human-reviewed before it appears here, and carries the verbatim excerpt from the payer's own posting that licenses it.

```sql
SELECT
    d.payer_name,
    m.lob,
    m.period,
    md.metric_name,
    m.value_text,
    m.value_numeric,
    m.unit,
    m.verbatim_excerpt
FROM DWV.PAYER_METRICS m
JOIN DWV.METRIC_DEFINITIONS md
    ON md.metric_id = m.metric_id
   AND md.is_current = TRUE
JOIN DWV.PAYER_DIRECTORY d
    ON d.payer_id = m.payer_id
   AND d.is_current = TRUE
WHERE m.is_current = TRUE
ORDER BY d.payer_name, md.metric_name;
```

To compare one metric across payers, filter on `metric_id` (for example `PA_APPROVAL_RATE_STD`) and sort by `value_numeric`.

## Tracking changes over time

This is a maintained reference, not a one-time snapshot, and it tracks change at the row and field level rather than by versioning source files. Three layers work together: `FEEDS` / `FEEDS_FILES` tell you which release you are on, `RULE_AUDIT_LOG` tells you what changed and why, and the SCD2 lifecycle columns (`row_version`, `is_current`, `curation_valid_from`, `curation_valid_to`) let you reconstruct what the reference asserted on any past date. See [How time works in this product](/data-catalog/cms-prior-auth-compliance-dataset.md#how-time-works-in-this-product) for the mental model.

## 14. What has changed in the reference, and why? ★

The field-level audit trail of every substantive editorial change (corrections, verifications, and supersessions), each with its customer-facing reason. This is the proof the reference is maintained.

```sql
SELECT
    changed_at,
    table_name,
    row_pk,
    change_type,
    change_reason
FROM DWV.RULE_AUDIT_LOG
WHERE change_type IN ('VERIFIED', 'SUPERSEDED', 'CORRECTION')
ORDER BY changed_at DESC
LIMIT 50;
```

To see only what changed since your last sync, add a date predicate, `AND changed_at >= '2026-06-01'` (your last-checked date), and drop the `change_type` filter to include the initial `CREATED` events as well.

## 15. Which release am I on, and what shipped in each one?

The full curation-release history from `FEEDS_FILES`: one row per published release, with `is_latest = TRUE` marking the release you are currently on. Every data row's `feeds_files_id` points at the release that shipped it.

```sql
SELECT
    release_id,
    release_date,
    title,
    description,
    is_latest
FROM DWV.FEEDS_FILES
ORDER BY release_date DESC, release_id DESC;
```

## 16. What did the reference assert on a given date?

Point-in-time reconstruction. Because rows are versioned append-only (never overwritten), filtering an obligation's applicability rows to the versions whose curation-validity interval contains your as-of date returns the reference exactly as it stood then. Replace the date with the as-of date you care about; a date earlier than a row's `curation_valid_from` returns nothing for that row, because the reference had not asserted it yet.

```sql
SELECT
    entity_type_id,
    applicability,
    compliance_date_anchor,
    compliance_date_display,
    row_version
FROM DWV.OBLIGATION_APPLICABILITY
WHERE obligation_id = 'CMS-0057-F:PA-DECISION-TIMEFRAMES'
  AND curation_valid_from <= DATE '2027-06-01'
  AND (curation_valid_to IS NULL OR curation_valid_to > DATE '2027-06-01')
ORDER BY entity_type_id;
```

## 17. What is the full history of one obligation row?

Two queries reconstruct the complete life of a single applicability fact. First, every version of the row from `OBLIGATION_APPLICABILITY`, the current version plus any superseded predecessors, in order:

```sql
SELECT
    row_version,
    row_status,
    is_current,
    applicability,
    compliance_date_anchor,
    compliance_date_display,
    curation_valid_from,
    curation_valid_to,
    superseded_by_row_id
FROM DWV.OBLIGATION_APPLICABILITY
WHERE obligation_id = 'CMS-0057-F:PA-DECISION-TIMEFRAMES'
  AND entity_type_id = 'MA_ORG'
ORDER BY row_version;
```

Then the field-level audit entries for that same row. In `RULE_AUDIT_LOG`, `row_pk` is the row's natural key: for the composite-key applicability table it is `obligation_id | entity_type_id` (space-pipe-space); single-key tables use the bare primary key.

```sql
SELECT
    changed_at,
    change_type,
    field_changed,
    old_value,
    new_value,
    change_reason,
    triggering_document_id
FROM DWV.RULE_AUDIT_LOG
WHERE table_name = 'OBLIGATION_APPLICABILITY'
  AND row_pk = 'CMS-0057-F:PA-DECISION-TIMEFRAMES | MA_ORG'
ORDER BY changed_at;
```

## 18. What is currently under verification, and why?

The quarantine ledger from `CURATION_STATUS`. Rows flagged `needs_verification` are excluded from `COMPLIANCE_GOLD` and every derived view until confirmed; this query surfaces them with the reason they are held, how long they have been held (`days_in_quarantine`), and the current row count of the table they belong to (`table_rows_current`) so you can read quarantined items as a share of the whole.

```sql
SELECT
    table_name,
    row_pk,
    quarantine_reason,
    blocked_on,
    interpretation_confidence,
    days_in_quarantine,
    table_rows_current,
    customer_note
FROM DWV.CURATION_STATUS
WHERE status = 'QUARANTINED'
ORDER BY days_in_quarantine DESC NULLS LAST;
```

## Using Snowflake Cortex on the text columns

Much of this dataset is dense free text: verbatim Federal Register rule text (`requirement_text_verbatim`), payers' free-text service lists (`PA_ITEMS_SERVICES_LIST` values), exemption notes, and the audit trail's change reasons. Snowflake Cortex AI functions run directly on those columns inside your account, with no data movement and no external API keys. Four patterns consumers use:

{% hint style="info" %}
Cortex AI functions are billed to your account per use, and model availability varies by cloud region; swap in any model available in yours, or enable cross-region inference. `AI_EXTRACT` and `AI_CLASSIFY` need no model parameter; `AI_COMPLETE` takes a model name.
{% endhint %}

### Ask the whole product a question with the semantic view

The share includes the **`COMPLIANCE_ANALYST` semantic view**. For fully natural-language querying, point **Cortex Analyst** at it (Snowsight → AI & ML → Cortex Analyst → `DWV.COMPLIANCE_ANALYST`). The same semantic model is queryable in SQL with the `SEMANTIC_VIEW()` construct, with no joins to write and relationships and definitions built in:

```sql
-- How many obligations apply to each program?
SELECT *
FROM SEMANTIC_VIEW(
    DWV.COMPLIANCE_ANALYST
    DIMENSIONS entity_type_name
    METRICS obligation_count
)
ORDER BY obligation_count DESC;
```

```sql
-- Benchmark payers on one posted metric; the semantic view resolves the
-- payer-directory and metric-definition joins for you.
SELECT *
FROM SEMANTIC_VIEW(
    DWV.COMPLIANCE_ANALYST
    DIMENSIONS payer_name, metric_name
    METRICS avg_metric_value
    WHERE metric_name ILIKE '%standard%approval%'
)
ORDER BY avg_metric_value DESC;
```

```sql
-- What is coming that is proposed but not yet binding, by category?
SELECT *
FROM SEMANTIC_VIEW(
    DWV.COMPLIANCE_ANALYST
    DIMENSIONS obligation_category, date_certainty
    METRICS obligation_count
    WHERE binding_status = 'PROPOSED_NOT_BINDING'
)
ORDER BY obligation_count DESC;
```

### Ask the rule text a question

For a compliance analyst: extract a specific answer from the verbatim rule text instead of reading the Federal Register; here, the standard prior-authorization decision deadline.

```sql
SELECT
    obligation_id,
    obligation_name,
    AI_EXTRACT(
        text => requirement_text_verbatim,
        responseFormat => [['deadline',
            'What is the deadline for standard prior authorization decisions?']]
    ):response:deadline::string AS answer_from_rule_text
FROM DWV.RULE_OBLIGATIONS
WHERE obligation_id = 'CMS-0057-F:PA-DECISION-TIMEFRAMES'
  AND is_current = TRUE;
```

### Normalize payers' free-text service lists

For a benchmarking analyst: every payer phrases its "items and services requiring prior authorization" differently. Classify the free text into comparable categories to build a cross-payer view.

```sql
SELECT
    d.payer_name,
    m.period,
    m.value_text AS services_requiring_pa,
    AI_CLASSIFY(
        m.value_text,
        ['imaging and radiology', 'surgical procedures',
         'durable medical equipment', 'behavioral health',
         'pharmacy / specialty drugs', 'mixed / multiple categories']
    ):labels[0]::string AS service_category
FROM DWV.PAYER_METRICS m
JOIN DWV.PAYER_DIRECTORY d
    ON d.payer_id = m.payer_id
   AND d.is_current = TRUE
WHERE m.metric_id = 'PA_ITEMS_SERVICES_LIST'
  AND m.is_current = TRUE
  AND LENGTH(m.value_text) > 20;  -- skip stub values (titles, raw counts)
```

### Summarize an obligation for a briefing

For regulatory affairs: one-paragraph summaries of the longest rule-text passages, straight from the verbatim source.

```sql
SELECT
    obligation_id,
    obligation_name,
    AI_COMPLETE(
        'mistral-large2',
        'Summarize this regulatory requirement in 2-3 sentences: '
        || requirement_text_verbatim
    ) AS summary
FROM DWV.RULE_OBLIGATIONS
WHERE obligation_category = 'API'
  AND is_current = TRUE;
```

### A plain-English obligations register

For consultants and readiness reviews: generate a one-sentence plain-English statement of every obligation that applies to an entity type, joined from the gold table. Uses `COMPLETE` with a named model; pick one available in your region.

```sql
SELECT
    g.obligation_id,
    g.compliance_date_display,
    AI_COMPLETE(
        'mistral-large2',
        'In one sentence, state what this CMS obligation requires a Medicare Advantage organization to do: '
        || o.requirement_text_verbatim
    ) AS plain_english_requirement
FROM DWV.COMPLIANCE_GOLD g
JOIN DWV.RULE_OBLIGATIONS o
    ON o.obligation_id = g.obligation_id
   AND o.is_current = TRUE
WHERE g.entity_type_id = 'MA_ORG'
  AND g.applicability = 'REQUIRED'
ORDER BY g.compliance_date_anchor;
```

***

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


---

# 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-prior-auth-compliance-dataset/compliance-questions-cookbook.md?ask=<question>&goal=<endgoal>
```

`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.