Skip to main content

AI_COMPLETE and Prompt Fundamentals

While task-specific functions like CORTEX.SUMMARIZE are great for quick jobs, they are "black boxes." You can't tell the model how you want it to summarize the text (e.g., "Summarize this in 3 bullet points tailored for a CEO").

For custom generative AI tasks, use AI_COMPLETE(). It is the current version of SNOWFLAKE.CORTEX.COMPLETE() and provides direct access to supported LLMs.


️ The Basic AI_COMPLETE Request​

The simplest way to use AI_COMPLETE is by passing the model name and prompt string.

SELECT AI_COMPLETE(
'mistral-large',
'Write a polite email to a customer named John apologizing for a 2-day shipping delay.'
) AS GENERATED_EMAIL;

Model note: Supported models vary by region and change over time. Check the current Snowflake model documentation instead of hard-coding a long-term model list.


️ Injecting Data into Prompts​

The true power of Cortex in SQL is dynamically building prompts using data stored in your tables. You can use SQL string concatenation (|| or CONCAT()) to inject table columns into your prompt.

-- Assume we have a table called SALES_ALERTS
SELECT
CUSTOMER_NAME,
AI_COMPLETE(
'llama3-8b',
'You are an expert sales representative. Write a short, personalized outreach email to '
|| CUSTOMER_NAME ||
' who recently bought '
|| PRODUCT_NAME ||
'. Offer them a 10% discount on a complimentary accessory.'
) AS OUTREACH_EMAIL
FROM SALES_ALERTS;

Advanced Prompting: System Prompts and JSON Objects​

For production applications, sending a single string isn't always enough. You often need to set a "System Prompt" to define the persona of the AI, followed by a "User Prompt".

The legacy SNOWFLAKE.CORTEX.COMPLETE syntax accepts a conversation-history array. For new implementations, prefer AI_COMPLETE and its prompt-object interface where supported.

SELECT SNOWFLAKE.CORTEX.COMPLETE(
'llama3-70b',
[
{
'role': 'system',
'content': 'You are a strict data extraction bot. Your job is to extract the names of any cities mentioned in the text. Return ONLY a comma-separated list of cities. Do not say "Here are the cities:". If no cities are found, return "NONE".'
},
{
'role': 'user',
'content': 'Our regional managers are flying from Chicago to Atlanta tomorrow morning for the quarterly review.'
}
]
) AS EXTRACTED_CITIES;

Expected Outcome: Chicago, Atlanta


️ Controlling Model Behavior (Options)​

When dealing with LLMs, you can tweak parameters like temperature to control how "creative" or "deterministic" the model is.

You can pass model parameters to AI_COMPLETE to control response behavior.

SELECT AI_COMPLETE(
model => 'mistral-large2',
prompt => 'Write a creative marketing slogan for a new waterproof running shoe.',
model_parameters => {
'temperature': 0.8,
'max_tokens': 50
}
) AS CREATIVE_SLOGAN;

Common Options​

  • temperature (Float 0.0 - 1.0): 0.0 makes the model strictly deterministic (good for data extraction). 1.0 makes the model highly creative (good for marketing copy).
  • max_tokens (Integer): Limits the length of the generated response, which is crucial for controlling compute costs.
  • top_p (Float 0.0 - 1.0): Another way to control randomness (nucleus sampling).

Parsing JSON Responses​

A common pattern in data engineering is asking the LLM to return its answer as a structured JSON object so it can be parsed into actual table columns.

-- 1. Ask the model to return JSON
WITH LLM_RESPONSE AS (
SELECT AI_COMPLETE(
'llama3-8b',
'Extract the product name and price from this text: "I bought the SuperWidget 3000 for $45.99." Return ONLY valid JSON in this format: {"product": "name", "price": 0.00}'
) AS RAW_JSON_STRING
)
-- 2. Parse the string into Snowflake variant/JSON and extract fields
SELECT
PARSE_JSON(RAW_JSON_STRING):product::VARCHAR AS EXTRACTED_PRODUCT,
PARSE_JSON(RAW_JSON_STRING):price::FLOAT AS EXTRACTED_PRICE
FROM LLM_RESPONSE;

Expected Outcome:

EXTRACTED_PRODUCTEXTRACTED_PRICE
SuperWidget 300045.99

By combining string concatenation, system prompts, JSON formatting, and Snowflake's PARSE_JSON capabilities, you can build incredibly robust generative AI pipelines directly inside your data warehouse!