How to run generative AI on SQL tables with Snowflake Cortex

The question that decides whether Cortex belongs in your stack is not what it can do. It is what it costs once the table has millions of rows instead of five.

That question exists because the model call is an ordinary SQL function. It sits inside a SELECT, composes with WHERE, JOIN and GROUP BY, and runs once per row. So this walkthrough goes in that order. Access first, then the functions on a small slice, then the credit consumption before anything scales up.

Before you start

• A Snowflake account on Standard edition or above, in a region where Cortex is available.
• A running warehouse. The AI functions are billed on the tokens they process, and the query around them still consumes warehouse credits, so you pay for both.
• ACCOUNTADMIN once, to grant access.
• A table with a text column. The examples use support.tickets, with ticket_id, body and region.

Granting access

Cortex access lives in a database role called SNOWFLAKE.CORTEX_USER. It cannot be granted to a user directly, only to a role, which is the first thing that trips people up:

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS ai_analyst;
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO ROLE ai_analyst;
GRANT USAGE ON WAREHOUSE ai_wh TO ROLE ai_analyst;
GRANT ROLE ai_analyst TO USER my_user;

Two habits are worth adopting right away. The first is to put AI work on its own warehouse, so the credits appear separated in your billing without any extra tagging. The second is to grant the database role to a purpose-built role instead of something broad like ANALYST, because revoking access later is the only real spending control you have.

The first call

AI_COMPLETE is the general-purpose function. It takes a model name and a prompt, and returns text. Since model availability changes by region and by release, start by listing what your account can actually call:

SHOW CORTEX BASE MODELS;

Then use one of those names below:

USE ROLE ai_analyst;
USE WAREHOUSE ai_wh;

SELECT AI_COMPLETE(
  'claude-4-sonnet',   -- replace with a model from the list above
  'Summarize this support ticket in one sentence: ' || body
) AS summary
FROM support.tickets
LIMIT 5;

Note the LIMIT 5. Without it, the statement runs one model call per row, and a table with two million tickets will happily oblige. Every AI function here behaves the same way, so develop against a LIMIT and remove it only when the prompt is settled.

If you find older material online using SNOWFLAKE.CORTEX.COMPLETE, that is the previous generation. AI_COMPLETE is the updated version, and the same rename ran across the family, so SENTIMENT became AI_SENTIMENT and CLASSIFY_TEXT became AI_CLASSIFY. Copying a 2024 tutorial gets you working but deprecated syntax.

The functions that replace a pipeline

Free-text prompting is the least interesting part of Cortex. The task-specific functions are where the SQL actually gets shorter, because they return typed values you can group and aggregate, instead of prose you would then have to parse.

Classification into your own categories:

SELECT
    ticket_id,
    region,
    AI_CLASSIFY(body, ['billing', 'bug', 'feature request', 'churn risk']) AS category
FROM support.tickets;

Filtering in natural language, inside the WHERE clause:

SELECT ticket_id, body
FROM support.tickets
WHERE AI_FILTER(
    'This message describes a customer threatening to cancel: ' || body
);

Finally, aggregation across rows, which is the function that removes the most code. AI_AGG reads an entire column against a single prompt and is not bound by the model context window, so there is no chunking loop to write:

SELECT
    region,
    AI_AGG(body, 'What are the three most repeated complaints in these tickets?') AS themes
FROM support.tickets
WHERE created_at >= DATEADD('day', -7, CURRENT_DATE())
GROUP BY region;

Reading the bill

Do this on day one rather than after the invoice arrives. Every call is recorded in the Account Usage schema. Run SELECT * against the view once to see its current columns, since it has changed shape more than once, then aggregate:

SELECT
    function_name,
    model_name,
    SUM(token_credits) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_AI_FUNCTIONS_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY credits DESC;

One trap here: CORTEX_FUNCTIONS_USAGE_HISTORY, the view most existing tutorials point at, is no longer updated. Use CORTEX_AI_FUNCTIONS_USAGE_HISTORY for full coverage, or CORTEX_AISQL_USAGE_HISTORY. Data can take a few hours to appear, so an empty result right after your first query is expected rather than a permissions problem.

Four things that bite later

Preview status. Several of these functions, AI_FILTER and AI_AGG among them, are marked as preview in Snowflake's documentation, and preview means the signature can change under you. So check the current status of every function you depend on before it reaches a scheduled task.

Model and region availability. When the model you want is missing from that SHOW CORTEX BASE MODELS list, the account needs cross-region inference, which is an ACCOUNTADMIN decision made once for the whole account through CORTEX_ENABLED_CROSS_REGION, never per user or per session. The real question there is compliance rather than cost, so if your data cannot leave a jurisdiction, that parameter is the conversation to have before writing any SQL.

Cost scales with rows, not with queries. A warehouse costs the same whether the query touches ten rows or ten million. An AI function does not. The mental model you built tuning SQL stops applying here, and the discipline that replaces it is simple: filter before the function call, never after.

Non-determinism. The same prompt on the same row can return different text on different days. If a downstream table depends on the output, materialize the result and version it, rather than calling the function inside a view that recomputes on every read.

The limit of putting the model in SQL

Inference now sits next to your data, under the same access controls and the same query engine. That covers a large class of work that used to justify a separate service.

Meaning is the part that does not come with it. The model can classify a ticket, but it has no idea what your company counts as an active customer, which revenue definition finance signed off on, or which of four customer_id columns is the governed one. Those definitions live in a semantic layer, and this blueprint on the semantic layer as a single source of business meaning covers how that layer sits between raw tables and anything that answers questions.

Skip it and Cortex still works. You just get fluent answers that quietly disagree with the finance report, produced faster than before.