How to run generative AI on SQL tables with Snowflake Cortex

A developer's walkthrough demonstrates how to run generative AI on SQL tables using Snowflake Cortex, covering access setup, AI functions like AI_COMPLETE and AI_CLASSIFY, and cost monitoring. The guide emphasizes testing on small data slices and tracking credit consumption to avoid unexpected expenses on large tables.

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. ACCOUNTADMIN once, to grant access. support.tickets , with ticket id , body and region .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. 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. 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; 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. 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. 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 https://bixtech.ai/aifirst-data-architecture-a-practical-blueprint-for-the-future-of-enterprise-intelligence/?utm source=hashnode&utm campaign=backlinks 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.