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.
-- 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';-- 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';-- 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';