E-Commerce AI Analytics in Snowflake

Forecast sales, detect anomalies, and analyze reviews with Snowflake Cortex.

Introduction

30 Second Summary

Every online store generates mountains of sales data and customer reviews. The challenge is turning that raw information into predictions about what happens next and understanding how customers actually feel about your products.

In this project, you will build an AI-powered analytics layer on top of e-commerce data using Snowflake's built-in machine learning and AI functions. You'll forecast future sales, detect unusual patterns, and analyze customer sentiment, all in pure SQL.

What You'll Build

An AI analytics pipeline in Snowflake that uses ML forecasting, anomaly detection, and LLM-powered sentiment analysis to generate business insights from e-commerce data.

By the end of this project, you'll have:

A trained forecasting model that predicts the next 14 days of sales with confidence intervals.
An anomaly detection model that automatically flags unusual sales days in your data.
Automated sentiment scores on customer reviews using Snowflake Cortex AI, categorized as positive, neutral, or negative.
Secret Mission: Build a multi-series forecasting model that predicts sales per product category and compare forecast accuracy across categories.

Are there any prerequisites?

You'll need basic SQL knowledge (SELECT, JOIN, GROUP BY) and an existing Snowflake trial account with remaining credits.

Snowflake's free trial comes with $400 in credits. This project uses less than 1 credit total, so you have plenty of room.

Set Up Your Snowflake Workspace

Before you can train ML models or run AI functions, you need a dedicated workspace in Snowflake to keep everything organized.

In this step, you will create a database, schema, and virtual warehouse specifically for this project. You will also grant yourself the permissions needed to use Snowflake Cortex AI functions later.

In this step, get ready to:

Log into Snowflake and open a SQL worksheet.
Create a dedicated database, schema, and warehouse for the project.
Grant the Cortex user role to your account.

Log into Snowflake and open a SQL worksheet

All the work in this project happens inside Snowsight, Snowflake's web interface. You will write and run SQL directly in a worksheet.

Open your browser and go to app.snowflake.com.
Sign in with the credentials you used when creating your Snowflake trial account.
In the navigation menu on the left, select Projects, then Worksheets.
Click the + button and select SQL Worksheet.

A blank worksheet opens where you can type and run SQL statements.

Make sure your role is set to ACCOUNTADMIN. Check the role selector in the top-left area of the worksheet. If it shows a different role, click it and select ACCOUNTADMIN.

Why ACCOUNTADMIN?

Trial accounts default to the ACCOUNTADMIN role, which has full privileges to create databases, warehouses, and grant roles. This is the simplest path for a learning project.

Create your database, schema, and warehouse

A database holds your tables, a schema organizes objects within that database, and a warehouse provides the compute power to run your queries.

Create the project database and schema by pasting this SQL into your worksheet and clicking the Run All button (or pressing Cmd+Shift+Enter on macOS, Ctrl+Shift+Enter on Windows):

-- Create dedicated workspace
CREATE OR REPLACE DATABASE ecommerce_ai;
CREATE OR REPLACE SCHEMA ecommerce_ai.analytics;
USE DATABASE ecommerce_ai;
USE SCHEMA analytics;

What does this code do?

CREATE OR REPLACE DATABASE ecommerce_ai creates a fresh database named ecommerce_ai to hold all project objects.
CREATE OR REPLACE SCHEMA ecommerce_ai.analytics creates a schema called analytics inside that database for organizing your tables and models.
The two USE statements set your active context so future queries run against this database and schema automatically.

You should see a success message for each statement in the results pane at the bottom of the worksheet.

Seeing a permission error?

Make sure your worksheet role is set to ACCOUNTADMIN. Click the role selector in the top-left of the worksheet and switch to ACCOUNTADMIN if it shows a different role.

Help me fix this permission error

Now create your virtual warehouse by adding the following SQL below the previous statements in the same worksheet, then running it:

-- Create a warehouse (XS to conserve credits)
CREATE OR REPLACE WAREHOUSE ai_wh
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;

USE WAREHOUSE ai_wh;

What does this code do?

CREATE OR REPLACE WAREHOUSE ai_wh creates a compute warehouse named ai_wh.
WAREHOUSE_SIZE = 'XSMALL' sets the smallest (cheapest) size to conserve your trial credits.
AUTO_SUSPEND = 60 shuts the warehouse down after 60 seconds of inactivity so you stop burning credits when idle.
AUTO_RESUME = TRUE automatically restarts the warehouse when you run your next query.

You should see success messages for both statements in the results pane.

Warehouse not showing as active?

If you see a warning about the warehouse, make sure you ran the USE WAREHOUSE ai_wh; statement. This tells Snowflake to use your new warehouse for all future queries in this worksheet.

Help me troubleshoot my warehouse setup

Grant the Cortex user role

Snowflake Cortex AI functions (like sentiment analysis) require a special database role before you can call them.

Grant yourself the Cortex user role by adding this statement to your worksheet and running it:

-- Grant Cortex user role (run as ACCOUNTADMIN if needed)
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO USER CURRENT_USER();

What does this do?

