BankOrg Data Platform / Data Engineering
Gold
The Gold layer in PRD_GOLD: the product group seed, the two balance facts, the four business marts by day, branch, product group and customer, their tests and the build.
The Gold layer is the database PRD_GOLD, with one schema per business domain. It holds four marts that sum the silver facts by day, branch, product group and customer, so a user does not have to join and aggregate them. The two balance facts they need are built in PRD_SILVER.CORE. The columns and types of the marts are in Gold: Data Modeling, and their figures on the Dashboard.
Create the database
Create the PRD_GOLD database and the four schemas as SYSADMIN (script 41_create_gold_db.sql), and let DBT_ROLE create tables in each.
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS PRD_GOLD;
CREATE SCHEMA IF NOT EXISTS PRD_GOLD.ACCOUNT;
CREATE SCHEMA IF NOT EXISTS PRD_GOLD.TRANSACTION;
CREATE SCHEMA IF NOT EXISTS PRD_GOLD.BRANCH;
CREATE SCHEMA IF NOT EXISTS PRD_GOLD.CUSTOMER;
GRANT USAGE ON DATABASE PRD_GOLD TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_GOLD.ACCOUNT TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_GOLD.TRANSACTION TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_GOLD.BRANCH TO ROLE DBT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE VIEW ON SCHEMA PRD_GOLD.CUSTOMER TO ROLE DBT_ROLE;Configure dbt
In dbt_project.yml each folder of marts is a table in its own PRD_GOLD schema. Gold has no PII column, so the masking post-hook of Silver is not applied.
models:
bankorg:
marts:
account:
+materialized: table
+database: PRD_GOLD
+schema: ACCOUNT
transaction:
+materialized: table
+database: PRD_GOLD
+schema: TRANSACTION
branch:
+materialized: table
+database: PRD_GOLD
+schema: BRANCH
customer:
+materialized: table
+database: PRD_GOLD
+schema: CUSTOMERProduct group
The seed account_product_group maps the account type to a product group. A loan is negative and a deposit positive, so the marts keep them in separate rows or columns and never add them together; the bank's own accounts (INTERNAL) are left out of Gold.
account_type,product_group
SAVINGS,DEPOSIT
CHECKING,DEPOSIT
LOAN,CREDIT
CASH,INTERNAL
INTEREST_EXPENSE,INTERNAL
FEE_INCOME,INTERNALBalance facts
The balance of an account is the sum of its credits minus the sum of its debits (ADR 0004). Two incremental facts in PRD_SILVER.CORE carry it, and each run recomputes only the accounts that have a new, changed or deleted posting (ADR 0005), with delete+insert per account, so a rerun changes nothing.
| Model | Grain | Rows |
|---|---|---|
| fact_account_balance_hist | One row per POSTED, not deleted posting of an account, with the balance after it | 13,666 |
| fact_account_balance_daily | End-of-day balance per account and day, from the first posting to the snapshot date; a day without postings carries the previous balance | 911,596 |
Marts
The marts read only from Silver. An account is joined to its current version in dim_account (is_current), because the valid_from of a snapshot version is the time the snapshot saw the change, not a business date; an account that moves branch moves its whole history to the new branch (ADR 0006). Each mart also carries branch_name, the current name of that branch. daily_account_balance is the model of the first mart:
select
d.balance_date,
a.branch_code,
b.branch_name,
g.product_group,
count(*) as account_count,
sum(d.closing_balance) as total_closing_balance,
sum(d.day_inflow) as total_inflow,
sum(d.day_outflow) as total_outflow
from {{ ref('fact_account_balance_daily') }} d
join {{ ref('dim_account') }} a on a.account_id = d.account_id and a.is_current
join {{ ref('account_product_group') }} g on g.account_type = a.account_type
left join {{ ref('dim_branch') }} b on b.branch_code = a.branch_code and b.is_current
where g.product_group != 'INTERNAL'
group by d.balance_date, a.branch_code, b.branch_name, g.product_group| Table | Grain | What a row holds | Rows |
|---|---|---|---|
| ACCOUNT.daily_account_balance | balance_date, branch_code, product_group | Customer accounts, total closing balance, inflow and outflow, account count | 301,732 |
| TRANSACTION.daily_transaction_summary | transaction_date, branch_code, product_group, transaction_type | Posted and failed counts and the posted amount | 6,689 |
| BRANCH.branch_performance | report_date, branch_code | Deposit and loan balances and account counts next to the day's posted and failed transactions | 194,110 |
| CUSTOMER.customer_360 | customer_id | Open accounts and balances by product group on the latest day, posted activity of the last 30 days; no PII column | 343 |
The gold tables are rebuilt on every run. They are small aggregates, so a rebuild is cheap and always consistent with Silver.
customer_360
One row per current customer, with the open accounts and balances on the latest day of fact_account_balance_daily (the column as_of_date) and the posted transactions of the 30 days up to that day. A customer with no account keeps a row with zeros. It has no name, national ID or contact column.
Tests
| Kind | What is checked |
|---|---|
| Keys | unique_combination_of_columns on the grain of each mart; unique and not_null on customer_id of customer_360 |
| Relationships | branch_code of every mart to dim_branch; customer_id of customer_360 to dim_customer; account_id of the balance facts to dim_account |
| Allowed values | product_group is DEPOSIT or CREDIT; transaction_type is one of the seven types |
| Balance facts | Five singular tests: the daily fact matches the history, has no gap, keeps the sign by product group, and the history matches the postings, the feed and a full rebuild |
| Marts against each other | Per day, daily_account_balance adds up to the silver daily balances, and branch_performance to daily_account_balance and daily_transaction_summary; on the latest day customer_360 adds up to daily_account_balance |
Build
The seed, the snapshots and the marts are built in dependency order by one selection:
dbt build --select path:seeds path:models/martsAfter the schemas were created, the four gold marts were built and tested on their own: 4 models and 45 tests passed.
dbt build --select path:models/marts --exclude path:models/marts/coreThe balances run to the snapshot date 2026-10-31.
Jobs
- The daily workflow
daily-dbt-staging.ymlbuilds the seed, the snapshots and every model undermarts, so the four gold marts are rebuilt after Silver (Staging). The test that compares the balance history with the feed runs after the gold build. - The Dashboard reads
daily_account_balanceanddaily_transaction_summarythrough secure views inPRD_GOLD.DASHBOARD.