BankOrg Docs

In progress

BankOrg Data Platform / Data Engineering / RAW Ingestion

Test Cases

Test cases for failed first loads: which ones the monitor retries, which ones it alerts, and the limits of a run.

Test cases for the load procedure and the monitor, covering the loads that fail on the first attempt and are retried or alerted. The cases use a test snapshot date, 2026-10-25, that has no real data. Cases 6, 8 and 14 change the job rules in OPS.LOAD_JOB_CONFIG, which are put back after the test.

Retry cases

#ScenarioSetupExpectedResult
1Marker missingCall LOAD_DATASET for the test date before the files exist, then upload the files and run the monitor.First run SKIPPED, retryable. The monitor sets RETRY, loads the date, and the row becomes OK.Passed. Attempt 1 SKIPPED (no _SUCCESS.json marker), retried by the monitor, attempt 2 OK with 499 rows loaded.
2Marker there, CSV missingUpload only _SUCCESS.json for the test date.SKIPPED (marker found but no data file). Retried once the CSV arrives.Passed. Attempt 1 SKIPPED (marker found but no data file), retried by the monitor, attempt 2 OK with 303 rows loaded.
3BackoffRun the monitor when the last attempt of the date ended less than RETRY_BACKOFF_MINUTES (30) ago. Done together with case 4: the abandoned close sets FINISHED_AT to the run time.State WAIT, RETRIED = 0.Passed. The run at 07:00 listed WAIT 1 [job 1 2026-10-20] in MONITOR_LOG.MESSAGE and retried 0.
4Stuck runInsert a PENDING row in LOAD_LOG with LOGGED_AT at the current time and let the hourly monitor run once it is older than STALE_AFTER_MINUTES (60).The monitor closes it as ERROR (abandoned), ABANDONED_CLOSED = 1. The date is in WAIT for the backoff, then the next run retries it.Passed for the close. The run at 07:00 closed attempt 2 as ERROR (abandoned: no result after 60 minutes), ABANDONED_CLOSED = 1. The retry by the next run is not checked yet.

Alert cases

#ScenarioSetupExpectedResult
5Marker does not match the CSVChange the row count in the marker of the test date.ERROR -20002, not retryable, state PERMANENT. One alert, no retry.Not run
6Retries used upKeep the marker missing. Set MAX_RETRIES = 2 and RETRY_BACKOFF_MINUTES = 0 on the job and run the monitor 3 times.State GAVE_UP. One alert, no further retries.Not run
7Alert only onceRun the monitor again after case 5 or 6.ALERTS = 0, ALERTED_AT already set.Not run
8Alerts turned offSet ALERT_ENABLED = FALSE on the job and repeat case 5.State PERMANENT, no alert, ALERTED_AT stays NULL.Not run
9EmailRun the monitor with a notification integration and a verified address, then with a wrong integration.EMAIL_STATUS = email sent. With the wrong integration: email FAILED, and the monitor run is still OK.Not run

Limits and edge cases

#ScenarioSetupExpectedResult
10Stop after 3 failures in a rowMake the four jobs fail on the same date (marker missing) with RETRY_BACKOFF_MINUTES = 0.3 retries fail, the run stops, STOPPED_EARLY = TRUE, the fourth is left for the next run.Not run
11Cap per runCALL MONITOR_LOAD_JOBS(2, 3) with 3 or more pairs in state RETRY.2 retried, STOPPED_EARLY = TRUE.Not run
12Already loadedRun the monitor with a date that has an OK row.State DONE, nothing happens.Not run
13Outside the windowUse a failed date older than RETRY_WINDOW_DAYS (7).The date is not looked at.Not run
14Disabled jobSet ENABLED = FALSE on the job.Ignored by the monitor. LOAD_DATASET returns SKIPPED.Not run
17Job already runningWith the PENDING row of case 4 younger than STALE_AFTER_MINUTES (60), call LOAD_DATASET for the same job and date.Returns SKIPPED, already running. Nothing is loaded.Passed. LOAD_DATASET returned SKIPPED: LOAD_BRANCHES 2026-10-20 is already running.

Long-running loads

Cases 18 and 19 use the snapshot date 2000-01-01, outside RETRY_WINDOW_DAYS, so the monitor does not retry it; its test rows are deleted from OPS.LOAD_LOG afterwards.

