BankOrg Docs

In progress

BankOrg Data Platform / Data Engineering

Staging

The four dbt staging views over RAW in PRD_STAGING.CORE_BANKING, how they are tested, and the daily GitHub Actions job that builds them, followed by the snapshots, the silver models and the gold marts.

The staging layer is four dbt views in PRD_STAGING.CORE_BANKING, one per RAW table in PRD_RAW.CORE_BANKING. RAW is the bronze layer; staging types and cleans it without business rules.

Create the database

Create the PRD_STAGING database and the CORE_BANKING schema as SYSADMIN (script 21_create_staging_db.sql), and let DBT_ROLE create views in it. The views themselves are created by dbt.

Snowflake
USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS PRD_STAGING;
CREATE SCHEMA IF NOT EXISTS PRD_STAGING.CORE_BANKING;

GRANT USAGE ON DATABASE PRD_STAGING TO ROLE DBT_ROLE;
GRANT USAGE, CREATE VIEW, CREATE TABLE ON SCHEMA PRD_STAGING.CORE_BANKING TO ROLE DBT_ROLE;

Configure dbt

In dbt_project.yml the staging models are views in PRD_STAGING, schema CORE_BANKING.

dbt/dbt_project.yml
models:
  bankorg:
    staging:
      core_banking:
        +materialized: view
        +database: PRD_STAGING
        +schema: CORE_BANKING

Models

ModelGrainWhat it does
stg_core_banking__branchesOne row per branch_codeLatest snapshot, typed dates, row_hash
stg_core_banking__customersOne row per customer_idLatest snapshot, typed dates, row_hash
stg_core_banking__accountsOne row per account_idLatest snapshot, typed numbers and dates, row_hash
stg_core_banking__transactionsOne row per change event of one side of a posting legTyped amount and timestamps, leg_no, direction, is_latest_event flag

Branches, customers and accounts read the latest _SNAPSHOT_DATE of RAW and keep one row per key; when a snapshot was loaded twice, the row of the latest load wins. Transactions keep every change event, and an event delivered twice keeps one row.

Transaction legs

A transaction can post to several accounts, so RAW TRANSACTIONS carries LEG_NO and DIRECTION after TRANSACTION_ID. The staging view reads leg_no as a number (an empty value is leg 1) and direction as DR or CR (an empty value is CR for deposits, incoming transfers and interest, and DR for the other types). An event is identified by transaction_id, leg_no, direction, change_ts and change_seq, and is_latest_event is ranked per transaction_id, leg_no and direction (ADR 0003).

Row hash

Branches, customers and accounts carry a row_hash made with dbt_utils.generate_surrogate_key. The SCD Type 2 snapshots (ADR 0001) open a new version when it changes.

stg_core_banking__customers.sql
{{ dbt_utils.generate_surrogate_key([
    'first_name', 'last_name', 'date_of_birth', 'national_id', 'email', 'phone',
    'address_line', 'city', 'province', 'branch_code', 'segment', 'kyc_status',
    'customer_status', 'open_date'
]) }} as row_hash
ModelColumns in the hash
branchesbranch_name, region, city, province, address_line, branch_type, branch_status, open_date, close_date
customersfirst_name, last_name, date_of_birth, national_id, email, phone, address_line, city, province, branch_code, segment, kyc_status, customer_status, open_date
accountscustomer_id, account_number, account_type, account_status, currency, credit_limit, interest_rate, branch_code, open_date, close_date (current_balance is left out because it changes every day)

Tests

dbt build --select staging ran 4 models, 54 tests and 5 unit tests, and all passed. dbt source freshness passed for the four sources. After LEG_NO and DIRECTION were added, dbt build -s stg_core_banking__transactions ran 1 model, 18 tests and 5 unit tests, and all passed.

KindWhat is checked
Keys and relationsunique and not_null on each key; relationships between accounts, customers, branches and transactions
Allowed valuesaccepted_values on segment, KYC status, customer status, account type, account status, currency, transaction type, status, channel and change type
Cast failurescast_not_lost: a typed column is NULL while the RAW value is filled (dates, balances, amounts, timestamps, change_seq)
Value checksamount >= 0, credit_limit >= 0, interest_rate between 0 and 1, close_date >= open_date, date_of_birth between 1900-01-01 and today
Reconciliation with RAWOne singular test per model: the row count equals the distinct keys of the latest RAW snapshot (distinct events for transactions)
Unit testsLatest snapshot wins, a repeated load keeps one row, values are trimmed and typed with a bad value becoming NULL, a repeated event keeps one row, is_latest_event marks the last event
Source freshness_LOADED_AT of each RAW table: warn after 24 hours, error after 48 hours

Daily dbt job

The GitHub Actions workflow Daily dbt (daily-dbt-staging.yml) is started at 05:30 Vietnam time (22:30 UTC) by the Cloudflare Worker bankorg-workflow-trigger, right after the 05:00 load tasks. It runs one job per dataset: branches, customers, accounts and transactions.

  • Each job waits for its own RAW load. It calls the macro assert_dataset_loaded, which fails unless OPS.LOAD_LOG has an OK row for the dataset and date, and tries again every 5 minutes for up to 25 attempts.
  • When the load is there, the job builds only its own staging model. The relationships tests and unit tests are left out of this step.
  • After the four jobs pass, a job runs the relationships tests of the staging models (path:models/staging) and dbt source freshness.
  • When those pass, a last job builds the seed, the snapshots and the marts (path:snapshots path:seeds path:models/marts) with their tests, so Silver and Gold follow the day's staging.
Wait for the load of branches
dbt run-operation assert_dataset_loaded --target prod \
  --args "{dataset: branches, snapshot_date: 2026-10-18}"
Build branches
dbt build --target prod \
  --select stg_core_banking__branches \
  --exclude "test_name:relationships" \
  --exclude-resource-type unit_test

The date is yesterday in Vietnam time, or the date typed when the workflow is started by hand. The workflow connects to Snowflake as DBT_USER with six repository secrets:

SecretValue
SNOWFLAKE_ACCOUNTAccount identifier
SNOWFLAKE_USERDBT_USER
SNOWFLAKE_PRIVATE_KEYContents of the encrypted private key; the workflow writes it to a temporary file
SNOWFLAKE_PRIVATE_KEY_PASSPHRASEPassphrase of the key
SNOWFLAKE_ROLEDBT_ROLE
SNOWFLAKE_WAREHOUSEDBT_WH