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
| # | Scenario | Setup | Expected | Result |
|---|---|---|---|---|
| 1 | Marker missing | Call 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. |
| 2 | Marker there, CSV missing | Upload 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. |
| 3 | Backoff | Run 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. |
| 4 | Stuck run | Insert 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
| # | Scenario | Setup | Expected | Result |
|---|---|---|---|---|
| 5 | Marker does not match the CSV | Change the row count in the marker of the test date. | ERROR -20002, not retryable, state PERMANENT. One alert, no retry. | Not run |
| 6 | Retries used up | Keep 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 |
| 7 | Alert only once | Run the monitor again after case 5 or 6. | ALERTS = 0, ALERTED_AT already set. | Not run |
| 8 | Alerts turned off | Set ALERT_ENABLED = FALSE on the job and repeat case 5. | State PERMANENT, no alert, ALERTED_AT stays NULL. | Not run |
| 9 | Run 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
| # | Scenario | Setup | Expected | Result |
|---|---|---|---|---|
| 10 | Stop after 3 failures in a row | Make 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 |
| 11 | Cap per run | CALL MONITOR_LOAD_JOBS(2, 3) with 3 or more pairs in state RETRY. | 2 retried, STOPPED_EARLY = TRUE. | Not run |
| 12 | Already loaded | Run the monitor with a date that has an OK row. | State DONE, nothing happens. | Not run |
| 13 | Outside the window | Use a failed date older than RETRY_WINDOW_DAYS (7). | The date is not looked at. | Not run |
| 14 | Disabled job | Set ENABLED = FALSE on the job. | Ignored by the monitor. LOAD_DATASET returns SKIPPED. | Not run |
| 17 | Job already running | With 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.
| # | Scenario | Setup | Expected | Result |
|---|---|---|---|---|
| 18 | Old row, session still running | Open 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). |
| 19 | Old row, session finished | Let 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
| # | Scenario | Setup | Expected | Result |
|---|---|---|---|---|
| 15 | Load task before the files exist | Resume 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 |
| 16 | The monitor itself fails | Force 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.
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.
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.
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.
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.
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.
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.
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.
What is checked
| Table | Columns |
|---|---|
| OPS.MONITOR_LOG | Counts of the run, STOPPED_EARLY, EMAIL_STATUS, MESSAGE |
| OPS.LOAD_LOG | STATUS, IS_RETRYABLE, ATTEMPT_NO, MONITOR_RUN_ID, ALERTED_AT |
| OPS.MONITOR_STATE_TMP | State of each job and date, same session only |