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