Chatbots with Snowflake Cortex Analyst

The pitch is simple: your business users type a question in plain English, and Snowflake returns a SQL-backed answer. No dashboard. No analyst queue. No waiting until next sprint.

Cortex Analyst is Snowflake's text-to-SQL layer. It sits between a natural-language interface and your warehouse, translating questions into queries against a semantic model you define. When it works, it's genuinely useful. When it doesn't, the failure modes are instructive.

We've built a few of these. Here's what we've learned.

How Cortex Analyst works

Three layers:

1. Semantic model. A YAML file that describes your tables, columns, relationships, and business definitions. This is the bridge between "what did we sell last quarter?" and SELECT SUM(revenue) FROM mart_orders WHERE order_date >= '2025-07-01'.

2. Cortex LLM. Snowflake's hosted language model interprets the user's question against the semantic model and generates SQL. You don't choose the model — Snowflake manages it.

3. Query execution. The generated SQL runs against your Snowflake tables. Results come back as structured data you can render however you want — chat interface, embedded table, chart.

The semantic model is where all the work lives.

The semantic model is everything

A minimal semantic model looks like this:

name: revenue_model
tables:
  - name: mart_orders
    description: "One row per completed order"
    columns:
      - name: order_date
        description: "Date the order was placed"
        data_type: DATE
      - name: revenue
        description: "Order total in USD"
        data_type: NUMBER
      - name: customer_segment
        description: "Customer tier: enterprise, mid-market, or smb"
        data_type: VARCHAR
        sample_values: ["enterprise", "mid-market", "smb"]

Every column needs a plain-English description. The LLM uses these descriptions to map user questions to the right fields. Vague descriptions produce vague SQL.

Rules we follow:

• One semantic model per domain. Don't try to cover your entire warehouse in one file. Revenue is one model. Marketing attribution is another. Product usage is a third.
• Descriptions should answer "what does this column mean to a business user?" Not "VARCHAR(255) nullable." The model knows the data type. It doesn't know that customer_segment means the tier assigned during onboarding.
• Include sample values for categorical columns. Without them, the LLM guesses — and it guesses wrong on domain-specific enums.
• Define metrics explicitly. If "revenue" means SUM(order_total) WHERE status = 'completed', say that in the description. Don't make the LLM infer the filter.

Building the chat interface

Cortex Analyst exposes a REST API. You send a message, you get back SQL + results. The interface is yours to build.

A minimal integration:

import snowflake.connector
import json
 
conn = snowflake.connector.connect(
    account="your_account",
    user="your_user",
    password="your_password",
    warehouse="COMPUTE_WH",
    database="ANALYTICS",
    schema="PUBLIC"
)
 
response = conn.cursor().execute("""
    SELECT SNOWFLAKE.CORTEX.COMPLETE(
        'analyst',
        PARSE_JSON(%s)
    )
""", (json.dumps({
    "messages": [{"role": "user", "content": "What was total revenue last quarter?"}],
    "semantic_model": "@ANALYTICS.PUBLIC.REVENUE_MODEL/revenue_model.yaml"
}),)).fetchone()
 
print(response[0])

In production, you'd wrap this in a Streamlit app, a Slack bot, or an internal tool. The pattern is the same: user message in, structured response out.

Where it works well

Recurring questions with clear answers. "What was revenue last month?" / "How many new customers in Q3?" / "Top 10 products by units sold." These are questions with unambiguous SQL translations against well-defined tables.

Executive dashboards without the dashboard. A CFO who wants a number doesn't want to open Looker, find the right explore, set the right filters, and hope the cache is fresh. They want to type a question and get a number.

Data democratization (real, not buzzword). When a product manager can ask "how many users activated the new feature this week?" without filing a ticket, you've genuinely removed friction from the data access path.

Where it breaks

Ambiguous questions. "How are we doing?" has no SQL translation. The model will guess, and the guess will be wrong in a way that looks confident. Users who don't verify the generated SQL will make decisions on bad data.

Multi-hop reasoning. "Which marketing channel has the best ROI if we account for customer lifetime value?" requires joining across domains, applying a definition of LTV, and computing attribution. Cortex Analyst doesn't chain reasoning steps — it generates one query.

Data quality issues. Text-to-SQL amplifies whatever's in your warehouse. If your customer_segment column has 47 distinct values including typos, the model will query against the typos. Cortex Analyst trusts your data implicitly.

Schema drift. When your dbt models change — a column gets renamed, a table gets split — the semantic model needs to be updated in lockstep. There's no automatic sync. If the YAML says revenue and the table now has total_revenue_usd, the model breaks silently.

The honest assessment

Cortex Analyst is most useful as a shortcut for known questions, not as a replacement for analysis. It works when:

• The semantic model is tight — well-described columns, explicit metrics, sample values.
• The questions are scoped — one domain, one table or join, one answer.
• The users understand they're getting SQL, not intelligence. The model translates; it doesn't think.

It doesn't work as a "just point it at the warehouse and let people ask anything" tool. That expectation — which the marketing sometimes implies — leads to disappointment.

What we recommend

If you're considering Cortex Analyst:

1. Start with one domain. Revenue, or product usage, or marketing — pick one. Build the semantic model for that domain only.
2. Invest in the semantic model, not the chat UI. The interface is trivial. The descriptions, sample values, and metric definitions are where accuracy lives.
3. Gate it behind a "show SQL" toggle. Every answer should show the generated query. Users who can read SQL catch errors. Users who can't should know the query exists.
4. Keep the semantic model in version control. It's a YAML file. Put it in Git next to your dbt models. Review changes in PRs. Deploy with your data pipeline.
5. Pair it with dbt. Your dbt mart models are the natural tables for Cortex Analyst to query. The dbt descriptions can seed your semantic model descriptions. The testing layer gives you confidence the underlying data is correct.

The broader pattern

Cortex Analyst is Snowflake's entry in the text-to-SQL space. Databricks has AI/BI Genie. Google has Duet AI for BigQuery. The pattern is converging: semantic layer + LLM + natural language interface.

The vendor doesn't matter as much as the semantic model does. Teams that invest in describing their data well — clear column names, explicit business definitions, tested data quality — will get value from whichever text-to-SQL layer they pick. Teams that skip that work will blame the tool.

The semantic model is the product. Everything else is plumbing.

We've built Cortex Analyst implementations on top of Snowflake data stacks — from semantic model design to chat interface to production rollout. If you're exploring this for your team, book a discovery call and we'll tell you honestly whether it fits your use case.