MAQAP_Bronze.sql

-- MAQAP Bronze layer
-- Lean by design: two tables, both read by Silver/Gold below. Nothing is
-- ingested that doesn't feed something downstream.

CREATE SCHEMA IF NOT EXISTS quant_db.bronze;

-- 1. Equities: CSV load via internal stage (same pattern as the original
--    single-ticker version, now loading all 5 tickers from one file)
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()
);


-- Upload equities_prices.csv to the stage (Snowsight "+ Files" button, or
-- PUT file:// from SnowSQL), then run:
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;

-- 2. FOMC calendar: 16 known-in-advance dates. Small enough that a stage and
--    file format would be overkill - a plain INSERT is the leanest way to
--    land reference data this size, and it's the one table whose future
--    values are genuinely knowable, which is what makes it usable as a
--    forecast feature later (see 03_gold.sql).
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');