Skip to main content

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.

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:

FilePurpose
01-banking-edw-overview.mdBusiness story, departments, tools, and source systems
02-source-system-data-flow.mdDNA deep dive and source-to-EDW flow
03-medallion-layer-design.mdRaw, Bronze, Silver, Gold, and Report responsibilities
04-sample-ddl.sqlDatabase, schema, stage, and table setup
05-transformations.sqlBronze, Silver DTS, Silver Domain, and Gold examples
06-kpi-reporting-exercises.mdLearner 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​

#SystemVendorPurposeData Examples
1EncompassICE Mortgage TechnologyMortgage loan origination, from application to closeLoans, borrowers, properties, milestones
2SalesforceSalesforce IncCRM, lead management, customer 360Contacts, opportunities, campaigns, cases
3BakerHillBakerHill, ExperianCommercial and small business lendingCommercial loans, spreads, risk ratings
4DNAFiservCore banking platform for accounts, GL, and transactionsAccounts, transactions, GL, customers
5SynapsysSynapsys SolutionsCustomer service and interaction trackingCases, interactions, service requests
6Commercial CtrBottomline TechnologiesOnline/mobile banking for business clientsPayments, users, entitlements, transfers
7PassportBottomline TechnologiesACH, wires, and payment hubWire transfers, ACH batches, payment status
8FitekFISTrust accounting and estate managementTrust accounts, holdings, distributions
9LPL FinancialLPL FinancialBrokerage and investment advisoryPortfolios, trades, positions, statements
10VerafinVerafin, NasdaqAML, fraud detection, BSA complianceAlerts, SARs, CTRs, investigation cases

Ingestion Frequency by System​

#SystemFrequencyReason
1EncompassNightly BatchLoan files change during business hours
2SalesforceNightly BatchCRM updates during the day
3BakerHillNightly BatchCommercial loans reviewed daily
4DNANightly BatchCore posts EOD and GL closes nightly
5SynapsysNightly BatchService cases summarized daily
6Commercial CtrSnowpipePayment activity needs faster refresh
7PassportSnowpipeWire/ACH status needs near-real-time visibility
8FitekWeekly BatchTrust positions change slowly
9LPL FinancialDaily BatchMarket positions update after close
10VerafinSnowpipeFraud 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​

FunctionExampleSource System That Triggers It
Account OpeningCustomer opens new checking accountBranch teller / online app
Direct DepositPaycheck $3,500 credits checkingPassport, ACH network
Mortgage PaymentAuto-pay debits checking $1,800Encompass, loan servicing
Wire TransferCustomer sends $10,000 wireCommercial Center / Passport
ACH PaymentVendor payment, bill payPassport, ACH hub
Debit Card SwipeBuy coffee $5.75Card network, Visa/MC
ATM WithdrawalCustomer takes out $200 cashMoniview, ATM network
Loan FundingNew mortgage funded, account createdEncompass
Commercial LoanBusiness loan disbursedBakerHill
Interest PostingMonthly savings interest $0.82DNA internal, calculated
CD Maturity12-month CD matures, auto-renewsDNA internal, scheduled
Trust DistributionTrust pays beneficiary $5,000Fitek, trust system
Investment TransferMove $25K to brokerageLPL Financial
Account FreezeFraud detected, block accountVerafin
Customer UpdateAddress change, phone updateSalesforce / Synapsys
Online TransferCustomer moves $500 between accountsCommercial Center
Check DepositMobile check deposit $1,000Mobile banking
NSF/OverdraftInsufficient funds, charge feeDNA internal, rules engine
GL PostingEnd-of-day books balancedDNA internal, scheduled
Statement GenMonthly statement producedDNA internal, scheduled

DNA Main Tables​

DNA TableWhat it holds
ACCTEvery account: type, status, open date, branch
ACCTTRNEvery debit/credit that hits an account
CUSTCustomer master: name, SSN, DOB, address
ACCTLOANLoan accounts: rate, term, payment schedule
ACCTSAVSavings/CD accounts: rate, maturity date
GLBank's financial books: assets, liabilities
BRANCHBranch locations
CUSTADDRCustomer 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​