SNOWFLAKE.CORTEX_USER is a database role that gives you permission to call Cortex AI functions like SENTIMENT. Without it, those function calls would fail with a permissions error.

CURRENT_USER() resolves to whatever username you are signed in as, so you do not need to look up your exact username.

You should see a success message in the results pane confirming the grant was applied.

Grant statement failing?

This command requires the ACCOUNTADMIN role. Double-check that your worksheet role is set to ACCOUNTADMIN in the role selector at the top-left of the worksheet.

Help me fix this grant error

✔️ Awesome, I've got everything!

Great. Make sure all statements ran successfully before moving on.

ⓧ I'd like to double check the full code

Here is the complete setup SQL. Every statement should show a success message when run:

-- Create dedicated workspace
CREATE OR REPLACE DATABASE ecommerce_ai;
CREATE OR REPLACE SCHEMA ecommerce_ai.analytics;
USE DATABASE ecommerce_ai;
USE SCHEMA analytics;

-- Create a warehouse (XS to conserve credits)
CREATE OR REPLACE WAREHOUSE ai_wh
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE;

USE WAREHOUSE ai_wh;

-- Grant Cortex user role (run as ACCOUNTADMIN if needed)
GRANT DATABASE ROLE SNOWFLAKE.CORTEX_USER TO USER CURRENT_USER();

Your workspace is ready. Next up, you will generate realistic e-commerce data to feed into your AI models.

Create Sample E-Commerce Data

Your Snowflake workspace is ready with a dedicated database, schema, and warehouse. Now you need data for the AI models to learn from.

You'll generate a full year of daily sales revenue that includes realistic patterns like weekday/weekend variation and seasonal trends. You'll also create customer review text that the sentiment analysis model will score later.

In this step, get ready to:

Generate 365 days of daily sales revenue with realistic business patterns and anomalous events.
Verify the data shape with a summary query.
Create a customer reviews table with mixed positive, negative, and neutral text.

Generate daily sales time-series data

Machine learning forecasting models learn from historical patterns in time-series data. You'll create a year of daily revenue figures with built-in weekday/weekend effects, seasonal trends, and random noise to simulate real e-commerce sales.

In the Snowsight navigation menu, select Projects then Worksheets.
Select the + button, then select SQL Worksheet to create a new worksheet.
Click the auto-generated title at the top of the worksheet and rename it to create_data.

Why a separate worksheet?

Each worksheet in Snowsight keeps its own session history and auto-saves independently. Using a separate worksheet per logical task makes it easy to re-run or reference your code later.

Paste the following code into your new worksheet to generate 365 days of sales data:

-- Generate 365 days of daily sales data with realistic patterns
CREATE OR REPLACE TABLE daily_sales AS
WITH date_spine AS (
    SELECT
        DATEADD(day, seq4(), '2023-06-01'::DATE) AS sale_date
    FROM TABLE(GENERATOR(ROWCOUNT => 365))
),
sales_with_patterns AS (
    SELECT
        sale_date,
        -- Base sales + weekday effect + seasonal trend + random noise
        GREATEST(
            ROUND(
                500
                + (CASE WHEN DAYOFWEEK(sale_date) IN (0, 6) THEN -80 ELSE 50 END)
                + (50 * SIN(2 * PI() * DATEDIFF(day, '2023-06-01', sale_date) / 365))
                + (UNIFORM(-60, 60, RANDOM()))
            , 2),
            50
        ) AS daily_revenue
    FROM date_spine
)
SELECT * FROM sales_with_patterns;

What does this code do?

`TABLE(GENERATOR(ROWCOUNT => 365))` produces 365 rows. The seq4() function numbers them 0 through 364, and DATEADD turns each number into a consecutive date starting from 2023-06-01.

The revenue formula combines a $500 base, a weekday boost (+$50) or weekend dip (-$80), a seasonal sine wave that peaks mid-year, and random noise between -$60 and +$60. GREATEST(..., 50) ensures no day falls below $50 in revenue.

Select the entire CREATE statement and click Run in the top right corner (or press Ctrl+Enter on Windows, Cmd+Return on macOS).

You should see a success message in the results panel confirming the table was created.

Seeing an error about the table or warehouse?

Make sure your ai_wh warehouse is active. If it suspended, run USE WAREHOUSE ai_wh; first.
Confirm your worksheet context shows ecommerce_ai as the database and analytics as the schema. You can check this in the context selector above the worksheet.
If neither is set, run USE DATABASE ecommerce_ai; and USE SCHEMA analytics; at the top of your worksheet.

Help me debug my Snowflake table creation error

Add these UPDATE statements below your CREATE TABLE code to inject anomalous data points:

-- Insert a few anomalous days (big spikes for flash sales)
UPDATE daily_sales
SET daily_revenue = daily_revenue * 3.5
WHERE sale_date IN ('2023-11-24', '2023-12-26', '2024-03-15');

-- Insert a dip (system outage simulation)
UPDATE daily_sales
SET daily_revenue = daily_revenue * 0.2
WHERE sale_date = '2024-01-10';

Why inject anomalies?

