Cortex Analyst and Cortex Search: Shipping Chat With Your Data That Answers Correctly

Snowflake sells Cortex Analyst and Cortex Search as “chat with your data.” What they actually are is more specific and more useful: Analyst is text-to-SQL compiled against a semantic model you write, and Search is managed hybrid retrieval over text columns you point it at. One answers “how much,” the other answers “what did we say.”

I run both behind one internal Slack bot. The bot is fine. The work was not the bot: it was the semantic model YAML, the search service definition, and the router in between. None of that is on the marketing page, so this post is the version I wish I had read first.

The vendor-neutral architecture — why semantic models beat raw DDL, why lookups should retrieve instead of generate — is in RAG over warehouse data. This post is what changes when you buy the Snowflake implementation.

flowchart LR
  user[Slack bot] --> router{"Metric or lookup?"}
  router -->|metric| analyst["Cortex Analyst API"]
  analyst --> yaml[Semantic model YAML on a stage]
  yaml --> sql[SQL you execute yourself]
  router -->|lookup| search["Cortex Search service"]
  search --> chunks[Ranked chunks with metadata]

Analyst returns SQL, not answers. Search returns chunks, not answers. Your app owns the last mile.

Cortex Analyst: the YAML is the product

Cortex Analyst takes a natural-language question plus a semantic model file and returns SQL. It does not execute the SQL. That surprised me and I now consider it the best design decision in the product: execution happens in your session, under your role, with your query tag. The blast-radius argument from agent access to the warehouse applies unchanged.

The semantic model is a YAML file on a stage. Everything Analyst knows about your data is in that file. A minimal one that actually works:

name: finance
description: Core revenue metrics for the finance team.
tables:
  - name: fct_order_lines
    base_table:
      database: analytics
      schema: gold
      table: fct_order_lines
    dimensions:
      - name: region
        expr: region
        data_type: varchar
        sample_values: ['AMER', 'EMEA', 'APAC']
      - name: segment
        expr: segment
        data_type: varchar
    time_dimensions:
      - name: shipped_at
        expr: shipped_at
        data_type: timestamp_ntz
    measures:
      - name: net_revenue
        expr: gross_amount - refund_amount - credit_amount
        default_aggregation: sum
        data_type: number
        description: Gross minus refunds and credits, ship-date recognized.
verified_queries:
  - name: net revenue by region last quarter
    question: What was net revenue by region last quarter?
    sql: >
      select region, sum(gross_amount - refund_amount - credit_amount)
      from analytics.gold.fct_order_lines
      where shipped_at >= date_trunc('quarter', dateadd('quarter', -1, current_date()))
        and shipped_at < date_trunc('quarter', current_date())
      group by region

Three things carried most of the accuracy in my testing.

Descriptions. Analyst leans on them hard. “Gross minus refunds and credits, ship-date recognized” is the difference between the right measure and a plausible one. Write them like you are onboarding a new analyst, because you are.

Sample values. Without ['AMER', 'EMEA', 'APAC'], a user who types “Europe” gets region = 'Europe' and zero rows. With them, Analyst maps to EMEA.

Verified queries. Each one is a worked example the model imitates. Every time the bot gets a recurring question wrong, I fix it by adding a verified query, not by prompt engineering. After three months mine has 40 of them and they are the single highest-leverage section of the file.

Point the model at gold tables only — the same curated marts your dbt project already tests. A semantic model over bronze is a faster way to serve wrong numbers.

Cortex Search: managed hybrid retrieval

Cortex Search is the other half: you point a service at a text column, Snowflake embeds it, indexes it, and serves hybrid (vector plus keyword) retrieval with reranking. The hybrid part matters more than the demos admit — pure vector search is bad at exact identifiers, and warehouse questions are full of exact identifiers. “Error XJ-4402 in the Acme account” needs keyword match on the code and semantic match on the rest. Hybrid does both; the broader landscape is in vector databases comparison.

create or replace cortex search service support_notes_search
  on note_text
  attributes customer_id, note_type, created_at
  warehouse = cortex_wh
  target_lag = '1 hour'
  as
select
  note_id,
  note_text,
  customer_id,
  note_type,
  created_at
from analytics.gold.support_notes_chunked
where note_text is not null
  and length(note_text) > 40;

Two operational notes that cost me time.

TARGET_LAG here works like Dynamic Tables: it is a freshness contract paid for by the named warehouse. The service watches the source and re-embeds changed rows. A one-hour lag on a table that churns constantly is a warehouse that rarely sleeps. Size the lag to the question — support notes can be an hour stale; a policy corpus can be a day stale.

Chunking is still your job. The service indexes whatever rows you give it. Feed it 40-page documents as single rows and retrieval returns 40-page chunks. I chunk upstream in dbt to 300-500 token passages with the document title prepended to each — the same rules as any embedding pipeline, just materialized as a table before the service definition.

