BankOrg Docs

In progress

BankOrg Data Platform / Data Engineering / RAW Ingestion

Monitor Task

The hourly monitor task: retries failed RAW loads, alerts on permanent errors and exhausted retries, and logs each run.

MONITOR_LOAD_JOBS_TASK retries the loads that failed and raises an alert for the ones that need a person. It calls the procedure OPS.MONITOR_LOAD_JOBS and is created in PRD_RAW.OPS, next to the four load tasks, on the same warehouse LOAD_WH.

Schedule

The task starts at 06:00 Vietnam time (Asia/Ho_Chi_Minh), after the 05:00 load tasks, and repeats every hour until 23:00. A task never starts a new run while its previous run is still going.

Snowflake
USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

CREATE OR REPLACE TASK MONITOR_LOAD_JOBS_TASK
    WAREHOUSE = LOAD_WH
    SCHEDULE = 'USING CRON 0 6-23 * * * Asia/Ho_Chi_Minh'
    COMMENT = 'Hourly 06:00-23:00: retry failed RAW loads and alert on permanent errors / exhausted retries'
AS
    CALL OPS.MONITOR_LOAD_JOBS();

The task calls the procedure with its defaults: at most 10 retries per run, stop after 3 failures in a row, no email.

Rules per job

The retry rules are columns of OPS.LOAD_JOB_CONFIG, one value per job: MAX_RETRIES (default 5), RETRY_BACKOFF_MINUTES (default 30), RETRY_WINDOW_DAYS (default 7), STALE_AFTER_MINUTES (default 60) and ALERT_ENABLED (default TRUE). A disabled job is ignored.

Parameters

ParameterTypeMeaning
P_MAX_RETRIES_PER_RUNNUMBER, default 10At most this many retries in one run. They run one after another.
P_STOP_AFTER_FAILURESNUMBER, default 3The run stops after this many failed retries in a row. Counted within one run, over job and date pairs of any job; a success resets it. Pairs not tried stay in state RETRY for the next run.
P_EMAIL_INTEGRATIONVARCHAR, default NULLNotification integration used to send the alert digest. NULL: no email.
P_EMAIL_TOVARCHAR, default NULLVerified recipient of the digest. NULL: no email.

Steps

StepWhat happens
1. Register the runInsert a RUNNING row in OPS.MONITOR_LOG. Its RUN_ID is passed to every retry.
2. Close abandoned runsLOAD_LOG rows still PENDING or RUNNING after STALE_AFTER_MINUTES are checked with OPS.IS_SESSION_ALIVE on their SESSION_ID. A row whose session has no running query becomes ERROR, retryable, with the message "abandoned". A row whose session still runs a query is left open and counted in the run message.
3. Work out the stateFor every enabled job and every snapshot date that has log rows in the last RETRY_WINDOW_DAYS days, a state is computed (table below) and kept in the temporary table OPS.MONITOR_STATE_TMP.
4. AlertDates in state PERMANENT or GAVE_UP of jobs with ALERT_ENABLED get ALERTED_AT set, so they are alerted once. With an integration and a recipient, one digest email lists them.
5. RetryDates in state RETRY are loaded with OPS.LOAD_DATASET(job, date, FALSE, run id), oldest date first. A failed retry does not stop the others.
6. SummaryThe MONITOR_LOG row becomes OK with the counts and a message. The monitor does not fail when a retry fails; it fails, and the task run with it, only when the monitor itself errors.

States of a date

A date that has no log row at all is not looked at.

StateConditionAction
DONEAn OK row exists for the dateNothing
RUNNINGA PENDING or RUNNING row exists: younger than STALE_AFTER_MINUTES, or older with its session still running a queryNothing
PERMANENTThe last attempt failed with IS_RETRYABLE = FALSEAlert, no retry
GAVE_UPFailed attempts reached MAX_RETRIESAlert, no retry
WAITThe last attempt ended less than RETRY_BACKOFF_MINUTES agoNothing, next run
RETRYNone of the above, e.g. missing markerCall LOAD_DATASET

Logs

