Snowflake Cortex AISQL (including LLM functions)¶

AISQL functions¶

These task-specific functions are purpose-built managed functions that automate routine tasks, like simple summaries and quick translations, that don’t require any customization.

• AI_COMPLETE: Generates a completion for a given text string or image using a selected LLM. Use this function for most generative AI tasks.

  • AI_COMPLETE is the updated version of COMPLETE (SNOWFLAKE.CORTEX).

• AI_CLASSIFY: Classifies text or images into user-defined categories.

  • AI_CLASSIFY is the updated version of CLASSIFY_TEXT (SNOWFLAKE.CORTEX) with support for multi-label and image classification.

• AI_FILTER: Returns True or False for a given text or image input, allowing you to filter results in SELECT, WHERE, or JOIN ... ON clauses.

• AI_AGG: Aggregates a text column and returns insights across multiple rows based on a user-defined prompt. This function isn’t subject to context window limitations.

• AI_EMBED: Generates an embedding vector for a text or image input, which can be used for similarity search, clustering, and classification tasks.

  • AI_EMBED is the updated version of EMBED_TEXT_1024 (SNOWFLAKE.CORTEX).

• AI_EXTRACT: Extracts information from an input string or file, for example, text, images, and documents. Supports multiple languages.

  • AI_EXTRACT is the updated version of EXTRACT_ANSWER (SNOWFLAKE.CORTEX).

• AI_SENTIMENT: Extracts sentiment scores from text.

  • AI_SENTIMENT is the updated version of SENTIMENT (SNOWFLAKE.CORTEX).

• AI_SUMMARIZE_AGG: Aggregates a text column and returns a summary across multiple rows. This function isn’t subject to context window limitations.

• AI_SIMILARITY: Calculates the embedding similarity between two inputs.

• AI_TRANSCRIBE: Transcribes audio files stored in a stage, extracting text, timestamps, and speaker information.

• AI_PARSE_DOCUMENT: Extracts text (using OCR mode) or text with layout information (using LAYOUT mode) from documents in an internal or external stage.

  • AI_PARSE_DOCUMENT is the updated version of PARSE_DOCUMENT (SNOWFLAKE.CORTEX).

• TRANSLATE (SNOWFLAKE.CORTEX): Translates text between supported languages.

• SUMMARIZE (SNOWFLAKE.CORTEX): Returns a summary of the text that you’ve specified.

Helper functions¶

Helper functions are purpose-built managed functions that reduce cases of failures when running other AISQL functions, for example by getting the count of tokens in an input prompt to ensure the call doesn’t exceed a model limit.

• TO_FILE: Creates a reference to a file in an internal or external stage for use with AI_COMPLETE and other functions that accept files.

• COUNT_TOKENS (SNOWFLAKE.CORTEX): Given an input text, returns the token count based on the model or Cortex function specified.

• PROMPT: Helps you build prompt objects for use with AI_COMPLETE and other functions.

• TRY_COMPLETE (SNOWFLAKE.CORTEX): Works like the COMPLETE function, but returns NULL when the function could not execute instead of an error code.

Cortex Guard¶

Cortex Guard is an option of the AI_COMPLETE (or SNOWFLAKE.CORTEX.COMPLETE) function designed to filter possible unsafe and harmful responses from a language model. Cortex Guard is currently built with Meta’s Llama Guard 3. Cortex Guard works by evaluating the responses of a language model before that output is returned to the application. Once you activate Cortex Guard, language model responses which may be associated with violent crimes, hate, sexual content, self-harm, and more are automatically filtered. See COMPLETE arguments for syntax and examples.

Account-level allowlist parameter¶

You can control model access across your entire account using the CORTEX_MODELS_ALLOWLIST parameter. Supported features will respect the value of this parameter and prevent use of models that are not in the allowlist.

