snowflake_schema.sql

CREATE SCHEMA IF NOT EXISTS RISKQUEUE_ANALYTICS;

CREATE TABLE IF NOT EXISTS RISKQUEUE_ANALYTICS.SCORING_HISTORY (
    EVENT_ID VARCHAR(128) NOT NULL,
    TRANSACTION_ID VARCHAR(120) NOT NULL,
    INGESTED_AT TIMESTAMP_TZ NOT NULL,
    STEP INTEGER NOT NULL,
    TRANSACTION_TYPE VARCHAR(20) NOT NULL,
    AMOUNT NUMBER(18, 2) NOT NULL,
    FRAUD_PROBABILITY FLOAT NOT NULL,
    EXPECTED_LOSS NUMBER(18, 2) NOT NULL,
    RISK_BAND VARCHAR(20) NOT NULL,
    DECISION VARCHAR(20) NOT NULL,
    MODEL_VERSION VARCHAR(80) NOT NULL,
    LOADED_AT TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (EVENT_ID, TRANSACTION_ID)
);

CREATE TABLE IF NOT EXISTS RISKQUEUE_ANALYTICS.DAILY_MODEL_MONITORING (
    MONITOR_DATE DATE NOT NULL,
    MODEL_VERSION VARCHAR(80) NOT NULL,
    TRANSACTIONS NUMBER NOT NULL,
    AVERAGE_SCORE FLOAT,
    REVIEW_RATE FLOAT,
    AVERAGE_EXPECTED_LOSS FLOAT,
    REFRESHED_AT TIMESTAMP_TZ NOT NULL DEFAULT CURRENT_TIMESTAMP(),
    PRIMARY KEY (MONITOR_DATE, MODEL_VERSION)
);

CREATE OR REPLACE VIEW RISKQUEUE_ANALYTICS.DAILY_SCORING_SUMMARY AS
SELECT
    TO_DATE(INGESTED_AT) AS SCORE_DATE,
    MODEL_VERSION,
    COUNT(*) AS TRANSACTIONS,
    AVG(FRAUD_PROBABILITY) AS AVERAGE_SCORE,
    AVG(IFF(DECISION = 'review', 1, 0)) AS REVIEW_RATE,
    AVG(EXPECTED_LOSS) AS AVERAGE_EXPECTED_LOSS
FROM RISKQUEUE_ANALYTICS.SCORING_HISTORY
GROUP BY 1, 2;