TimeEventDNA ImpactSource
7:00 AMDirect deposit from employerDNA credits checking +$3,500Passport/ACH
7:05 AMAuto-pay mortgageDNA debits checking -$1,800Encompass
8:30 AMBuy coffee, debit cardDNA debits checking -$5.75Card Network
12:00 PMTransfer savings to checkingDNA: savings -$200, checking +$200Commercial Ctr
3:00 PMWire transfer to vendorDNA debits checking -$10,000Passport
3:01 PMVerafin checks the wireReads DNA data to score itVerafin
5:00 PMInterest accrual on savingsDNA calculates $0.82Internal
6:00 PMEnd-of-day GL postingDNA closes the booksInternal
NightDNA sends daily extract to EDWAll of today's activityNightly 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​

ItemDetail
DatabaseEDW_STAGE
PurposeLand files exactly as received. No transformation. No cleanup.
WhatCSV, JSON, DAT files from source systems sit on Snowflake stage.
WhoManaged 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

Example: DNA Account Extract​

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​

ItemDetail
SchemaBRONZE
PurposeLoad raw files into proper Snowflake tables and add audit tracking.
WhatStructured tables with original source columns plus ADT_* audit columns.
WhoManaged 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​

ColumnPurpose
ADT_LOAD_DATEWhen this record was loaded into Bronze
ADT_FILE_NAMEWhich file it came from
ADT_FILE_SOURCESource system identifier, such as DNA or ENCOMPASS
ADT_HASH_KEYHash of all business columns for change detection
ADT_PURGE_YNSoft-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.

ItemDetail
SchemaSILVER
PurposeAdd SCD Type 2 history and join/enrich across sources.
WhatVersioned records with start/end dates plus enriched domain models.
WhoManaged by the edw-core dbt project using Dynamic Tables.

Silver DTS: Data Time Series​

ItemDetail
Model Pattern{source}_{table}_dts, for example dna_acct_dts or encompass_loan_dts
PurposePreserve history so the bank can answer "what did we know at that time?"

DTS Versioning Columns​

ColumnPurpose
ADT_START_DATEWhen this version of the record became active
ADT_END_DATEWhen this version was superseded, with NULL meaning current
ADT_VERSIONVersion number, such as 1, 2, 3
ADT_CURRENT_FLAGY if this is the latest version
ADT_PURGE_YNSoft-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​

ItemDetail
Model Patternstg_silver_{source}_{table}_{domain}_{floworder}, for example stg_silver_dna_account_acct_10
PurposeJoin multiple DTS tables into business domains.
DomainsAccount, 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​

ItemDetail
SchemaGOLD
PurposeFlattened, aggregated, business-ready tables with calculated metrics.
WhatPoint-in-time snapshots, balances, totals, counts, and financial calculations.
WhoManaged 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​

ItemDetail
SchemaREPORT
PurposeViews on top of Gold for specific reporting needs.
WhatSnowflake views only. No storage, just SQL logic on Gold tables.
WhoConsumed 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​

LayerSchemaWhat it doesMaterializationKey Addition
Raw/StageEDW_STAGELand files as-isExternal StageNothing, raw files
BronzeBRONZEStructure plus audit trackingIncremental TableADT_LOAD, ADT_HASH
Silver DTSSILVERSCD2 versioned historyDynamic TableStart/end dates, version
Silver DomainSILVERJoin across sourcesDynamic TableEnriched business view
GoldGOLDAggregates plus calculationsDynamic TableMetrics, amounts, KPIs
ReportREPORTReporting viewsViewFiltered/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​

KPIWhat it measuresGold Table
Total DepositsSum of all deposit balancesGOLD.DEPOSIT_SUMMARY
Net New AccountsOpened minus closed accountsGOLD.ACCOUNT_GROWTH
Account Attrition RatePercent accounts closed this monthGOLD.ACCOUNT_GROWTH
Average Balance per AccountTotal deposits divided by number of accountsGOLD.ACCOUNT_SUMMARY
Dormant Account PercentAccounts with no activity for 90+ daysGOLD.ACCOUNT_ACTIVITY
Fee IncomeNSF fees, service charges, ATM feesGOLD.FEE_REVENUE
Cost per AccountOperating cost divided by number of accountsGOLD.COST_METRICS
Cross-sell RatioProducts per householdGOLD.HOUSEHOLD_SUMMARY

2. Lending / Mortgage KPIs​