The CORTEX_MODELS_ALLOWLIST parameter can be set to 'All', 'None', or to a comma-separated list of model names. This parameter can only be set at the account level, not at the user or session levels. Only the ACCOUNTADMIN role can set the parameter using the ALTER ACCOUNT command.

Examples:

• To allow access to all models:

  ALTER ACCOUNT SET CORTEX_MODELS_ALLOWLIST = 'All';
  

  Copy

• To allow access to the mistral-large2 and llama3.1-70b models:

  ALTER ACCOUNT SET CORTEX_MODELS_ALLOWLIST = 'mistral-large2,llama3.1-70b';
  

  Copy

• To prevent access to any model:

  ALTER ACCOUNT SET CORTEX_MODELS_ALLOWLIST = 'None';
  

  Copy

Use RBAC, as described in the following section, to provide specific roles with access beyond what you’ve specified in the allowlist.

Role-based access control (RBAC)¶

Although Cortex models are not themselves Snowflake objects, Snowflake lets you create model objects in the SNOWFLAKE.MODELS schema that represent the Cortex models. By applying RBAC to these objects, you can control access to models the same way you would any other Snowflake object. Supported features accept the identifiers of objects in SNOWFLAKE.MODELS wherever a model can be specified.

CALL SNOWFLAKE.MODELS.CORTEX_BASE_MODELS_REFRESH();

SHOW MODELS IN SNOWFLAKE.MODELS;

SHOW APPLICATION ROLES IN APPLICATION SNOWFLAKE;

-- set up access
USE SECONDARY ROLES NONE;
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET CORTEX_MODELS_ALLOWLIST = 'MISTRAL-LARGE2';
CALL SNOWFLAKE.MODELS.CORTEX_BASE_MODELS_REFRESH();
GRANT APPLICATION ROLE SNOWFLAKE."CORTEX-MODEL-ROLE-LLAMA3.1-70B" TO ROLE PUBLIC;

-- test access
USE ROLE PUBLIC;

-- this succeeds because mistral-large2 is in the allowlist
SELECT AI_COMPLETE('MISTRAL-LARGE2', 'Hello');

-- this succeeds because the role has access to the model object
SELECT AI_COMPLETE('SNOWFLAKE.MODELS."LLAMA3.1-70B"', 'Hello');

-- this fails because the first argument is
-- neither an identifier for an accessible model object
-- nor is it a model name in the allowlist
SELECT AI_COMPLETE('SNOWFLAKE-ARCTIC', 'Hello');

Common pitfalls¶

• Access to a model (whether by allowlist or RBAC) does not always mean that it can be used. It may still be subject to cross-region, deprecation, or other availability constraints. These restrictions can result in error messages that seem similar to model access errors.

• Model access controls only govern use of a model, and not the use of a feature itself, which may have its own access controls. For example, access to AI_COMPLETE is governed by the CORTEX_USER database role. See Required privileges for more information.

• Not all features support model access controls. See the supported features table to see which access control methods a given feature supports.

• Secondary roles can obscure permissions. For example, if a user has ACCOUNTADMIN as a secondary role, all model objects may appear accessible. Disable secondary roles temporarily when verifying permissions.

• Keep in mind that you must use model object identifiers with RBAC and that these are quoted identifiers and are therefore case-sensitive. See QUOTED_IDENTIFIERS_IGNORE_CASE for more information.

Supported features¶

Model access controls are supported by the following features:

Track costs for AI services¶

To track credits used for AI Services including LLM Functions in your account, use the METERING_HISTORY view:

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_DAILY_HISTORY
  WHERE SERVICE_TYPE='AI_SERVICES';

Track credit consumption for AISQL functions¶

To view the credit and token consumption for each AISQL function call, use the CORTEX_FUNCTIONS_USAGE_HISTORY view:

SELECT *
  FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_USAGE_HISTORY;

You can also view the credit and token consumption for each query within your Snowflake account. Viewing the credit and token consumption for each query helps you identify queries that are consuming the most credits and tokens.

