AI_COMPLETE

Overview

Generates a completion for a given text string or image using a selected Large Language Model (LLM). This is the primary function for most generative AI tasks in Snowflake.

Syntax

AI_COMPLETE(
  model_name,
  prompt,
  [options]
)

AI_COMPLETE(
  model_name,
  prompt,
  [options]
)

AI_COMPLETE(
  model_name,
  prompt,
  [options]
)

Parameters

• model_name (VARCHAR): Name of the LLM model to use (e.g., 'claude-4-sonnet', 'llama3.1-70b', 'mistral-large2')

• prompt (VARCHAR or OBJECT): The input text or conversation history

• options (OBJECT): Optional parameters like max_tokens, temperature, etc.

Use Cases

• Text generation and creative writing

• Question answering

• Code generation

• Data analysis and insights

• Conversational AI

• Content summarization

• Multi-turn conversations

Code Examples

Example 1: Simple Text Completion

SELECT AI_COMPLETE(
  'llama3.1-70b',
  'Explain what a data warehouse is in one sentence.'
) AS

SELECT AI_COMPLETE(
  'llama3.1-70b',
  'Explain what a data warehouse is in one sentence.'
) AS

SELECT AI_COMPLETE(
  'llama3.1-70b',
  'Explain what a data warehouse is in one sentence.'
) AS

Output:

A data warehouse is a centralized repository that stores integrated data from multiple sources, optimized for analysis and reporting rather than transaction processing

A data warehouse is a centralized repository that stores integrated data from multiple sources, optimized for analysis and reporting rather than transaction processing

A data warehouse is a centralized repository that stores integrated data from multiple sources, optimized for analysis and reporting rather than transaction processing

Example 2: Using Claude for SQL Generation

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  'Write a SQL query to find the top 5 customers by total order value from a table called orders with columns: customer_id, order_date, order_total'
) AS

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  'Write a SQL query to find the top 5 customers by total order value from a table called orders with columns: customer_id, order_date, order_total'
) AS

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  'Write a SQL query to find the top 5 customers by total order value from a table called orders with columns: customer_id, order_date, order_total'
) AS

Output:

SELECT 
    customer_id,
    SUM(order_total) AS total_value
FROM orders
GROUP BY customer_id
ORDER BY total_value DESC
LIMIT 5

SELECT 
    customer_id,
    SUM(order_total) AS total_value
FROM orders
GROUP BY customer_id
ORDER BY total_value DESC
LIMIT 5

SELECT 
    customer_id,
    SUM(order_total) AS total_value
FROM orders
GROUP BY customer_id
ORDER BY total_value DESC
LIMIT 5

Example 3: Batch Processing with Table Data

SELECT 
    product_name,
    AI_COMPLETE(
        'mistral-large2',
        'Generate a compelling 50-word product description for: ' || product_name
    ) AS marketing_copy
FROM products
LIMIT 10

SELECT 
    product_name,
    AI_COMPLETE(
        'mistral-large2',
        'Generate a compelling 50-word product description for: ' || product_name
    ) AS marketing_copy
FROM products
LIMIT 10

SELECT 
    product_name,
    AI_COMPLETE(
        'mistral-large2',
        'Generate a compelling 50-word product description for: ' || product_name
    ) AS marketing_copy
FROM products
LIMIT 10

Example 4: Multi-turn Conversation

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  [
    {'role': 'user', 'content': 'What is Snowflake?'},
    {'role': 'assistant', 'content': 'Snowflake is a cloud data platform.'},
    {'role': 'user', 'content': 'What are its main features?'}
  ]
) AS

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  [
    {'role': 'user', 'content': 'What is Snowflake?'},
    {'role': 'assistant', 'content': 'Snowflake is a cloud data platform.'},
    {'role': 'user', 'content': 'What are its main features?'}
  ]
) AS

SELECT AI_COMPLETE(
  'claude-4-sonnet',
  [
    {'role': 'user', 'content': 'What is Snowflake?'},
    {'role': 'assistant', 'content': 'Snowflake is a cloud data platform.'},
    {'role': 'user', 'content': 'What are its main features?'}
  ]
) AS

Data Output Examples

Simple Query Response

Input: "What is 15% of 240?"
Output: "36"

Input: "What is 15% of 240?"
Output: "36"

Input: "What is 15% of 240?"
Output: "36"

Structured Data Generation

Input: "Generate a JSON object for a customer with name John Doe, age 35, email john@example.com"
Output: 
{
  "name": "John Doe",
  "age": 35,
  "email": "john@example.com"
}

Input: "Generate a JSON object for a customer with name John Doe, age 35, email john@example.com"
Output: 
{
  "name": "John Doe",
  "age": 35,
  "email": "john@example.com"
}

Input: "Generate a JSON object for a customer with name John Doe, age 35, email john@example.com"
Output: 
{
  "name": "John Doe",
  "age": 35,
  "email": "john@example.com"
}

Available Models

Limitations & Considerations

Token Limits

• Input + Output cannot exceed the model's context window

• Example: claude-4-sonnet allows 200K tokens total; if input is 150K, output is limited to 32K (max output) or 50K (remaining context)

Cost

• Billing based on input AND output tokens

• Costs vary by model (see Snowflake pricing)

• Use AI_COUNT_TOKENS to estimate costs before processing

Performance

• Use MEDIUM warehouse or smaller

• Larger warehouses don't improve performance

• Best for batch processing, not real-time

Regional Availability

Available via cross-region inference in most regions. Check availability:

• AWS US West/East: ✓

• Azure East US: ✓

• EU regions: ✓ (most models)

Related Functions

• AI_COUNT_TOKENS - Count tokens before processing

• PROMPT - Build dynamic prompts

• TRY_COMPLETE - Returns NULL on error instead of failing