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:
| Field | Description |
|---|---|
sql | The generated SQL query |
results | Query result rows |
interpretation | How the question was understood |
confidence | Confidence 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
| Technique | Impact | Effort |
|---|---|---|
| Add descriptions to all columns | High | Low |
| Add synonyms for business terms | High | Low |
| Create VQRs for top 20 questions | Very High | Medium |
| Add sample values to dimensions | Medium | Low |
| Define relationships explicitly | High | Medium |
| Add filters for common conditions | Medium | Low |
Handling Ambiguous Questions
When a question is ambiguous, Cortex Analyst may:
- Pick the most likely interpretation based on semantic model context
- Return a lower confidence score indicating uncertainty
- 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.