Snowflake Machine Learning Functions: Forecasting
If you think that Snowflake is just a cloud-based data warehouse, join me in this article as I present the Machine Learning (ML) resources included in Snowflake, which allow us to apply ML models quickly and easily, without needing to be specialists in data science or programming anything. An additional benefit is that we can do everything with SQL, or that it does not leave people who do not know Python, for example.
Many advanced analysts with some knowledge of SQL can benefit from the use and application of these resources. Its usefulness lies mainly in situations where we do not need a perfect model or when we do not have the skills or knowledge necessary to dedicate ourselves to the creation of each model.
A preview of temporary series is one of the most used preview tools on a day-to-day basis, and generally we do just Excel and with a limited amount of data.
Advantages of doing this in Snowflake
- Integrated with Snowflake
- Not an additional product or feature
- It’s right next to your data; no integration or data movement required
- It’s a self-managed process; we don’t need any infrastructure to manage
- High performance, with built-in parallelization
- SQL-based dataflow, which simplifies
- Ready to collaborate on Snowflake
Step by step guide
For this guide, I’ll be using one of Snowflake’s quickstarts: https://quickstarts.snowflake.com/guide/getting-started-with-snowflake-cortex-ml-forecasting-and-classification/index.html?index=..%2F..index#0
Snowflake Trial Account (Free)
To get started, the first thing we need is a Snowflake trial account, which is free and can be opened in a few minutes at:https://signup.snowflake.com/
Fill in your first and last name, email address and reason for registration.
Click Continue.
We fill in the fields “Company”, “Position” and “Snowflake Edition”. The most popular is Enterprise, so let’s stick with that.
The next option is the cloud of your choice. Snowflake is multi-cloud, which is a big plus since it doesn’t rely on a single cloud provider. For the test, you can select any cloud provider; this won’t affect your experience and since it’s a managed infrastructure, you won’t have to worry about configuration details.
Depending on the cloud provider you select, you can select the region of your choice for your test account.
Here you can see the regions where Snowflake is available.
Then select “Get Started”.
You will then receive an email with the link to activate your test account.
Getting Started with Snowflake
To start, we’ll work in Snowsight, Snowflake’s web-based graphical interface.
Once logged in, we’ll click on Sheets on the left to start working with SQL.
In Worksheets, we create a new sheet by clicking on the + sign on the right side of the screen.
For this example, and for simplicity, we will ensure that the selected account is Accountadmin. We can validate this in the spreadsheet in the top right corner.
Setting up data in Snowflake
To get started, we’ll create the necessary objects: the warehouse (computing capacity), the database, and its schema.
We’ll use data stored in an S3 bucket and load it into Snowflake.
To do this, we’ll use the following code; you can cut and paste it into your spreadsheet.
- It's generally suggested to use accountadmin for quickstarts, but any role with sufficient privileges should work.
USE ROLE ACCOUNTADMIN;
- Create development database and schema for our work:
CREATE OR REPLACE DATABASE quickstart;
CREATE OR REPLACE SCHEMA ml_functions;
- Use appropriate resources:
USE DATABASE quickstart;
USE SCHEMA ml_functions;
- Create a warehouse to work with:
CREATE OR REPLACE WAREHOUSE quickstart_wh;
USE WAREHOUSE quickstart_wh;
- Create a csv file format that will be used for stage ingestion:
CREATE OR REPLACE FILE FORMAT quickstart.ml_functions.csv_ff
TYPE = 'csv'
SKIP_HEADER = 1,
COMPRESSION = AUTO;
- Create an external stage pointing to AWS S3 to load our data:
CREATE OR REPLACE STAGE s3load
COMMENT = 'Quickstart S3 Stage Connection'
URL = 's3://sfquickstarts/hol_snowflake_cortex_ml_for_sql/'
FILE_FORMAT = quickstart.ml_functions.csv_ff;
- Define our table schema
CREATE OR REPLACE TABLE quickstart.ml_functions.bank_marketing(
CUSTOMER_ID TEXT,
AGE NUMBER,
JOB TEXT,
MARITAL TEXT,
EDUCATION TEXT,
DEFAULT TEXT,
HOUSING TEXT,
LOAN TEXT,
CONTACT TEXT,
MONTH TEXT,
DAY_OF_WEEK TEXT,
DURATION NUMBER(4, 0),
CAMPAIGN NUMBER(2, 0),
PDAYS NUMBER(3, 0),
PREVIOUS NUMBER(1, 0),
POUTCOME TEXT,
EMPLOYEE_VARIATION_RATE NUMBER(2, 1),
CONSUMER_PRICE_INDEX NUMBER(5, 3),
CONSUMER_CONFIDENCE_INDEX NUMBER(3,1),
EURIBOR_3_MONTH_RATE NUMBER(4, 3),
NUMBER_EMPLOYEES NUMBER(5, 1),
CLIENT_SUBSCRIBED BOOLEAN,
TIMESTAMP TIMESTAMP_NTZ(9)
);
- Ingest data from S3 into our table:
COPY INTO quickstart.ml_functions.bank_marketing
FROM @s3load/customers.csv;
- See a sample of the ingested data:
SELECT * FROM quickstart.ml_functions.bank_marketing LIMIT 100;To execute the SQL statements, we select the blue “Execute” button in the upper right corner of the screen. Note that if we click on the corresponding tab, we can choose to execute all the statements sequentially.
Exploratory Data Analysis
We can do this directly from the Snowflake interface, in a simple way. It easily provides us with key metrics and even charts by data type to visualize data distributions.
Let’s see what data we have.
Copy and paste the following code:
- Query a sample of ingested data
SELECT *
FROM bank_marketing
LIMIT 100;We see the result:
We can see the data in the columns, and on the right side it automatically generates a chart for numbers or dates and a frequency count for categorical (text) or binary data.
If you want to review the data dictionary for the dataset, you can do so here:https://archive.ics.uci.edu/dataset/222/bank+marketing?_fsi=TNKw8Mx5&_fsi=TNKw8Mx5
Forecast
In this example, the goal is to predict how many customers will sign up for the term deposit product.
To do this, we will create a predictive model along with metrics that will help us understand the accuracy of these predictions.
This will help us project the company’s revenue as well as the staffing requirements to perform these tasks.
In this scenario, we want to predict the number of customers per day, divided between those over and under 40 years old. This distinction will help us better tailor our messaging and better target our different customer segments.
The model will be trained on the historical count of placements (customers making deposits) per day.
To do this, you need to:
- Create a label to identify customers over and under 40
- Create a table to train the model, including the timestamp, the number of subscriptions per day, and the age group they belong to
- Create a table to help the model make predictions
To do this, copy, paste, and run the following code:
- Break down the data we want to train on and create age groups.
- Create groups for 18 and under, 18–30, 30–40, 40–50, 50–60, and 60 and over.
CREATE OR REPLACE TABLE bank_marketing_age_bins AS SELECT
timestamp,
client_subscribed,
euribor_3_month_rate,
CASE
WHEN age <= 40 THEN '40 and under'
ELSE '40 and up'
END as age_bin
FROM bank_marketing;
- Now count the subscriptions by day and age group.
CREATE OR REPLACE VIEW daily_subscriptions AS SELECT
timestamp,
age_bin,
AVG(euribor_3_month_rate) as interest_rate,
SUM(CASE WHEN client_subscribed = TRUE THEN 1 ELSE 0 END) AS subscribed_count
FROM bank_marketing_age_bins
GROUP BY timestamp, age_bin;
- Let's analyze our data for the 40+ category.
select * from daily_subscriptions where age_bin = '40 and up';
- We will split the training data and keep a one-month set for predictions.
CREATE OR REPLACE TABLE forecast_training AS SELECT
*
FROM daily_subscriptions
WHERE timestamp <= (SELECT MAX(timestamp) - INTERVAL '30 days' FROM daily_subscriptions);
CREATE OR REPLACE TABLE forecast_future_values AS SELECT
timestamp,
age_bin,
interest_rate
FROM daily_subscriptions
WHERE timestamp NOT IN (SELECT timestamp FROM forecast_training);As you can see, in Snowflake everything is created as database objects, perhaps unlike some common practices where we use flat files for datasets. In Snowflake, the process is greatly simplified because the data remains in Snowflake; it does not need to leave the platform. We can take advantage of all its computational power, as well as its scalability and parallelism. All data remains governed within the platform and with all security mechanisms, without data movement or the need to use other tools.
Snowflake AI & ML Studio
Now that we have created all the necessary datasets, we will use the new AI & ML Studio to set up the prediction.
In Snowsight, go to AI & ML, click on it, and select Studio.
In AI & ML Studio, we select Prediction and click Create to open the wizard that will guide us step by step in creating our model.
We give the model a name, select the function we will work with and a warehouse to run it (computational capacity). When finished, we select “Let’s go”.
We select the database and schema that contain the information we will work with. A preview of the data in the table we will use is displayed on the right.
In our example, we are using the Quickstart database, the ML_Functions schema, and the Forecast_Training table. On the right side of the screen, we can see a preview of the data. When you are done, click Next.
Get Gabriel Jiménez’s stories in your inbox
Join Medium for free to get updates from this writer.
In the next step, we select the target variable, that is, what we want to forecast. Since this is a time series, we need a column with a timestamp.
First, we select subscribed_count and click Next.
We select timestamp and Next.
Important
I believe this step is essential because one of the biggest needs of businesses is not just to create forecasts, but to do so automatically for different categories. This applies, for example, to retail, where we want to forecast sales for each store independently, since demand varies from store to store or region. It also applies to each product or product line or, as in our example, to each category of customers we have: those over or under 40 years old.
This functionality, and the ease with which we set it up, is an important component of access and simplicity for machine learning models that can be leveraged by analysts or business engineers who do not need to be experts in data science or foreign languages. This gives the Snowflake platform great flexibility and openness to solve more business challenges.
Now, we will select the identifier for each data series that we will forecast. We select the age_bin column and we can see in the visualization on the right the two categories that we created for this column. Depending on our strategy and ability to create personalized marketing content, this can be even more granular, including categories every 5 years or by generations: Z, X, Boomers, etc.
Click Next.
Next, we can select additional exogenous variables (features) that may influence the results, such as local holidays or weather conditions. In our case, we will skip this step. Click “Ignore this option”.
As a final step, we capture the forecast horizon. Since we’re using daily data, we set the horizon to 14, which will give us a forecast for the next 14 days.
We select the forecast range, or how much the outcome is likely to vary from what we predicted, and finally, a name for the table where the forecasts will be stored.
Everything is stored in a database object, which has the added benefit of ensuring that we don’t lose them or leave them in some uncontrolled and easily compromised flat file.
Once we’ve done that, we click Done.
What’s the output of the wizard? A new spreadsheet with the entire process (and code) we just set up, saving time from doing it with the mouse by just clicking and selecting.
This code contains the steps to create the model, train it, generate predictions, and evaluate the results — the whole process!
The first thing we will do is prepare the training data, check that the timestamp has the correct data type, and to make it more efficient, include only the necessary columns.
Model training
Next, we train the model.
Here we can see how simple it is to use the Snowflake function in SQL. We call the forecast function, which is in the Snowflake.ML library. With input_data, we tell it where to get the data to train the model.
Since this is a multi-series model, we tell it which column we will use to create the time series.
Since this is a time series forecast, we need a column that specifies the periodicity of the data, in this case, timestamp.
Finally, the target column, which is what we want to forecast: subscribed_count.
- We train the forecast model.
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST forecast_subscriptions_model(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'forecast_training_v1'),
SERIES_COLNAME => 'age_bin',
TIMESTAMP_COLNAME => 'timestamp',
TARGET_COLNAME => 'subscribed_count'
);Let’s run the code.
After the model is trained, we can generate predictions and save the results in a table for future reference.
As seen in the code, we have named the newly trained model, along with the horizon parameters as 14, and the prediction interval, which we have selected as 95%.
- We have generated predictions and stored the results in a table.
BEGIN
- This is the step that creates your predictions.
CALL Pronostico_depositos!FORECAST(
FORECASTING_PERIODS => 14,
- Here we set your prediction interval.
CONFIG_OBJECT => {'prediction_interval': 0.95}
);
These steps store the results in a table.
- These steps store the predictions in a table.
LET x := SQLID;
CREATE TABLE My_forecasts_2025_04_30 AS SELECT * FROM TABLE(RESULT_SCAN(:x));
END;Here, we add a where = ’40 and above’ to visualize results for just one segment of customers. Remember, we created two time series, one for those under 40 and one for those over 40. We also included a date range for visualization.
SELECT TIMESTAMP, subscribed_count AS actual, NULL AS forecast, NULL AS lower_bound, NULL AS upper_bound
FROM forecast_training
WHERE age_bin = '40 and up' and TIMESTAMP > '2010–02–01'
UNION ALL
SELECT ts as TIMESTAMP, NULL AS actual, forecast, lower_bound, upper_bound
FROM forecasts
WHERE series = '40 and up';If we run the code above, we can view the results graphically in Snowsight.
In the results window at the bottom of the screen, we can choose between viewing results or graphs.
Select Graph.
We select actual and forecast to see the current values, followed by the model forecast.
We add the forecast column and in the aggregation we select None.
With this setup, we can graph the results to see the history and forecast generated by the model.
In the chart, we can see some pronounced peaks and valleys in the data. Knowing the business and the data would make it easier to interpret these peaks and valleys, helping us better understand customer behavior.
All within Snowflake, without any additional tools.
Model evaluation
We now have a trained model that is ready to generate predictions. Before using it to make business decisions based on its results, it is a good practice to evaluate its performance.
To do this, we will review the model’s performance metrics, by running the following command:
- Inspect your model's accuracy metrics.
CALL forecast_subscriptions_model!SHOW_EVALUATION_METRICS();The results include metrics for both categories we use for prediction: 40 years or older and 40 years or younger.
There are several standard metrics to evaluate the model output, such as MAE, MAPE, MSE, SMAPE, and other specific metrics.
For simplicity, we will focus on MAE, or Mean Absolute Error, which is explained in general terms: “In the context of data science and regression models, MAE stands for Mean Absolute Error. It is a metric that measures the accuracy of a model by calculating the average difference between the predicted and actual values. In other words, it indicates how much, on average, the model’s predictions deviate from the actual values.”
Therefore, the lower the MAE, the better our model is.
Automate training and inference
Once we have the model ready and are happy with its results, we can schedule it to run so that it can be trained on new data and generate predictions on a regular basis. This is very useful for companies that need weekly results to support data-driven decision making.
To do this, we can use a cron job to schedule weekly data updates with a task to train the model. The next step is to schedule the predictions on a weekly basis so that as soon as we have the updated data, we can run the prediction based on the latest data. Once this is done, we have an automated process for updating the data and generating predictions based on it.
This helps us move from being reactive, simply reviewing what happened in the past, to being proactive, based on past data, creating a model that helps us predict what will happen and make informed decisions based on the data.
Conclusion
With that, we reviewed the complete process of using the Forecast function, which calls a pre-trained SQL model, which is executed along with the data, avoiding data movement and taking advantage of Snowflake’s performance to execute it.
Summary:
- Create a trial account in Snowflake
- Create the necessary database objects
- Load data from cloud storage
- Exploratory data analysis in Snowsight (Snowflake web interface)
- Use the AI and ML wizard to build the predictive model
- Train the model (with multiple time series, a key differentiator)
- Evaluate model performance
- Automate the data loading and prediction process
In this way, we can see that with Snowflake, you don’t need to be data scientists or make a significant investment for this, we can cover all the needs within the life cycle of our data, from loading to generating business results based on predictive models, all within the same product and, most importantly, without moving data.
