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.
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.
models:
bankorg:
staging:
core_banking:
+materialized: view
+database: PRD_STAGING
+schema: CORE_BANKINGModels
| Model | Grain | What it does |
|---|---|---|
| stg_core_banking__branches | One row per branch_code | Latest snapshot, typed dates, row_hash |
| stg_core_banking__customers | One row per customer_id | Latest snapshot, typed dates, row_hash |
| stg_core_banking__accounts | One row per account_id | Latest snapshot, typed numbers and dates, row_hash |
| stg_core_banking__transactions | One row per change event of one side of a posting leg | Typed 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.
{{ 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| Model | Columns in the hash |
|---|---|
| branches | branch_name, region, city, province, address_line, branch_type, branch_status, open_date, close_date |
| customers | first_name, last_name, date_of_birth, national_id, email, phone, address_line, city, province, branch_code, segment, kyc_status, customer_status, open_date |
| accounts | customer_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.
| Kind | What is checked |
|---|---|
| Keys and relations | unique and not_null on each key; relationships between accounts, customers, branches and transactions |
| Allowed values | accepted_values on segment, KYC status, customer status, account type, account status, currency, transaction type, status, channel and change type |
| Cast failures | cast_not_lost: a typed column is NULL while the RAW value is filled (dates, balances, amounts, timestamps, change_seq) |
| Value checks | amount >= 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 RAW | One singular test per model: the row count equals the distinct keys of the latest RAW snapshot (distinct events for transactions) |
| Unit tests | Latest 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 unlessOPS.LOAD_LOGhas anOKrow 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
relationshipstests and unit tests are left out of this step. - After the four jobs pass, a job runs the
relationshipstests of the staging models (path:models/staging) anddbt 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.
dbt run-operation assert_dataset_loaded --target prod \
--args "{dataset: branches, snapshot_date: 2026-10-18}"dbt build --target prod \
--select stg_core_banking__branches \
--exclude "test_name:relationships" \
--exclude-resource-type unit_testThe 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:
| Secret | Value |
|---|---|
| SNOWFLAKE_ACCOUNT | Account identifier |
| SNOWFLAKE_USER | DBT_USER |
| SNOWFLAKE_PRIVATE_KEY | Contents of the encrypted private key; the workflow writes it to a temporary file |
| SNOWFLAKE_PRIVATE_KEY_PASSPHRASE | Passphrase of the key |
| SNOWFLAKE_ROLE | DBT_ROLE |
| SNOWFLAKE_WAREHOUSE | DBT_WH |