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

[ビジネスユーザー]
       │ 「先月の商品カテゴリ別売上は？」
       ▼
[Cortex Analyst API]
       │
       ├─ 1. セマンティックモデル（YAML）を読み込み
       │     └─ テーブル構造・カラムの意味・計算式を把握
       │
       ├─ 2. LLMが自然言語を解析
       │     └─ 意図を理解し、該当するテーブル・カラムをマッピング
       │
       ├─ 3. SQLクエリを生成（SELECT文のみ）
       │     └─ セマンティックモデルの定義に従ったJOIN・集計を構築
       │
       └─ 4. SQLを返却（自動実行 or ユーザー確認後実行）
              └─ 結果はJSON形式でAPIレスポンスに含まれる

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: "EC売上分析用セマンティックモデル"

tables:
  - name: orders
    base_table:
      database: ANALYTICS_DB
      schema: PUBLIC
      table: FACT_ORDERS
    description: "注文トランザクションテーブル"

    dimensions:
      - name: order_id
        expr: ORDER_ID
        description: "注文の一意識別子"
        data_type: VARCHAR

      - name: product_category
        expr: PRODUCT_CATEGORY
        description: "商品カテゴリ（家電、食品、衣料品など）"
        data_type: VARCHAR

    time_dimensions:
      - name: order_date
        expr: ORDER_DATE
        description: "注文日"
        data_type: DATE

    measures:
      - name: total_revenue
        expr: SUM(ORDER_AMOUNT)
        description: "合計売上金額"
        data_type: NUMBER

      - name: order_count
        expr: COUNT(DISTINCT ORDER_ID)
        description: "注文件数"
        data_type: NUMBER

      - name: avg_order_value
        expr: AVG(ORDER_AMOUNT)
        description: "平均注文単価"
        data_type: NUMBER

  - name: customers
    base_table:
      database: ANALYTICS_DB
      schema: PUBLIC
      table: DIM_CUSTOMERS
    description: "顧客マスタ"

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

      - name: region
        expr: REGION
        description: "顧客の所在地域"
        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: "月次カテゴリ別売上"
    question: "商品カテゴリ別の月次売上を教えて"
    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.

# 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": "先月の地域別売上トップ5を教えて"}
    ]
)

# レスポンスからSQLと結果を取得
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.

-- Cortex Analyst利用ユーザーへの権限付与例
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