Each run is one row in OPS.MONITOR_LOG: dates checked, retry candidates, retried, retried OK and failed, alerts, email status, whether it stopped early, and the message. Each retry is a row in OPS.LOAD_LOG whose MONITOR_RUN_ID is the run that started it; loads started by a load task or by hand have it NULL. The row also keeps the SESSION_ID of the session that runs the attempt.

Loads that run long

A load can take longer than STALE_AFTER_MINUTES. OPS.LOAD_DATASET writes CURRENT_SESSION() into LOAD_LOG.SESSION_ID when it registers the attempt. Its own CALL stays RUNNING in that session until the procedure ends, so the procedure OPS.IS_SESSION_ALIVE reads INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION and returns TRUE when the session has a query RUNNING, QUEUED, BLOCKED or RESUMING_WAREHOUSE. It returns FALSE for a NULL session id (rows written before the column existed), for the current session, and when the history cannot be read; the monitor then applies the age rule alone.

The monitor calls it in step 2 for every row older than STALE_AFTER_MINUTES. LOAD_DATASET calls it in its overlap guard, so an old row with a live session still counts as a run in progress and no second run of the same job and date starts. The result is in the monitor message, e.g. abandoned closed: 1 (1 still running past the stale limit, left open).

snowflake/13_create_raw_ops_job_config_and_log.sql
USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

-- A run's own CALL stays RUNNING in its session until the procedure ends, so "the session has a running
-- query" means the run is alive. The query id of that CALL is not available inside the procedure,
-- which is why LOAD_LOG keeps the session id. FALSE for NULL (rows written before SESSION_ID existed),
-- for the current session (an earlier call of this session has already ended) and when the history
-- cannot be read: the caller then falls back to the age rule (STALE_AFTER_MINUTES).
CREATE OR REPLACE PROCEDURE IS_SESSION_ALIVE(P_SESSION_ID NUMBER)
RETURNS BOOLEAN
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    v_running NUMBER DEFAULT 0;
BEGIN
    IF (P_SESSION_ID IS NULL OR P_SESSION_ID = CURRENT_SESSION()::NUMBER) THEN
        RETURN FALSE;
    END IF;
    SELECT COUNT(*) INTO :v_running
        FROM TABLE(PRD_RAW.INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION(SESSION_ID => :P_SESSION_ID, RESULT_LIMIT => 10000))
        WHERE EXECUTION_STATUS IN ('RUNNING', 'QUEUED', 'BLOCKED', 'RESUMING_WAREHOUSE');
    RETURN v_running > 0;
EXCEPTION
    WHEN OTHER THEN
        RETURN FALSE;
END;
$$;

Grant the task privilege

SYSADMIN owns the task, so the account-level EXECUTE TASK privilege granted for the load tasks covers it too (GRANT EXECUTE TASK ON ACCOUNT TO ROLE SYSADMIN, see Daily Load Tasks).

Resume the task

The task is created suspended.

Snowflake
ALTER TASK MONITOR_LOAD_JOBS_TASK RESUME;

Run and check

The monitor can be run once by hand or through its task:

Snowflake
CALL OPS.MONITOR_LOAD_JOBS();

EXECUTE TASK MONITOR_LOAD_JOBS_TASK;
Snowflake
SELECT * FROM OPS.MONITOR_LOG ORDER BY RUN_ID DESC;

SELECT * FROM OPS.LOAD_LOG WHERE MONITOR_RUN_ID IS NOT NULL ORDER BY LOG_ID DESC;

SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(TASK_NAME => 'MONITOR_LOAD_JOBS_TASK'))
ORDER BY SCHEDULED_TIME DESC;

Procedure DDL

snowflake/16_create_proc_monitor_load_jobs.sql
USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

