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.
RAW
PRD_RAW database, the bronze layer. Source data as loaded: snapshots and CDC change events.
Staging
PRD_STAGING database. dbt views: typed, latest snapshot, deduplicated, with a row hash.
Silver
PRD_SILVER database. dbt snapshots (SCD Type 2), the core dimensions, the transaction fact and the account balance facts.
Gold
PRD_GOLD database. Four business marts, one schema per domain: account, transaction, branch and customer.
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.
| Time | What runs | Page |
|---|---|---|
| 00:30 | Cloudflare Worker starts the GitHub Actions workflow Daily feed: generate the day and upload it to GCS | Daily feed job |
| 05:00 | Snowflake tasks: load each feed into RAW | Daily load tasks |
| 05:30 | Cloudflare 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 test | Staging |
| 06:00 to 23:00 | Snowflake task: hourly monitor, retries failed loads and leaves a long load running while its session is alive | Monitor 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 snapshotwith thecheckstrategy on arow_hashcolumn, 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_transactionmerges 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_STAGINGandPRD_SILVERinstead of demo names. - One ADR per design pattern: each pattern-driven choice gets an architecture decision record.