Real e-commerce data has irregular spikes from flash sales (like Black Friday on 2023-11-24) and unexpected dips from system outages. These intentional outliers give the anomaly detection model you'll train in Step 4 something concrete to find.

Multiplying by 3.5 creates revenue roughly 3x above normal. Multiplying by 0.2 drops revenue to one-fifth of its usual value.

Select both UPDATE statements and click Run.

You should see a confirmation showing rows updated for each statement (3 rows for the spikes, 1 row for the dip).

Verify your sales data

Add this verification query below your UPDATE statements and run it:

-- Verify the data
SELECT
    COUNT(*) AS total_days,
    MIN(sale_date) AS start_date,
    MAX(sale_date) AS end_date,
    ROUND(AVG(daily_revenue), 2) AS avg_daily_revenue,
    ROUND(MIN(daily_revenue), 2) AS min_revenue,
    ROUND(MAX(daily_revenue), 2) AS max_revenue
FROM daily_sales;

What should I see?

total_days: 365.
start_date: 2023-06-01.
end_date: 2024-05-30.
avg_daily_revenue: somewhere around $500-$560 (varies due to random noise).
min_revenue: very low (the outage dip day, around $20-$120).
max_revenue: very high (one of the spike days, around $1700-$2100).

Confirm your results grid shows 365 total days with a start date of 2023-06-01.

Seeing 0 rows or unexpected values?

If the table is empty, re-run the CREATE TABLE statement first. The UPDATE statements only work on an existing table with data.
If you accidentally ran the UPDATEs multiple times, the spike values will compound (e.g., revenue * 3.5 * 3.5). Re-run the CREATE TABLE statement to reset the data, then run the UPDATEs once.

Help me fix my daily_sales data verification results

Create the customer reviews table

The sentiment analysis model needs text data to score. You'll create a reviews table with 12 customer reviews across different products, each with naturally varied language.

Add this code below your verification query to create and populate the reviews table:

-- Create customer reviews table
CREATE OR REPLACE TABLE customer_reviews (
    review_id INT,
    product_name VARCHAR,
    review_text VARCHAR,
    review_date DATE
);

INSERT INTO customer_reviews VALUES
(1, 'Wireless Mouse', 'Absolutely love this mouse! Great battery life and very responsive. Best purchase this year.', '2024-04-01'),
(2, 'USB-C Cable', 'Cable stopped working after 2 weeks. Very disappointed with the quality.', '2024-04-02'),
(3, 'Desk Lamp', 'Decent lamp for the price. Nothing special but gets the job done.', '2024-04-03'),
(4, 'Mechanical Keyboard', 'The typing experience is incredible! Cherry switches feel amazing. Worth every penny.', '2024-04-04'),
(5, 'Monitor Stand', 'Arrived damaged and customer service was unhelpful. Would not recommend.', '2024-04-05'),
(6, 'Webcam HD', 'Good video quality for meetings. Easy setup. Slightly overpriced though.', '2024-04-06'),
(7, 'Notebook (A5)', 'Perfect size for daily notes. Paper quality is excellent, no bleed-through.', '2024-04-07'),
(8, 'Wireless Mouse', 'Scroll wheel broke after a month. Returning it.', '2024-04-08'),
(9, 'Mechanical Keyboard', 'Too loud for an open office. Build quality is good but not practical for my setup.', '2024-04-09'),
(10, 'Desk Lamp', 'Beautiful design and the dimming feature is very useful. Highly recommend!', '2024-04-10'),
(11, 'USB-C Cable', 'Works perfectly fine. Fast charging as advertised. No complaints.', '2024-04-11'),
(12, 'Monitor Stand', 'Sturdy build, holds my ultrawide perfectly. Assembly took 5 minutes.', '2024-04-12');

What's in this data?

The 12 reviews span 6 products with a natural mix of enthusiastic praise, mild disappointment, and neutral "gets the job done" language. Some products have multiple reviews with conflicting opinions (e.g., the Wireless Mouse has one glowing and one negative review).

In Step 5, Snowflake Cortex AI will score each review's sentiment on a scale from -1 (very negative) to +1 (very positive).

Select both the CREATE TABLE and INSERT statements, then click Run.

You should see confirmation messages for the table creation and 12 rows inserted.

Seeing a syntax error on the INSERT?

Check that the closing parenthesis after the last review row (row 12) ends with a semicolon, not a comma.
Each VALUES row must be wrapped in parentheses and separated by commas, except the final row which has no trailing comma.
Make sure all single quotes in the review text are standard straight quotes, not curly/smart quotes from a word processor.

Help me fix my INSERT INTO customer_reviews syntax error

✔️ Awesome, I've got everything!

Great. Double check that your worksheet is named create_data and all statements ran without errors.

ⓧ I'd like to double check the full code

