Time Series Forecasting
What Is Snowflake ML Forecasting?
SNOWFLAKE.ML.FORECAST predicts future values from historical time series data. It automatically handles trend detection, seasonality, and missing timestamps. The function supports single-series and multi-series forecasting in a single model.
Data Preparation
The training table must contain at minimum:
| Column | Type | Description |
|---|---|---|
| Timestamp | TIMESTAMP_NTZ or DATE | The time dimension |
| Value | NUMBER or FLOAT | The metric to forecast |
| Series identifier (optional) | VARCHAR | Groups rows into independent series |
Additional columns can be included as exogenous variables (features that help the forecast but are not themselves forecasted).
-- Example: daily sales data
CREATE OR REPLACE TABLE daily_sales (
sale_date DATE,
store_id VARCHAR,
revenue FLOAT,
is_holiday BOOLEAN
);
Requirements:
- Timestamps must be regularly spaced (daily, hourly, etc.). Gaps are filled automatically.
- At least 2 full seasonal cycles are recommended (e.g., 2 years of monthly data to capture annual seasonality).
- NULL values in the target column are interpolated during training.
Training a Single-Series Forecast
-- Total company revenue forecast
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST revenue_forecast(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'COMPANY_DAILY_REVENUE'),
TIMESTAMP_COLNAME => 'REVENUE_DATE',
TARGET_COLNAME => 'DAILY_REVENUE'
);
Training a Multi-Series Forecast
When data contains multiple independent series (e.g., revenue per store), specify the series column:
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST store_forecast(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'DAILY_SALES'),
SERIES_COLNAME => 'STORE_ID',
TIMESTAMP_COLNAME => 'SALE_DATE',
TARGET_COLNAME => 'REVENUE'
);
This trains one model per series while sharing learned structure across all series.
Generating Forecasts
-- Forecast the next 30 days
CALL store_forecast!FORECAST(FORECASTING_PERIODS => 30);
The output contains:
| Column | Description |
|---|---|
SERIES | Series identifier (multi-series only) |
TS | Forecasted timestamp |
FORECAST | Predicted value |
LOWER_BOUND | Lower confidence interval |
UPPER_BOUND | Upper confidence interval |
-- Store forecast results
CREATE OR REPLACE TABLE revenue_predictions AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Using Exogenous Variables
Exogenous variables provide additional context for the forecast. You must supply future values for these variables at prediction time.
-- Train with holiday flag
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST sales_forecast(
INPUT_DATA => SYSTEM$REFERENCE('TABLE', 'DAILY_SALES'),
TIMESTAMP_COLNAME => 'SALE_DATE',
TARGET_COLNAME => 'REVENUE',
SERIES_COLNAME => 'STORE_ID'
);
-- Predict with known future holidays
CALL sales_forecast!FORECAST(
FORECASTING_PERIODS => 30,
EXOGENOUS_DATA => SYSTEM$REFERENCE('TABLE', 'FUTURE_CALENDAR')
);
The FUTURE_CALENDAR table must contain the same timestamp, series, and exogenous columns — but not the target column.
Practical Example: Demand Prediction
-- Prepare historical demand data
CREATE OR REPLACE VIEW demand_history AS
SELECT
order_date,
product_category,
SUM(quantity_ordered) AS daily_demand
FROM orders
WHERE order_date >= DATEADD('year', -2, CURRENT_DATE())
GROUP BY order_date, product_category;
-- Train model
CREATE OR REPLACE SNOWFLAKE.ML.FORECAST demand_model(
INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'DEMAND_HISTORY'),
TIMESTAMP_COLNAME => 'ORDER_DATE',
TARGET_COLNAME => 'DAILY_DEMAND',
SERIES_COLNAME => 'PRODUCT_CATEGORY'
);
-- Forecast next 90 days
CALL demand_model!FORECAST(FORECASTING_PERIODS => 90);
-- Identify categories with forecasted demand spikes
SELECT
series AS product_category,
ts AS forecast_date,
forecast AS predicted_demand
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE forecast > 500
ORDER BY forecast DESC;
Handling Seasonality
Snowflake ML automatically detects seasonality patterns in the data. For best results:
- Provide sufficient history — At least 2 full seasonal cycles. For weekly patterns, provide 2+ weeks. For annual patterns, provide 2+ years.
- Consistent granularity — Do not mix daily and weekly rows. Aggregate to a single frequency before training.
- Avoid truncated periods — If training data ends mid-month, the model may learn an artificial drop pattern.
Best Practices
- Evaluate before deploying — Hold out the most recent period and compare forecasted vs actual values using MAPE or RMSE.
- Retrain regularly — As new data accumulates, retrain to capture recent trends.
- Size your warehouse — Multi-series models with thousands of series benefit from larger warehouses (LARGE or above).
- Monitor prediction intervals — Wide confidence intervals indicate high uncertainty. Investigate the underlying data.
- Combine with anomaly detection — Flag actuals that fall outside forecast confidence intervals as potential anomalies.