Skip to main content

Task-Specific AI Functions

Historically, unstructured data (like emails, call transcripts, and product reviews) was a blind spot for data warehouses. Extracting insights required moving data to external NLP (Natural Language Processing) tools or building complex Python pipelines.

With Snowflake Cortex Task-Specific Functions, you can perform advanced LLM-based analytics on unstructured data using standard SQL SELECT statements.


️ Setup: Sample Data​

Let's create a sample table of customer support tickets to run our LLM analytics against.

-- Create sample table
CREATE OR REPLACE TABLE CUSTOMER_TICKETS (
TICKET_ID INT,
CUSTOMER_NAME VARCHAR,
LANGUAGE VARCHAR,
TICKET_TEXT VARCHAR
);

-- Insert sample tickets
INSERT INTO CUSTOMER_TICKETS (TICKET_ID, CUSTOMER_NAME, LANGUAGE, TICKET_TEXT) VALUES
(1, 'Alice', 'en', 'I am extremely frustrated. My package was supposed to arrive 3 days ago and the tracking has not updated. I want a refund immediately!'),
(2, 'Bob', 'en', 'The new dashboard feature is absolutely amazing! It saves my team hours of work every week. Great job to the engineering team.'),
(3, 'Carlos', 'es', 'Hola, no puedo acceder a mi cuenta desde esta mañana. ¿Me pueden ayudar a restablecer mi contraseña? Gracias.'),
(4, 'Diana', 'en', 'Attached are the server logs from the crash on Friday. The issue seems to be a memory leak in the billing module that triggers when batch processing exceeds 5000 records. We have temporarily disabled batching to stabilize the system, but we need a permanent patch from your support team ASAP.');

1. AI_SENTIMENT​

The AI_SENTIMENT() function analyzes text and returns a float score between -1 (highly negative) and 1 (highly positive). A score near 0 indicates neutral sentiment.

This is incredibly useful for prioritizing angry customer tickets or tracking brand health over time.

SELECT 
TICKET_ID,
TICKET_TEXT,
AI_SENTIMENT(TICKET_TEXT) AS SENTIMENT_SCORE
FROM CUSTOMER_TICKETS
WHERE LANGUAGE = 'en'
ORDER BY SENTIMENT_SCORE ASC;

Expected Outcome:

TICKET_IDSENTIMENT_SCORE
1-0.854 (Highly Negative - Needs refund)
4-0.120 (Slightly Negative / Neutral - Technical issue)
20.920 (Highly Positive - Praise)

2. CORTEX.SUMMARIZE​

Reading through a 5-page support ticket or a 1-hour call transcript wastes valuable analyst time. SNOWFLAKE.CORTEX.SUMMARIZE() automatically generates a concise summary of the provided text.

SELECT 
TICKET_ID,
TICKET_TEXT,
SNOWFLAKE.CORTEX.SUMMARIZE(TICKET_TEXT) AS TICKET_SUMMARY
FROM CUSTOMER_TICKETS
WHERE TICKET_ID = 4;

Expected Outcome: The model might return something like: "The user reported a memory leak in the billing module during batch processing over 5000 records, causing a server crash. They have disabled batching temporarily and requested a permanent patch."


3. AI_TRANSLATE​

Data teams often struggle with multi-lingual datasets. AI_TRANSLATE(text, from_language, to_language) allows you to standardize text into a common language for downstream analytics.

Let's translate Carlos's Spanish ticket into English.

SELECT 
CUSTOMER_NAME,
TICKET_TEXT AS ORIGINAL_SPANISH,
AI_TRANSLATE(TICKET_TEXT, 'es', 'en') AS TRANSLATED_ENGLISH
FROM CUSTOMER_TICKETS
WHERE LANGUAGE = 'es';

Expected Outcome: "Hello, I cannot access my account since this morning. Can you help me reset my password? Thank you."

Tip: If you don't know the source language, you can pass an empty string '' as the from_language and Cortex will attempt to auto-detect it!


4. CORTEX.EXTRACT_ANSWER​

Sometimes you don't want a summary; you want a specific fact extracted from a large block of text. SNOWFLAKE.CORTEX.EXTRACT_ANSWER(source_document, question) pulls the exact answer out.

SELECT 
SNOWFLAKE.CORTEX.EXTRACT_ANSWER(
TICKET_TEXT,
'What module caused the crash?'
) AS CRASH_SOURCE
FROM CUSTOMER_TICKETS
WHERE TICKET_ID = 4;

Expected Outcome: A JSON object will be returned containing the answer:

[
{
"answer": "the billing module",
"score": 0.89
}
]

Real-World Pattern: Enriching Data Pipelines​

Because these functions are callable from SQL, you can embed them in controlled batch pipelines. Cortex AI Functions do not support Dynamic Tables, so use Tasks, INSERT, MERGE, or an external orchestrator.

CREATE OR REPLACE TABLE ENRICHED_TICKETS AS
WITH TRANSLATED AS (
SELECT
TICKET_ID,
CUSTOMER_NAME,
AI_TRANSLATE(TICKET_TEXT, '', 'en') AS ENGLISH_TEXT
FROM CUSTOMER_TICKETS
)
SELECT
TICKET_ID,
CUSTOMER_NAME,
ENGLISH_TEXT,
AI_SENTIMENT(ENGLISH_TEXT) AS SENTIMENT_SCORE
FROM TRANSLATED;

In the next module, we'll look at what happens when these built-in functions aren't enough, and you need to write custom prompts to the foundation models using CORTEX.COMPLETE.