-- Generate 365 days of daily sales data with realistic patterns
CREATE OR REPLACE TABLE daily_sales AS
WITH date_spine AS (
    SELECT
        DATEADD(day, seq4(), '2023-06-01'::DATE) AS sale_date
    FROM TABLE(GENERATOR(ROWCOUNT => 365))
),
sales_with_patterns AS (
    SELECT
        sale_date,
        -- Base sales + weekday effect + seasonal trend + random noise
        GREATEST(
            ROUND(
                500
                + (CASE WHEN DAYOFWEEK(sale_date) IN (0, 6) THEN -80 ELSE 50 END)
                + (50 * SIN(2 * PI() * DATEDIFF(day, '2023-06-01', sale_date) / 365))
                + (UNIFORM(-60, 60, RANDOM()))
            , 2),
            50
        ) AS daily_revenue
    FROM date_spine
)
SELECT * FROM sales_with_patterns;

-- Insert a few anomalous days (big spikes for flash sales)
UPDATE daily_sales
SET daily_revenue = daily_revenue * 3.5
WHERE sale_date IN ('2023-11-24', '2023-12-26', '2024-03-15');

-- Insert a dip (system outage simulation)
UPDATE daily_sales
SET daily_revenue = daily_revenue * 0.2
WHERE sale_date = '2024-01-10';

-- Verify the data
SELECT
    COUNT(*) AS total_days,
    MIN(sale_date) AS start_date,
    MAX(sale_date) AS end_date,
    ROUND(AVG(daily_revenue), 2) AS avg_daily_revenue,
    ROUND(MIN(daily_revenue), 2) AS min_revenue,
    ROUND(MAX(daily_revenue), 2) AS max_revenue
FROM daily_sales;

-- Create customer reviews table
CREATE OR REPLACE TABLE customer_reviews (
    review_id INT,
    product_name VARCHAR,
    review_text VARCHAR,
    review_date DATE
);

INSERT INTO customer_reviews VALUES
(1, 'Wireless Mouse', 'Absolutely love this mouse! Great battery life and very responsive. Best purchase this year.', '2024-04-01'),
(2, 'USB-C Cable', 'Cable stopped working after 2 weeks. Very disappointed with the quality.', '2024-04-02'),
(3, 'Desk Lamp', 'Decent lamp for the price. Nothing special but gets the job done.', '2024-04-03'),
(4, 'Mechanical Keyboard', 'The typing experience is incredible! Cherry switches feel amazing. Worth every penny.', '2024-04-04'),
(5, 'Monitor Stand', 'Arrived damaged and customer service was unhelpful. Would not recommend.', '2024-04-05'),
(6, 'Webcam HD', 'Good video quality for meetings. Easy setup. Slightly overpriced though.', '2024-04-06'),
(7, 'Notebook (A5)', 'Perfect size for daily notes. Paper quality is excellent, no bleed-through.', '2024-04-07'),
(8, 'Wireless Mouse', 'Scroll wheel broke after a month. Returning it.', '2024-04-08'),
(9, 'Mechanical Keyboard', 'Too loud for an open office. Build quality is good but not practical for my setup.', '2024-04-09'),
(10, 'Desk Lamp', 'Beautiful design and the dimming feature is very useful. Highly recommend!', '2024-04-10'),
(11, 'USB-C Cable', 'Works perfectly fine. Fast charging as advertised. No complaints.', '2024-04-11'),
(12, 'Monitor Stand', 'Sturdy build, holds my ultrawide perfectly. Assembly took 5 minutes.', '2024-04-12');

Your e-commerce data is loaded and ready. Next up, you'll train a forecasting model to predict future sales based on the patterns you just created.

Forecast Future Sales with Snowflake ML

You now have 365 days of realistic sales data sitting in your daily_sales table. The next question any business would ask is: what will sales look like next week?

Snowflake ML lets you train a time-series forecasting model with a single SQL statement. In this step, you'll train that model on your sales history and generate a 14-day revenue prediction complete with confidence intervals.

In this step, get ready to:

Train a forecasting model on your daily sales data.
Generate a 14-day sales forecast with prediction intervals.
Query the forecast results and review model evaluation metrics.

Train the forecasting model

Snowflake's ML forecasting function takes your historical time-series data and automatically selects the best algorithm to predict future values. All you need to provide is the table, the timestamp column, and the target column.

In the left navigation menu, select Projects, then Worksheets.
Click the + button and select SQL Worksheet to open a new worksheet.
Train your forecasting model by pasting and running this SQL:

-- Train a forecasting model on daily sales
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST sales_forecast_model(
    INPUT_DATA => TABLE(daily_sales),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue'
);

What does this code do?

CREATE OR REPLACE SNOWFLAKE.ML.FORECAST creates a named ML model object called sales_forecast_model in your schema.

INPUT_DATA => TABLE(daily_sales) points the model at your 365-day sales table.
TIMESTAMP_COLNAME => 'sale_date' tells the model which column holds your time axis.
TARGET_COLNAME => 'daily_revenue' tells the model which column to predict.

Snowflake automatically selects the best algorithm and trains the model. No Python, no hyperparameter tuning, no infrastructure to manage.

