PowderInsights | Semantic Views and Cortex Analyst: Building a Text-to-SQL Layer Your Business Can Trust

Every text-to-SQL demo looks brilliant for ten minutes and then someone asks "what was revenue last quarter?" and gets a number nobody recognises. The reason is almost never the language model. It is that the model was handed raw tables and had to guess what revenue means, which date column counts, whether cancelled orders are excluded, and how to join five tables.

Snowflake's answer is the semantic view: a first-class schema object that stores the business meaning of your data — facts, dimensions, metrics, relationships, and synonyms — in the database, where it is versioned, governed, and shared. Cortex Analyst and Snowflake Intelligence generate SQL against that definition instead of against raw tables. This tutorial builds one end to end.

Why a semantic layer, not a prompt

Without a semantic layer, accuracy depends on prompt engineering that nobody owns. With one:

Metrics are defined once. net_revenue is a single expression, not four analysts' interpretations.
Joins are declared, not inferred. The model picks from relationships you defined; it cannot invent a fan-out join.
Synonyms are explicit. "Bookings", "sales", and "top line" can all resolve to the same metric.
Governance is inherited. The generated query runs as the calling user, so masking policies and row access policies still apply.

The semantic view is a contract between the data team and the assistant. Everything below is about writing that contract well.

Step 1: model the underlying data properly first

A semantic view is a description, not a repair. Point it at a clean star-shaped mart — conformed dimensions, one grain per fact table, surrogate or natural keys that actually join. If your mart is a pile of wide, denormalised extracts with duplicated grains, fix that first; the assistant will faithfully reproduce your modelling mistakes at conversational speed.

Assume this mart:

analytics.f_orders      (order_id, customer_key, product_key, order_date, net_amount, status)
analytics.d_customer    (customer_key, customer_name, segment, country)
analytics.d_product     (product_key, product_name, category)

Step 2: create the semantic view

CREATE OR REPLACE SEMANTIC VIEW analytics.sales_sv
  TABLES (
    orders AS analytics.f_orders
      PRIMARY KEY (order_id)
      WITH SYNONYMS ('sales', 'transactions')
      COMMENT = 'One row per customer order line, all channels',
    customers AS analytics.d_customer
      PRIMARY KEY (customer_key)
      WITH SYNONYMS ('accounts', 'clients'),
    products AS analytics.d_product
      PRIMARY KEY (product_key)
  )
  RELATIONSHIPS (
    orders_to_customers AS orders (customer_key) REFERENCES customers,
    orders_to_products  AS orders (product_key)  REFERENCES products
  )
  FACTS (
    orders.net_amount AS net_amount
      COMMENT = 'Order line value net of discounts, excludes tax and shipping'
  )
  DIMENSIONS (
    orders.order_date AS order_date
      WITH SYNONYMS ('date', 'booking date')
      COMMENT = 'Date the order was placed, not shipped',
    customers.segment AS segment WITH SYNONYMS ('customer segment', 'tier'),
    customers.country AS country,
    products.category AS category WITH SYNONYMS ('product category', 'line of business')
  )
  METRICS (
    orders.net_revenue AS SUM(orders.net_amount)
      COMMENT = 'Net revenue. Excludes orders with status = CANCELLED.',
    orders.order_count AS COUNT(orders.order_id),
    orders.avg_order_value AS SUM(orders.net_amount) / NULLIF(COUNT(DISTINCT orders.order_id), 0)
  )
  COMMENT = 'Certified sales semantic model owned by the Data Platform team';

Three things do the heavy lifting here:

RELATIONSHIPS removes join guessing.
COMMENT is not documentation for humans — it is prompt context. "Date the order was placed, not shipped" prevents a whole class of wrong answers.
WITH SYNONYMS covers the vocabulary your business actually uses instead of the vocabulary in your column names.

If cancelled orders must be excluded everywhere, do not rely on the comment. Filter them in a view underneath the semantic view, or define the metric with the condition inside the aggregate, so the rule cannot be bypassed.

Step 3: query it directly

Semantic views are queryable SQL objects, which means you can unit-test them without any AI in the loop:

SELECT * FROM SEMANTIC_VIEW(
  analytics.sales_sv
  METRICS  net_revenue, order_count
  DIMENSIONS segment, category
)
ORDER BY net_revenue DESC;

Run this alongside the equivalent hand-written SQL against the mart and compare. If the two disagree, the semantic view is wrong and no assistant will save you. This is also how you regression-test after a schema change:

