Snowflake Cortex: Time Series Forecasting Introduction


Snowflake Cortex: Time Series Forecasting Introduction

In today’s data-driven landscape, artificial intelligence (AI) has permeated every corner of industry and technology. From personalized recommendations on streaming platforms to optimizing supply chains in manufacturing, AI algorithms are revolutionizing how we approach problems and extract insights from data. As organizations race to leverage this transformative power, platforms like Snowflake, known for their cloud data warehousing solutions, are at the forefront of integrating AI capabilities directly into their offerings.  

It is in this context that Snowflake Cortex emerges. Cortex provides capabilities that could be grouped into two main families: first, services that leverage large language models (LLM), and second, a fully managed machine learning model service that provides a wide range of ML-powered functions. In this article, we’ll explore the later and, specifically, one of its most popular ML-functions: forecasting.  

But fear not: the best part of Cortex is that all these goodies can be used with the simplicity that characterizes Snowflake and using a few simple SQL commands (or Python, in which case you can use Snowpark ML). Note that, at the time of writing this article, some of the functionalities of Cortex are still in private preview, so please consider this if you attempt to harness its capabilities in a production environment. 

Forecasting the Sales of Electrical Vehicles in Australia

For this article, I will be using a fictional dataset that represents sales of Electric Vehicles (EV) in Sydney. I have the number of units sold across several EV models for the last few years:

This is my “sydney_ev_daily_sales” table in Snowflake that includes sales for all vehicles. Let’s first see what sales look like for a single model:  

SELECT * FROM sydney_ev_daily_sales WHERE EV_NAME = 'Nissan Leaf';

Using the awesome built-in visualisation feature of Snowsight, we can plot this data and observe the oscillatory pattern of our sales. Notice that the most recent record is for late March 2024.

Our objective: to forecast what the sales for Q2 2024 will be based on this data for one of the vehicles. Let’s get started! 

First, we want to slice our data to use a portion of it to train our forecasting model. So we’ll create a view with last year data for a specific model:

-- Create view containing the latest years worth of sales data for a given model:  

CREATE OR REPLACE VIEW last_year_nissan_sales_view AS ( 
        TO_TIMESTAMP_NTZ(DATE_SALES) AS timestamp, 
        DATE_SALES > (SELECT MAX(DATE_SALES) - INTERVAL '1 year' FROM sydney_ev_daily_sales) 
        AND EV_NAME = 'Nissan Leaf' 
    GROUP BY all 

Important: You may have noticed that I casted my date field as a timestamp. That’s a requirement of the functions we’ll use later. Let’s now look at what do the sales for that given model look like: 

And here is when the magic begins. Our goal is to estimate the sales for this vehicle. To do so, we’ll simply use the keyword FORECAST, a built-in function capable of running time series predictions. The syntax is pretty simple, but, in essence, it takes in three parameters: a) what data are we using to train the model (it can be a view or a table), b) what is the timestamp column and c) what column are we trying to predict the value of.  You can see below how I populated it:  

-- Build Forecasting model; this could take a few seconds 

    INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'last_year_nissan_sales_view'), 

While the syntax is simple under the hood, this is a computationally expensive query: using a standard warehouse, you may need to wait several seconds for this to command to execute. For such scenarios, we could well consider using a Snowpark-optimized warehouse, but the standard warehouse will do for the purposes of this article. Also, before using this technology, please bear in mind some of the considerations outlined by Snowflake, including its metadata usage policy. 

Once this command is executed, our model has been trained and is ready to be used. Let’s first test the waters: using the CALL command, I will pass 10 as the number of periods I want the model to forecast (that is, number of timestamps). This, in turn, will expose some additional bits of the model: its lower and upper bounds. In other words, the range of values that your model is predicting your sales to fall within:

Let’s run it again to run a prediction for a longer period. This time, however, we will invoke the model but also scan the last query so we can store the results in a new table that will contain our predictions for our EV. Make sure you run these two commands together:

-- Create predictions, and save results to a table. Run these two commands together to store results 

CALL nissan_sales_forecast!FORECAST(FORECASTING_PERIODS => 30); 

CREATE OR REPLACE TABLE nissan_predictions AS ( 

And that’s pretty much it. We can now visualize our actual and forecasted sales by running a simple UNION ALL and, again, using the awesome built-in features from Snowflake: 

-- Visualize the results, overlaid on top of one another 

    NULL AS forecast 

    TS AS timestamp, 
    timestamp asc;

And sure enough: we see that our models have projected sales for Q2 2024. Note: you can adjust the visualization on the right-hand side to add the FORECAST column if it’s not plotted by default: 

Here, we have only scratched the surface, but as you can see, with a few basic commands you can train, deploy and evaluate ML models for time-series forecasting tasks. Whether predicting sales trends, anticipating demand fluctuations or optimizing resource allocation, Cortex can equip your team with the tools that you need to extract valuable insights from time-series data. 

Want More AI Capabilities in Snowflake

  1. Snowflake Cortex: Time Series Forecasting Introduction

More About the Author

Roger Garcia

Solutions Engineer
Snowflake Cortex: Time Series Forecasting Introduction In today’s data-driven landscape, artificial intelligence (AI) has permeated every corner of industry and technology. From ...
Exploring Tableau Pulse To Replace Traditional Dashboards In the business intelligence space, the main medium used up until very recently to interact with data was that of a compilation of data ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!