Getting Started with Snowflake Cortex AISQL: LLM Functions in Plain SQL

Snowflake Cortex is the set of AI capabilities that run inside your Snowflake account: serverless LLM functions you call from SQL, plus the higher-level services (Cortex Analyst, Cortex Search, Snowflake Intelligence) built on top of them. The point of this tutorial is the SQL layer — Cortex AISQL — because it is the fastest way to put an LLM to work on data you already have, with nothing to deploy and no data leaving Snowflake.

Three properties make Cortex AISQL different from calling an external LLM API:

1. Serverless. There is no endpoint, container, or key to manage. The functions are billed in credits like any other serverless feature.
2. Governed. The same roles, masking policies, and row-access policies that govern the table govern what the model sees. If a user cannot SELECT a column, they cannot summarize it either.
3. In-region. Inference runs inside Snowflake's boundary (or cross-region if you explicitly enable it with CORTEX_ENABLED_CROSS_REGION). For regulated data this is the feature.

The function family

The current AISQL functions (the AI_* family; the older SNOWFLAKE.CORTEX.* functions still work) cover most of what teams actually need:

Function	What it does
AI_COMPLETE	General text generation against a named model; optional structured (JSON) output
AI_CLASSIFY	Assign text (or an image) to one of a list of categories you supply
AI_FILTER	Return a boolean for a natural-language condition — use it in a WHERE clause
AI_AGG	Aggregate many rows of text into one answer (summarize a whole column, per group)
AI_SUMMARIZE_AGG	Aggregation specialised for summaries
AI_SENTIMENT	Sentiment scoring, including per-aspect sentiment
AI_EXTRACT	Pull named fields out of free text or documents
AI_TRANSLATE	Translate between languages
AI_EMBED	Produce vector embeddings for similarity search

Check the AISQL reference for the model list available in your region; it changes frequently.

Worked example: support tickets

Assume a SUPPORT_TICKETS table with TICKET_ID, CUSTOMER_ID, CREATED_AT, and a free-text BODY. We want to (a) classify each ticket, (b) flag the angry ones, and (c) produce a weekly summary per product area.

Step 1 — classify

CREATE OR REPLACE TABLE support_tickets_enriched AS
SELECT
  ticket_id,
  customer_id,
  created_at,
  body,
  AI_CLASSIFY(
    body,
    ['billing', 'login / access', 'bug report', 'feature request', 'other']
  ):labels[0]::string AS category
FROM support_tickets
WHERE created_at >= DATEADD(day, -7, CURRENT_DATE());

AI_CLASSIFY returns an object with a labels array; for single-label classification you take the first element. Add {'output_mode': 'multi'} as a third argument if a ticket can legitimately belong to several categories.

Step 2 — filter with natural language

SELECT ticket_id, customer_id, category
FROM support_tickets_enriched
WHERE AI_FILTER(PROMPT('Is this customer threatening to cancel or asking for a refund? {0}', body));

AI_FILTER is the function people underestimate. It turns a question a human would ask into a predicate, and it composes with ordinary SQL — join it to your account table and you have a churn-risk list without a single line of Python.

Step 3 — summarize per group

SELECT
  category,
  AI_AGG(
    body,
    'Summarize the main issues in these support tickets in three bullet points. Mention recurring product names.'
  ) AS weekly_summary
FROM support_tickets_enriched
GROUP BY category;

AI_AGG handles the context-window problem for you: it chunks and reduces across however many rows are in the group, so a category with 4,000 tickets summarizes as reliably as one with 40.

Step 4 — structured extraction

When you need fields rather than prose, ask AI_COMPLETE for JSON and validate it with a schema:

SELECT
  ticket_id,
  AI_COMPLETE(
    model => 'mistral-large2',
    prompt => 'Extract the product name and the customer''s requested action from this ticket: ' || body,
    response_format => {
      'type': 'json',
      'schema': {
        'type': 'object',
        'properties': {
          'product': {'type': 'string'},
          'requested_action': {'type': 'string'}
        },
        'required': ['product', 'requested_action']
      }
    }
  ) AS extracted
FROM support_tickets_enriched
LIMIT 100;

Structured output is what makes the results joinable. Parse extracted:product::string like any other VARIANT.

Cost and credit notes

• AISQL functions are billed per million tokens (input plus output), and the rate varies by model — often by an order of magnitude between the smallest and largest. Snowflake's Service Consumption Table (linked from the pricing page) has the current rates.
• Materialize, do not recompute. Write results to a table (as in Step 1) rather than putting AI_* calls in a view that every dashboard refresh re-runs.
• Start small. Run new prompts on LIMIT 100 and inspect the output before scaling to the full table.
• Use the smallest model that passes your quality bar. Classification and extraction rarely need the largest model.
• Track it. SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY shows tokens and credits by function and model; put a budget or resource monitor on the warehouse that runs AI jobs.
• The warehouse still bills for the query's run time, independent of the token charge. Use a small warehouse for AI jobs — the work happens in the Cortex service, not in your compute.

When to use Cortex vs an external LLM API

Use Cortex AISQL when:

• The input is already in Snowflake and the output belongs in Snowflake
• The data is governed (PII, PHI, contracts) and you do not want to build an egress review
• The workload is set-based — thousands of rows, the same prompt — rather than interactive
• You want a SQL-only skill set to maintain the pipeline

Reach for an external API (via an External Function or from an application) when:

• You need a specific model Cortex does not offer in your region
• The use case is a real-time, per-request chat product rather than batch enrichment
• You need fine-tuned models beyond what Cortex Fine-tuning supports

For most analytics teams the first bucket is much bigger than they expect. The pattern above — classify, filter, aggregate, extract, all in one CREATE TABLE AS — replaces what used to be a Python service, a queue, a secrets vault, and a data-movement review.

Need help putting Cortex to work on governed data? See our Snowflake Cortex & AI consulting service or contact us.