Skip to main content

DNA ACCT — Dimensional Data Modeling (Star Schema)

This module walks through a real-world banking data model from the Fiserv DNA Core Banking system. We will use it to demonstrate Dimensional Data Modeling concepts, Star Schema design, and how KPIs are derived from your data model.

Why This Example?

The DNA ACCT model is used by hundreds of banks and credit unions worldwide. Understanding it gives you hands-on experience with a production-grade Star Schema that handles ~75M+ transaction rows and daily snapshots across multiple accounts.


1. The Star Schema — ER Diagram​

The DNA_ACCT table is the central dimension (Master Account). Every other table connects back to it via the foreign key ACCTNBR. This is the classic Star Schema pattern — one central table surrounded by related fact and dimension tables.


2. How to Read This Diagram​

Parent TableChild TableJoin KeyCardinalityBusiness Meaning
DNA_ACCTDNA_RTXNACCTNBR1 : ManyOne account has many transactions
DNA_ACCTDNA_WH_ACCTCOMMONACCTNBR1 : ManyOne account has many daily snapshots
DNA_ACCTDNA_WH_ACCTDEPOSITACCTNBR1 : ManyOne account has many deposit snapshots
DNA_ACCTDNA_ACCTBALHISTACCTNBR1 : ManyOne account has many balance history rows
DNA_ACCTDNA_ACCTCOMBOACCTNBR1 : ManyOne account can be part of many combos
DNA_ACCTDNA_ACCTNAMEADDRACCTNBR1 : ManyOne account has many name/address records
DNA_RTXNDNA_RTXNBALACCTNBR + RTXNNBR1 : 1Each transaction has exactly one running balance

3. Table Descriptions​

Table 1: DNA_ACCT — Master Account (Central Dimension)​

The central account master record in Fiserv DNA. Contains one row per bank account (checking, savings, loan, CD). Every other DNA table joins back via ACCTNBR.

Grain: 1 row = 1 bank account

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)PK — Unique account number
2MJACCTTYPCDVARCHAR(2)Major account type (D=Deposit, L=Loan, C=CD)
3CURRACCTSTATCDVARCHAR(2)Current status (A=Active, C=Closed, D=Dormant)
4BRANCHORGNBRNUMBER(5)Branch org number where account is held
5OPENDATEDATEDate the account was originally opened
6DATEMATDATEMaturity date (for CDs and loans, NULL for checking)
7PRODCDVARCHAR(10)Product code (CHK01, SAV02, CD360, HELOC)
8OFFCRNBRNUMBER(6)Assigned officer/relationship manager number
9ANB_IDVARCHAR(4)Bank/institution identifier (always 'ANB')
10DATELASTMAINTDATEDate record was last updated in source system

Sample Data:

ACCTNBRMJACCTTYPCDCURRACCTSTATCDBRANCHORGNBROPENDATEDATEMATPRODCDOFFCRNBRANB_IDMAINT
100234DA1012019-03-15NULLCHK014521ANB2025-07-01
200891LA2052021-08-222031-08-22HELOC3310ANB2025-06-28

Table 2: DNA_RTXN — Retail Transactions (Fact Table)​

Every financial transaction posted to an account. Includes deposits, withdrawals, transfers, fees, and interest postings. High-volume fact table (~75M rows).

Grain: 1 row = 1 transaction event

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2RTXNNBRNUMBER(12)PK — Unique transaction sequence number
3RTXNTYPCDVARCHAR(4)Transaction type (DEP, WDL, XFR, FEE, INT)
4TRANAMTNUMBER(15,2)Transaction amount (+credit, −debit)
5ORIGPOSTDATEDATEOriginal posting date
6EFFDATEDATEEffective date (when funds available)
7RTXNCDVARCHAR(4)Transaction code (more granular than type)
8DESCRVARCHAR(60)Transaction description / memo
9SRCCDVARCHAR(4)Source channel (ATM, POS, ACH, TEL, BRN)
10DATELASTMAINTDATEDate record was last maintained

Sample Data:

ACCTNBRRTXNNBRRTXNTYPCDTRANAMTORIGPOSTDATEEFFDATERTXNCDDESCRSRCCD
100234987654001DEP5,000.002025-07-012025-07-01DCHKPayroll Direct DepACH
100234987654002WDL-200.002025-07-022025-07-02WATMATM WithdrawalATM

