MAQAP_Gold.sql

-- MAQAP Gold layer

CREATE SCHEMA IF NOT EXISTS quant_db.gold;

-- 1. Returns + 30-day moving average. Kept for dashboard context (not fed
--    into the model - see the note in v_ml_training_data below on why).
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;

-- 2. ML training view: only columns the model should see.
--    is_fomc_week is the one engineered feature. daily_log_return and
--    sma_30_day are deliberately left out - they're derived from
--    close_price itself, so their future values aren't knowable, and
--    SNOWFLAKE.ML.FORECAST requires future values for every feature it's
--    trained on. FOMC dates are published months ahead, so this one is
--    legitimate to carry into the forecast call.
-- Note: this started as IFF(EXISTS(correlated subquery), 1, 0). Snowflake's
-- SNOWFLAKE.ML.FORECAST training procedure statically rewrites the input
-- query and can't do that through a correlated subquery sitting inside a
-- VIEW - it fails with "Unsupported subquery type cannot be evaluated
-- inside VIEW object". A LEFT JOIN does the same job without the subquery.
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;

-- 3. Future feature scaffold: next 30 weekdays per ticker, with the same
--    is_fomc_week flag computed the same way. This is what SNOWFLAKE.ML.FORECAST
--    reads to know which future dates to predict and what the feature
--    looks like on each one - it replaces a plain FORECASTING_PERIODS => 30.
CREATE OR REPLACE VIEW quant_db.gold.v_future_fomc_features AS
WITH future_dates AS (
    SELECT trade_date FROM (
        SELECT DATEADD('day', SEQ4(), CURRENT_DATE()) AS trade_date
        FROM TABLE(GENERATOR(ROWCOUNT => 45))
    )
    WHERE DAYOFWEEK(trade_date) NOT IN (0, 6)   -- 0 = Sunday, 6 = Saturday
    ORDER BY trade_date
    LIMIT 30
),
tickers AS (
    SELECT DISTINCT asset_symbol FROM quant_db.gold.asset_metrics
)
SELECT
    t.asset_symbol,
    d.trade_date,
    COALESCE(MAX(IFF(ABS(DATEDIFF('day', d.trade_date, f.decision_date)) <= 2, 1, 0)), 0) AS is_fomc_week
FROM future_dates d
CROSS JOIN tickers t
LEFT JOIN quant_db.bronze.fomc_meeting_dates f
  ON ABS(DATEDIFF('day', d.trade_date, f.decision_date)) <= 2
GROUP BY t.asset_symbol, d.trade_date;


select * from quant_db.gold.asset_metrics