Skip to main content

Cortex Analyst — Natural Language to SQL

What is Cortex Analyst​

Cortex Analyst is a Snowflake service that translates natural language questions into SQL queries using a semantic view as context. It reads your defined dimensions, measures, relationships, and verified queries to generate accurate SQL without requiring users to know the schema.

Key characteristics:

  • Fully managed by Snowflake — no infrastructure to deploy
  • Uses semantic views for grounding, not raw table schemas
  • Supports verified queries for high-accuracy answers
  • Returns both the generated SQL and the result set
  • Operates within Snowflake's security model (RBAC applies)

How Cortex Analyst Uses Semantic Views​

 "What was Q4 revenue by region?"
│
▼
Cortex Analyst
│
├── Reads semantic view YAML
├── Identifies: measure=total_revenue, dimension=region, time=order_date
├── Checks VQRs for a match
├── Generates SQL with correct joins, filters, aggregations
│
▼
Executes SQL → Returns results

The semantic view provides the mapping from business concepts to physical columns. Without it, the model would need to guess column meanings from names alone.

ANALYST Function Syntax​

-- Basic usage
SELECT SNOWFLAKE.CORTEX.ANALYST(
'What is the total revenue by region?',
SEMANTIC_VIEW_REFERENCE => 'analytics_db.public.sales_semantic_view'
);

The function returns a JSON object containing:

FieldDescription
sqlThe generated SQL query
resultsQuery result rows
interpretationHow the question was understood
confidenceConfidence level of the answer

Building a Cortex Analyst Application​

Step 1: Create the Semantic View​

CREATE OR REPLACE SEMANTIC VIEW analytics_db.public.sales_sv AS $$
name: sales_analytics
tables:
- name: orders
base_table:
database: analytics_db
schema: public
table: fact_orders
dimensions:
- name: region
expr: customer_region
description: "Customer region"
measures:
- name: total_revenue
expr: SUM(amount)
description: "Total order revenue"
default_aggregation: sum
time_dimensions:
- name: order_date
expr: created_at
description: "Order date"
data_type: date
$$;

Step 2: Test with Direct SQL​

SELECT SNOWFLAKE.CORTEX.ANALYST(
'What is total revenue by region for the last 6 months?',
SEMANTIC_VIEW_REFERENCE => 'analytics_db.public.sales_sv'
);

Step 3: Build a Streamlit Chat Interface​

import streamlit as st
from snowflake.snowpark.context import get_active_session

session = get_active_session()
st.title("Sales Analytics Chat")

if "messages" not in st.session_state:
st.session_state.messages = []

for msg in st.session_state.messages:
with st.chat_message(msg["role"]):
st.markdown(msg["content"])

if prompt := st.chat_input("Ask a question about sales data"):
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.markdown(prompt)

result = session.sql(f"""
SELECT SNOWFLAKE.CORTEX.ANALYST(
'{prompt.replace("'", "''")}',
SEMANTIC_VIEW_REFERENCE => 'analytics_db.public.sales_sv'
)
""").collect()

response = result[0][0]
with st.chat_message("assistant"):
st.markdown(response)
st.session_state.messages.append({"role": "assistant", "content": response})

Accuracy Optimization​

TechniqueImpactEffort
Add descriptions to all columnsHighLow
Add synonyms for business termsHighLow
Create VQRs for top 20 questionsVery HighMedium
Add sample values to dimensionsMediumLow
Define relationships explicitlyHighMedium
Add filters for common conditionsMediumLow

Handling Ambiguous Questions​

When a question is ambiguous, Cortex Analyst may:

  1. Pick the most likely interpretation based on semantic model context
  2. Return a lower confidence score indicating uncertainty
  3. Generate SQL that may not match user intent

To reduce ambiguity:

  • Add more VQRs covering different phrasings
  • Use descriptive synonyms
  • Provide sample values so the model understands the data domain
  • Add descriptions that clarify edge cases (e.g., "Revenue excludes refunds and taxes")

Permissions Required​

-- Grant usage on the semantic view
GRANT USAGE ON SEMANTIC VIEW analytics_db.public.sales_sv TO ROLE analyst_role;

-- The role also needs SELECT on underlying tables
GRANT SELECT ON TABLE analytics_db.public.fact_orders TO ROLE analyst_role;

Next Steps​

Proceed to Cortex Agents to build agents that combine Cortex Analyst with search and Python tools.