Table 3: DNA_RTXNBAL — Transaction Running Balance​

Running balance AFTER each transaction posts. One-to-one with DNA_RTXN — every transaction produces exactly one balance record.

Grain: 1 row = 1 balance per transaction

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2RTXNNBRNUMBER(12)FK → DNA_RTXN (which transaction)
3SUBACCTNBRNUMBER(4)Sub-account number (1=primary, 2=interest)
4AMTNUMBER(15,2)Running balance after this transaction
5PREVBALAMTNUMBER(15,2)Balance before this transaction posted
6HOLDAMTNUMBER(15,2)Hold amount (pending funds not yet released)
7AVAILBALAMTNUMBER(15,2)Available balance (AMT minus holds)
8BALCATCDVARCHAR(4)Balance category code (LEDG, AVAIL, MEMO)
9ANB_IDVARCHAR(4)Bank/institution identifier
10DATELASTMAINTDATEDate this record was last maintained

Sample Data:

ACCTNBRRTXNNBRSUBACCTAMTPREVBALAMTHOLDAMTAVAILBALAMTBALCATCDANB_IDMAINT
100234987654001142,350.0037,350.000.0042,350.00LEDGANB2025-07-01
100234987654002142,150.0042,350.000.0042,150.00LEDGANB2025-07-02

Table 4: DNA_WH_ACCTCOMMON — Daily Account Snapshot​

A daily "photograph" of every account's key metrics. One row per account per day. Used for trend analysis, balance tracking, and reporting over time.

Grain: 1 row = 1 account on 1 day

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2EFFDATEDATEEffective/snapshot date
3BOOKBALANCENUMBER(15,2)Book balance as of that date
4NOTEINTRATENUMBER(7,4)Interest rate on that date
5PRODUCTVARCHAR(30)Product code/name for the account
6AVAILBALANCENUMBER(15,2)Available balance (after holds/floats)
7ACCRINTERESTNUMBER(15,2)Accrued interest not yet posted
8OFFCRNBRNUMBER(6)Assigned officer number on that date
9ACCTSTATCDVARCHAR(2)Account status as of snapshot date
10ANB_IDVARCHAR(4)Bank/institution identifier

Sample Data:

ACCTNBREFFDATEBOOKBALANCENOTEINTRATEPRODUCTAVAILBALACCRINTOFFCRNBRSTATANB_ID
1002342025-07-0142,350.000.0150Premier Chk42,350.005.284521AANB
1002342025-07-0242,150.000.0150Premier Chk42,150.0010.564521AANB

Table 5: DNA_WH_ACCTDEPOSIT — Daily Deposit Account Snapshot​

Deposit-specific daily metrics that supplement ACCTCOMMON. Contains interest earned, overdraft info, and available balance. Joins 1:1 with ACCTCOMMON on ACCTNBR + EFFDATE.

Grain: 1 row = 1 deposit account on 1 day

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2EFFDATEDATEEffective/snapshot date (same as COMMON)
3YTDAMTINTNUMBER(15,2)Year-to-date interest earned
4OVERDRAFTBALNUMBER(15,2)Current overdraft balance (0 if none)
5NOTEAVAILNUMBER(15,2)Available balance (after holds)
6MTDAMTINTNUMBER(15,2)Month-to-date interest earned
7LASTDEPAMTNUMBER(15,2)Last deposit amount
8LASTDEPDATEDATEDate of last deposit
9LASTWDLAMTNUMBER(15,2)Last withdrawal amount
10LASTWDLDATEDATEDate of last withdrawal

Sample Data:

ACCTNBREFFDATEYTDAMTINTOVERDRAFTNOTEAVAILMTDAMTINTLASTDEPAMTLASTDEPDTLASTWDLAMTLASTWDLDT
1002342025-07-01312.450.0042,350.005.285,000.002025-07-01200.002025-06-28
2008912025-07-0145.00150.001,200.002.10500.002025-06-15800.002025-06-30

Table 6: DNA_ACCTBALHIST — Account Balance History​

Historical daily balances broken down by sub-account. Useful for accounts with multiple sub-balances (principal, interest, escrow). More granular than ACCTCOMMON.