Click the Run button (or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
Wait for the model to finish training. This may take 30-60 seconds. You should see a success message confirming the model was created.

Seeing a privilege error?

The forecasting function requires the CREATE SNOWFLAKE.ML.FORECAST privilege on the schema. If you're using the ACCOUNTADMIN role (the default for trial accounts), you already have this.

If you switched roles, run this to grant the privilege:

GRANT CREATE SNOWFLAKE.ML.FORECAST ON SCHEMA ecommerce_ai.analytics TO ROLE <your_role>;

help me fix a privilege error when training a Snowflake ML forecast model

Generate a 14-day sales forecast

Your trained model is now stored as an object in your schema. You can call its !FORECAST method to generate predictions for any number of future periods.

Generate a 14-day forecast and save it to a new table by running this SQL:

-- Generate a 14-day forecast
CREATE OR REPLACE TABLE sales_predictions AS
SELECT * FROM TABLE(sales_forecast_model!FORECAST(
    FORECASTING_PERIODS => 14
));

What does this code do?

sales_forecast_model!FORECAST() calls the forecast method on your trained model.
FORECASTING_PERIODS => 14 tells it to predict the next 14 days beyond your training data.
The results are stored in a new sales_predictions table so you can query them later.

Each row includes a predicted value plus a lower and upper bound. The default prediction interval is 0.95, meaning 95% of future points are expected to fall within those bounds.

Run the statement. You should see a confirmation that the sales_predictions table was created successfully.

Model not found error?

Make sure you ran the training statement from the previous substep first and it completed successfully. The model must exist before you can call its methods.

Also check that your worksheet context is set to the ecommerce_ai database and analytics schema using the dropdowns at the top of the worksheet.

help me troubleshoot a model not found error in Snowflake ML

Query the forecast results and review evaluation metrics

Your predictions are stored in a table, but the raw column names are generic. A formatted query makes the results much easier to read.

View your formatted forecast by running this query:

-- View the forecast results
SELECT
    ts AS forecast_date,
    ROUND(forecast, 2) AS predicted_revenue,
    ROUND(lower_bound, 2) AS lower_bound,
    ROUND(upper_bound, 2) AS upper_bound
FROM sales_predictions
ORDER BY ts;

What do these columns mean?

ts is the forecasted date (aliased as forecast_date).
forecast is the model's best prediction for that day's revenue.
lower_bound and upper_bound form the 95% prediction interval. The model is 95% confident actual revenue will fall between these two values.

Run the query. You should see 14 rows, one for each forecasted day, with the predicted revenue and its confidence bounds.
Check how well your model fits the data by running this command:

-- Check model evaluation metrics
CALL sales_forecast_model!SHOW_EVALUATION_METRICS();

What are evaluation metrics?

This returns metrics that measure how accurately the model captures patterns in your data. Key metrics include:

MAE (Mean Absolute Error): average dollar amount the model is off by.
MAPE (Mean Absolute Percentage Error): average percentage the model is off by.
Lower values mean better accuracy. Your model may show higher error because of the anomalous spikes you intentionally added to the data.

Run the CALL statement. You should see a table of metric names and their corresponding values.

Seeing zero rows in your forecast?

Make sure you ran the CREATE TABLE statement that saves predictions, not just the inner SELECT. The query in this substep reads from the sales_predictions table, which must exist first.

help me debug empty forecast results in Snowflake ML

✔️ Awesome, I've got everything!

Great. Double check you've run all the statements in this step successfully before moving on.

ⓧ I'd like to double check the full code

Here is the complete SQL for this step. Compare it against what you have in your worksheet.

-- Train a forecasting model on daily sales
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST sales_forecast_model(
    INPUT_DATA => TABLE(daily_sales),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue'
);

-- Generate a 14-day forecast
CREATE OR REPLACE TABLE sales_predictions AS
SELECT * FROM TABLE(sales_forecast_model!FORECAST(
    FORECASTING_PERIODS => 14
));

-- View the forecast results
SELECT
    ts AS forecast_date,
    ROUND(forecast, 2) AS predicted_revenue,
    ROUND(lower_bound, 2) AS lower_bound,
    ROUND(upper_bound, 2) AS upper_bound
FROM sales_predictions
ORDER BY ts;

-- Check model evaluation metrics
CALL sales_forecast_model!SHOW_EVALUATION_METRICS();

You now have a trained ML model that predicts future sales. Next up, you'll build an anomaly detection model that flags unusual days in your historical data, so you can automatically spot flash sales and outages.

Detect Anomalies in Sales Data

Your forecasting model can predict what sales will look like over the next two weeks. But predicting the future is only half the story. What about unusual events that already happened in your historical data?

Detecting anomalies helps you automatically spot flash sales, system outages, or suspicious activity that broke the normal sales pattern. In this step, you'll train Snowflake's anomaly detection model to learn what "normal" looks like, then use it to flag the outlier days you planted in Step 2.

In this step, get ready to:

Split your sales data into training and detection sets.
Train an anomaly detection model using unsupervised learning.
Run anomaly detection and review which days were flagged.

Train an anomaly detection model

An anomaly detection model needs a baseline of "normal" behavior to learn from. You'll split your sales data so the model trains on the first 300 days, then you can detect outliers across the full dataset.

In Snowsight, click the + button next to your worksheet tabs to open a new SQL worksheet.
Create the training and detection views by running this code:

-- Split data: use first 300 days for training, rest for detection
CREATE OR REPLACE VIEW training_data AS
SELECT * FROM daily_sales
WHERE sale_date < '2024-04-01';

CREATE OR REPLACE VIEW detection_data AS
SELECT * FROM daily_sales
WHERE sale_date >= '2024-04-01';

What does this code do?

Views are saved queries that act like virtual tables. They don't duplicate data, they just filter the original table on the fly.

training_data contains sales before April 2024 (about 300 days). This is what the model learns "normal" from.
detection_data contains sales from April 2024 onward. You'll use this later if you want to test on unseen data only.

Select both statements and click Run. You should see success messages for each view creation.

Now train the anomaly detection model on your training data by running this code:

-- Train anomaly detection model
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sales_anomaly_model(
    INPUT_DATA => TABLE(training_data),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue',
    LABEL_COLNAME => ''
);

What does this code do?

CREATE SNOWFLAKE.ML.ANOMALY_DETECTION trains a model that learns the normal patterns in your time-series data.
INPUT_DATA => TABLE(training_data) feeds the model your 300-day training view.
LABEL_COLNAME => '' tells the model you have no pre-labeled anomalies. This makes it unsupervised. The model figures out what's unusual on its own.

Select this statement and click Run. Training takes 15-30 seconds.
You should see a success message confirming sales_anomaly_model was created.

Seeing a privilege error?

Make sure you're using the ACCOUNTADMIN role. Click your name in the top left of Snowsight, then Switch Role, then ACCOUNTADMIN.
Confirm the ecommerce_ai database and analytics schema are selected in the worksheet context dropdown at the top of your worksheet.

Help me fix a privilege error when creating an anomaly detection model in Snowflake.

Run the model to identify outlier days

The trained model knows what normal sales look like. Now you'll run it against your full dataset to find days that don't fit the pattern.

Run anomaly detection on the full sales dataset by running this code:

-- Detect anomalies in the full dataset
CREATE OR REPLACE TABLE anomaly_results AS
SELECT * FROM TABLE(sales_anomaly_model!DETECT_ANOMALIES(
    INPUT_DATA => TABLE(daily_sales),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue'
));

What does this code do?

The !DETECT_ANOMALIES method runs your trained model against input data. For each day, the model compares actual revenue to what it predicted as "normal" and flags anything outside the 99% confidence interval.

You're running detection on the full daily_sales table (all 365 days), not just the detection subset. This lets you catch the anomalous spikes and dips you inserted across the entire year.

Select the statement and click Run.
You should see a success message confirming the anomaly_results table was created.

Model not found error?

Make sure the model training from the previous chunk completed successfully before running detection.
Verify you're in the same database and schema where you created the model (ecommerce_ai.analytics).

Help me troubleshoot a model not found error in Snowflake.

Query the flagged anomalies

The results table contains a row for every day, with columns indicating whether each day was anomalous and how far it deviated from normal. Let's filter down to just the flagged days.

View the days flagged as anomalous by running this query:

-- View flagged anomalies
SELECT
    ts AS sale_date,
    ROUND(y, 2) AS actual_revenue,
    ROUND(forecast, 2) AS expected_revenue,
    ROUND(distance, 2) AS z_score,
    is_anomaly
FROM anomaly_results
WHERE is_anomaly = TRUE
ORDER BY ts;

What do these columns mean?

y is the actual revenue recorded that day.
forecast is what the model expected revenue to be based on normal patterns.
distance is the z-score. It measures how many standard deviations the actual value is from the expected value. Higher absolute values mean more unusual.
is_anomaly is a boolean flag. The model sets this to TRUE when the data point falls outside the 99% confidence interval.

Select the query and click Run.
You should see the anomalous days appear in the results pane. Look for the dates you inserted as spikes (2023-11-24, 2023-12-26, 2024-03-15) and the dip (2024-01-10). The model should have flagged most or all of them.

Why might some anomalous days be missed?

The model uses a 99% prediction interval by default. If an anomalous value happens to fall just inside that wide band, it won't get flagged. Random noise in the training data also affects where the model draws the boundary.

Not seeing any results?

Make sure you're selecting only the final SELECT query, not the entire worksheet. Highlight just the query starting from SELECT down to the final semicolon.
Confirm the anomaly_results table was created successfully in the previous step.

Help me debug why my anomaly detection query returns no results.

✔️ Awesome, I've got everything!

Great. Double check you've saved your worksheet before moving on.

ⓧ I'd like to double check the full code

Here's the complete code for this step. Make sure your worksheet matches:

-- Split data: use first 300 days for training, rest for detection
CREATE OR REPLACE VIEW training_data AS
SELECT * FROM daily_sales
WHERE sale_date < '2024-04-01';

CREATE OR REPLACE VIEW detection_data AS
SELECT * FROM daily_sales
WHERE sale_date >= '2024-04-01';

-- Train anomaly detection model
CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION sales_anomaly_model(
    INPUT_DATA => TABLE(training_data),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue',
    LABEL_COLNAME => ''
);

-- Detect anomalies in the full dataset
CREATE OR REPLACE TABLE anomaly_results AS
SELECT * FROM TABLE(sales_anomaly_model!DETECT_ANOMALIES(
    INPUT_DATA => TABLE(daily_sales),
    TIMESTAMP_COLNAME => 'sale_date',
    TARGET_COLNAME => 'daily_revenue'
));

-- View flagged anomalies
SELECT
    ts AS sale_date,
    ROUND(y, 2) AS actual_revenue,
    ROUND(forecast, 2) AS expected_revenue,
    ROUND(distance, 2) AS z_score,
    is_anomaly
FROM anomaly_results
WHERE is_anomaly = TRUE
ORDER BY ts;

Your anomaly detection model can now automatically flag unusual days in your sales data. Next up, you'll add the final piece of your AI analytics layer: using Snowflake Cortex AI to analyze customer review sentiment.

Analyze Customer Review Sentiment

You've built models that predict future revenue and flag unusual days. Now it's time to understand what your customers are actually saying.

Snowflake Cortex AI includes a SENTIMENT function that scores any text on a scale from -1 (most negative) to 1 (most positive). You'll use it to analyze your customer reviews without writing a single line of Python.

In this step, get ready to:

Score customer reviews using SNOWFLAKE.CORTEX.SENTIMENT.
Categorize reviews as positive, neutral, or negative based on score thresholds.
Build a product-level sentiment summary showing review distribution.

Score and categorize customer reviews

The SNOWFLAKE.CORTEX.SENTIMENT function takes a text column and returns a floating-point score. Scores at or above 0.5 indicate positive sentiment analysis, scores at or below -0.5 indicate negative sentiment, and anything in between is neutral.

Create a new SQL worksheet in Snowsight by selecting Projects then Worksheets, then clicking the + button and choosing SQL Worksheet.
Create the review_sentiment table by running the following query:

-- Run sentiment analysis on customer reviews
CREATE OR REPLACE TABLE review_sentiment AS
SELECT
    review_id,
    product_name,
    review_text,
    review_date,
    SNOWFLAKE.CORTEX.SENTIMENT(review_text) AS sentiment_score,
    CASE
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(review_text) >= 0.5 THEN 'Positive'
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(review_text) <= -0.5 THEN 'Negative'
        ELSE 'Neutral'
    END AS sentiment_category
FROM customer_reviews;

What does this query do?

This query reads every row in customer_reviews and calls SNOWFLAKE.CORTEX.SENTIMENT on the review text. The function returns a score between -1 and 1 for each review.

The CASE expression then maps each score into a human-readable category. Scores >= 0.5 become 'Positive', scores <= -0.5 become 'Negative', and everything else becomes 'Neutral'.

Click the Run button (or press Cmd+Enter on macOS, Ctrl+Enter on Windows).
You should see a success message confirming the review_sentiment table was created.

Seeing an error about CORTEX?

If you see a permissions error mentioning Cortex, the SNOWFLAKE.CORTEX_USER database role may not be granted to your user. Switch your role to ACCOUNTADMIN and re-run the GRANT statement from Step 1.

If your Snowflake trial is in a region that doesn't support Cortex AI Functions, you'll see a region-related error. Most common trial regions (US East, US West, EU) are supported.

Help me fix my Cortex permissions error

Query the sentiment results

Now that the scores exist in a table, you can query them to see which reviews are most positive and most negative.

View the scored reviews ordered from most positive to most negative by running this query:

-- View results
SELECT
    review_id,
    product_name,
    LEFT(review_text, 60) AS review_preview,
    ROUND(sentiment_score, 4) AS sentiment_score,
    sentiment_category
FROM review_sentiment
ORDER BY sentiment_score DESC;

What should I see?

You should see 12 rows sorted from the highest sentiment score (most positive review) at the top to the lowest score (most negative) at the bottom. Each row shows a truncated preview of the review text, its numerical score, and the category label.

Reviews like "Absolutely love this mouse!" should score near 1.0 and show as Positive. Reviews like "Arrived damaged" should score near -1.0 and show as Negative.

Click Run to execute the query.
You should see a results table with 12 rows, each showing the sentiment_score and sentiment_category columns populated.

All scores showing as 0 or NULL?

Make sure you ran the CREATE OR REPLACE TABLE review_sentiment query first. The SELECT query reads from the review_sentiment table, not directly from customer_reviews.

Help me debug my sentiment query results

Build a sentiment summary by product

Individual scores are useful, but a product-level summary reveals which products have the happiest (or most frustrated) customers overall.

Create a product-level summary by running this query:

-- Sentiment summary by product
SELECT
    product_name,
    COUNT(*) AS total_reviews,
    ROUND(AVG(sentiment_score), 4) AS avg_sentiment,
    SUM(CASE WHEN sentiment_category = 'Positive' THEN 1 ELSE 0 END) AS positive_count,
    SUM(CASE WHEN sentiment_category = 'Neutral' THEN 1 ELSE 0 END) AS neutral_count,
    SUM(CASE WHEN sentiment_category = 'Negative' THEN 1 ELSE 0 END) AS negative_count
FROM review_sentiment
GROUP BY product_name
ORDER BY avg_sentiment DESC;

What does this query do?

This groups all reviews by product_name and calculates an average sentiment score per product. The SUM(CASE ...) expressions count how many reviews fell into each category.

Products with multiple reviews (like Wireless Mouse) will show a mixed profile. Products with only one review will clearly lean positive or negative.

Click Run to execute the query.
You should see one row per product, ordered from highest average sentiment to lowest. Products like Mechanical Keyboard and Notebook (A5) should appear near the top with higher average scores.

Seeing unexpected counts?

Check that you're querying from review_sentiment (which has the sentiment_category column), not from customer_reviews (which doesn't).