SHOW SEMANTIC VIEWS IN SCHEMA analytics;
DESCRIBE SEMANTIC VIEW analytics.sales_sv;

Step 4: expose it to Cortex Analyst

Cortex Analyst turns a natural-language question into SQL against the semantic model and returns both the answer and the generated query. From SQL you can call it through the Cortex Analyst REST API, or from a Streamlit in Snowflake app; the important part is what you pass — the semantic view name, not a pile of table DDL.

A minimal Python client inside Streamlit in Snowflake:

import json, _snowflake, streamlit as st

resp = _snowflake.send_snow_api_request(
    "POST", "/api/v2/cortex/analyst/message", {}, {},
    {
        "semantic_view": "ANALYTICS.SALES_SV",
        "messages": [
            {"role": "user",
             "content": [{"type": "text", "text": "Net revenue by segment for the last full quarter"}]}
        ],
    },
    {}, 30000,
)
body = json.loads(resp["content"])
for item in body["message"]["content"]:
    if item["type"] == "sql":
        st.code(item["statement"], language="sql")
    elif item["type"] == "text":
        st.write(item["text"])

Always surface the generated SQL in the UI. Users trust an answer far more when they can see the query, and analysts catch semantic-model bugs that no evaluation suite will.

Step 5: Snowflake Intelligence on top

Snowflake Intelligence is the agent experience: users chat, and an agent decides which tools to use — Cortex Analyst over your semantic views for structured questions, Cortex Search over unstructured documents, and custom tools for actions. You register an agent, attach the semantic view as a tool, and give it instructions:

Attach one well-scoped semantic view per subject area rather than a single sprawling model. Agents choose tools better when the tool descriptions are distinct.
Write the agent's instructions like an onboarding note: which model answers which kind of question, what the fiscal calendar is, when to refuse.
Give the agent a dedicated role, and grant that role only what it needs.

Step 6: evaluate before you launch

This is the step teams skip and then regret. Build a spreadsheet — or better, a table — of 30 to 100 real questions with verified answers, and run it every time the model or the semantic view changes.

CREATE OR REPLACE TABLE analytics.analyst_eval (
  question         STRING,
  expected_sql     STRING,
  expected_value   NUMBER,
  actual_value     NUMBER,
  passed           BOOLEAN,
  run_ts           TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
);

Score on the answer, not on SQL string similarity — there are many correct queries for one question. Track the pass rate over time and treat a drop as a release blocker. Also log every real user question and its generated SQL; the failures are your backlog of missing synonyms, metrics, and comments.

-- Which questions are users actually asking?
SELECT question, COUNT(*) AS asks
FROM app.analyst_query_log
WHERE created_at >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY 1 ORDER BY asks DESC LIMIT 50;

Governance: the part that makes security teams say yes

Access is inherited. Cortex Analyst generates SQL that runs with the caller's privileges. A user who cannot see d_customer cannot get customer data through the chat interface.
Grant deliberately. GRANT SELECT ON SEMANTIC VIEW analytics.sales_sv TO ROLE analyst_ro; plus the underlying object grants.
Row access and masking policies still apply, so regional restrictions and PII masking carry through unchanged. Test this explicitly with a restricted test user — it is the demo that wins the approval meeting.
Cross-region inference may be required if the model your account uses is not hosted in your region; check CORTEX_ENABLED_CROSS_REGION against your data residency commitments before go-live.

Common failure modes

Symptom	Usual cause
Right shape, wrong number	Metric definition disagrees with the business rule (filters, grain, currency)
"I can't answer that" on reasonable questions	Missing dimension or synonym; vocabulary mismatch
Inflated totals	Fan-out join from an undeclared or wrong-cardinality relationship
Inconsistent period-over-period answers	No declared date dimension or ambiguous fiscal calendar
Slow responses	Semantic view over an unaggregated raw table; build a mart or add clustering

A realistic rollout

Pick one subject area with a clean mart and an engaged business owner.
Build the semantic view; validate with direct SEMANTIC_VIEW() queries.
Assemble 30 evaluation questions with the business owner; iterate until the pass rate is comfortably high.
Pilot with 5–10 users, log everything, fix vocabulary gaps weekly.
Only then expand to a second subject area — and reuse conformed dimensions so the two models agree.

Semantic views are the difference between an AI feature and an AI product. The modelling work is ordinary data engineering; the payoff is that the assistant answers questions the same way your certified dashboards do.

PowderInsights builds semantic models, Cortex Analyst applications, and Snowflake Intelligence deployments — including the evaluation harness and governance review. Get in touch to scope yours.