MAQAP_ML.sql

-- MAQAP ML layer
-- One model, 5 series (one per ticker) trained together via SERIES_COLNAME.
-- is_fomc_week is auto-detected as an exogenous feature because it's the
-- only column in v_ml_training_data besides series/timestamp/target.

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'
);

-- Because this model was trained with a feature, the forecast call takes
-- the future-feature view instead of a plain FORECASTING_PERIODS count -
-- the 30 rows per ticker in that view define both the horizon and the
-- feature values Cortex uses to adjust each prediction.
-- CREATE TABLE ... AS SELECT * FROM TABLE(model!FORECAST(...)) is used
-- instead of CALL + RESULT_SCAN(-1): it's a direct reference to this
-- query's own output, so nothing else running between the CALL and the
-- RESULT_SCAN can accidentally get captured instead.
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'
    )
);

-- Sanity check
SELECT * FROM quant_db.gold.predicted_prices ORDER BY series, ts LIMIT 20;

-- Optional: see which feature actually mattered
CALL quant_db.gold.stock_price_forecast_model!EXPLAIN_FEATURE_IMPORTANCE();


ALTER DYNAMIC TABLE quant_db.silver.equities_daily REFRESH;
ALTER DYNAMIC TABLE quant_db.gold.asset_metrics REFRESH;

select * from quant_db.gold.predicted_prices