BankOrg Docs

In progress

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

ParameterTypeMeaning
P_JOB_NAMEVARCHARJob 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_DATEDATEBusiness date to load, i.e. the folder snapshot_date=YYYY-MM-DD. No offset is applied; the load tasks pass yesterday.
P_OVERWRITEBOOLEAN, default FALSETRUE 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_IDNUMBER, default NULLSet 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

StepWhat happens
1. Read the configAn unknown job name raises an error. A disabled job returns SKIPPED without a log row.
2. Already loadedIf 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 guardIf 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 attemptInsert a PENDING row in LOAD_LOG with the next ATTEMPT_NO, the batch id and SESSION_ID = CURRENT_SESSION().
5. Overwrite checkP_OVERWRITE on a job without ALLOW_OVERWRITE is a permanent ERROR, e.g. for LOAD_TRANSACTIONS.
6. MarkerLIST the marker file of the date. Missing: the row becomes SKIPPED (retryable) and an error is raised.
7. RunningThe row becomes RUNNING.
8. Marker against CSVNo 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. ColumnsThe 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 SQLThe COPY INTO (and the DELETE for an overwrite) is built and stored in LOAD_LOG.SQL_TEXT.
11. OverwriteBEGIN TRANSACTION and delete the rows whose _SOURCE_FILE is in the date's folder.
12. COPY INTOLoad the date's CSV (FORCE = TRUE for an overwrite).
13. FinishCount 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 codeCauseRetryable
-20001Marker or data file missingYes, logged as SKIPPED
-20002Marker does not match the CSV (file name, row count or number of files)No
-20003Job configuration problem: unknown job, or target table not foundNo
-20004Overwrite not allowed for the jobNo
otherAny other failure, e.g. a COPY INTO error; rolled back for an overwriteNo, for now

Calls run

Initial load of the 2026-10-18 branches:

Snowflake
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:

Snowflake
CALL PRD_RAW.OPS.LOAD_DATASET('LOAD_BRANCHES', '2026-10-18'::DATE);
-- SKIPPED: LOAD_BRANCHES 2026-10-18 is already loaded

Overwrite of the same date:

Snowflake
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

snowflake/15_create_proc_load_dataset.sql
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;
$;