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 (Snowflake); on Databricks use the lowercase cms_prior_auth_compliance_dwv schema and lowercase table names. Several of these are the dataset's Snowflake Marketplace example queries (marked ★).

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

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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

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.

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 — the reference had not asserted it yet.

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:

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.

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.

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 — no data movement, no external API keys. Four patterns consumers use:

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.

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 — no joins to write, relationships and definitions are built in:

-- 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;

-- 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;

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

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.

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.

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.

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.