BankOrg Data Platform / Data Engineering / RAW Ingestion
LOAD_DATASET Procedure
The generic load procedure OPS.LOAD_DATASET: parameters, steps, log rows and errors.
OPS.LOAD_DATASET loads one business date of one feed from GCS into its RAW table. One procedure serves the four jobs; everything that differs per job is read from its row in OPS.LOAD_JOB_CONFIG. Each run is written to OPS.LOAD_LOG. The load tasks and the monitor call it.
Parameters
| Parameter | Type | Meaning |
|---|---|---|
| P_JOB_NAME | VARCHAR | Job in OPS.LOAD_JOB_CONFIG, e.g. LOAD_BRANCHES. Stage, folder, file format, pattern, marker, target table, excluded columns, overwrite rule and stale limit are read from its row. |
| P_SNAPSHOT_DATE | DATE | Business date to load, i.e. the folder snapshot_date=YYYY-MM-DD. No offset is applied; the load tasks pass yesterday. |
| P_OVERWRITE | BOOLEAN, default FALSE | TRUE deletes the date's rows and loads its files again with FORCE = TRUE, in one transaction. Only for jobs with ALLOW_OVERWRITE = TRUE. |
| P_MONITOR_RUN_ID | NUMBER, default NULL | Set by the monitor to link the attempt to its row in OPS.MONITOR_LOG. NULL for runs started by a load task or by hand. |
Steps
| Step | What happens |
|---|---|
| 1. Read the config | An unknown job name raises an error. A disabled job returns SKIPPED without a log row. |
| 2. Already loaded | If LOAD_LOG has an OK row for the job and date and P_OVERWRITE is FALSE, return "already loaded" at once, with no log row. |
| 3. Overlap guard | If a PENDING or RUNNING row of the same job and date is younger than STALE_AFTER_MINUTES, or older but its session still runs a query (OPS.IS_SESSION_ALIVE), return "already running". |
| 4. Register the attempt | Insert a PENDING row in LOAD_LOG with the next ATTEMPT_NO, the batch id and SESSION_ID = CURRENT_SESSION(). |
| 5. Overwrite check | P_OVERWRITE on a job without ALLOW_OVERWRITE is a permanent ERROR, e.g. for LOAD_TRANSACTIONS. |
| 6. Marker | LIST the marker file of the date. Missing: the row becomes SKIPPED (retryable) and an error is raised. |
| 7. Running | The row becomes RUNNING. |
| 8. Marker against CSV | No data file yet is SKIPPED (retryable). Otherwise exactly one CSV is expected, with the file name and row count written in the marker; a difference is a permanent ERROR. |
| 9. Columns | The source columns are read from the target table: the columns that do not start with _ and are not in EXCLUDE_COLUMNS, in table order. The load metadata columns are added, and _SNAPSHOT_DATE only if the table has it. |
| 10. Build and log the SQL | The COPY INTO (and the DELETE for an overwrite) is built and stored in LOAD_LOG.SQL_TEXT. |
| 11. Overwrite | BEGIN TRANSACTION and delete the rows whose _SOURCE_FILE is in the date's folder. |
| 12. COPY INTO | Load the date's CSV (FORCE = TRUE for an overwrite). |
| 13. Finish | Count the rows of this batch, COMMIT for an overwrite, set the row to OK with ROWS_LOADED and FINISHED_AT. |
Log row
Each run is one row in OPS.LOAD_LOG. It is inserted as PENDING, becomes RUNNING when the load starts, and ends as OK, SKIPPED or ERROR with ROWS_LOADED, ERROR_CODE, IS_RETRYABLE and FINISHED_AT. Every error is also raised, so the task run shows as failed.
| Error code | Cause | Retryable |
|---|---|---|
| -20001 | Marker or data file missing | Yes, logged as SKIPPED |
| -20002 | Marker does not match the CSV (file name, row count or number of files) | No |
| -20003 | Job configuration problem: unknown job, or target table not found | No |
| -20004 | Overwrite not allowed for the job | No |
| other | Any other failure, e.g. a COPY INTO error; rolled back for an overwrite | No, for now |
Calls run
Initial load of the 2026-10-18 branches:
CALL PRD_RAW.OPS.LOAD_DATASET('LOAD_BRANCHES', '2026-10-18'::DATE);
-- OK: branches loaded for 2026-10-18 (50 rows)The same call again returns at once, without a new log row:
CALL PRD_RAW.OPS.LOAD_DATASET('LOAD_BRANCHES', '2026-10-18'::DATE);
-- SKIPPED: LOAD_BRANCHES 2026-10-18 is already loadedOverwrite of the same date:
CALL PRD_RAW.OPS.LOAD_DATASET('LOAD_BRANCHES', '2026-10-18'::DATE, TRUE);
-- OK: branches loaded for 2026-10-18 (overwrite, 50 old rows deleted)Procedure DDL
USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;
-- P_SNAPSHOT_DATE: the business date to load, i.e. the folder snapshot_date=YYYY-MM-DD. Always required,
-- and no offset is applied here: the task subtracts the day offset before it calls this procedure.
-- P_MONITOR_RUN_ID: set by the monitor (OPS.MONITOR_LOG.RUN_ID) so the attempt is linked to its monitor run;
-- NULL for runs started by the load task or by hand.
-- P_OVERWRITE = TRUE (manual only, and only if the job has ALLOW_OVERWRITE): delete the date's rows
-- and load its files again in one transaction (a failed load restores the old rows).
CREATE OR REPLACE PROCEDURE LOAD_DATASET(
P_JOB_NAME VARCHAR,
P_SNAPSHOT_DATE DATE,
P_OVERWRITE BOOLEAN DEFAULT FALSE,
P_MONITOR_RUN_ID NUMBER DEFAULT NULL
)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$
DECLARE
-- fixed settings
v_marker_ff VARCHAR DEFAULT 'PRD_RAW.CORE_BANKING.JSON_FF';
-- job config
v_job_id NUMBER;
v_dataset VARCHAR;
v_stage VARCHAR;
v_folder VARCHAR;
v_ff VARCHAR;
v_pattern VARCHAR;
v_marker VARCHAR;
v_db VARCHAR;
v_schema VARCHAR;
v_table VARCHAR;
v_target VARCHAR;
v_exclude VARCHAR;
v_allow_ow BOOLEAN;
v_stale NUMBER;
v_total NUMBER;
v_enabled NUMBER;
-- run
v_snap DATE;
v_date VARCHAR;
v_path VARCHAR;
v_batch VARCHAR;
v_seq NUMBER DEFAULT 1;
v_active NUMBER DEFAULT 0;
v_sess NUMBER;
v_alive BOOLEAN;
v_done NUMBER DEFAULT 0;
v_ready NUMBER DEFAULT 1;
-- target table layout
v_cols VARCHAR;
v_ncols NUMBER;
v_has_snap NUMBER;
v_ins VARCHAR;
v_sel VARCHAR;
-- marker vs CSV
v_mfile VARCHAR;
v_mrows NUMBER;
v_cfile VARCHAR;
v_cfiles NUMBER;
v_crows NUMBER;
-- result
v_sql VARCHAR;
v_del_sql VARCHAR;
v_deleted NUMBER DEFAULT 0;
v_rows NUMBER DEFAULT 0;
v_msg VARCHAR;
e_not_ready EXCEPTION (-20001, 'Feed not ready: marker or data file is missing');
e_mismatch EXCEPTION (-20002, 'Marker does not match the CSV file');
e_config EXCEPTION (-20003, 'Job configuration problem, see LOAD_LOG.MESSAGE');
e_no_overwrite EXCEPTION (-20004, 'Overwrite is not allowed for this job');
BEGIN
-- 1) Read the job config (unknown name = error, disabled = quiet skip)
SELECT COUNT(*), COUNT_IF(ENABLED) INTO :v_total, :v_enabled
FROM PRD_RAW.OPS.LOAD_JOB_CONFIG WHERE JOB_NAME = :P_JOB_NAME;
IF (v_total = 0) THEN
RAISE e_config;
END IF;
IF (v_enabled = 0) THEN
RETURN 'SKIPPED: job ' || P_JOB_NAME || ' is disabled';
END IF;
SELECT JOB_ID, DATASET, SOURCE_STAGE, SOURCE_FOLDER, FILE_FORMAT, FILE_PATTERN, MARKER_FILE,
TARGET_DATABASE, TARGET_SCHEMA, TARGET_TABLE, EXCLUDE_COLUMNS, ALLOW_OVERWRITE, STALE_AFTER_MINUTES
INTO :v_job_id, :v_dataset, :v_stage, :v_folder, :v_ff, :v_pattern, :v_marker,
:v_db, :v_schema, :v_table, :v_exclude, :v_allow_ow, :v_stale
FROM PRD_RAW.OPS.LOAD_JOB_CONFIG WHERE JOB_NAME = :P_JOB_NAME AND ENABLED
ORDER BY JOB_ID DESC LIMIT 1;
v_target := v_db || '.' || v_schema || '.' || v_table;
-- 2) Date to load (as given), GCS folder of that date, batch id of this run
v_snap := :P_SNAPSHOT_DATE;
v_date := TO_VARCHAR(:v_snap, 'YYYY-MM-DD');
v_path := v_stage || '/' || v_folder || '/snapshot_date=' || v_date || '/';
v_batch := v_dataset || '_' || v_date || '_' || TO_VARCHAR(CURRENT_TIMESTAMP(), 'YYYYMMDDHH24MISS');
-- 2b) Already loaded (an OK row exists) and no overwrite requested: return at once, no log row, no error.
-- A corrected file for an already loaded date needs P_OVERWRITE = TRUE.
IF (NOT :P_OVERWRITE) THEN
SELECT COUNT(*) INTO :v_done FROM PRD_RAW.OPS.LOAD_LOG
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND STATUS = 'OK';
IF (v_done > 0) THEN
RETURN 'SKIPPED: ' || P_JOB_NAME || ' ' || v_date || ' is already loaded';
END IF;
END IF;
-- 3) Another run of this job and date still in progress: do nothing. In progress = younger than the
-- stale limit, or older but its session still has a running query (a long load is not dead)
-- (a RESULTSET, not a CURSOR: a cursor query does not take :variables without OPEN ... USING)
LET rs_active RESULTSET := (
SELECT SESSION_ID, LOGGED_AT > DATEADD(minute, -1 * :v_stale, CURRENT_TIMESTAMP()) AS YOUNG
FROM PRD_RAW.OPS.LOAD_LOG
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND STATUS IN ('PENDING', 'RUNNING'));
FOR r IN rs_active DO
IF (r.YOUNG) THEN
v_active := v_active + 1;
ELSE
-- cursor fields cannot be used as bind variables in CALL: copy to a variable first
v_sess := r.SESSION_ID;
CALL PRD_RAW.OPS.IS_SESSION_ALIVE(:v_sess) INTO :v_alive;
IF (v_alive) THEN
v_active := v_active + 1;
END IF;
END IF;
END FOR;
IF (v_active > 0) THEN
RETURN 'SKIPPED: ' || P_JOB_NAME || ' ' || v_date || ' is already running';
END IF;
-- 4) Register this attempt right away as PENDING (ATTEMPT_NO 1 = first run of this date)
SELECT COUNT(*) + 1 INTO :v_seq FROM PRD_RAW.OPS.LOAD_LOG
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap;
INSERT INTO PRD_RAW.OPS.LOAD_LOG (JOB_ID, MONITOR_RUN_ID, DATASET, SNAPSHOT_DATE, ATTEMPT_NO, STATUS, ROWS_LOADED, MESSAGE, BATCH_ID, SESSION_ID)
SELECT :v_job_id, :P_MONITOR_RUN_ID, :v_dataset, :v_snap, :v_seq, 'PENDING', 0, 'started', :v_batch, CURRENT_SESSION()::NUMBER;
-- 5) Overwrite requested but not allowed for this job: permanent error, nothing is touched
IF (:P_OVERWRITE AND NOT :v_allow_ow) THEN
v_msg := 'overwrite is not allowed for job ' || P_JOB_NAME;
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'ERROR', MESSAGE = :v_msg, ERROR_CODE = -20004, IS_RETRYABLE = FALSE,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE e_no_overwrite;
END IF;
-- 6) Look for the marker file
EXECUTE IMMEDIATE 'LIST ' || v_path || v_marker;
SELECT COUNT(*) INTO :v_ready FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
-- 7) No marker: feed not ready, update the log row and fail the run (retryable)
IF (v_ready = 0) THEN
v_msg := 'no ' || v_marker || ' marker for ' || v_dataset || ' ' || v_date;
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'SKIPPED', MESSAGE = :v_msg, IS_RETRYABLE = TRUE, BATCH_ID = NULL,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE e_not_ready;
END IF;
-- 8) Marker found: the load starts now
UPDATE PRD_RAW.OPS.LOAD_LOG SET STATUS = 'RUNNING', MESSAGE = 'loading'
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
-- 9) Compare the marker with the CSV: exactly one CSV, same file name, same row count
-- (no CSV at all is not a mismatch: the file has not arrived yet, retryable)
-- (marker read with the JSON format; CSV rows counted with the job's file format, header skipped)
EXECUTE IMMEDIATE 'SELECT $1:file::VARCHAR, $1:rows::NUMBER FROM ' || v_path
|| ' (FILE_FORMAT => ''' || v_marker_ff || ''', PATTERN => ''.*' || REPLACE(v_marker, '.', '[.]') || ''')';
SELECT $1, $2 INTO :v_mfile, :v_mrows FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
EXECUTE IMMEDIATE 'LIST ' || v_path || ' PATTERN = ''' || v_pattern || '''';
SELECT COUNT(*), MAX(SPLIT_PART("name", '/', -1)) INTO :v_cfiles, :v_cfile
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
-- Marker is there but the data file is not (yet): same as a missing marker, retryable
IF (v_cfiles = 0) THEN
v_msg := 'marker found but no data file (' || v_pattern || ') yet for ' || v_dataset || ' ' || v_date;
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'SKIPPED', MESSAGE = :v_msg, IS_RETRYABLE = TRUE, BATCH_ID = NULL,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE e_not_ready;
END IF;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_path
|| ' (FILE_FORMAT => ''' || v_ff || ''', PATTERN => ''' || v_pattern || ''')';
SELECT $1 INTO :v_crows FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
IF (v_cfiles <> 1 OR v_mfile <> v_cfile OR v_mrows <> v_crows) THEN
v_msg := v_dataset || ' ' || v_date || ': marker says file ' || COALESCE(v_mfile, 'NULL') || ', '
|| COALESCE(v_mrows::VARCHAR, 'NULL') || ' rows; found ' || v_cfiles || ' CSV file(s), last '
|| COALESCE(v_cfile, 'none') || ', ' || v_crows || ' rows';
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'ERROR', MESSAGE = :v_msg, ERROR_CODE = -20002, IS_RETRYABLE = FALSE,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE e_mismatch;
END IF;
-- 10) Read the target table layout: source columns = those not starting with '_' and not listed in
-- EXCLUDE_COLUMNS, in table order; the load metadata columns are added below,
-- _SNAPSHOT_DATE only if the table has it
EXECUTE IMMEDIATE 'SELECT LISTAGG(IFF(IS_SRC, COLUMN_NAME, NULL), '', '') WITHIN GROUP (ORDER BY ORDINAL_POSITION), '
|| 'COUNT_IF(IS_SRC), COUNT_IF(COLUMN_NAME = ''_SNAPSHOT_DATE'') FROM ('
|| 'SELECT COLUMN_NAME, ORDINAL_POSITION, (LEFT(COLUMN_NAME, 1) <> ''_'' AND NOT ARRAY_CONTAINS('
|| 'UPPER(COLUMN_NAME)::VARIANT, SPLIT(REPLACE(UPPER(''' || REPLACE(COALESCE(v_exclude, ''), '''', '''''') || '''), '' '', ''''), '','')'
|| ')) AS IS_SRC FROM ' || v_db || '.INFORMATION_SCHEMA.COLUMNS '
|| 'WHERE TABLE_SCHEMA = ''' || v_schema || ''' AND TABLE_NAME = ''' || v_table || ''')';
SELECT $1, $2, $3 INTO :v_cols, :v_ncols, :v_has_snap FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
IF (COALESCE(v_ncols, 0) = 0) THEN
v_msg := 'target table ' || v_target || ' not found or has no source columns';
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'ERROR', MESSAGE = :v_msg, ERROR_CODE = -20003, IS_RETRYABLE = FALSE,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE e_config;
END IF;
-- 11) Build the statements: positional $1..$n for the source columns (excluded columns get their default),
-- then the metadata columns
SELECT LISTAGG(' || VALUE, ', ') WITHIN GROUP (ORDER BY VALUE) INTO :v_sel
FROM TABLE(FLATTEN(ARRAY_GENERATE_RANGE(1, :v_ncols + 1)));
v_ins := v_cols || ', _SOURCE_FILE, _FILE_ROW_NUMBER, _BATCH_ID';
v_sel := v_sel || ', METADATA$FILENAME, METADATA$FILE_ROW_NUMBER, ''' || v_batch || '''';
IF (v_has_snap > 0) THEN
v_ins := v_ins || ', _SNAPSHOT_DATE';
v_sel := v_sel || ', TO_DATE(''' || v_date || ''')';
END IF;
v_sql := 'COPY INTO ' || v_target || ' (' || v_ins || ') FROM (SELECT ' || v_sel || ' FROM ' || v_path || ')'
|| ' FILE_FORMAT = (FORMAT_NAME = ''' || v_ff || ''') PATTERN = ''' || v_pattern || ''''
|| ' ON_ERROR = ABORT_STATEMENT FORCE = ' || IFF(:P_OVERWRITE, 'TRUE', 'FALSE');
-- rows of a date are found by their source file path (works for every table, with or without _SNAPSHOT_DATE)
v_del_sql := 'DELETE FROM ' || v_target || ' WHERE _SOURCE_FILE LIKE ''%snapshot_date=' || v_date || '/%''';
-- 12) Keep the generated SQL in the log row (easy to copy and run by hand)
UPDATE PRD_RAW.OPS.LOAD_LOG
SET SQL_TEXT = IFF(:P_OVERWRITE, :v_del_sql || ';\n', '') || :v_sql || ';'
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
-- 13) Overwrite only: remove the date's existing rows (rolled back if the load fails)
IF (:P_OVERWRITE) THEN
BEGIN TRANSACTION;
EXECUTE IMMEDIATE :v_del_sql;
SELECT $1 INTO :v_deleted FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
END IF;
-- 14) Load the date's CSV (already-loaded files are skipped unless overwriting)
EXECUTE IMMEDIATE :v_sql;
-- 15) Count the rows this run loaded (0 when the files were already loaded)
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_target || ' WHERE _BATCH_ID = ''' || v_batch || '''';
SELECT $1 INTO :v_rows FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
IF (:P_OVERWRITE) THEN
COMMIT;
END IF;
-- 16) Update the log row and return the result
v_msg := v_dataset || ' loaded for ' || v_date;
IF (:P_OVERWRITE) THEN
v_msg := v_msg || ' (overwrite, ' || v_deleted || ' old rows deleted)';
END IF;
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'OK', ROWS_LOADED = :v_rows, MESSAGE = :v_msg, FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RETURN 'OK: ' || v_msg;
EXCEPTION
-- Already logged above (not ready, mismatch, config, overwrite not allowed): just fail the run
WHEN e_not_ready OR e_mismatch OR e_config OR e_no_overwrite THEN
RAISE;
-- Any other failure (missing stage, bad file, COPY error): roll back, log it, fail the run.
-- IS_RETRYABLE is FALSE for every error for now; once real SQLCODEs show up in LOAD_LOG,
-- set it TRUE for the transient ones (timeouts, lock conflicts) so the monitor retries them.
WHEN OTHER THEN
IF (:P_OVERWRITE) THEN
ROLLBACK;
END IF;
v_msg := COALESCE(v_dataset, P_JOB_NAME) || ' load failed for ' || COALESCE(v_date, 'unknown date')
|| ' - ' || SQLERRM;
UPDATE PRD_RAW.OPS.LOAD_LOG
SET STATUS = 'ERROR', MESSAGE = :v_msg, ERROR_CODE = :SQLCODE, IS_RETRYABLE = FALSE,
FINISHED_AT = CURRENT_TIMESTAMP()
WHERE JOB_ID = :v_job_id AND SNAPSHOT_DATE = :v_snap AND ATTEMPT_NO = :v_seq;
RAISE;
END;
$;