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.0makes the model strictly deterministic (good for data extraction).1.0makes 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_PRODUCT | EXTRACTED_PRICE |
|---|---|
| SuperWidget 3000 | 45.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!