Help me fix my sentiment summary query

✔️ Awesome, I've got everything!

Great. Make sure all three queries are saved in your worksheet for reference.

ⓧ I'd like to double check the full code

Here is the complete sentiment_analysis.sql worksheet:

-- Run sentiment analysis on customer reviews
CREATE OR REPLACE TABLE review_sentiment AS
SELECT
    review_id,
    product_name,
    review_text,
    review_date,
    SNOWFLAKE.CORTEX.SENTIMENT(review_text) AS sentiment_score,
    CASE
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(review_text) >= 0.5 THEN 'Positive'
        WHEN SNOWFLAKE.CORTEX.SENTIMENT(review_text) <= -0.5 THEN 'Negative'
        ELSE 'Neutral'
    END AS sentiment_category
FROM customer_reviews;

-- View results
SELECT
    review_id,
    product_name,
    LEFT(review_text, 60) AS review_preview,
    ROUND(sentiment_score, 4) AS sentiment_score,
    sentiment_category
FROM review_sentiment
ORDER BY sentiment_score DESC;

-- Sentiment summary by product
SELECT
    product_name,
    COUNT(*) AS total_reviews,
    ROUND(AVG(sentiment_score), 4) AS avg_sentiment,
    SUM(CASE WHEN sentiment_category = 'Positive' THEN 1 ELSE 0 END) AS positive_count,
    SUM(CASE WHEN sentiment_category = 'Neutral' THEN 1 ELSE 0 END) AS neutral_count,
    SUM(CASE WHEN sentiment_category = 'Negative' THEN 1 ELSE 0 END) AS negative_count
