BankOrg Docs

In progress

BankOrg Data Platform

Architecture

The data layers of the platform and how data moves between them.

Data layers

The platform is organised in four layers. Data moves from RAW through Staging and Silver to Gold.

  1. RAW

    PRD_RAW database, the bronze layer. Source data as loaded: snapshots and CDC change events.

  2. Staging

    PRD_STAGING database. dbt views: typed, latest snapshot, deduplicated, with a row hash.

  3. Silver

    PRD_SILVER database. dbt snapshots (SCD Type 2), the core dimensions, the transaction fact and the account balance facts.

  4. Gold

    PRD_GOLD database. Four business marts, one schema per domain: account, transaction, branch and customer.

Solid border: documented. Dashed: placeholder in the navigation.

RAW layer

RAW is the bronze layer of the medallion architecture. It lives in the PRD_RAW database, with one schema per source domain. CORE_BANKING is the one modeled so far; the PAYMENTS and CARDS schemas are listed but have no tables documented yet.

The source domain is Core Banking, loaded into PRD_RAW.CORE_BANKING as append-only tables with VARCHAR source columns: BRANCHES, CUSTOMERS and ACCOUNTS as daily full snapshots keyed by _SNAPSHOT_DATE, and TRANSACTIONS as change data capture (CDC) events.

Loading patterns

Each RAW table is tagged with how it is loaded:

  • Reference snapshot: small reference data, loaded as a snapshot.
  • Daily snapshot feed: a snapshot loaded every day, carrying a snapshot_date.
  • CDC change events: change events carrying a change type (insert, update, delete) and a change timestamp.

See the table-by-table view in RAW.

Staging layer

Staging lives in the PRD_STAGING database, schema CORE_BANKING. It has four dbt views, one per RAW table: the source columns are typed, branches, customers and accounts keep the latest snapshot with a row_hash, and transactions keep every change event. See the views and their columns in Staging: Core Banking, and the models, tests and daily dbt job in Staging: Data Engineering.

Silver layer

Silver lives in the PRD_SILVER database. The schema SNAPSHOTS holds the SCD Type 2 history of branches, customers and accounts, built with dbt snapshot. The schema CORE holds the dimensions dim_branch, dim_customer and dim_account, built from the snapshots, and the fact fact_transaction, an incremental merge with one row per side of a posting leg. The two balance facts, fact_account_balance_hist and fact_account_balance_daily, are in the same schema. See the tables in Silver: Snapshots and Silver: Core, and the models and tests in Silver: Data Engineering.

Gold layer

Gold lives in the PRD_GOLD database, one schema per business domain. It holds four marts that sum the silver facts by day, branch, product group and customer: daily_account_balance in ACCOUNT, daily_transaction_summary in TRANSACTION, branch_performance in BRANCH and customer_360 in CUSTOMER. The two balance facts they use stay in PRD_SILVER.CORE, and Gold reads only from Silver. A loan and a deposit are never added together, and no gold table has a PII column. See the tables in Gold: Data Modeling, how they are built in Gold: Data Engineeringand the Dashboard.

Daily flow

Times are Vietnam time.

Daily RAW and dbt jobsAt 00:30 a Cloudflare cron starts the Daily feed workflow, which uploads the CSV and marker files to GCS. At 05:00 four Snowflake load tasks call LOAD_DATASET, which reads GCS and fills the RAW tables. A monitor task runs the procedure MONITOR_LOAD_JOBS every hour from 06:00 to 23:00 to retry failed loads. The monitor and LOAD_DATASET call IS_SESSION_ALIVE to tell whether an old log row is still running. At 05:30 a Cloudflare cron starts the Daily dbt workflow: four staging jobs wait for their load, then the staging checks run, then the snapshots and the silver models are built, and then the four gold marts.00:3005:00hourly05:30Cron triggerCloudflare WorkerDaily feedgenerate + uploadGCS landingCSV + marker file4 load tasksSnowflake tasksLOAD_DATASETreads GCS, COPY INTORAW tables4 tables + LOAD_LOGMonitor taskSnowflake, 06-23hRetry monitorMONITOR_LOAD_JOBSSession checkIS_SESSION_ALIVECron triggerCloudflare Worker4 staging jobswait for OK, buildStaging checkstests + freshnessSnapshots, silverSCD2, dims, facts, balancesGold marts4 marts + testsCloudflare cronRAW: GCS and Snowflakedbt: GitHub Actions
Times are Vietnam time. The arrow from the retry monitor to LOAD_DATASET is a retry. Both procedures call IS_SESSION_ALIVE: a log row older than the stale limit is closed as abandoned only when its session has no running query. The arrow from the RAW tables to the staging jobs is the wait for the load.
TimeWhat runsPage
00:30Cloudflare Worker starts the GitHub Actions workflow Daily feed: generate the day and upload it to GCSDaily feed job
05:00Snowflake tasks: load each feed into RAWDaily load tasks
05:30Cloudflare Worker starts the GitHub Actions workflow Daily dbt: build and test staging, each dataset once its RAW load is OK, then the relationships tests and source freshness, then the seed, snapshots and silver models, then the four gold marts and the balance-vs-feed testStaging
06:00 to 23:00Snowflake task: hourly monitor, retries failed loads and leaves a long load running while its session is aliveMonitor task

Databases and environments

Sources are always read from PRD_RAW, and the staging views are written to PRD_STAGING.CORE_BANKING. Snapshots and the core marts are written to PRD_SILVER.SNAPSHOTS and PRD_SILVER.CORE, and the gold marts to PRD_GOLD.

Design principles

  • Idempotent: re-running a batch must not duplicate data or change history.
  • Daily: the pipeline runs once a day. The daily feed, the Snowflake load tasks and the dbt job are scheduled separately.
  • Key-pair authentication: dbt connects with a service user and a private key, with no password. Credentials come from environment variables only.

Stack

  • Snowflake as the data platform
  • dbt for transformation, snapshots and tests
  • Python virtual environment with pinned dependencies for running dbt

Key decisions

  • SCD Type 2 with dbt snapshots: history for customers, accounts and branches uses native dbt snapshot with the check strategy on a row_hash column, instead of a hand-written incremental merge. An unchanged hash produces no new version, so a repeated run is a no-op. Recorded in ADR 0001.
  • Incremental merge and the posting-leg grain for the transaction fact: fact_transaction merges on the load time of each event, and its grain is one side (DR or CR) of a posting leg, so a transaction posting to several accounts keeps every posting. Recorded in ADR 0002 and ADR 0003.
  • Balance facts recomputed per affected account, and a gold schema per domain: the two balance facts rebuild only the accounts with a new, changed or deleted posting, and the marts join to the current version of an account. Recorded in ADR 0005 and ADR 0006.
  • RAW is created by Snowflake scripts, not dbt: the DDL is kept as numbered SQL files, and the dbt project does not create RAW tables.
  • Production-style naming: objects use names such as PRD_RAW, PRD_STAGING and PRD_SILVER instead of demo names.
  • One ADR per design pattern: each pattern-driven choice gets an architecture decision record.