Multi-Stock Price Forecast Platform (Snowflake, Cortex ML, Streamlit)

📌 Project Overview

An end-to-end Medallion pipeline (Bronze → Silver → Gold) built entirely inside Snowflake. It ingests daily OHLCV data for five large-cap stocks, engineers one deliberately-chosen feature, trains a multi-series Cortex ML forecasting model, and serves 30-day forecasts through a Streamlit in Snowflake dashboard.

The project is intentionally lean: every table in the Bronze layer feeds something downstream. There's no ingested-but-unused data, no external orchestration tool, no separate ML infrastructure.

Not investment advice. This is a learning project. 30-day forecasts are a statistical model's best guess with a confidence band, not a recommendation.

🏗️ Architecture & Core Technologies

• Ingestion: Python (yfinance) → Snowflake Internal Stage → COPY INTO
• Transformation: Dynamic Tables (TARGET_LAG), SQL window functions
• Machine Learning: SNOWFLAKE.ML.FORECAST with one exogenous feature
• Serving: Streamlit in Snowflake, Altair, Pandas

Sources → Bronze → Silver → Gold → Cortex ML → Streamlit

---

Step 1: Bronze — raw ingestion

USE ROLE ACCOUNTADMIN;
CREATE WAREHOUSE IF NOT EXISTS quant_wh WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60;
CREATE DATABASE IF NOT EXISTS quant_db;
CREATE SCHEMA IF NOT EXISTS quant_db.bronze;

Equities — fetch_stock_data.py pulls AAPL, MSFT, GOOGL, AMZN, NVDA via yfinance into one CSV, loaded through a standard internal stage + COPY INTO:

CREATE OR REPLACE FILE FORMAT quant_db.bronze.csv_format
  TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1;
CREATE OR REPLACE STAGE quant_db.bronze.equities_internal_stage
  FILE_FORMAT = quant_db.bronze.csv_format;

CREATE OR REPLACE TABLE quant_db.bronze.raw_equities (
  ticker STRING, trade_date DATE, open_price NUMBER(10,4), high_price NUMBER(10,4),
  low_price NUMBER(10,4), close_price NUMBER(10,4), volume NUMBER,
  ingested_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);

COPY INTO quant_db.bronze.raw_equities (ticker, trade_date, open_price, high_price, low_price, close_price, volume)
  FROM @quant_db.bronze.equities_internal_stage PURGE = TRUE;

FOMC calendar — the one engineered feature source. Only 16 rows, and the future meeting dates are published months ahead, which is exactly what makes it usable later. A stage would be overkill for data this size, so it's a plain INSERT:

CREATE OR REPLACE TABLE quant_db.bronze.fomc_meeting_dates (decision_date DATE);

INSERT INTO quant_db.bronze.fomc_meeting_dates (decision_date) VALUES
  ('2025-01-29'), ('2025-03-19'), ('2025-05-07'), ('2025-06-18'),
  ('2025-07-30'), ('2025-09-17'), ('2025-10-29'), ('2025-12-10'),
  ('2026-01-28'), ('2026-03-18'), ('2026-04-29'), ('2026-06-17'),
  ('2026-07-29'), ('2026-09-16'), ('2026-10-28'), ('2026-12-09');

An earlier version of this project also ingested a mock crypto feed (JSON from a public tutorial bucket) and a macro indicator from the Snowflake Marketplace. Neither was ever wired into Gold or the model, so both were dropped — a Bronze table that nothing reads is just cost and clutter.

---

Step 2: Silver — cleansing

One Dynamic Table. TARGET_LAG tells Snowflake how fresh the result needs to be; it works out the refresh schedule and incremental compute itself, replacing what would otherwise be a hand-built Airflow DAG.

CREATE SCHEMA IF NOT EXISTS quant_db.silver;

CREATE OR REPLACE DYNAMIC TABLE quant_db.silver.equities_daily
  TARGET_LAG = '1 day' WAREHOUSE = quant_wh AS
SELECT ticker AS asset_symbol, trade_date, close_price
FROM quant_db.bronze.raw_equities
WHERE close_price IS NOT NULL;

---

Step 3: Gold — metrics and the ML training view

asset_metrics computes return and moving-average context for the dashboard, but neither column is fed to the model: both are derived from close_price itself, so their future values aren't knowable — and SNOWFLAKE.ML.FORECAST requires known future values for any feature it trains on.

CREATE SCHEMA IF NOT EXISTS quant_db.gold;

CREATE OR REPLACE DYNAMIC TABLE quant_db.gold.asset_metrics
  TARGET_LAG = '1 day' WAREHOUSE = quant_wh AS
