BankOrg Docs

In progress

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.

Snowflake
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.

dbt/dbt_project.yml
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: CUSTOMER

Product 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.

dbt/seeds/account_product_group.csv
account_type,product_group
SAVINGS,DEPOSIT
CHECKING,DEPOSIT
LOAN,CREDIT
CASH,INTERNAL
INTEREST_EXPENSE,INTERNAL
FEE_INCOME,INTERNAL

Balance 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.

ModelGrainRows
fact_account_balance_histOne row per POSTED, not deleted posting of an account, with the balance after it13,666
fact_account_balance_dailyEnd-of-day balance per account and day, from the first posting to the snapshot date; a day without postings carries the previous balance911,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:

dbt/models/marts/account/daily_account_balance.sql
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
TableGrainWhat a row holdsRows
ACCOUNT.daily_account_balancebalance_date, branch_code, product_groupCustomer accounts, total closing balance, inflow and outflow, account count301,732
TRANSACTION.daily_transaction_summarytransaction_date, branch_code, product_group, transaction_typePosted and failed counts and the posted amount6,689
BRANCH.branch_performancereport_date, branch_codeDeposit and loan balances and account counts next to the day's posted and failed transactions194,110
CUSTOMER.customer_360customer_idOpen accounts and balances by product group on the latest day, posted activity of the last 30 days; no PII column343

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

KindWhat is checked
Keysunique_combination_of_columns on the grain of each mart; unique and not_null on customer_id of customer_360
Relationshipsbranch_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 valuesproduct_group is DEPOSIT or CREDIT; transaction_type is one of the seven types
Balance factsFive 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 otherPer 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:

Terminal
dbt build --select path:seeds path:models/marts

After the schemas were created, the four gold marts were built and tested on their own: 4 models and 45 tests passed.

Terminal
dbt build --select path:models/marts --exclude path:models/marts/core

The balances run to the snapshot date 2026-10-31.

Jobs

  • The daily workflow daily-dbt-staging.yml builds the seed, the snapshots and every model under marts, 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_balance and daily_transaction_summary through secure views in PRD_GOLD.DASHBOARD.