Celes Renata
25 lines
1.0 KiB
SQL
25 lines
1.0 KiB
SQL
-- Analytical fact table: prediction_vs_outcome
|
|
-- Prediction accuracy tracking: predicted signals vs realized market moves.
|
|
-- Partitioned by dt and model_version on MinIO.
|
|
-- Path: s3://stonks-lakehouse/warehouse/prediction_vs_outcome/dt={yyyy-mm-dd}/model_version={ver}/part-*.parquet
|
|
-- Requirements: 9.4, 9.5, 10.1, 10.3
|
|
-- Design ref: Section 7 (lake.prediction_vs_outcome)
|
|
|
|
CREATE TABLE IF NOT EXISTS lakehouse.stonks.prediction_vs_outcome (
|
|
recommendation_id VARCHAR,
|
|
ticker VARCHAR,
|
|
predicted_action VARCHAR,
|
|
predicted_confidence DOUBLE,
|
|
actual_move_pct DOUBLE,
|
|
outcome VARCHAR,
|
|
horizon_days INTEGER,
|
|
predicted_at TIMESTAMP(6) WITH TIME ZONE,
|
|
evaluated_at TIMESTAMP(6) WITH TIME ZONE,
|
|
model_version VARCHAR,
|
|
dt DATE
|
|
) WITH (
|
|
format = 'PARQUET',
|
|
partitioned_by = ARRAY['dt', 'model_version'],
|
|
external_location = 's3a://stonks-lakehouse/warehouse/prediction_vs_outcome/'
|
|
);
|