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.
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
| Parameter | Type | Meaning |
|---|---|---|
| P_MAX_RETRIES_PER_RUN | NUMBER, default 10 | At most this many retries in one run. They run one after another. |
| P_STOP_AFTER_FAILURES | NUMBER, default 3 | The 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_INTEGRATION | VARCHAR, default NULL | Notification integration used to send the alert digest. NULL: no email. |
| P_EMAIL_TO | VARCHAR, default NULL | Verified recipient of the digest. NULL: no email. |
Steps
| Step | What happens |
|---|---|
| 1. Register the run | Insert a RUNNING row in OPS.MONITOR_LOG. Its RUN_ID is passed to every retry. |
| 2. Close abandoned runs | LOAD_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 state | For 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. Alert | Dates 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. Retry | Dates 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. Summary | The 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.
| State | Condition | Action |
|---|---|---|
| DONE | An OK row exists for the date | Nothing |
| RUNNING | A PENDING or RUNNING row exists: younger than STALE_AFTER_MINUTES, or older with its session still running a query | Nothing |
| PERMANENT | The last attempt failed with IS_RETRYABLE = FALSE | Alert, no retry |
| GAVE_UP | Failed attempts reached MAX_RETRIES | Alert, no retry |
| WAIT | The last attempt ended less than RETRY_BACKOFF_MINUTES ago | Nothing, next run |
| RETRY | None of the above, e.g. missing marker | Call 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).
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.
ALTER TASK MONITOR_LOAD_JOBS_TASK RESUME;Run and check
The monitor can be run once by hand or through its task:
CALL OPS.MONITOR_LOAD_JOBS();
EXECUTE TASK MONITOR_LOAD_JOBS_TASK;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
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;
$$;