BankOrg Docs

In progress

BankOrg Data Platform / Data Modeling / RAW

OPS

Interactive physical model of the PRD_RAW.OPS schema: • the job configuration (load_job_config) • the load log (load_log) • the monitor log (monitor_log)

The OPS schema in PRD_RAW holds the operational metadata of the load jobs: their configuration, the log of every load run and the log of the monitor.

Banking Data Architecture Explorer

drag tables · scroll to zoom

Table DDL

The OPS tables are created by snowflake/13_create_raw_ops_job_config_and_log.sql, after the schema is created in 11_create_raw_db.sql. LOAD_JOB_CONFIG is seeded with the four core banking jobs.

snowflake/13_create_raw_ops_job_config_and_log.sql (LOAD_JOB_CONFIG)
-- Job configuration: one row per load job, read by the load procedure and the monitor.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

CREATE TABLE IF NOT EXISTS LOAD_JOB_CONFIG (
    JOB_ID                NUMBER        AUTOINCREMENT COMMENT 'Surrogate key, generated on insert',
    JOB_NAME              VARCHAR       NOT NULL COMMENT 'Job name, e.g. LOAD_BRANCHES. To change a job: disable the row (ENABLED = FALSE) and insert a new one',
    DATASET               VARCHAR       NOT NULL COMMENT 'Feed name, written to LOAD_LOG.DATASET',
    ENABLED               BOOLEAN       DEFAULT TRUE COMMENT 'FALSE = job and monitor ignore it',

    -- source (GCS)
    SOURCE_STAGE          VARCHAR       COMMENT 'External stage holding the feeds',
    SOURCE_FOLDER         VARCHAR       COMMENT 'Dataset folder; files live in <folder>/snapshot_date=YYYY-MM-DD/',
    FILE_FORMAT           VARCHAR       COMMENT 'File format used by COPY INTO',
    FILE_PATTERN          VARCHAR       COMMENT 'Regex (not a glob) of the data files, matched against the file path, e.g. .*branches[^/]*[.]csv; keeps the marker and stray files out',
    MARKER_FILE           VARCHAR       COMMENT 'Readiness marker file name in the date folder',

    -- target
    TARGET_DATABASE       VARCHAR       COMMENT 'Database of the RAW table loaded by COPY INTO',
    TARGET_SCHEMA         VARCHAR       COMMENT 'Schema of the RAW table',
    TARGET_TABLE          VARCHAR       COMMENT 'Name of the RAW table',
    EXCLUDE_COLUMNS       VARCHAR       COMMENT 'Comma separated target columns the load must not fill from the CSV, e.g. COL_A, COL_B. NULL = none. Columns starting with _ are always excluded (load metadata)',

    -- run rules
    ALLOW_OVERWRITE       BOOLEAN       DEFAULT FALSE COMMENT 'TRUE = a manual CALL with overwrite may delete and reload a date',
    STALE_AFTER_MINUTES   NUMBER        DEFAULT 60 COMMENT 'A PENDING / RUNNING log row older than this is treated as dead',

    -- monitor / retry rules
    MAX_RETRIES           NUMBER        DEFAULT 5 COMMENT 'Attempts per date before the monitor gives up and alerts',
    RETRY_BACKOFF_MINUTES NUMBER        DEFAULT 30 COMMENT 'Minimum wait after a failed attempt before the monitor retries',
    RETRY_WINDOW_DAYS     NUMBER        DEFAULT 7 COMMENT 'Monitor looks at the last N dates only',
    ALERT_ENABLED         BOOLEAN       DEFAULT TRUE COMMENT 'TRUE = send an alert when the job gives up or fails permanently',

    NOTES                 VARCHAR,
    CREATED_AT            TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
    UPDATED_AT            TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP(),
    -- the key is informational only, Snowflake does not enforce it
    CONSTRAINT PK_LOAD_JOB_CONFIG PRIMARY KEY (JOB_ID)
)
COMMENT = 'Load job configuration, one row per job';
snowflake/13_create_raw_ops_job_config_and_log.sql (MONITOR_LOG)
-- Monitor run log: one row per run of the monitor procedure.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