Grain: 1 row = 1 account + 1 sub-account + 1 day

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2SUBACCTNBRNUMBER(4)Sub-account (1=principal, 2=interest, 3=escrow)
3EFFDATEDATEDate of the balance
4BALAMTNUMBER(15,2)Balance amount for this sub-account
5PREVBALAMTNUMBER(15,2)Previous day's balance
6BALCHGAMTNUMBER(15,2)Daily change amount (BALAMT − PREVBALAMT)
7BALTYPCDVARCHAR(4)Balance type code (LEDG, AVAIL, COLL)
8MINBALAMTNUMBER(15,2)Minimum balance during the day
9ANB_IDVARCHAR(4)Bank/institution identifier
10DATELASTMAINTDATELast maintenance/update date

Sample Data:

ACCTNBRSUBACCTEFFDATEBALAMTPREVBALAMTBALCHGAMTBALTYPCDMINBALAMTANB_IDMAINT
10023412025-07-0142,350.0037,350.005,000.00LEDG37,350.00ANB2025-07-01
10023422025-07-0115.8410.565.28LEDG10.56ANB2025-07-01

Table 7: DNA_ACCTCOMBO — Account Combinations / Relationships​

Links accounts that belong together (e.g., checking + savings under same customer, or joint accounts). Defines account-to-account relationships.

Grain: 1 row = 1 account-to-account relationship link

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (primary account)
2COMBOACCTNBRVARCHAR(20)The linked/related account number
3COMBORELCDVARCHAR(4)Relationship type (JNT=Joint, LNK=Linked, BEN=Beneficiary)
4COMBOSEQNBRNUMBER(4)Sequence number for ordering relationships
5COMBOSTATCDVARCHAR(2)Combo status (A=Active, I=Inactive)
6COMBODESCVARCHAR(40)Description of the relationship
7COMBOSTARTDATEDATEDate relationship was established
8COMBOENDDATEDATEDate relationship ended (NULL if active)
9ANB_IDVARCHAR(4)Bank/institution identifier
10DATELASTMAINTDATELast maintenance/update date

Sample Data:

ACCTNBRCOMBOACCTNBRCOMBORELCDSEQSTATCOMBODESCSTARTDATEENDDATEANB_IDMAINT
100234100235LNK1ALinked Savings2019-03-15NULLANB2025-01-10
100234200891JNT2AJoint HELOC2021-08-22NULLANB2025-01-10

Table 8: DNA_ACCTNAMEADDR — Account Name and Address​

Customer name and mailing address tied to each account. An account can have multiple name/address records (primary, mailing, business). Used for customer identification.

Grain: 1 row = 1 name/address record per account

#ColumnTypeDescription
1ACCTNBRVARCHAR(20)FK → DNA_ACCT (which account)
2FIRSTNMVARCHAR(40)Customer first name
3LASTNMVARCHAR(40)Customer last name
4ADDR1VARCHAR(60)Street address line 1
5ADDR2VARCHAR(60)Street address line 2 (suite, apt, etc.)
6CITYVARCHAR(30)City name
7STATEVARCHAR(2)State abbreviation (TX, OK, NM, CO)
8ZIPCDVARCHAR(10)ZIP code (5 or 9 digit)
9ADDRTYPCDVARCHAR(4)Address type (PRIM=Primary, MAIL=Mailing, BUS=Business)
10DATELASTMAINTDATELast maintenance/update date

Sample Data:

ACCTNBRFIRSTNMLASTNMADDR1ADDR2CITYSTATEZIPCDADDRTYPCDMAINT
100234JohnSmith4520 Main StreetSuite 3AmarilloTX79101PRIM2024-11-05
100234JohnSmithPO Box 1234NULLAmarilloTX79105MAIL2024-11-05

4. Why KPIs Are Tied to Data Modeling​

The Golden Rule

You cannot build a KPI that your data model doesn't support. KPIs are the business output of your dimensional model — they answer "What questions can this Star Schema actually answer?"

A well-designed Star Schema doesn't exist in a vacuum. Its entire purpose is to enable measurable business outcomes — and those outcomes are expressed as KPIs (Key Performance Indicators). Here's the relationship:

How the DNA_ACCT Model Powers KPIs​

The DNA ACCT Star Schema supports KPIs across four business domains:

Domain 1: Account Analytics (Business Banking)​

