Skip to main content

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:

ColumnTypeDescription
TimestampTIMESTAMP_NTZ or DATEThe time dimension
ValueNUMBER or FLOATThe metric to forecast
Series identifier (optional)VARCHARGroups 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:

ColumnDescription
SERIESSeries identifier (multi-series only)
TSForecasted timestamp
FORECASTPredicted value
LOWER_BOUNDLower confidence interval
UPPER_BOUNDUpper 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:

  1. Provide sufficient history — At least 2 full seasonal cycles. For weekly patterns, provide 2+ weeks. For annual patterns, provide 2+ years.
  2. Consistent granularity — Do not mix daily and weekly rows. Aggregate to a single frequency before training.
  3. Avoid truncated periods — If training data ends mid-month, the model may learn an artificial drop pattern.

Best Practices​

  1. Evaluate before deploying — Hold out the most recent period and compare forecasted vs actual values using MAPE or RMSE.
  2. Retrain regularly — As new data accumulates, retrain to capture recent trends.
  3. Size your warehouse — Multi-series models with thousands of series benefit from larger warehouses (LARGE or above).
  4. Monitor prediction intervals — Wide confidence intervals indicate high uncertainty. Investigate the underlying data.
  5. Combine with anomaly detection — Flag actuals that fall outside forecast confidence intervals as potential anomalies.