Cortex AI: ML, LLMs & AI Workloads in SQL

In the modern data landscape, organizations are collecting vast amounts of data but often struggle to derive actionable insights quickly. Traditional analytics can tell you what happened, but AI workloads—like sentiment analysis, anomaly detection, and natural language processing—can tell you why and what to do next. However, moving data to specialized AI platforms introduces latency, cost, and security risks.

Snowflake Cortex AI changes this by bringing AI directly into your data warehouse. It provides a suite of SQL-based functions for machine learning inference, large language model (LLM) capabilities, and custom model deployment—all without leaving Snowflake. This means you can enrich your data with AI predictions in real-time, build intelligent applications, and automate decision-making using the same SQL you already know.

In this tutorial, you'll learn how to use Cortex AI for common tasks like sentiment analysis, translation, summarization, and anomaly detection. We'll cover production-ready examples, debugging techniques, and a real-world incident that highlights the importance of understanding model behavior. By the end, you'll be able to integrate AI into your data pipelines with confidence.

What is Snowflake Cortex AI?

Snowflake Cortex AI is a suite of AI capabilities built directly into Snowflake's data cloud. It allows you to perform machine learning inference, leverage large language models (LLMs), and run custom AI workloads using standard SQL. No need to export data or manage separate infrastructure.

All of these are accessible via SQL functions like SNOWFLAKE.CORTEX.COMPLETE, SNOWFLAKE.CORTEX.SENTIMENT, SNOWFLAKE.CORTEX.ANOMALY_DETECTION, etc. The underlying infrastructure is fully managed, so you only pay for the queries you run.

-- Check available Cortex functions
SELECT function_name
FROM information_schema.functions
WHERE function_schema = 'CORTEX'
  AND function_catalog = 'SNOWFLAKE';

-- Example: Sentiment analysis
SELECT SNOWFLAKE.CORTEX.SENTIMENT('I love this product!') AS sentiment;

Setting Up Your Environment

Before using Cortex AI, ensure your Snowflake account has the feature enabled. You can verify by running a simple function. If you get an error, contact your Snowflake admin.

1. Enable Cortex AI: Typically enabled by default in supported regions. Check with SELECT SYSTEM$ENABLE_CORTEX_AI(); (if needed).
2. Grant privileges: Your role needs USAGE ON DATABASE SNOWFLAKE and USAGE ON SCHEMA SNOWFLAKE.CORTEX.
3. Test connectivity: Run a simple sentiment analysis.

For custom models, you'll also need to set up a Snowpark environment (Python) and a stage for model artifacts.

-- Grant necessary privileges (as admin)
GRANT USAGE ON DATABASE SNOWFLAKE TO ROLE analyst_role;
GRANT USAGE ON SCHEMA SNOWFLAKE.CORTEX TO ROLE analyst_role;

-- Test connectivity
SELECT SNOWFLAKE.CORTEX.SENTIMENT('Hello world!') AS test;

Using LLMs for Text Generation and Summarization

Cortex AI provides access to powerful LLMs via the COMPLETE function. You can generate text, summarize documents, answer questions, and more. The function takes a model name and a prompt.

Example: Summarize customer reviews.

-- Summarize a long review
SELECT SNOWFLAKE.CORTEX.COMPLETE(
  'snowflake-arctic',
  'Summarize the following customer review in one sentence: "I bought this product last week and it broke after two days. The customer service was unhelpful and I had to wait a week for a refund. Very disappointed."'
) AS summary;

Sentiment Analysis and Translation

These are built on top of LLMs but optimized for speed and cost. Example: Analyze customer feedback in multiple languages.

-- Sentiment analysis on a table of reviews
SELECT review_id, review_text,
       SNOWFLAKE.CORTEX.SENTIMENT(review_text) AS sentiment
FROM customer_reviews;

-- Translate French reviews to English
SELECT review_id,
       SNOWFLAKE.CORTEX.TRANSLATE(review_text, 'fr', 'en') AS translated
FROM customer_reviews
WHERE SNOWFLAKE.CORTEX.DETECT_LANGUAGE(review_text) = 'fr';

Anomaly Detection and Forecasting

Cortex ML functions allow you to detect anomalies and forecast time-series data without writing Python. Use ANOMALY_DETECTION and FORECAST.

Example: Detect anomalies in daily sales.

-- Create a view with daily sales
CREATE OR REPLACE VIEW daily_sales AS
SELECT date, SUM(amount) AS sales
FROM transactions
GROUP BY date;

-- Detect anomalies
SELECT date, sales,
       SNOWFLAKE.CORTEX.ANOMALY_DETECTION(
         OBJECT_CONSTRUCT('timestamp_col', 'date', 'target_col', 'sales', 'contamination', 0.05)
       ) OVER (ORDER BY date) AS is_anomaly
FROM daily_sales;

Custom Models with Snowpark ML

For advanced use cases, you can train and deploy custom ML models using Snowpark ML. This involves writing Python code, registering the model in Snowflake, and then calling it via SQL.

Steps: 1. Train a model using Snowpark ML (e.g., XGBoost). 2. Save the model to a stage. 3. Register the model using CREATE MODEL. 4. Use PREDICT function in SQL.

Example: Train a simple linear regression model.

from snowflake.ml.modeling.linear_model import LinearRegression
from snowflake.ml.modeling.pipeline import Pipeline
from snowflake.snowpark import Session

# Create session
session = Session.builder.configs({...}).create()

# Load data
snowdf = session.table('training_data')

# Train model
pipeline = Pipeline([('lr', LinearRegression())])
pipeline.fit(snowdf, input_cols=['feature1', 'feature2'], label_cols=['target'])

# Save model to stage
pipeline.save('@model_stage/my_model')

# Register model in Snowflake
session.sql("CREATE MODEL my_model FROM REFERENCE @model_stage/my_model").collect()

Best Practices and Cost Optimization

Cortex AI pricing is based on the number of tokens processed (for LLMs) or compute time (for ML functions). To optimize costs: - Use smaller models (snowflake-arctic-lite) for high-volume tasks. - Batch requests: Instead of calling a function row-by-row, use a single SQL query that processes multiple rows. - Cache results: If the same input appears often, store the result in a table. - Monitor usage with QUERY_HISTORY view.

Also, consider security: Cortex AI functions run within Snowflake's security perimeter, so data never leaves. However, be cautious with sensitive data when using third-party models (e.g., Llama).

-- View recent Cortex AI usage
SELECT query_text, credits_used, start_time
FROM snowflake.account_usage.query_history
WHERE query_text LIKE '%SNOWFLAKE.CORTEX.%'
ORDER BY start_time DESC
LIMIT 10;