The following example query uses the CORTEX_FUNCTIONS_QUERY_USAGE_HISTORY view to show the credit and token consumption for all of your queries within your account.

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_QUERY_USAGE_HISTORY;

You can also use the same view to see the credit and token consumption for a specific query.

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.CORTEX_FUNCTIONS_QUERY_USAGE_HISTORY
WHERE query_id='<query-id>';

The query usage history is grouped by the models used in the query. For example, if you ran:

SELECT AI_COMPLETE('mistral-7b', 'Is a hot dog a sandwich'), AI_COMPLETE('mistral-large', 'Is a hot dog a sandwich');

The query usage history would show two rows, one for mistral-7b and one for mistral-large.

Large models¶

If you’re not sure where to start, try the most capable models first to establish a baseline to evaluate other models. claude-3-7-sonnet, reka-core, and mistral-large2 are the most capable models offered by Snowflake Cortex, and will give you a good idea what a state-of-the-art model can do.

• Claude 3-7 Sonnet is a leader in general reasoning and multimodal capabilities. It outperforms its predecessors in tasks that require reasoning across different domains and modalities. You can use its large output capacity to get more information from either structured or unstructured queries. Its reasoning capabilities and large context windows make it well-suited for agentic workflows.

• deepseek-r1 is a foundation model trained using large-scale reinforcement-learning (RL) without supervised fine-tuning (SFT). It can deliver high performance across math, code, and reasoning tasks. To access the model, set the cross-region inference parameter to AWS_US.

• mistral-large2 is Mistral AI’s most advanced large language model with top-tier reasoning capabilities. Compared to mistral-large, it’s significantly more capable in code generation, mathematics, reasoning, and provides much stronger multilingual support. It’s ideal for complex tasks that require large reasoning capabilities or are highly specialized, such as synthetic text generation, code generation, and multilingual text analytics.

• llama3.1-405b is an open source model from the llama3.1 model family from Meta with a large 128K context window. It excels in long document processing, multilingual support, synthetic data generation and model distillation.

• snowflake-llama3.1-405b is a model derived from the open source llama3.1 model. It uses the SwiftKV optimizations that have been developed by the Snowflake AI research team to deliver up to a 75% inference cost reduction. SwiftKV achieves higher throughput performance with minimal accuracy loss.

Medium models¶

• llama3.1-70b is an open source model that demonstrates state-of-the-art performance ideal for chat applications, content creation, and enterprise applications. It is a highly performant, cost effective model that enables diverse use cases with a context window of 128K. llama3-70b is still supported and has a context window of 8K.

• snowflake-llama3.3-70b is a model derived from the open source llama3.3 model. It uses the <SwiftKV optimizations https://www.snowflake.com/en/blog/up-to-75-lower-inference-cost-llama-meta-llm/> developed by the Snowflake AI research team to deliver up to a 75% inference cost reduction. SwiftKV achieves higher throughput performance with minimal accuracy loss.

• snowflake-arctic is Snowflake’s top-tier enterprise-focused LLM. Arctic excels at enterprise tasks such as SQL generation, coding and instruction following benchmarks.

• mixtral-8x7b is ideal for text generation, classification, and question answering. Mistral models are optimized for low latency with low memory requirements, which translates into higher throughput for enterprise use cases.

• The jamba-Instruct model is built by AI21 Labs to efficiently meet enterprise requirements. It is optimized to offer a 256k token context window with low cost and latency, making it ideal for tasks like summarization, Q&A, and entity extraction on lengthy documents and extensive knowledge bases.

• The AI21 Jamba 1.5 family of models is state-of-the-art, hybrid SSM-Transformer instruction following foundation models. The jamba-1.5-mini and jamba-1.5-large with a context length of 256K supports use cases such as structured output (JSON), and grounded generation.

Small models¶