CREATE OR REPLACE TABLE MONITOR_LOG (
    RUN_ID            NUMBER AUTOINCREMENT COMMENT 'Monitor run number',
    STARTED_AT        TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'When the monitor run started',
    FINISHED_AT       TIMESTAMP_LTZ COMMENT 'When the run ended, NULL while running or if it crashed',
    STATUS            VARCHAR        COMMENT 'RUNNING / OK / ERROR (ERROR = the monitor itself failed, see MESSAGE)',
    ABANDONED_CLOSED  NUMBER         COMMENT 'PENDING / RUNNING log rows closed as abandoned',
    DATES_CHECKED     NUMBER         COMMENT 'Job + date pairs examined',
    RETRY_CANDIDATES  NUMBER         COMMENT 'Pairs in state RETRY at the start of the run',
    RETRIED           NUMBER         COMMENT 'LOAD_DATASET calls made',
    RETRIED_OK        NUMBER         COMMENT 'Retries that ended OK',
    RETRIED_FAILED    NUMBER         COMMENT 'Retries that raised an error',
    ALERTS            NUMBER         COMMENT 'Dates alerted in this run (permanent error or gave up)',
    EMAIL_STATUS      VARCHAR        COMMENT 'sent / no alerts / not configured / FAILED: <error>',
    STOPPED_EARLY     BOOLEAN        COMMENT 'TRUE = stopped retrying (failures in a row or per-run cap)',
    MESSAGE           VARCHAR        COMMENT 'Summary text or the error message',
    CONSTRAINT PK_MONITOR_LOG PRIMARY KEY (RUN_ID)  -- informational only, Snowflake does not enforce it
)
COMMENT = 'Monitor run log, one row per run of MONITOR_LOAD_JOBS';
snowflake/13_create_raw_ops_job_config_and_log.sql (LOAD_LOG)
-- Run log: one row per load procedure run, status updated as the run progresses. Created last, it points to the two tables above.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA OPS;

CREATE OR REPLACE TABLE LOAD_LOG (
    LOG_ID          NUMBER AUTOINCREMENT,
    JOB_ID          NUMBER NOT NULL COMMENT 'Job that produced this run, FK to LOAD_JOB_CONFIG.JOB_ID',
    MONITOR_RUN_ID  NUMBER         COMMENT 'Monitor run that started this attempt, FK to MONITOR_LOG.RUN_ID; NULL = started by the load task or by hand',
    DATASET         VARCHAR        COMMENT 'Feed name, e.g. branches / customers / accounts / transactions',
    SNAPSHOT_DATE   DATE           COMMENT 'Business date that was loaded',
    ATTEMPT_NO      NUMBER         COMMENT 'Attempt number for this DATASET + SNAPSHOT_DATE: 1 = first run, 2+ = later runs (retries, manual reruns)',
    STATUS          VARCHAR        COMMENT 'PENDING (started) / RUNNING (loading) / OK / SKIPPED / ERROR',
    ROWS_LOADED     NUMBER         COMMENT 'Rows loaded by COPY INTO, 0 when skipped or failed',
    MESSAGE         VARCHAR        COMMENT 'Result text or error message',
    ERROR_CODE      NUMBER         COMMENT 'SQLCODE of the failure, NULL when OK or SKIPPED',
    IS_RETRYABLE    BOOLEAN        COMMENT 'TRUE = the monitor may retry (missing marker, transient error); FALSE = needs a fix first; NULL when OK',
    BATCH_ID        VARCHAR        COMMENT 'Same value as RAW _BATCH_ID, NULL when nothing was loaded',
    LOGGED_AT       TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'When the attempt started (row inserted)',
    FINISHED_AT     TIMESTAMP_LTZ COMMENT 'When the attempt ended, NULL while PENDING / RUNNING',
    SQL_TEXT        VARCHAR        COMMENT 'Generated DELETE (overwrite only) and COPY INTO statements of this attempt',
    ALERTED_AT      TIMESTAMP_LTZ COMMENT 'Set by the monitor when it alerted about this row (permanent error or gave up), so it alerts once',
    SESSION_ID      NUMBER         COMMENT 'Snowflake session that runs the attempt (CURRENT_SESSION()); a PENDING / RUNNING row whose session still has a running query is alive, however old it is',
    -- keys are informational only, Snowflake does not enforce them
    CONSTRAINT PK_LOAD_LOG PRIMARY KEY (LOG_ID),
    CONSTRAINT FK_LOAD_LOG_JOB FOREIGN KEY (JOB_ID) REFERENCES LOAD_JOB_CONFIG (JOB_ID),
    CONSTRAINT FK_LOAD_LOG_MONITOR FOREIGN KEY (MONITOR_RUN_ID) REFERENCES MONITOR_LOG (RUN_ID)
)
COMMENT = 'Load job run log, one row per procedure run, status updated as the run progresses';