SELECT
    asset_symbol, trade_date, close_price,
    LN(close_price / NULLIF(LAG(close_price) OVER (PARTITION BY asset_symbol ORDER BY trade_date), 0)) AS daily_log_return,
    AVG(close_price) OVER (PARTITION BY asset_symbol ORDER BY trade_date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS sma_30_day
FROM quant_db.silver.equities_daily;

is_fomc_week is the one feature that does go into training — a flag for being within two days of a scheduled Fed decision, a well-documented volatility driver:

CREATE OR REPLACE VIEW quant_db.gold.v_ml_training_data AS
SELECT
    m.asset_symbol, m.trade_date, m.close_price,
    COALESCE(MAX(IFF(ABS(DATEDIFF('day', m.trade_date, f.decision_date)) <= 2, 1, 0)), 0) AS is_fomc_week
FROM quant_db.gold.asset_metrics m
LEFT JOIN quant_db.bronze.fomc_meeting_dates f
  ON ABS(DATEDIFF('day', m.trade_date, f.decision_date)) <= 2
WHERE m.trade_date IS NOT NULL AND m.close_price IS NOT NULL
GROUP BY m.asset_symbol, m.trade_date, m.close_price;

A matching view, v_future_fomc_features, generates the next 30 weekdays per ticker with the same flag computed the same way — this is what tells the model both the forecast horizon and the feature values to use on each future date, replacing a plain FORECASTING_PERIODS => 30.

---

Step 4: Cortex ML — train and forecast

CREATE OR REPLACE SNOWFLAKE.ML.FORECAST quant_db.gold.stock_price_forecast_model(
    INPUT_DATA => TABLE(quant_db.gold.v_ml_training_data),
    SERIES_COLNAME => 'asset_symbol',
    TIMESTAMP_COLNAME => 'trade_date',
    TARGET_COLNAME => 'close_price'
);

CREATE OR REPLACE TABLE quant_db.gold.predicted_prices AS
SELECT * FROM TABLE(
    quant_db.gold.stock_price_forecast_model!FORECAST(
        INPUT_DATA => TABLE(quant_db.gold.v_future_fomc_features),
        SERIES_COLNAME => 'asset_symbol',
        TIMESTAMP_COLNAME => 'trade_date'
    )
);

One model trains on all five tickers at once via SERIES_COLNAME. CREATE TABLE AS SELECT * FROM TABLE(model!FORECAST(...)) is used instead of CALL ... ; SELECT * FROM TABLE(RESULT_SCAN(-1)) — the latter grabs the result of whatever query last ran, which is fragile if anything else executes in between.

---

Step 5: Streamlit — serving

Two views, toggled from the sidebar:

• Single stock — historical + forecast line with a confidence band, dashed FOMC markers
• Compare all stocks — all five tickers on one chart, indexed to 100 at the start of the window (raw dollar prices aren't comparable across tickers at very different price levels); forecast segments render as a dotted line in the same color as their stock, and a summary table shows each ticker's latest close, forecast, and projected % change

SELECT asset_symbol, trade_date, close_price, 'Historical' AS data_type, NULL AS lower_bound, NULL AS upper_bound
FROM quant_db.gold.asset_metrics WHERE trade_date >= DATEADD(day, -365, CURRENT_DATE());

SELECT series::STRING AS asset_symbol, ts::DATE AS trade_date, forecast AS close_price,
       'Forecast' AS data_type, lower_bound, upper_bound
FROM quant_db.gold.predicted_prices;

Full app: streamlit_app.py.

---

🐞 Gotchas encountered building this

• Correlated subqueries break SNOWFLAKE.ML.FORECAST's training procedure when they sit inside a view. IFF(EXISTS(...), 1, 0) failed with Unsupported subquery type cannot be evaluated inside VIEW object — the training procedure statically rewrites the input query and can't do that through a correlated subquery. A LEFT JOIN + GROUP BY does the same job without one.
• The SERIES column in FORECAST's output is VARIANT, not text. Pulling it in as series AS asset_symbol returned the literal string "AAPL" — quote marks included — while historical rows said AAPL. Two different-looking tickers, duplicated legend, forecast rows that never joined to their own historical rows. Fix: series::STRING AS asset_symbol.
• Snowpark's to_pandas() returns DATE columns as Python datetime.date, not pandas Timestamp. Comparing against pd.Timestamp.today() throws a TypeError. Cast explicitly with pd.to_datetime(...) right after loading.
• Exogenous features need known future values. Volume, moving averages, and other technical indicators can't be used as model inputs for forecasting, because you don't know their value 30 days out. Only genuinely calendar-known things — like the FOMC schedule — qualify.

Key architectural decisions

• Declarative orchestration — Dynamic Tables with TARGET_LAG instead of hand-built pipeline scheduling
• Zero data movement — ingestion, transformation, ML training, and the app all run inside Snowflake's compute/storage boundary
• One feature, chosen deliberately — rather than throwing every available column at the model, is_fomc_week was the only signal added, because it's the only one that satisfies the forecast function's future-value requirement