KPISource TableKey Columns
Total Active AccountsDNA_ACCTCURRACCTSTATCD, PRODCD
New Accounts Opened (per month)DNA_ACCTOPENDATE
Average Balance by ProductDNA_WH_ACCTCOMMONBOOKBALANCE, PRODUCT
Dormant Account RateDNA_ACCTCURRACCTSTATCD = 'D'
Maturity Schedule (CDs/Loans)DNA_ACCTDATEMAT, PRODCD

Domain 2: Transaction Analytics (Operations)​

KPISource TableKey Columns
Daily Transaction VolumeDNA_RTXNORIGPOSTDATE, RTXNTYPCD
Average Transaction SizeDNA_RTXNTRANAMT by RTXNTYPCD
Channel Distribution (ATM vs POS vs ACH)DNA_RTXNSRCCD
Fee RevenueDNA_RTXNRTXNTYPCD = 'FEE', TRANAMT
Deposit vs Withdrawal RatioDNA_RTXNRTXNTYPCD IN ('DEP','WDL')

Domain 3: Digital Banking (Architect Level)​

KPISource TableKey Columns
Digital Login Volume (logins/day/month)DETAILEDUSERLOGINLoginDateTime, Channel
Digital Adoption Rate (% customers using digital)USERACTIVITYUserId, ActivityType
Transfer Volume (count & $ transferred)BOOKTRANSFERACTIVITYAmount, Date

Domain 4: Data Quality / Operations​

KPISourceNotes
Data Load Freshness (hours since last load)Audit columnsADT_LOAD_DATE across all Bronze tables
dbt Test Pass Rate (% tests passing)EDW_PROD.TEST_AUDIT3,653 test tables
Daily Ingestion Volume (records loaded/day)All sources~3-5M records/day

5. Mapping KPIs Back to the Star Schema​

Here is a concrete example of how a KPI query maps to the Star Schema:

-- KPI: Average Balance by Product (Last 30 Days)
-- This query joins the FACT (daily snapshot) with the DIMENSION (master account)

SELECT
acct.PRODCD AS product_code,
COUNT(DISTINCT acct.ACCTNBR) AS total_accounts,
ROUND(AVG(snap.BOOKBALANCE), 2) AS avg_balance,
ROUND(SUM(snap.BOOKBALANCE), 2) AS total_deposits
FROM DNA_ACCT acct
JOIN DNA_WH_ACCTCOMMON snap
ON acct.ACCTNBR = snap.ACCTNBR
WHERE snap.EFFDATE >= CURRENT_DATE - 30
AND acct.CURRACCTSTATCD = 'A'
GROUP BY acct.PRODCD
ORDER BY total_deposits DESC;
-- KPI: Transaction Channel Distribution (Current Month)

SELECT
rtxn.SRCCD AS channel,
COUNT(*) AS txn_count,
ROUND(SUM(ABS(rtxn.TRANAMT)), 2) AS total_volume,
ROUND(AVG(ABS(rtxn.TRANAMT)), 2) AS avg_txn_size
FROM DNA_ACCT acct
JOIN DNA_RTXN rtxn
ON acct.ACCTNBR = rtxn.ACCTNBR
WHERE rtxn.ORIGPOSTDATE >= DATE_TRUNC('MONTH', CURRENT_DATE)
AND acct.CURRACCTSTATCD = 'A'
GROUP BY rtxn.SRCCD
ORDER BY txn_count DESC;

6. Key Takeaways​

ConceptWhat You Learned
Star SchemaDNA_ACCT is the central dimension; all other tables radiate outward via ACCTNBR
Fact vs DimensionDNA_RTXN (75M rows) is the fact table; DNA_ACCT is the dimension
GrainEach table has a clear grain (1 row = 1 account, 1 transaction, 1 daily snapshot, etc.)
Foreign KeysEvery child table uses ACCTNBR as FK back to the central dimension
1:1 vs 1:ManyDNA_RTXN → DNA_RTXNBAL is 1:1; all others are 1:Many from DNA_ACCT
Snapshot TablesDNA_WH_ACCTCOMMON and DNA_WH_ACCTDEPOSIT capture daily snapshots for trend analysis
KPIs from ModelsKPIs are only possible if the data model captures the right grain and attributes