#ScenarioSetupExpectedResult
18Old row, session still runningOpen a session and keep it busy with SELECT SYSTEM$WAIT(600). Insert a RUNNING row for LOAD_BRANCHES 2000-01-01 with LOGGED_AT two hours back and SESSION_ID set to that session. From a second session call OPS.IS_SESSION_ALIVE, then OPS.LOAD_DATASET for the same job and date, then the monitor.IS_SESSION_ALIVE returns TRUE. LOAD_DATASET returns SKIPPED, already running. The monitor leaves the row RUNNING and counts it as still running.Passed. IS_SESSION_ALIVE returned TRUE, LOAD_DATASET returned SKIPPED: LOAD_BRANCHES 2000-01-01 is already running, and the monitor message had (1 still running past the stale limit, left open).
19Old row, session finishedLet the SYSTEM$WAIT of case 18 end, then run the monitor.The row becomes ERROR, retryable, abandoned: no result after 60 minutes.Not run

End to end

#ScenarioSetupExpectedResult
15Load task before the files existResume the load tasks. Run a load task on a day without a published folder, then publish the files.The task run fails with a missing marker. The next hourly MONITOR_LOAD_JOBS_TASK run loads the date.Not run
16The monitor itself failsForce an error inside the procedure.MONITOR_LOG.STATUS = ERROR and the task run fails.Not run

Evidence

Case 1: marker missing

The marker of the accounts feed of 2026-10-19 was moved out of its folder to gs://bankorg-prd-raw-landing/_test/accounts_SUCCESS_2026-10-19.json, so the first load of that date fails.

Output of moving the accounts marker of 2026-10-19.

The accounts load of 2026-10-19 in OPS.LOAD_LOG: attempt 1 is SKIPPED with the message no _SUCCESS.json marker, then the monitor retried it and attempt 2 is OK with 499 rows loaded.

Load log of the accounts feed of 2026-10-19 after the marker was missing.

Case 2: marker there, CSV missing

The data file of the customers feed of 2026-10-19 was moved to gs://bankorg-prd-raw-landing/_test/customers_20261019.csv. _SUCCESS.json stays in the folder.

Output of moving the customers data file of 2026-10-19.

The customers load of 2026-10-19 in OPS.LOAD_LOG: attempt 1 is SKIPPED because the marker was found but the data file was not, then the monitor retried it after the CSV was moved back and attempt 2 is OK with 303 rows loaded.

Load log of the customers feed of 2026-10-19 after the data file was missing.

Cases 3 and 4: insert the PENDING row

A PENDING row was inserted in OPS.LOAD_LOG for the branches feed of 2026-10-20 as attempt 2, with LOGGED_AT at the current time (log id 1101, 2026-10-20 15:19:43 in the session time zone). Attempt 1 is the SKIPPED row, log id 1001. The row is used by case 3 and case 4: the monitor runs at 06:00 and 07:00 act on it.

Load log of the branches feed of 2026-10-20 after the PENDING row was inserted.

Case 3: backoff

At 06:00, monitor run 1501 leaves the PENDING row of case 4 alone. The row was logged at 05:19, so it is about 40 minutes old, younger than STALE_AFTER_MINUTES (60). The date is treated as still running and is not closed or retried.

At 07:00, monitor run 1601 closes that abandoned PENDING row, now about 100 minutes old and older than 60 minutes, and sets its FINISHED_AT to the run time. The last attempt of the date ended less than 30 minutes ago, so the date is in WAIT and nothing is retried.

The WAIT state is only in the temporary table, so it is read from MONITOR_LOG.MESSAGE: abandoned closed 1, retried 0, states at start DONE 11 and WAIT 1 for job 1 on 2026-10-20.

Monitor log of the run at 07:00 with the states at start.

Case 4: stuck run

The PENDING row inserted above is older than STALE_AFTER_MINUTES (60) at 07:00, so the same monitor run at 07:00 (run id 1601) closed it as an abandoned run: attempt 2 became ERROR with the message abandoned: no result after 60 minutes, retryable, and FINISHED_AT 17:00:11 in the session time zone.

Load log of the branches feed of 2026-10-20 after the run at 07:00.

Case 17: job already running

While the PENDING row of the branches feed of 2026-10-20 (case 4) was younger than STALE_AFTER_MINUTES (60), OPS.LOAD_DATASET was called for LOAD_BRANCHES and 2026-10-20. The overlap guard treated the PENDING row as a run in progress and returned SKIPPED: LOAD_BRANCHES 2026-10-20 is already running.

Result of calling LOAD_DATASET for the branches feed of 2026-10-20 while a run was in progress.

What is checked

TableColumns
OPS.MONITOR_LOGCounts of the run, STOPPED_EARLY, EMAIL_STATUS, MESSAGE
OPS.LOAD_LOGSTATUS, IS_RETRYABLE, ATTEMPT_NO, MONITOR_RUN_ID, ALERTED_AT
OPS.MONITOR_STATE_TMPState of each job and date, same session only