Snowflake Cortex Analyst: Natural Language to Analytics with Semantic Models

Snowflake Cortex Analyst lets business users ask questions in natural language and have the service automatically generate and run SQL queries against structured data. It uses a semantic model (defined in YAML) to understand the underlying table structure and business logic, producing accurate SQL. This article walks through how to design the semantic model, how the conversion flow works, and how to set up permissions.

What is Cortex Analyst?

Cortex Analyst is a managed Snowflake service that takes a natural-language question and generates a SQL query based on a semantic model. Traditional BI tools require a dashboard designer to predefine queries up front; with Cortex Analyst, end users simply ask free-form questions and get answers back.

The generated SQL is restricted to SELECT statements, so there is no risk of data modification. Snowflake's RBAC is also enforced, so data the user's role cannot access never appears in the results.

Natural Language to SQL Conversion Flow

[Business user]
       │ "What was last month's revenue by product category?"
       ▼
[Cortex Analyst API]
       │
       ├─ 1. Load the semantic model (YAML)
       │     └─ learn the table structure, column meanings and formulas
       │
       ├─ 2. The LLM parses the natural language
       │     └─ understands the intent and maps it to tables and columns
       │
       ├─ 3. Generate the SQL query (SELECT only)
       │     └─ build joins and aggregations per the semantic model
       │
       └─ 4. Return the SQL (run it automatically or after user confirmation)
              └─ the result is returned as JSON in the API response

Structure of the Semantic Model

Semantic models are written in YAML and placed on a Snowflake stage. The model defines logical tables, dimensions, measures, time dimensions, and relationships.

# semantic_model.yaml
name: sales_analytics
description: "Semantic model for e-commerce revenue analysis"

tables:
  - name: orders
    base_table:
      database: ANALYTICS_DB
      schema: PUBLIC
      table: FACT_ORDERS
    description: "Order transaction table"

    dimensions:
      - name: order_id
        expr: ORDER_ID
        description: "Unique order identifier"
        data_type: VARCHAR

      - name: product_category
        expr: PRODUCT_CATEGORY
        description: "Product category (electronics, food, apparel, and so on)"
        data_type: VARCHAR

    time_dimensions:
      - name: order_date
        expr: ORDER_DATE
        description: "Order date"
        data_type: DATE

    measures:
      - name: total_revenue
        expr: SUM(ORDER_AMOUNT)
        description: "Total revenue"
        data_type: NUMBER

      - name: order_count
        expr: COUNT(DISTINCT ORDER_ID)
        description: "Order count"
        data_type: NUMBER

      - name: avg_order_value
        expr: AVG(ORDER_AMOUNT)
        description: "Average order value"
        data_type: NUMBER

  - name: customers
    base_table:
      database: ANALYTICS_DB
      schema: PUBLIC
      table: DIM_CUSTOMERS
    description: "Customer master"

    dimensions:
      - name: customer_id
        expr: CUSTOMER_ID
        description: "Customer ID"
        data_type: VARCHAR

      - name: region
        expr: REGION
        description: "Customer region"
        data_type: VARCHAR

relationships:
  - name: order_customer
    left_table: orders
    right_table: customers
    join_type: LEFT
    relationship_columns:
      - left_column: CUSTOMER_ID
        right_column: CUSTOMER_ID

verified_queries:
  - name: "Monthly revenue by category"
    question: "Show me monthly revenue by product category"
    sql: |
      SELECT
        DATE_TRUNC('MONTH', o.ORDER_DATE) AS month,
        o.PRODUCT_CATEGORY,
        SUM(o.ORDER_AMOUNT) AS total_revenue
      FROM ANALYTICS_DB.PUBLIC.FACT_ORDERS o
      GROUP BY 1, 2
      ORDER BY 1, 3 DESC

Key Elements of the YAML Definition

Calling the API

Cortex Analyst can be invoked via the REST API or the Python SDK. You can also embed it in a Streamlit app inside Snowsight.

# Example call from the Python SDK
import json
from snowflake.core import Root

root = Root(session)

analyst = root.databases["ANALYTICS_DB"].schemas["PUBLIC"].cortex_analyst

response = analyst.send_message(
    semantic_model_file="@my_stage/semantic_model.yaml",
    messages=[
        {"role": "user", "content": "Show me last month's top 5 regions by revenue"}
    ]
)

# Pull the SQL and the result out of the response
for item in response.message.content:
    if item.type == "sql":
        print("Generated SQL:", item.statement)
    elif item.type == "text":
        print("Explanation:", item.text)

Permission Design

Cortex Analyst permissions follow Snowflake's standard RBAC model.

-- Example grants for Cortex Analyst users
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE analyst_role;
GRANT USAGE ON DATABASE analytics_db TO ROLE analyst_role;
GRANT USAGE ON SCHEMA analytics_db.public TO ROLE analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.public TO ROLE analyst_role;
GRANT READ ON STAGE analytics_db.public.my_stage TO ROLE analyst_role;
GRANT USAGE ON WAREHOUSE analyst_wh TO ROLE analyst_role;

Verified Queries

Registering question-SQL pairs in the verified_queries section of the semantic model improves SQL generation accuracy when similar questions come in. This is a few-shot prompting approach and is particularly effective when you have business-specific terminology or logic.

• Write each question the way a real business user would phrase it
• SQL should reference tables and columns defined in the semantic model
• Registering 10-20 verified queries significantly improves SQL generation accuracy

Limitations and Caveats

• Generated SQL is SELECT only (no DML or DDL is generated)
• Tables and columns not defined in the semantic model cannot be referenced
• Accuracy can drop on very complex joins (5 or more tables)
• Inadequate descriptions in the semantic model can lead to incorrect column mappings
• Cortex Analyst execution is currently billed via serverless credits

Exam Tips

• Semantic models are written in YAML and placed on a stage
• Generated SQL is limited to SELECT statements and RBAC is enforced
• verified_queries are a few-shot mechanism that improves SQL generation accuracy
• Clearly distinguish between Cortex Analyst and Cortex Search
• The SNOWFLAKE.CORTEX_USER database role must be granted

Sample Question

Frequently Asked Questions