FROM review_sentiment
GROUP BY product_name
ORDER BY avg_sentiment DESC;

Secret mission

Multi-Series Forecasting by Product Category

Your current forecast model treats all sales as one big number. But real businesses need to know which product categories are driving growth and which ones are slowing down. Can you build a model that forecasts each category independently?

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely within your Snowflake trial account, and the ai_wh warehouse auto-suspends after 60 seconds of inactivity. No credits are consumed while it is suspended.

Resources you used:

Database ecommerce_ai (contains all tables, views, ML models, and the analytics schema).
Warehouse ai_wh (XSMALL, auto-suspends after 60 seconds).

Keep everything running

No action needed. Choose this if you plan to keep experimenting with Snowflake ML functions or Cortex AI.

Your warehouse auto-suspends after 60 seconds of inactivity, so it will not consume credits while idle.
Your trial account has $400 in credits. This project used less than 1 credit total, so you have plenty of room for further exploration.
All tables, models, and views remain available in the ecommerce_ai database.

Pause - I'll come back to this later

Shut down the warehouse explicitly if you want to make sure no credits are consumed, even briefly.

Run this command in a new Snowsight worksheet to suspend the warehouse immediately:

ALTER WAREHOUSE ai_wh SUSPEND;

Your database, tables, views, and trained ML models all remain intact.
When you return, run USE WAREHOUSE ai_wh; and the warehouse will resume automatically.

