BankOrg Docs

In progress

BankOrg Data Platform / Data Modeling / RAW

Core Banking

Interactive physical model of the PRD_RAW.CORE_BANKING schema: tables, columns, keys and relationships.

Pick a table in the explorer or on the canvas to see its columns, keys and relationships; drag tables to rearrange them and scroll to zoom.

Banking Data Architecture Explorer

drag tables · scroll to zoom

Table DDL

The RAW tables are created by the scripts in the snowflake/ folder. All source columns are VARCHAR, and each table carries load metadata columns.

snowflake/12_create_raw_core_banking_tables.sql (BRANCHES)
-- Landing table for the branches reference snapshot (CSV in GCS).
-- Append-only; all source columns are VARCHAR so a bad value never fails the load.
-- Used for the branch_code relationship test and the row access policy mapping.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA CORE_BANKING;

CREATE TABLE IF NOT EXISTS BRANCHES (
    -- source columns (as delivered in the CSV)
    BRANCH_CODE        VARCHAR,
    BRANCH_NAME        VARCHAR,
    REGION             VARCHAR        COMMENT 'e.g. NORTH / CENTRAL / SOUTH',
    CITY               VARCHAR,
    PROVINCE           VARCHAR,
    ADDRESS_LINE       VARCHAR,
    BRANCH_TYPE        VARCHAR        COMMENT 'e.g. HEAD_OFFICE / FULL_SERVICE / SATELLITE',
    BRANCH_STATUS      VARCHAR        COMMENT 'e.g. ACTIVE / CLOSED',
    OPEN_DATE          VARCHAR,
    CLOSE_DATE         VARCHAR,

    -- load metadata
    _SNAPSHOT_DATE     DATE           COMMENT 'Business date of the feed',
    _SOURCE_FILE       VARCHAR        COMMENT 'METADATA$FILENAME',
    _FILE_ROW_NUMBER   NUMBER         COMMENT 'METADATA$FILE_ROW_NUMBER',
    _LOADED_AT         TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'Load time',
    _BATCH_ID          VARCHAR        COMMENT 'Run identifier, used for idempotency checks'
)
COMMENT = 'RAW branches reference snapshot, append-only, one full snapshot per _SNAPSHOT_DATE';

-- Time Travel retention for backfill / restore demo (stateful merger pattern)
ALTER TABLE BRANCHES SET DATA_RETENTION_TIME_IN_DAYS = 7;
snowflake/12_create_raw_core_banking_tables.sql (CUSTOMERS)
-- Landing table for the daily customers snapshot feed (CSV in GCS).
-- Append-only; all source columns are VARCHAR so a bad value never fails the load.
-- Typing, dedup and row_hash happen in dbt staging.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA CORE_BANKING;

CREATE TABLE IF NOT EXISTS CUSTOMERS (
    -- source columns (as delivered in the CSV)
    CUSTOMER_ID        VARCHAR,
    FIRST_NAME         VARCHAR,
    LAST_NAME          VARCHAR,
    DATE_OF_BIRTH      VARCHAR,
    NATIONAL_ID        VARCHAR,
    EMAIL              VARCHAR,
    PHONE              VARCHAR,
    ADDRESS_LINE       VARCHAR,
    CITY               VARCHAR,
    PROVINCE           VARCHAR,
    BRANCH_CODE        VARCHAR,
    SEGMENT            VARCHAR,
    KYC_STATUS         VARCHAR,
    CUSTOMER_STATUS    VARCHAR,
    OPEN_DATE          VARCHAR,

    -- load metadata
    _SNAPSHOT_DATE     DATE           COMMENT 'Business date of the feed',
    _SOURCE_FILE       VARCHAR        COMMENT 'METADATA$FILENAME',
    _FILE_ROW_NUMBER   NUMBER         COMMENT 'METADATA$FILE_ROW_NUMBER',
    _LOADED_AT         TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'Load time',
    _BATCH_ID          VARCHAR        COMMENT 'Run identifier, used for idempotency checks'
)
COMMENT = 'RAW customers snapshot feed, append-only, one full snapshot per _SNAPSHOT_DATE';

-- Time Travel retention for backfill / restore demo (stateful merger pattern)
ALTER TABLE CUSTOMERS SET DATA_RETENTION_TIME_IN_DAYS = 7;
snowflake/12_create_raw_core_banking_tables.sql (ACCOUNTS)
-- Landing table for the daily accounts snapshot feed (CSV in GCS).
-- Append-only; all source columns are VARCHAR so a bad value never fails the load.
-- Typing, dedup and row_hash happen in dbt staging.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA CORE_BANKING;