KPIWhat it measuresGold Table
Loan Pipeline VolumeTotal dollars in active applicationsGOLD.LOAN_PIPELINE
Pull-Through RatePercent of applications that fundGOLD.LOAN_PIPELINE
Average Days to CloseApplication date to funded dateGOLD.LOAN_PIPELINE
Funded Volume MTD/YTDDollar amount of loans fundedGOLD.LOAN_PRODUCTION
Approval RatePercent approved vs appliedGOLD.LOAN_PIPELINE
Denial Rate by ReasonWhy loans are deniedGOLD.LOAN_DENIALS
Weighted Average RateAverage interest rate of portfolioGOLD.LOAN_PORTFOLIO
Delinquency Rate 30/60/90Percent loans past dueGOLD.LOAN_DELINQUENCY
Net Charge-OffsLoans written off as lossesGOLD.LOAN_LOSSES
LTV DistributionLoan-to-value risk bandsGOLD.LOAN_RISK

3. Payments / Treasury KPIs​

KPIWhat it measuresGold Table
ACH VolumeDaily/monthly ACH transactions by count and dollarsGOLD.PAYMENT_VOLUME
Wire VolumeWire transfers processedGOLD.PAYMENT_VOLUME
Bill Pay AdoptionPercent customers using bill payGOLD.DIGITAL_ADOPTION
Online Banking LoginsDigital engagement metricGOLD.DIGITAL_ADOPTION
Transaction Failure RatePercent failed or rejected paymentsGOLD.PAYMENT_ERRORS
Average Transaction SizeDollars per payment typeGOLD.PAYMENT_VOLUME

4. Trust / Wealth KPIs​

KPIWhat it measuresGold Table
Assets Under Management (AUM)Total trust plus investment dollarsGOLD.AUM_SUMMARY
Trust Fee RevenueFees earned on trust accountsGOLD.TRUST_REVENUE
Net Asset FlowsMoney in vs money outGOLD.ASSET_FLOWS
Portfolio PerformanceReturns vs benchmarkGOLD.PORTFOLIO_PERF
Client Retention RatePercent clients retained year-over-yearGOLD.CLIENT_RETENTION

5. Compliance / Risk KPIs​

KPIWhat it measuresGold Table
SAR Filing Count MTDSuspicious Activity Reports filedGOLD.BSA_METRICS
SAR Filing TimelinessPercent filed within 30-day deadlineGOLD.BSA_METRICS
Alert-to-Case RatePercent alerts escalated to investigationGOLD.FRAUD_METRICS
False Positive RateAlerts that were not real fraudGOLD.FRAUD_METRICS
CTR VolumeCurrency Transaction Reports over $10KGOLD.BSA_METRICS
High-Risk Customer CountCustomers flagged elevated riskGOLD.RISK_SUMMARY
Fraud Loss AmountDollars lost to confirmed fraudGOLD.FRAUD_LOSSES

6. Overall Bank Health KPIs​

KPIWhat it measuresGold Table
Total AssetsEverything the bank ownsGOLD.BALANCE_SHEET
Net Interest Margin (NIM)Interest earned minus interest paidGOLD.INCOME_METRICS
Return on Assets (ROA)Net income divided by total assetsGOLD.INCOME_METRICS
Return on Equity (ROE)Net income divided by shareholder equityGOLD.INCOME_METRICS
Efficiency RatioOperating expense divided by revenueGOLD.INCOME_METRICS
Capital Ratio (Tier 1)Capital adequacy, regulatoryGOLD.CAPITAL_METRICS
Non-Performing AssetsLoans 90+ days past dueGOLD.ASSET_QUALITY
Provision for Loan LossesReserve for expected defaultsGOLD.ASSET_QUALITY
Customer GrowthNet new customers per monthGOLD.CUSTOMER_GROWTH
Revenue per EmployeeTotal revenue divided by FTE countGOLD.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 engineersGold, for business users
ACCTNBR = '000123456'ACCOUNT_NUMBER = '000123456'
CURBAL = 15234.50CURRENT_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 tablesPre-joined, flattened
Requires SQL knowledgeOpen dashboard and see answers

Learner Tasks​

  1. Identify the key source systems that feed account, customer, loan, payment, wealth, and risk data.
  2. Choose the correct ingestion frequency for each source system and explain why.
  3. Design Raw, Bronze, Silver DTS, Silver Domain, Gold, and Report objects for DNA account data.
  4. Explain how a changed balance creates a new SCD Type 2 version in Silver.
  5. Build a Gold ACCOUNT_SUMMARY design with business-friendly columns.
  6. 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.