Delete - I don't want to use this again

Remove all project resources and start fresh. This drops everything you created in this project.

Open a new worksheet in Snowsight and run the following commands:

-- Drop the database (removes all tables, views, models, and schema)
DROP DATABASE IF EXISTS ecommerce_ai;

-- Drop the warehouse
DROP WAREHOUSE IF EXISTS ai_wh;

Dropping the ecommerce_ai database removes the analytics schema and everything inside it. This includes all tables (daily_sales, customer_reviews, sales_predictions, anomaly_results, review_sentiment, daily_sales_by_category, category_predictions), views, and trained ML models.
Dropping the ai_wh warehouse removes the compute resource.
Your Snowflake trial account remains active. You can continue using it for other projects.

Nice Work!

You just built an AI-powered analytics layer on e-commerce data using pure SQL in Snowflake. No Python scripts, no external ML platforms, no API integrations. Just SQL and Snowflake's built-in intelligence.

You've learned how to:

Train a time-series forecasting model that predicts future sales with confidence intervals using Snowflake ML functions.
Build an anomaly detection model that automatically flags unusual business days based on z-score distance from expected values.
Apply LLM-powered sentiment analysis to customer reviews using Snowflake Cortex AI without any external APIs or infrastructure.
Built multi-series forecasting to predict sales per product category and compared model accuracy across segments.

Ready to quiz yourself?