• The llama3.2-1b and llama3.2-3b models support context length of 128K tokens and are state-of-the-art in their class for use cases like summarization, instruction following, and rewriting tasks. The Llama 3.2 models deliver multilingual capabilities, with support for English, German, French, Italian, Portuguese, Hindi, Spanish and Thai.

• llama3.1-8b is ideal for tasks that require low to moderate reasoning. It’s a light-weight, ultra-fast model with a context window of 128K. llama3-8b and llama2-70b-chat are still supported models that provide a smaller context window and relatively lower accuracy.

• mistral-7b is ideal for your simplest summarization, structuration, and question answering tasks that need to be done quickly. It offers low latency and high throughput processing for multiple pages of text with its 32K context window.

• gemma-7b is suitable for simple code and text completion tasks. It has a context window of 8,000 tokens but is surprisingly capable within that limit, and quite cost-effective.

The following table provides information on how popular models perform on various benchmarks, including the models offered by Snowflake Cortex AI_COMPLETE as well as a few other popular models.

Call Cortex AISQL functions in Snowpark Python¶

You can use Snowflake Cortex AISQL functions in the Snowpark Python API. These functions include the following. Note that the functions in Snowpark Python have names in Pythonic “snake_case” format, with words separated by underscores and all letters in lowercase.

• ai_agg

• ai_classify

• ai_complete

• ai_filter

• ai_similarity

• ai_summarize_agg

from snowflake.snowpark.functions import ai_agg, col

df = session.create_dataframe([
    [1, "Excellent product!"],
    [1, "Great battery life."],
    [1, "A bit expensive but worth it."],
    [2, "Terrible customer service."],
    [2, "Won’t buy again."],
], schema=["product_id", "review"])

# Summarize reviews per product
summary_df = df.group_by("product_id").agg(
    ai_agg(col("review"), "Summarize the customer reviews in one sentence.")
)
summary_df.show()

from snowflake.snowpark.functions import ai_classify, col

df = session.create_dataframe([
    ["I dream of backpacking across South America."],
    ["I made the best pasta yesterday."],
], schema=["sentence"])

df = df.select(
    "sentence",
    ai_classify(col("sentence"), ["travel", "cooking"]).alias("classification")
)
df.show()

from snowflake.snowpark.functions import ai_filter, prompt, col

df = session.create_dataframe(["Canada", "Germany", "Japan"], schema=["country"])

filtered_df = df.select(
    "country",
    ai_filter(prompt("Is {0} in Asia?", col("country"))).alias("is_in_asia")
)
filtered_df.show()

Call Cortex AISQL functions in Snowflake ML¶

Snowflake ML contains the older AISQL functions, those with names that don’t begin with “AI”. These functions are supported in version 1.1.2 and later of Snowflake ML. The names are rendered in Pythonic “snake_case” format, with words separated by underscores and all letters in lowercase.

If you run your Python script outside of Snowflake, you must create a Snowpark session to use these functions. See Connecting to Snowflake for instructions.

from snowflake.cortex import complete, extract_answer, sentiment, summarize, translate

text = """
    The Snowflake company was co-founded by Thierry Cruanes, Marcin Zukowski,
    and Benoit Dageville in 2012 and is headquartered in Bozeman, Montana.
"""

print(complete("llama2-70b-chat", "how do snowflakes get their unique patterns?"))
print(extract_answer(text, "When was snowflake founded?"))
print(sentiment("I really enjoyed this restaurant. Fantastic service!"))
print(summarize(text))
print(translate(text, "en", "fr"))

from snowflake.cortex import complete, CompleteOptions

model_options1 = CompleteOptions(
    {'max_tokens':30}
)

print(complete("llama3.1-8b", "how do snowflakes get their unique patterns?", options=model_options1))

from snowflake.cortex import summarize
from snowflake.snowpark.functions import col

article_df = session.table("articles")
article_df = article_df.withColumn(
    "abstract_summary",
    summarize(col("abstract_text"))
)
article_df.collect()