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.
| Scenario | Without VQR | With VQR |
|---|---|---|
| "What is our MRR?" | May guess the wrong filter logic | Uses exact verified calculation |
| "Top customers by LTV" | Might miss inactive customer exclusion | Includes all business rules |
| "QoQ revenue growth" | Could miscalculate quarter boundaries | Uses 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:
| Practice | Description |
|---|---|
| Ownership | Assign each VQR to a team or individual |
| Review cadence | Review VQRs quarterly for accuracy |
| Change process | Require PR approval for VQR modifications |
| Testing | Automated tests that run VQR SQL and validate output |
| Documentation | Record 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:
- Store semantic view YAML in a Git repository
- Review changes via pull requests
- Deploy to Snowflake via a CI/CD pipeline using
snowCLI or SQL scripts - Tag releases for rollback capability
- 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.