CREATE TABLE IF NOT EXISTS ACCOUNTS (
    -- source columns (as delivered in the CSV)
    ACCOUNT_ID         VARCHAR,
    CUSTOMER_ID        VARCHAR        COMMENT 'Links to CUSTOMERS.CUSTOMER_ID',
    ACCOUNT_NUMBER     VARCHAR,
    ACCOUNT_TYPE       VARCHAR        COMMENT 'e.g. SAVINGS / CHECKING / LOAN / CREDIT_CARD',
    ACCOUNT_STATUS     VARCHAR        COMMENT 'e.g. ACTIVE / DORMANT / FROZEN / CLOSED',
    CURRENCY           VARCHAR,
    CURRENT_BALANCE    VARCHAR,
    CREDIT_LIMIT       VARCHAR,
    INTEREST_RATE      VARCHAR,
    BRANCH_CODE        VARCHAR        COMMENT 'Drives the row access policy',
    OPEN_DATE          VARCHAR,
    CLOSE_DATE         VARCHAR,

    -- load metadata
    _SNAPSHOT_DATE     DATE           COMMENT 'Business date of the feed',
    _SOURCE_FILE       VARCHAR        COMMENT 'METADATA$FILENAME',
    _FILE_ROW_NUMBER   NUMBER         COMMENT 'METADATA$FILE_ROW_NUMBER',
    _LOADED_AT         TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'Load time',
    _BATCH_ID          VARCHAR        COMMENT 'Run identifier, used for idempotency checks'
)
COMMENT = 'RAW accounts snapshot feed, append-only, one full snapshot per _SNAPSHOT_DATE';

-- Time Travel retention for backfill / restore demo (stateful merger pattern)
ALTER TABLE ACCOUNTS SET DATA_RETENTION_TIME_IN_DAYS = 7;
snowflake/12_create_raw_core_banking_tables.sql (TRANSACTIONS)
-- Landing table for the transactions CDC feed (CSV delta files in GCS).
-- Append-only: every change event is a new row, nothing is updated or deleted here.
-- Current state is derived downstream (latest event per TRANSACTION_ID).
-- All source columns are VARCHAR so a bad value never fails the load.

USE ROLE SYSADMIN;
USE DATABASE PRD_RAW;
USE SCHEMA CORE_BANKING;

CREATE TABLE IF NOT EXISTS TRANSACTIONS (
    -- source columns (as delivered in the CSV)
    TRANSACTION_ID     VARCHAR,
    LEG_NO             VARCHAR        COMMENT 'Posting leg number inside the transaction (1, 2, ...); empty is read as 1',
    DIRECTION          VARCHAR        COMMENT 'DR = debit, CR = credit; one DR and one CR share a LEG_NO; empty is derived from TRANSACTION_TYPE in staging',
    ACCOUNT_ID         VARCHAR        COMMENT 'Links to ACCOUNTS.ACCOUNT_ID',
    TRANSACTION_TYPE   VARCHAR        COMMENT 'e.g. DEPOSIT / WITHDRAWAL / TRANSFER_IN / TRANSFER_OUT / FEE / INTEREST',
    AMOUNT             VARCHAR,
    CURRENCY           VARCHAR,
    TRANSACTION_TS     VARCHAR        COMMENT 'When the transaction happened at the source',
    CHANNEL            VARCHAR        COMMENT 'e.g. ATM / BRANCH / ONLINE / MOBILE',
    TRANSACTION_STATUS VARCHAR        COMMENT 'e.g. PENDING / POSTED / REVERSED / FAILED',
    COUNTERPARTY_ACCOUNT VARCHAR      WITH TAG (PRD_GOVERNANCE.MASKING.PII = 'identifier'),
    DESCRIPTION        VARCHAR,

    -- CDC columns (as delivered by the source)
    CHANGE_TYPE        VARCHAR        COMMENT 'I = insert, U = update, D = delete',
    CHANGE_TS          VARCHAR        COMMENT 'When the change was made at the source',
    CHANGE_SEQ         VARCHAR        COMMENT 'Source sequence number, orders events with the same CHANGE_TS',

    -- load metadata
    _SNAPSHOT_DATE     DATE           COMMENT 'Business date of the delta (snapshot_date= folder)',
    _SOURCE_FILE       VARCHAR        COMMENT 'METADATA$FILENAME',
    _FILE_ROW_NUMBER   NUMBER         COMMENT 'METADATA$FILE_ROW_NUMBER',
    _LOADED_AT         TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT 'Load time',
    _BATCH_ID          VARCHAR        COMMENT 'Run identifier, used for idempotency checks'
)
COMMENT = 'RAW transactions CDC feed, append-only, one row per change event of one side (DR or CR) of a posting leg';

-- Time Travel retention for backfill / restore demo (stateful merger pattern)
ALTER TABLE TRANSACTIONS SET DATA_RETENTION_TIME_IN_DAYS = 7;

-- Stream that downstream tasks consume. APPEND_ONLY is enough because
-- this table only ever receives inserts.
CREATE STREAM IF NOT EXISTS TRANSACTIONS_STREAM
    ON TABLE TRANSACTIONS
    APPEND_ONLY = TRUE
    COMMENT = 'Change stream over RAW.TRANSACTIONS, consumed by the CDC task';