Filters ride along at query time, and they are how you enforce tenant isolation:

import snowflake.connector, json

conn = snowflake.connector.connect(...)  # rag_reader role

def search_notes(question: str, customer_id: str, k: int = 5):
    payload = json.dumps({
        "query": question,
        "columns": ["note_text", "note_type", "created_at"],
        "filter": {"@eq": {"customer_id": customer_id}},
        "limit": k,
    })
    cur = conn.cursor()
    cur.execute(
        """
        select parse_json(
          snowflake.cortex.search_preview(
            'analytics.gold.support_notes_search', %s
          )
        )['results']
        """,
        (payload,),
    )
    return json.loads(cur.fetchone()[0])

The filter is applied by the service, not by the LLM. That is the correct place for it: a prompt-injected question cannot un-filter another tenant’s rows.

The router and the last mile

Neither service decides which of them should answer. My router is one cheap CORTEX.COMPLETE classification call — “does this require computing a number from many rows, or looking up facts?” — and it has been over 95% accurate in logs. Ambiguous questions go to Analyst, because its failure mode (SQL the user can read and reject) is safer than Search’s (plausible chunks stitched into a wrong number).

The last mile is yours too. Analyst returns SQL: you execute it under a read-only role, render the rows, and always show the SQL. Search returns chunks: you pass them to a completion call with citations required. The end-to-end pattern matches Cortex AI for data engineers — Cortex functions are SQL and pipeline objects, not magic.

flowchart TD
  q[Question] --> cls["CORTEX.COMPLETE classifier"]
  cls -->|metric| an[Analyst returns SQL]
  an --> exec["Execute as rag_reader with query tag"]
  exec --> show[Render rows plus the SQL]
  cls -->|lookup| se[Search returns chunks]
  se --> comp["COMPLETE with citations required"]
  comp --> show2[Answer with linked sources]

Both paths end with the evidence visible: the SQL or the sources.

Cost, briefly: Analyst bills per message, Search bills embedding plus serving plus the refresh warehouse. My internal bot with ~50 daily users costs less than the smallest BI license tier we pay for. The line item that grows is Search refresh on churny sources — watch it the way you watch any TARGET_LAG.

Pitfalls

Treating the semantic model as set-and-forget. A measure renamed in dbt but not in the YAML is a silent wrong answer. Put the YAML in the dbt repo and diff both in CI.

No sample values on categorical dimensions. Users type “Europe,” your data says EMEA, the query returns zero rows, and the bot says “there was no revenue in Europe.” That answer gets screenshotted.

Indexing unclipped documents in Search. Chunk upstream. The service retrieves rows; make the rows passage-sized.

Letting the app execute Analyst SQL as a broad role. Analyst hands you SQL precisely so you can run it under rag_reader with a timeout and a tag. Running it as the app’s admin role throws away the design.

Skipping verified queries. They are the accuracy feature. Ten verified queries beat any amount of description-polishing.

One giant semantic model. Past ~30 tables, accuracy degrades and the file becomes unreviewable. Split by domain: finance.yaml, product.yaml, route by topic.

FAQ

Cortex Analyst vs building my own text-to-SQL with COMPLETE? Analyst, if your data is in Snowflake. The semantic model format, verified-query imitation, and clarifying-question behavior are things you would spend a quarter rebuilding badly. Your leverage is in the YAML, which is portable effort.

Does Cortex Search replace a dedicated vector database? For text that already lives in Snowflake, yes — no sync pipeline is a real architectural win. For sub-50ms product-facing retrieval or data outside Snowflake, a dedicated store still wins. See vector databases comparison.

Can Analyst join across multiple tables? Yes, via relationships you declare in the YAML. Keep it to a star — facts joined to dimensions. If you find yourself declaring snowflaked five-hop paths, build a flatter mart instead.

How do I evaluate accuracy before launch? A golden set: 50-plus real questions with hand-verified answers, rerun on every YAML change. I gate deploys on it. The first version of my semantic model scored 60%; verified queries and sample values took it above 90%.

What about data leaving Snowflake? It does not. Both services run inside the account boundary under Snowflake’s model hosting. For most security teams that is the entire reason this stack wins over wiring up an external LLM.

What this means for data engineers

Cortex Analyst and Cortex Search move the hard parts of warehouse RAG into managed services, but they do not move your parts: the semantic model, the chunked gold tables, the router, and the read-only execution path. Those are data engineering artifacts — YAML in a repo, dbt models with tests, a role with grants.

Treat the semantic model like a dbt model: versioned, reviewed, tested against a golden set. Treat the Search service like a Dynamic Table: a freshness contract with a warehouse bill. Do that and the bot answers correctly for reasons you can explain in a PR.