-- P_MAX_RETRIES_PER_RUN: at most this many retries per monitor run (retries run one after another).
-- P_STOP_AFTER_FAILURES: stop the run after this many failed retries in a row (probably a shared cause).
--   Counted within one run, over job + date pairs of any job; a success resets it. Pairs not tried
--   stay in state RETRY for the next run.
-- P_EMAIL_INTEGRATION / P_EMAIL_TO: notification integration and recipients for the alert digest;
--   NULL = no email, the alerts are still marked in LOAD_LOG and shown in the return value.
CREATE OR REPLACE PROCEDURE MONITOR_LOAD_JOBS(
    P_MAX_RETRIES_PER_RUN NUMBER  DEFAULT 10,
    P_STOP_AFTER_FAILURES NUMBER  DEFAULT 3,
    P_EMAIL_INTEGRATION   VARCHAR DEFAULT NULL,
    P_EMAIL_TO            VARCHAR DEFAULT NULL
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
    v_tz        VARCHAR DEFAULT 'Asia/Ho_Chi_Minh';
    v_run_id    NUMBER;
    v_abandoned NUMBER  DEFAULT 0;
    v_long      NUMBER  DEFAULT 0;
    v_sess      NUMBER;
    v_log_id    NUMBER;
    v_stale     NUMBER;
    v_alive     BOOLEAN;
    v_checked  NUMBER  DEFAULT 0;
    v_cand      NUMBER  DEFAULT 0;
    v_stopped   BOOLEAN DEFAULT FALSE;
    v_summary   VARCHAR;
    v_states    VARCHAR;
    v_alerts    NUMBER  DEFAULT 0;
    v_digest    VARCHAR;
    v_tried     NUMBER  DEFAULT 0;
    v_ok        NUMBER  DEFAULT 0;
    v_failed    NUMBER  DEFAULT 0;
    v_in_a_row  NUMBER  DEFAULT 0;
    v_res       VARCHAR;
    v_job_name  VARCHAR;
    v_job_date  DATE;
    v_mail      VARCHAR DEFAULT 'not configured';
    c_stale CURSOR FOR
        SELECT l.LOG_ID, l.SESSION_ID, c.STALE_AFTER_MINUTES
        FROM PRD_RAW.OPS.LOAD_LOG l
        JOIN PRD_RAW.OPS.LOAD_JOB_CONFIG c ON l.JOB_ID = c.JOB_ID
        WHERE l.STATUS IN ('PENDING', 'RUNNING')
          AND l.LOGGED_AT < DATEADD(minute, -1 * c.STALE_AFTER_MINUTES, CURRENT_TIMESTAMP());
    c_retry CURSOR FOR
        SELECT JOB_NAME, SNAP FROM PRD_RAW.OPS.MONITOR_STATE_TMP WHERE ACTION = 'RETRY' ORDER BY SNAP, N_FAIL, JOB_ID;
BEGIN
    -- 0) Register this monitor run in MONITOR_LOG
    INSERT INTO PRD_RAW.OPS.MONITOR_LOG (STATUS) VALUES ('RUNNING');
    SELECT MAX(RUN_ID) INTO :v_run_id FROM PRD_RAW.OPS.MONITOR_LOG WHERE STATUS = 'RUNNING';

    -- 1) Runs that never finished (killed task, crash) stay PENDING / RUNNING: close them as retryable errors.
    --    Only runs older than STALE_AFTER_MINUTES whose session has no running query any more; a long load
    --    that is still executing stays PENDING / RUNNING (state RUNNING below) however old it is.
    FOR s IN c_stale DO
        -- cursor fields cannot be used as bind variables in CALL / UPDATE: copy them to variables first
        v_log_id := s.LOG_ID;
        v_sess   := s.SESSION_ID;
        v_stale  := s.STALE_AFTER_MINUTES;
        CALL PRD_RAW.OPS.IS_SESSION_ALIVE(:v_sess) INTO :v_alive;
        IF (v_alive) THEN
            v_long := v_long + 1;
        ELSE
            UPDATE PRD_RAW.OPS.LOAD_LOG
                SET STATUS = 'ERROR', IS_RETRYABLE = TRUE, FINISHED_AT = CURRENT_TIMESTAMP(),
                    MESSAGE = 'abandoned: no result after ' || :v_stale || ' minutes'
                WHERE LOG_ID = :v_log_id;
            v_abandoned := v_abandoned + 1;
        END IF;
    END FOR;

    -- 2) State of every enabled job and snapshot date that has log rows, inside its RETRY_WINDOW_DAYS
    CREATE OR REPLACE TEMPORARY TABLE PRD_RAW.OPS.MONITOR_STATE_TMP AS
    WITH agg AS (
        SELECT JOB_ID, SNAPSHOT_DATE,
               COUNT_IF(STATUS = 'OK') AS N_OK,
               COUNT_IF(STATUS IN ('SKIPPED', 'ERROR')) AS N_FAIL,
               COUNT_IF(STATUS IN ('PENDING', 'RUNNING')) AS N_ACTIVE
        FROM PRD_RAW.OPS.LOAD_LOG GROUP BY JOB_ID, SNAPSHOT_DATE
    ),
    latest AS (
        SELECT JOB_ID, SNAPSHOT_DATE, LOG_ID, STATUS, IS_RETRYABLE, FINISHED_AT, MESSAGE, ALERTED_AT
        FROM PRD_RAW.OPS.LOAD_LOG
        QUALIFY ROW_NUMBER() OVER (PARTITION BY JOB_ID, SNAPSHOT_DATE ORDER BY LOG_ID DESC) = 1
    )
    SELECT c.JOB_ID, c.JOB_NAME, a.SNAPSHOT_DATE AS SNAP, c.ALERT_ENABLED,
           a.N_FAIL,
           l.LOG_ID AS LAST_LOG_ID, l.MESSAGE AS LAST_MESSAGE, l.ALERTED_AT AS LAST_ALERTED_AT,
           CASE
               WHEN a.N_OK > 0 THEN 'DONE'
               WHEN a.N_ACTIVE > 0 THEN 'RUNNING'
               WHEN l.STATUS = 'ERROR' AND NOT COALESCE(l.IS_RETRYABLE, FALSE) THEN 'PERMANENT'
               WHEN a.N_FAIL >= c.MAX_RETRIES THEN 'GAVE_UP'
               WHEN l.FINISHED_AT > DATEADD(minute, -1 * c.RETRY_BACKOFF_MINUTES, CURRENT_TIMESTAMP()) THEN 'WAIT'
               ELSE 'RETRY'
           END AS ACTION
    FROM PRD_RAW.OPS.LOAD_JOB_CONFIG c
    JOIN agg a    ON a.JOB_ID = c.JOB_ID
    JOIN latest l ON l.JOB_ID = a.JOB_ID AND l.SNAPSHOT_DATE = a.SNAPSHOT_DATE
    WHERE c.ENABLED
      AND a.SNAPSHOT_DATE > DATEADD(day, -1 * c.RETRY_WINDOW_DAYS, CONVERT_TIMEZONE(:v_tz, CURRENT_TIMESTAMP())::DATE);

    SELECT COUNT(*), COUNT_IF(ACTION = 'RETRY') INTO :v_checked, :v_cand FROM PRD_RAW.OPS.MONITOR_STATE_TMP;

    -- Which job + date is in which state (DONE only as a count), kept in MONITOR_LOG.MESSAGE because the state table is temporary
    SELECT LISTAGG(t.TXT, '; ') WITHIN GROUP (ORDER BY t.ACTION) INTO :v_states
        FROM (SELECT ACTION,
                     ACTION || ' ' || COUNT(*)
                     || IFF(ACTION = 'DONE', '',
                            ' [' || LISTAGG('job ' || JOB_ID || ' ' || TO_VARCHAR(SNAP), ', ') WITHIN GROUP (ORDER BY JOB_ID, SNAP) || ']') AS TXT
              FROM PRD_RAW.OPS.MONITOR_STATE_TMP GROUP BY ACTION) t;

    -- 3) Alert once for dates that need a person: permanent error, or retries used up
    SELECT COUNT(*),
           LISTAGG(JOB_NAME || ' ' || TO_VARCHAR(SNAP) || ' [' || ACTION || ']: ' || COALESCE(LAST_MESSAGE, 'no log row'),
                   '\n') WITHIN GROUP (ORDER BY SNAP, JOB_NAME)
        INTO :v_alerts, :v_digest
        FROM PRD_RAW.OPS.MONITOR_STATE_TMP
        WHERE ACTION IN ('PERMANENT', 'GAVE_UP') AND ALERT_ENABLED
          AND LAST_LOG_ID IS NOT NULL AND LAST_ALERTED_AT IS NULL;

    IF (v_alerts = 0) THEN
        v_mail := 'no alerts';
    ELSE
        UPDATE PRD_RAW.OPS.LOAD_LOG SET ALERTED_AT = CURRENT_TIMESTAMP()
            WHERE LOG_ID IN (SELECT LAST_LOG_ID FROM PRD_RAW.OPS.MONITOR_STATE_TMP
                             WHERE ACTION IN ('PERMANENT', 'GAVE_UP') AND ALERT_ENABLED
                               AND LAST_LOG_ID IS NOT NULL AND LAST_ALERTED_AT IS NULL);
        IF (P_EMAIL_INTEGRATION IS NOT NULL AND P_EMAIL_TO IS NOT NULL) THEN
            BEGIN
                CALL SYSTEM$SEND_EMAIL(:P_EMAIL_INTEGRATION, :P_EMAIL_TO,
                    'RAW load jobs need attention (' || v_alerts || ')', :v_digest);
                v_mail := 'email sent';
            EXCEPTION
                WHEN OTHER THEN
                    v_mail := 'email FAILED: ' || SQLERRM;
            END;
        END IF;
    END IF;

    -- 4) Retry one date at a time (LOAD_DATASET has its own overlap guard); one failing date never stops
    --    the others, but several failures in a row stop the run
    FOR rec IN c_retry DO
        IF (v_tried >= P_MAX_RETRIES_PER_RUN OR v_in_a_row >= P_STOP_AFTER_FAILURES) THEN
            BREAK;
        END IF;
        v_tried := v_tried + 1;
        BEGIN
            -- cursor fields cannot be used as bind variables in CALL: copy them to variables first
            v_job_name := rec.JOB_NAME;
            v_job_date := rec.SNAP;
            CALL PRD_RAW.OPS.LOAD_DATASET(:v_job_name, :v_job_date, FALSE, :v_run_id) INTO :v_res;
            IF (STARTSWITH(v_res, 'OK')) THEN
                v_ok := v_ok + 1;
                v_in_a_row := 0;
            END IF;
        EXCEPTION
            WHEN OTHER THEN
                v_failed := v_failed + 1;
                v_in_a_row := v_in_a_row + 1;
        END;
    END FOR;

    -- 5) Summary: stopped early = some RETRY dates were left for the next run (failures in a row or per-run cap)
    v_stopped := v_cand > v_tried;
    v_summary := 'abandoned closed: ' || v_abandoned || IFF(v_long > 0, ' (' || v_long || ' still running past the stale limit, left open)', '')
        || ', retried: ' || v_tried || ' (ok ' || v_ok || ', failed ' || v_failed
        || '), alerts: ' || v_alerts || ' (' || v_mail || ')'
        || IFF(v_stopped, ', stopped early: ' || (v_cand - v_tried) || ' date(s) left for the next run', '')
        || '. States at start: ' || COALESCE(v_states, 'none');
    UPDATE PRD_RAW.OPS.MONITOR_LOG
        SET STATUS = 'OK', FINISHED_AT = CURRENT_TIMESTAMP(), ABANDONED_CLOSED = :v_abandoned,
            DATES_CHECKED = :v_checked, RETRY_CANDIDATES = :v_cand, RETRIED = :v_tried, RETRIED_OK = :v_ok,
            RETRIED_FAILED = :v_failed, ALERTS = :v_alerts, EMAIL_STATUS = :v_mail,
            STOPPED_EARLY = :v_stopped, MESSAGE = :v_summary
        WHERE RUN_ID = :v_run_id;
    -- the monitor itself does not fail when a retry fails
    RETURN v_summary;

EXCEPTION
    -- The monitor itself failed (not a retry): record it and fail the task run
    WHEN OTHER THEN
        UPDATE PRD_RAW.OPS.MONITOR_LOG
            SET STATUS = 'ERROR', FINISHED_AT = CURRENT_TIMESTAMP(), MESSAGE = :SQLERRM
            WHERE RUN_ID = :v_run_id;
        RAISE;
END;
$$;