Capstone Project: Banking EDW Medallion Architecture
In this capstone, you act as the data engineering team for ANBTX, a regional bank that runs lending, retail banking, payments, wealth, compliance, and customer service operations across many source systems.
Your objective is to design the enterprise data warehouse flow from source applications into Snowflake using a medallion architecture: raw/stage, bronze, silver, gold, and report.
Project Scope
- Map departments, teams, tools, and source systems.
- Explain how all systems feed the DNA core banking platform.
- Design the EDW flow from source files into Snowflake.
- Define what each medallion layer stores and adds.
- Identify banking KPIs and the Gold tables that serve them.
- Explain who consumes Gold layer metrics.
Recommended Learning File Split
For learning, this capstone is best kept as one narrative markdown lesson first. If you later convert it into a hands-on lab, split it into 6 focused files:
| File | Purpose |
|---|
01-banking-edw-overview.md | Business story, departments, tools, and source systems |
02-source-system-data-flow.md | DNA deep dive and source-to-EDW flow |
03-medallion-layer-design.md | Raw, Bronze, Silver, Gold, and Report responsibilities |
04-sample-ddl.sql | Database, schema, stage, and table setup |
05-transformations.sql | Bronze, Silver DTS, Silver Domain, and Gold examples |
06-kpi-reporting-exercises.md | Learner tasks, dashboard prompts, and validation questions |
For the course navigation, keep 8 to 12 capstone lessons total. That is enough variety for real-world practice without making the sidebar feel crowded.
ANBTX Organization
ANBTX Organization
|
+-- Mortgage/Lending Department
| +-- Loan Officers
| +-- Processors
| +-- Underwriters
| +-- Closers
| |
| +-- Tools:
| +-- 1. Encompass (LOS) <- Loan origination, application to close
| +-- 2. Salesforce (CRM) <- Customer relationships, lead tracking
| +-- 3. BakerHill <- Commercial lending decisions
|
+-- Operations / Branch Banking Department
| +-- Tellers
| +-- Branch Managers
| +-- Service Representatives
| |
| +-- Tools:
| +-- 4. DNA (Fiserv) <- Core banking: accounts, transactions, GL
| +-- 5. Synapsys <- Customer service, case management
|
+-- Payments / Treasury Department
| +-- Payment Analysts
| +-- ACH/Wire Specialists
| +-- Cash Management Team
| |
| +-- Tools:
| +-- 6. Commercial Center <- Online banking, business payments
| +-- 7. Passport (Bottomline) <- ACH, wire transfers, payment processing
|
+-- Trust / Wealth Management Department
| +-- Trust Officers
| +-- Financial Advisors
| +-- Portfolio Managers
| |
| +-- Tools:
| +-- 8. Fitek <- Trust accounting, estate management
| +-- 9. LPL Financial <- Brokerage, investment management
|
+-- Compliance / Risk Department
| +-- BSA/AML Analysts
| +-- Compliance Officers
| +-- Risk Managers
| |
| +-- Tool:
| +-- 10. Verafin <- Anti-money laundering, fraud detection
|
+-- Data / IT Department
+-- Data Engineers
+-- DBAs
+-- BI Analysts
|
+-- Tool:
+-- EDW (Snowflake) <- Collects data from all 10 systems above
Data Flow: All 10 Systems to EDW
+---------------+ +---------------+ +---------------+
| 1. Encompass | | 2. Salesforce | | 3. BakerHill |
| (LOS) | | (CRM) | | (Comm Lend) |
+-------+-------+ +-------+-------+ +-------+-------+
| | |
| | |
+-------v-------+ +-------v-------+ +-------v-------+
| 4. DNA | | 5. Synapsys | | 6. Comm Ctr |
| (Core Bank) | | (Cust Svc) | | (Online Bank) |
+-------+-------+ +-------+-------+ +-------+-------+
| | |
| | |
+-------v-------+ +-------v-------+ +-------v-------+
| 7. Passport | | 8. Fitek | | 9. LPL |
| (Payments) | | (Trust) | | (Investments) |
+-------+-------+ +-------+-------+ +-------+-------+
| | |
| +-------v-------+ |
| | 10. Verafin | |
| | (AML/Fraud) | |
| +-------+-------+ |
| | |
v v v
=========================================================
| SNOWFLAKE STAGE (EDW_STAGE) |
| Files land here via Batch / Snowpipe |
=========================================================
|
v
=========================================================
| BRONZE (Raw Ingestion) |
| One set of tables per source system |
=========================================================
|
v
=========================================================
| SILVER (SCD2 + Domain Models) |
| Versioned history, enriched joins |
=========================================================
|
v
=========================================================
| GOLD / REPORT |
| Business-ready aggregations and views |
=========================================================
Source System Detail
| # | System | Vendor | Purpose | Data Examples |
|---|
| 1 | Encompass | ICE Mortgage Technology | Mortgage loan origination, from application to close | Loans, borrowers, properties, milestones |
| 2 | Salesforce | Salesforce Inc | CRM, lead management, customer 360 | Contacts, opportunities, campaigns, cases |
| 3 | BakerHill | BakerHill, Experian | Commercial and small business lending | Commercial loans, spreads, risk ratings |
| 4 | DNA | Fiserv | Core banking platform for accounts, GL, and transactions | Accounts, transactions, GL, customers |
| 5 | Synapsys | Synapsys Solutions | Customer service and interaction tracking | Cases, interactions, service requests |
| 6 | Commercial Ctr | Bottomline Technologies | Online/mobile banking for business clients | Payments, users, entitlements, transfers |
| 7 | Passport | Bottomline Technologies | ACH, wires, and payment hub | Wire transfers, ACH batches, payment status |
| 8 | Fitek | FIS | Trust accounting and estate management | Trust accounts, holdings, distributions |
| 9 | LPL Financial | LPL Financial | Brokerage and investment advisory | Portfolios, trades, positions, statements |
| 10 | Verafin | Verafin, Nasdaq | AML, fraud detection, BSA compliance | Alerts, SARs, CTRs, investigation cases |
Ingestion Frequency by System
| # | System | Frequency | Reason |
|---|
| 1 | Encompass | Nightly Batch | Loan files change during business hours |
| 2 | Salesforce | Nightly Batch | CRM updates during the day |
| 3 | BakerHill | Nightly Batch | Commercial loans reviewed daily |
| 4 | DNA | Nightly Batch | Core posts EOD and GL closes nightly |
| 5 | Synapsys | Nightly Batch | Service cases summarized daily |
| 6 | Commercial Ctr | Snowpipe | Payment activity needs faster refresh |
| 7 | Passport | Snowpipe | Wire/ACH status needs near-real-time visibility |
| 8 | Fitek | Weekly Batch | Trust positions change slowly |
| 9 | LPL Financial | Daily Batch | Market positions update after close |
| 10 | Verafin | Snowpipe | Fraud alerts need immediate visibility |
DNA Core Banking Deep Dive
DNA, short for Digital Network Architecture by Fiserv, is the heart of the bank. Every account, transaction, and customer record lives here.
DNA Daily Functions
| Function | Example | Source System That Triggers It |
|---|
| Account Opening | Customer opens new checking account | Branch teller / online app |
| Direct Deposit | Paycheck $3,500 credits checking | Passport, ACH network |
| Mortgage Payment | Auto-pay debits checking $1,800 | Encompass, loan servicing |
| Wire Transfer | Customer sends $10,000 wire | Commercial Center / Passport |
| ACH Payment | Vendor payment, bill pay | Passport, ACH hub |
| Debit Card Swipe | Buy coffee $5.75 | Card network, Visa/MC |
| ATM Withdrawal | Customer takes out $200 cash | Moniview, ATM network |
| Loan Funding | New mortgage funded, account created | Encompass |
| Commercial Loan | Business loan disbursed | BakerHill |
| Interest Posting | Monthly savings interest $0.82 | DNA internal, calculated |
| CD Maturity | 12-month CD matures, auto-renews | DNA internal, scheduled |
| Trust Distribution | Trust pays beneficiary $5,000 | Fitek, trust system |
| Investment Transfer | Move $25K to brokerage | LPL Financial |
| Account Freeze | Fraud detected, block account | Verafin |
| Customer Update | Address change, phone update | Salesforce / Synapsys |
| Online Transfer | Customer moves $500 between accounts | Commercial Center |
| Check Deposit | Mobile check deposit $1,000 | Mobile banking |
| NSF/Overdraft | Insufficient funds, charge fee | DNA internal, rules engine |
| GL Posting | End-of-day books balanced | DNA internal, scheduled |
| Statement Gen | Monthly statement produced | DNA internal, scheduled |
DNA Main Tables
| DNA Table | What it holds |
|---|
ACCT | Every account: type, status, open date, branch |
ACCTTRN | Every debit/credit that hits an account |
CUST | Customer master: name, SSN, DOB, address |
ACCTLOAN | Loan accounts: rate, term, payment schedule |
ACCTSAV | Savings/CD accounts: rate, maturity date |
GL | Bank's financial books: assets, liabilities |
BRANCH | Branch locations |
CUSTADDR | Customer addresses |
Every other system at the bank ultimately talks to DNA. When Encompass funds a loan, it tells DNA to create the account. When Commercial Center processes a payment, it tells DNA to debit or credit. DNA is the single source of truth for "how much money is where."
That is why DNA tables are the largest and most referenced in the EDW. They are the foundation that everything else joins to.
All Systems Flow Into DNA
This view shows the real pattern clearly: every major system interacts with DNA, and DNA becomes the central ledger that the bank's EDW consumes for downstream reporting and analytics.
A Customer's Day Through DNA
| Time | Event | DNA Impact | Source |
|---|
| 7:00 AM | Direct deposit from employer | DNA credits checking +$3,500 | Passport/ACH |
| 7:05 AM | Auto-pay mortgage | DNA debits checking -$1,800 | Encompass |
| 8:30 AM | Buy coffee, debit card | DNA debits checking -$5.75 | Card Network |
| 12:00 PM | Transfer savings to checking | DNA: savings -$200, checking +$200 | Commercial Ctr |
| 3:00 PM | Wire transfer to vendor | DNA debits checking -$10,000 | Passport |
| 3:01 PM | Verafin checks the wire | Reads DNA data to score it | Verafin |
| 5:00 PM | Interest accrual on savings | DNA calculates $0.82 | Internal |
| 6:00 PM | End-of-day GL posting | DNA closes the books | Internal |
| Night | DNA sends daily extract to EDW | All of today's activity | Nightly Batch |
EDW Medallion Layers
Each layer adds more structure, more business logic, and more value. Data flows left to right, getting cleaner and more useful at each step.
+-----------+ +--------+ +--------+ +------+ +--------+
| RAW/STAGE | ---> | BRONZE | ---> | SILVER | ---> | GOLD | ---> | REPORT |
| EDW_STAGE | | BRONZE | | SILVER | | GOLD | | REPORT |
+-----------+ +--------+ +--------+ +------+ +--------+
| Files land| |Structured |Versioned |Flattened |Views for
| as-is from| |tables with |history and |aggregated |dashboards
| sources | |audit cols |domains |metrics |and reports
+-----------+ +--------+ +--------+ +------+ +--------+
Layer 1: Raw / Stage
| Item | Detail |
|---|
| Database | EDW_STAGE |
| Purpose | Land files exactly as received. No transformation. No cleanup. |
| What | CSV, JSON, DAT files from source systems sit on Snowflake stage. |
| Who | Managed by the edw-stage dbt project, separate from edw-core. |
Example: Encompass Loan File
File: @EDW_STAGE.PROD.ENCOMPASS_STAGE/encompass_loans_20240806.csv
LOAN_GUID,LN_NBR,BORR_FNAME,BORR_LNAME,LN_AMT,INT_RATE,APP_DT,STATUS
{A1B2C3},2024-001001,James,Smith,350000,6.75,01/10/2024,Active
{D4E5F6},2024-001002,Robert,Garcia,225000,6.25,02/05/2024,Active
{G7H8I9},2024-001003,David,Martinez,415000,6.50,03/01/2024,Funded
File: @EDW_STAGE.PROD.DNA_STAGE/dna_acct_20240806.dat (pipe-delimited)
ACCTNBR|ACCTTYP|OPNDT|CURBAL|STATUSCD|BRANCHNBR
000123456|CHK|20180315|15234.50|A|001
000789012|SAV|20200101|52100.00|A|003
000345678|CDA|20230601|100000.00|M|001
Raw / Stage Rules
- No column renaming.
- No type casting.
- No filtering.
- Just raw file to stage location.
Layer 2: Bronze
| Item | Detail |
|---|
| Schema | BRONZE |
| Purpose | Load raw files into proper Snowflake tables and add audit tracking. |
| What | Structured tables with original source columns plus ADT_* audit columns. |
| Who | Managed by the edw-stage dbt project using incremental tables. |
| Model Pattern | {source}_{table}_nbs, for example dna_acct_nbs or encompass_loan_nbs |
Bronze Audit Columns
| Column | Purpose |
|---|
ADT_LOAD_DATE | When this record was loaded into Bronze |
ADT_FILE_NAME | Which file it came from |
ADT_FILE_SOURCE | Source system identifier, such as DNA or ENCOMPASS |
ADT_HASH_KEY | Hash of all business columns for change detection |
ADT_PURGE_YN | Soft-delete flag when a record is missing from the latest file |
Example: DNA Account in Bronze
SELECT * FROM EDW_PROD.BRONZE.DNA_ACCT_NBS;
ACCTNBR | ACCTTYP | CURBAL | STATUSCD | ADT_LOAD_DATE | ADT_FILE_NAME | ADT_HASH_KEY
----------+---------+----------+----------+---------------------+----------------------+-------------
000123456 | CHK | 15234.50 | A | 2024-08-06 06:00:00 | dna_acct_20240806.dat| 7291038475612
000789012 | SAV | 52100.00 | A | 2024-08-06 06:00:00 | dna_acct_20240806.dat| 3847561029384
000345678 | CDA |100000.00 | M | 2024-08-06 06:00:00 | dna_acct_20240806.dat| 9182736450192
Example: Next Day Balance Changed
ACCTNBR | CURBAL | ADT_LOAD_DATE | ADT_FILE_NAME | ADT_HASH_KEY
----------+----------+---------------------+----------------------+-------------
000123456 | 12000.00 | 2024-08-07 06:00:00 | dna_acct_20240807.dat| 5550198273641 <- DIFFERENT, balance changed
000789012 | 52100.00 | 2024-08-07 06:00:00 | dna_acct_20240807.dat| 3847561029384 <- SAME, no change
Bronze Rules
- Still no business logic or renaming.
- Only adds audit/tracking columns.
- Detects changes by hash comparison.
- Flags deleted records with
ADT_PURGE_YN.
Layer 3: Silver
The Silver schema has two sub-layers: DTS and Domain.
| Item | Detail |
|---|
| Schema | SILVER |
| Purpose | Add SCD Type 2 history and join/enrich across sources. |
| What | Versioned records with start/end dates plus enriched domain models. |
| Who | Managed by the edw-core dbt project using Dynamic Tables. |
Silver DTS: Data Time Series
| Item | Detail |
|---|
| Model Pattern | {source}_{table}_dts, for example dna_acct_dts or encompass_loan_dts |
| Purpose | Preserve history so the bank can answer "what did we know at that time?" |
DTS Versioning Columns
| Column | Purpose |
|---|
ADT_START_DATE | When this version of the record became active |
ADT_END_DATE | When this version was superseded, with NULL meaning current |
ADT_VERSION | Version number, such as 1, 2, 3 |
ADT_CURRENT_FLAG | Y if this is the latest version |
ADT_PURGE_YN | Soft-delete indicator |
Example: Account Balance Tracked Over Time
SELECT * FROM EDW_PROD.SILVER.DNA_ACCT_DTS
WHERE ACCTNBR = '000123456';
ACCTNBR | CURBAL | ADT_START_DATE | ADT_END_DATE | ADT_VERSION | ADT_CURRENT_FLAG
----------+----------+----------------+--------------+-------------+-----------------
000123456 | 15234.50 | 2024-08-06 | 2024-08-07 | 1 | N
000123456 | 12000.00 | 2024-08-07 | 2024-08-10 | 2 | N
000123456 | 13500.00 | 2024-08-10 | NULL | 3 | Y <- current
This is the history. You can see the balance at any point in time.
Silver Domain: Enriched Models
| Item | Detail |
|---|
| Model Pattern | stg_silver_{source}_{table}_{domain}_{floworder}, for example stg_silver_dna_account_acct_10 |
| Purpose | Join multiple DTS tables into business domains. |
| Domains | Account, customer, address, cashbox, and similar business areas. |
Example: Customer Domain Joins DNA, Salesforce, and Synapsys
SELECT * FROM EDW_PROD.SILVER.CUSTOMER;
CUSTOMER_ID | FULL_NAME | EMAIL (Salesforce) | PHONE (DNA) | RISK_SCORE (Verafin) | LAST_INTERACTION (Synapsys)
------------+---------------+--------------------+-------------+----------------------+-----------------------------
C00001 | James Smith | jsmith@email.com | 555-0101 | LOW | 2024-08-01
C00002 | Robert Garcia | rgarcia@gmail.com | 555-0201 | MEDIUM | 2024-07-28
Data from three different systems is combined into one customer view.
Silver Rules
- DTS uses hash change detection to create new versions.
- Domain models only join on
ADT_CURRENT_FLAG = 'Y', the latest version.
- All Silver models are materialized as Dynamic Tables.
Layer 4: Gold
| Item | Detail |
|---|
| Schema | GOLD |
| Purpose | Flattened, aggregated, business-ready tables with calculated metrics. |
| What | Point-in-time snapshots, balances, totals, counts, and financial calculations. |
| Who | Managed by the edw-core dbt project using Dynamic Tables. |
What Gold Adds
- Aggregations such as sum, count, and average.
- Calculated amounts such as interest earned and fees charged.
- As-of-date snapshots such as balance as of month-end.
- Flattened structures so consumers do not need to join multiple tables.
- Business-friendly column names.
Example: Account Summary Flattened From Silver
SELECT * FROM EDW_PROD.GOLD.ACCOUNT_SUMMARY;
ACCOUNT_NUMBER | ACCOUNT_TYPE | CUSTOMER_NAME | BRANCH | CURRENT_BALANCE | MTD_DEPOSITS | MTD_WITHDRAWALS | YTD_INTEREST | DAYS_SINCE_LAST_TXN | RISK_FLAG
---------------+--------------+---------------+-------------+-----------------+--------------+-----------------+--------------+---------------------+----------
000123456 | Checking | James Smith | Main Street | $13,500.00 | $5,200.00 | $3,800.00 | $0.00 | 2 | N
000789012 | Savings | Robert Garcia | Downtown | $52,100.00 | $1,000.00 | $0.00 | $42.15 | 15 | N
000345678 | CD | Lisa Anderson | Main Street | $100,000.00 | $0.00 | $0.00 | $4,125.00 | 60 | N
Example: Loan Pipeline Aggregated From Encompass Silver
SELECT * FROM EDW_PROD.GOLD.LOAN_PIPELINE;
LOAN_OFFICER | MONTH | APPLICATIONS | FUNDED | DENIED | TOTAL_VOLUME | AVG_LOAN_AMT | AVG_DAYS_TO_CLOSE | PULL_THROUGH_PCT
--------------+---------+--------------+--------+--------+--------------+--------------+-------------------+-----------------
Sarah Johnson | 2024-08 | 12 | 8 | 2 | $3,200,000 | $400,000 | 42 | 66.7%
Michael Chen | 2024-08 | 9 | 6 | 1 | $2,850,000 | $475,000 | 38 | 66.7%
Gold Rules
- No more technical columns such as
ADT_* or hash keys.
- Use business-friendly names such as
ACCOUNT_TYPE, not ACCTTYP.
- Pre-calculate metrics so analysts do not need to compute them repeatedly.
- Support point-in-time questions such as "what was the balance on March 31?"
Layer 5: Report
| Item | Detail |
|---|
| Schema | REPORT |
| Purpose | Views on top of Gold for specific reporting needs. |
| What | Snowflake views only. No storage, just SQL logic on Gold tables. |
| Who | Consumed by BI tools such as Tableau, Power BI, dashboards, and analysts. |
Example: Monthly Loan Summary View
CREATE VIEW EDW_PROD.REPORT.V_MONTHLY_LOAN_SUMMARY AS
SELECT *
FROM EDW_PROD.GOLD.LOAN_PIPELINE
WHERE MONTH = DATE_TRUNC('MONTH', CURRENT_DATE());
Report Rules
- Views only, with no materialized storage.
- Filter, rename, or reshape Gold data for specific consumers.
- Add a security layer so users can access reports without exposing all Gold tables.
Complete Flow: One Record's Journey
Scenario: Customer opens a checking account with $5,000, then withdraws $200 the next day.
Day 1: Account Opened
RAW/STAGE: File arrives: "000123456|CHK|20240806|5000.00|A|001"
|
BRONZE: 000123456 | CHK | 5000.00 | A | ADT_LOAD=2024-08-06 | ADT_HASH=ABC123
|
SILVER DTS: 000123456 | CHK | 5000.00 | START=2024-08-06 | END=NULL | V=1 | CURRENT=Y
|
SILVER DOM: CUST_ID=C00001 | NAME=James Smith | ACCT=000123456 | BAL=5000 | RISK=LOW
|
GOLD: ACCT=000123456 | TYPE=Checking | CUSTOMER=James Smith | BALANCE=$5,000
|
REPORT: Shows in "New Accounts This Week" dashboard
Day 2: Withdrawal of $200
RAW/STAGE: New file arrives: "000123456|CHK|20240807|4800.00|A|001"
|
BRONZE: 000123456 | CHK | 4800.00 | A | ADT_LOAD=2024-08-07 | ADT_HASH=DEF456 <- hash changed
|
SILVER DTS: V1: 000123456 | 5000.00 | START=08-06 | END=08-07 | V=1 | CURRENT=N <- closed
V2: 000123456 | 4800.00 | START=08-07 | END=NULL | V=2 | CURRENT=Y <- new version
|
GOLD: ACCT=000123456 | BALANCE=$4,800 | MTD_WITHDRAWALS=$200
|
REPORT: Balance updated on customer dashboard
Summary: What Each Layer Does
| Layer | Schema | What it does | Materialization | Key Addition |
|---|
| Raw/Stage | EDW_STAGE | Land files as-is | External Stage | Nothing, raw files |
| Bronze | BRONZE | Structure plus audit tracking | Incremental Table | ADT_LOAD, ADT_HASH |
| Silver DTS | SILVER | SCD2 versioned history | Dynamic Table | Start/end dates, version |
| Silver Domain | SILVER | Join across sources | Dynamic Table | Enriched business view |
| Gold | GOLD | Aggregates plus calculations | Dynamic Table | Metrics, amounts, KPIs |
| Report | REPORT | Reporting views | View | Filtered/shaped output |
Gold Layer Banking KPIs and Reports
The Gold layer answers questions that executives, analysts, and regulators ask daily. It pre-computes KPIs so business users do not need to write SQL or understand joins.
1. Retail Banking KPIs
| KPI | What it measures | Gold Table |
|---|
| Total Deposits | Sum of all deposit balances | GOLD.DEPOSIT_SUMMARY |
| Net New Accounts | Opened minus closed accounts | GOLD.ACCOUNT_GROWTH |
| Account Attrition Rate | Percent accounts closed this month | GOLD.ACCOUNT_GROWTH |
| Average Balance per Account | Total deposits divided by number of accounts | GOLD.ACCOUNT_SUMMARY |
| Dormant Account Percent | Accounts with no activity for 90+ days | GOLD.ACCOUNT_ACTIVITY |
| Fee Income | NSF fees, service charges, ATM fees | GOLD.FEE_REVENUE |
| Cost per Account | Operating cost divided by number of accounts | GOLD.COST_METRICS |
| Cross-sell Ratio | Products per household | GOLD.HOUSEHOLD_SUMMARY |
2. Lending / Mortgage KPIs
| KPI | What it measures | Gold Table |
|---|
| Loan Pipeline Volume | Total dollars in active applications | GOLD.LOAN_PIPELINE |
| Pull-Through Rate | Percent of applications that fund | GOLD.LOAN_PIPELINE |
| Average Days to Close | Application date to funded date | GOLD.LOAN_PIPELINE |
| Funded Volume MTD/YTD | Dollar amount of loans funded | GOLD.LOAN_PRODUCTION |
| Approval Rate | Percent approved vs applied | GOLD.LOAN_PIPELINE |
| Denial Rate by Reason | Why loans are denied | GOLD.LOAN_DENIALS |
| Weighted Average Rate | Average interest rate of portfolio | GOLD.LOAN_PORTFOLIO |
| Delinquency Rate 30/60/90 | Percent loans past due | GOLD.LOAN_DELINQUENCY |
| Net Charge-Offs | Loans written off as losses | GOLD.LOAN_LOSSES |
| LTV Distribution | Loan-to-value risk bands | GOLD.LOAN_RISK |
3. Payments / Treasury KPIs
| KPI | What it measures | Gold Table |
|---|
| ACH Volume | Daily/monthly ACH transactions by count and dollars | GOLD.PAYMENT_VOLUME |
| Wire Volume | Wire transfers processed | GOLD.PAYMENT_VOLUME |
| Bill Pay Adoption | Percent customers using bill pay | GOLD.DIGITAL_ADOPTION |
| Online Banking Logins | Digital engagement metric | GOLD.DIGITAL_ADOPTION |
| Transaction Failure Rate | Percent failed or rejected payments | GOLD.PAYMENT_ERRORS |
| Average Transaction Size | Dollars per payment type | GOLD.PAYMENT_VOLUME |
4. Trust / Wealth KPIs
| KPI | What it measures | Gold Table |
|---|
| Assets Under Management (AUM) | Total trust plus investment dollars | GOLD.AUM_SUMMARY |
| Trust Fee Revenue | Fees earned on trust accounts | GOLD.TRUST_REVENUE |
| Net Asset Flows | Money in vs money out | GOLD.ASSET_FLOWS |
| Portfolio Performance | Returns vs benchmark | GOLD.PORTFOLIO_PERF |
| Client Retention Rate | Percent clients retained year-over-year | GOLD.CLIENT_RETENTION |
5. Compliance / Risk KPIs
| KPI | What it measures | Gold Table |
|---|
| SAR Filing Count MTD | Suspicious Activity Reports filed | GOLD.BSA_METRICS |
| SAR Filing Timeliness | Percent filed within 30-day deadline | GOLD.BSA_METRICS |
| Alert-to-Case Rate | Percent alerts escalated to investigation | GOLD.FRAUD_METRICS |
| False Positive Rate | Alerts that were not real fraud | GOLD.FRAUD_METRICS |
| CTR Volume | Currency Transaction Reports over $10K | GOLD.BSA_METRICS |
| High-Risk Customer Count | Customers flagged elevated risk | GOLD.RISK_SUMMARY |
| Fraud Loss Amount | Dollars lost to confirmed fraud | GOLD.FRAUD_LOSSES |
6. Overall Bank Health KPIs
| KPI | What it measures | Gold Table |
|---|
| Total Assets | Everything the bank owns | GOLD.BALANCE_SHEET |
| Net Interest Margin (NIM) | Interest earned minus interest paid | GOLD.INCOME_METRICS |
| Return on Assets (ROA) | Net income divided by total assets | GOLD.INCOME_METRICS |
| Return on Equity (ROE) | Net income divided by shareholder equity | GOLD.INCOME_METRICS |
| Efficiency Ratio | Operating expense divided by revenue | GOLD.INCOME_METRICS |
| Capital Ratio (Tier 1) | Capital adequacy, regulatory | GOLD.CAPITAL_METRICS |
| Non-Performing Assets | Loans 90+ days past due | GOLD.ASSET_QUALITY |
| Provision for Loan Losses | Reserve for expected defaults | GOLD.ASSET_QUALITY |
| Customer Growth | Net new customers per month | GOLD.CUSTOMER_GROWTH |
| Revenue per Employee | Total revenue divided by FTE count | GOLD.EFFICIENCY |
Who Consumes the Gold Layer?
GOLD LAYER
|
+-- Board of Directors -> "How's the bank performing?" (ROA, NIM, Growth)
|
+-- CFO / Finance -> "What's our revenue?" (Fee income, NII, Efficiency)
|
+-- Chief Lending Officer -> "How's the pipeline?" (Volume, Pull-through, Days to close)
|
+-- Chief Risk Officer -> "What's our exposure?" (Delinquency, Charge-offs, LTV)
|
+-- BSA Officer -> "Are we compliant?" (SARs filed, Timeliness, Alerts)
|
+-- Branch Managers -> "How's my branch?" (Deposits, New accounts, Cross-sell)
|
+-- Regulators (OCC/FDIC) -> "Prove you're safe" (Capital ratios, Asset quality)
|
+-- BI/Analysts -> "Dig deeper" (Trends, Segmentation, Forecasting)
Gold exists so a branch manager can open a dashboard and immediately see answers without needing to know SQL, joins, or what ADT_CURRENT_FLAG means.
Why Gold Exists: Silver vs Gold
| Silver, for engineers | Gold, for business users |
|---|
ACCTNBR = '000123456' | ACCOUNT_NUMBER = '000123456' |
CURBAL = 15234.50 | CURRENT_BALANCE = $15,234.50 |
ACCTTYP = 'CHK' | ACCOUNT_TYPE = 'Checking' |
| One row per version (SCD2) | One row per account, current |
Technical columns such as ADT_* | Business columns only |
| Must join 5 tables | Pre-joined, flattened |
| Requires SQL knowledge | Open dashboard and see answers |
Learner Tasks
- Identify the key source systems that feed account, customer, loan, payment, wealth, and risk data.
- Choose the correct ingestion frequency for each source system and explain why.
- Design Raw, Bronze, Silver DTS, Silver Domain, Gold, and Report objects for DNA account data.
- Explain how a changed balance creates a new SCD Type 2 version in Silver.
- Build a Gold
ACCOUNT_SUMMARY design with business-friendly columns.
- Pick five KPIs and map each one to its source systems, Silver dependencies, and Gold table.
Completion Criteria
You are done when you can explain the full banking data journey from a customer transaction in DNA to a business KPI in the Gold layer, including which layer owns raw storage, audit tracking, versioned history, enrichment, aggregation, and reporting access.