Every data team is being asked the same question this year: "can our AI assistant just query the warehouse?" The honest answer used to be "only with a pile of glue code and a service account you would rather not talk about." The Model Context Protocol (MCP) changed that. MCP is an open standard for exposing tools and data to LLM clients, and Snowflake now sits on both sides of it — Snowflake can serve MCP tools over your governed data, and Snowflake-hosted agents can consume MCP tools from elsewhere.
This tutorial walks through the practical build: what an MCP server over Snowflake actually exposes, how Cortex Agents orchestrate those tools, and — the part that decides whether this reaches production — how to scope, budget, and audit it.
It assumes you have read our pieces on Cortex AISQL and semantic views and Cortex Analyst. Those cover the SQL layer; this one covers the agent layer on top of it.
The mental model
Three pieces, and people conflate them constantly:
| Piece | What it is | Who calls it |
|---|---|---|
| Tool | One capability: "answer analytical questions over the sales semantic view", "search the contracts corpus", "run this stored procedure" | The agent |
| MCP server | A standard endpoint that advertises a list of tools and executes them under an identity | Any MCP client: Claude, an IDE, your own app, another agent |
| Cortex Agent | The orchestrator that plans, picks tools, calls them, and composes an answer | The end user or application |
The important consequence: the MCP server is a permissions boundary, not just a protocol adapter. Whatever role it runs as, the model can reach. Design that role first and the rest is plumbing.
Step 1: build the tools before the agent
An agent is only as good as the tools you hand it. Two carry most of the weight.
Structured questions — a semantic view
Cortex Analyst answers natural-language questions by generating SQL against a semantic view, which encodes tables, joins, metrics, and synonyms so the model does not have to guess.
CREATE OR REPLACE SEMANTIC VIEW analytics.sales_sv
TABLES (
orders AS analytics.fct_orders
PRIMARY KEY (order_id)
WITH SYNONYMS ('sales', 'transactions'),
customers AS analytics.dim_customer
PRIMARY KEY (customer_id)
)
RELATIONSHIPS (
orders_to_customers AS orders (customer_id) REFERENCES customers (customer_id)
)
FACTS (
orders.amount AS amount
)
DIMENSIONS (
orders.order_date AS order_date WITH SYNONYMS ('date', 'booking date'),
customers.region AS region
)
METRICS (
orders.total_revenue AS SUM(orders.amount)
WITH SYNONYMS ('revenue', 'bookings')
COMMENT = 'Gross booked revenue, excludes cancellations',
orders.order_count AS COUNT(orders.order_id)
);
Spend your effort in the COMMENT and WITH SYNONYMS clauses. An agent that returns a plausible but wrong number is worse than one that returns nothing, and ninety percent of wrong numbers trace back to an ambiguous metric definition.
Unstructured questions — a Cortex Search service
CREATE OR REPLACE CORTEX SEARCH SERVICE analytics.contract_search
ON chunk
ATTRIBUTES (contract_id, counterparty, effective_date)
WAREHOUSE = cortex_wh
TARGET_LAG = '1 hour'
AS (
SELECT chunk, contract_id, counterparty, effective_date
FROM analytics.contract_chunks
);
Now a question like "what did we bill Northwind last quarter and does their contract allow the new rate?" has a numeric tool and a document tool. That combination is exactly what agents are for.
Step 2: expose the tools through an MCP server
Snowflake lets you declare an MCP server as a database object that bundles tools and is reached over a REST endpoint with normal Snowflake authentication.
CREATE OR REPLACE MCP SERVER analytics.revenue_mcp
FROM SPECIFICATION
$$
tools:
- name: query_sales
identifier: analytics.sales_sv
type: CORTEX_ANALYST_MESSAGE
description: >
Answer quantitative questions about orders, revenue, customers and
regions. Use for anything involving a number, trend or ranking.
- name: search_contracts
identifier: analytics.contract_search
type: CORTEX_SEARCH_SERVICE_QUERY
description: >
Search executed customer contracts. Use for questions about terms,
renewal dates, pricing clauses or obligations.
title: Contract search
$$;
GRANT USAGE ON MCP SERVER analytics.revenue_mcp TO ROLE ai_assistant_role;
Syntax for MCP server objects is still moving — check the current documentation for your account's release before copying this verbatim — but the shape is stable: a named object, a declarative tool list, and ordinary GRANT USAGE.
An MCP client then connects to the account endpoint (https://<account>.snowflakecomputing.com/api/v2/databases/<db>/schemas/<schema>/mcp-servers/<name>) using OAuth or a programmatic access token, lists the tools, and calls them. Client configuration is a few lines of JSON in Claude Desktop, an IDE, or your own application — the point of the standard is that you do not write a bespoke integration per client.
Those description strings are load-bearing. They are the only thing the model reads when deciding which tool to call. Write them like a routing rule, not like documentation: say what the tool is for and when not to use it.
Step 3: orchestration with Cortex Agents
If you want the planning loop inside Snowflake rather than in a desktop client, use a Cortex Agent. It takes the same tool set, plans a sequence, calls tools, and returns an answer plus its reasoning trace.
CREATE OR REPLACE AGENT analytics.revenue_agent
WITH PROFILE = '{"display_name": "Revenue Assistant"}'
FROM SPECIFICATION
$$
instructions:
response: >
You are a revenue analyst. Always state the date range and filters used.
If a metric is ambiguous, ask a clarifying question instead of guessing.
Never speculate about figures that no tool returned.
orchestration: >
Prefer query_sales for numbers. Use search_contracts only for contract
language. You may call both and combine the results.
tools:
- tool_spec:
name: query_sales
type: cortex_analyst_text_to_sql
- tool_spec:
name: search_contracts
type: cortex_search
tool_resources:
query_sales:
semantic_view: analytics.sales_sv
search_contracts:
name: analytics.contract_search
max_results: 5
$$;
Agents are also callable from the REST API (/api/v2/cortex/agent:run) for embedding in your own product, and Snowflake Intelligence provides a ready-made chat UI over the same objects for business users.
Step 4: the controls that make this shippable
This is the section that separates a demo from a deployment.
Scope the role, not the prompt
Prompt instructions are guidance; RBAC is enforcement. Create a dedicated role that can see only what the assistant should ever see:
CREATE ROLE ai_assistant_role;
GRANT USAGE ON DATABASE analytics TO ROLE ai_assistant_role;
GRANT USAGE ON SCHEMA analytics TO ROLE ai_assistant_role;
GRANT SELECT ON VIEW analytics.v_orders_agent_safe TO ROLE ai_assistant_role;
GRANT USAGE ON SEMANTIC VIEW analytics.sales_sv TO ROLE ai_assistant_role;
GRANT USAGE ON CORTEX SEARCH SERVICE analytics.contract_search TO ROLE ai_assistant_role;
GRANT USAGE ON WAREHOUSE cortex_wh TO ROLE ai_assistant_role;
Point the semantic view at curated, agent-safe views rather than raw tables. Masking policies and row access policies still apply — an agent querying as ai_assistant_role sees masked emails exactly as a human in that role would, which is the single best argument for putting the assistant inside the warehouse instead of shipping extracts to an external model.
Cap the compute
CREATE WAREHOUSE cortex_wh WITH
WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
STATEMENT_TIMEOUT_IN_SECONDS = 120;
CREATE OR REPLACE RESOURCE MONITOR ai_rm WITH
CREDIT_QUOTA = 100 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
TRIGGERS ON 80 PERCENT DO NOTIFY
ON 100 PERCENT DO SUSPEND;
ALTER WAREHOUSE cortex_wh SET RESOURCE_MONITOR = ai_rm;
Agents are chatty: one user question can become several tool calls, each a warehouse query plus token consumption. Resource monitors cover the warehouse; a budget (SNOWFLAKE.CORE.BUDGET) covers the serverless AI credits that resource monitors do not. Set both before you invite users.
Watch what it is actually doing
-- Token and credit consumption by AI feature
SELECT function_name, model_name,
SUM(token_credits) AS credits,
COUNT(*) AS calls
FROM snowflake.account_usage.cortex_functions_usage_history
WHERE start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2 ORDER BY credits DESC;
-- Every statement the assistant role ran
SELECT start_time, user_name, query_text, total_elapsed_time/1000 AS secs
FROM snowflake.account_usage.query_history
WHERE role_name = 'AI_ASSISTANT_ROLE'
AND start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;
Because every tool call lands as a normal Snowflake query, your existing audit story covers agents for free. Add an event table and Snowflake Trail if you want structured traces of the planning steps themselves.
Evaluate before you trust
Build a golden set of thirty to fifty questions with agreed answers, store it as a table, and re-run it whenever you change the semantic view, the tool descriptions, or the model. Track accuracy as a percentage over time. Teams that skip this step discover regressions from a business user, in a meeting, with the CFO present.
Where this breaks in practice
- Too many tools. Past roughly a dozen, routing accuracy falls off. Build several narrow agents rather than one that does everything.
- Vague metric definitions. "Revenue" meaning three different things in three departments is a data modelling problem the agent will faithfully expose.
- Write access. Resist giving an agent a tool that runs DML. If you must, wrap it in a stored procedure with explicit validation and a human confirmation step.
- Regional availability. Cortex features, MCP server support, and specific models vary by cloud region; verify availability and any cross-region inference settings before you promise a delivery date.
- Feature churn. This area of the platform changes fast. Pin your account's release notes, and re-test agent behaviour after each behaviour-change release.
A sensible rollout order
- One semantic view over one well-governed mart, validated by the business owner
- A dedicated role, a capped warehouse, a budget, and a monitoring query
- Cortex Analyst alone, exposed to five friendly users, with the golden question set
- Add Cortex Search once the numbers are trusted
- Wrap both in an MCP server so IDEs, chat clients, and your own apps share one governed surface
- Only then add multi-step agents and, later, write-capable tools
Skipping to step six is the most common failure we are called in to unwind.
PowderInsights builds governed AI-on-Snowflake layers — semantic models, Cortex Analyst and Search services, MCP endpoints, and the RBAC and cost controls around them. If you are planning agentic access to your Snowflake data, get in touch and we will walk through the architecture with your team.