Skip to main content

Verified Queries and Metrics

Overview​

Verified Query Representations (VQRs) are curated question-SQL pairs embedded in a semantic view. When a user asks a question that matches or closely resembles a VQR, Cortex Analyst uses the verified SQL as a template instead of generating from scratch. This dramatically improves accuracy for high-value, frequently-asked questions.

Why Verified Queries Improve Accuracy​

Without VQRs, Cortex Analyst must infer the correct SQL from the semantic model alone. This works well for straightforward questions but can struggle with:

  • Complex business logic (e.g., fiscal year calculations)
  • Domain-specific terminology that maps to non-obvious SQL
  • Multi-step aggregations with specific filter conditions
  • Questions where multiple valid SQL interpretations exist

VQRs eliminate ambiguity by providing the authoritative SQL for critical questions.

ScenarioWithout VQRWith VQR
"What is our MRR?"May guess the wrong filter logicUses exact verified calculation
"Top customers by LTV"Might miss inactive customer exclusionIncludes all business rules
"QoQ revenue growth"Could miscalculate quarter boundariesUses fiscal calendar logic

Creating Verified Queries in YAML​

VQRs are defined in the verified_queries section of your semantic view YAML:

verified_queries:
- name: monthly_revenue_by_region
question: "What is the monthly revenue by region?"
sql: |
SELECT
DATE_TRUNC('month', o.created_at) AS month,
c.customer_region AS region,
SUM(o.amount) AS total_revenue
FROM analytics_db.public.fact_orders o
JOIN analytics_db.public.dim_customers c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC
verified_at: "2025-06-15"
verified_by: "data_engineering_team"

- name: top_10_customers_by_spend
question: "Who are our top 10 customers by total spend?"
sql: |
SELECT
c.customer_name,
c.customer_region,
SUM(o.amount) AS total_spend
FROM analytics_db.public.fact_orders o
JOIN analytics_db.public.dim_customers c
ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
AND c.is_active = TRUE
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10
verified_at: "2025-06-15"
verified_by: "data_engineering_team"

Adding VQRs to a Semantic View​

Include the verified_queries section in your CREATE SEMANTIC VIEW statement:

CREATE OR REPLACE SEMANTIC VIEW analytics_db.public.sales_semantic_view
AS $$
name: sales_analytics
tables:
- name: orders
base_table:
database: analytics_db
schema: public
table: fact_orders
dimensions:
- name: order_status
expr: status
description: "Order status"
measures:
- name: total_revenue
expr: SUM(amount)
description: "Total revenue"
default_aggregation: sum
verified_queries:
- name: monthly_revenue_by_region
question: "What is the monthly revenue by region?"
sql: |
SELECT DATE_TRUNC('month', created_at) AS month,
customer_region AS region,
SUM(amount) AS total_revenue
FROM analytics_db.public.fact_orders
WHERE status = 'completed'
GROUP BY 1, 2
ORDER BY 1 DESC
$$;

Testing Verified Queries​

Always validate VQRs before deploying:

-- Step 1: Run the VQR SQL directly to confirm it returns correct results
SELECT DATE_TRUNC('month', created_at) AS month,
SUM(amount) AS total_revenue
FROM analytics_db.public.fact_orders
WHERE status = 'completed'
GROUP BY 1 ORDER BY 1 DESC;

-- Step 2: Ask Cortex Analyst the matching question
SELECT SNOWFLAKE.CORTEX.ANALYST(
'What is the monthly revenue?',
SEMANTIC_VIEW_REFERENCE => 'analytics_db.public.sales_semantic_view'
);

-- Step 3: Compare the generated SQL against your VQR

Metrics Governance​

VQRs serve as governed metric definitions. Establish a process for managing them:

PracticeDescription
OwnershipAssign each VQR to a team or individual
Review cadenceReview VQRs quarterly for accuracy
Change processRequire PR approval for VQR modifications
TestingAutomated tests that run VQR SQL and validate output
DocumentationRecord business context for each VQR's logic

Versioning Semantic Models​

As your semantic model evolves, version it carefully:

-- Use naming conventions to track versions
CREATE SEMANTIC VIEW analytics_db.public.sales_semantic_view_v2
AS $$ ... $$;

-- Or use Git-based version control for the YAML
-- Store YAML files in a repository and deploy via CI/CD

A recommended workflow:

  1. Store semantic view YAML in a Git repository
  2. Review changes via pull requests
  3. Deploy to Snowflake via a CI/CD pipeline using snow CLI or SQL scripts
  4. Tag releases for rollback capability
  5. Keep a changelog of metric definition changes

Writing Effective VQRs​

Guidelines for VQR questions:

  • Be specific: "What is the total revenue by region for the last 12 months?" not "Show revenue"
  • Cover variations: add multiple VQRs for the same metric with different phrasings
  • Include edge cases: questions involving NULLs, fiscal periods, or excluded categories
  • Match real user questions: review Cortex Analyst query logs to find common patterns

Next Steps​

Proceed to Cortex Analyst to build an application that uses your semantic view and verified queries.