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.
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 Table | Child Table | Join Key | Cardinality | Business Meaning |
|---|---|---|---|---|
DNA_ACCT | DNA_RTXN | ACCTNBR | 1 : Many | One account has many transactions |
DNA_ACCT | DNA_WH_ACCTCOMMON | ACCTNBR | 1 : Many | One account has many daily snapshots |
DNA_ACCT | DNA_WH_ACCTDEPOSIT | ACCTNBR | 1 : Many | One account has many deposit snapshots |
DNA_ACCT | DNA_ACCTBALHIST | ACCTNBR | 1 : Many | One account has many balance history rows |
DNA_ACCT | DNA_ACCTCOMBO | ACCTNBR | 1 : Many | One account can be part of many combos |
DNA_ACCT | DNA_ACCTNAMEADDR | ACCTNBR | 1 : Many | One account has many name/address records |
DNA_RTXN | DNA_RTXNBAL | ACCTNBR + RTXNNBR | 1 : 1 | Each 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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | PK — Unique account number |
| 2 | MJACCTTYPCD | VARCHAR(2) | Major account type (D=Deposit, L=Loan, C=CD) |
| 3 | CURRACCTSTATCD | VARCHAR(2) | Current status (A=Active, C=Closed, D=Dormant) |
| 4 | BRANCHORGNBR | NUMBER(5) | Branch org number where account is held |
| 5 | OPENDATE | DATE | Date the account was originally opened |
| 6 | DATEMAT | DATE | Maturity date (for CDs and loans, NULL for checking) |
| 7 | PRODCD | VARCHAR(10) | Product code (CHK01, SAV02, CD360, HELOC) |
| 8 | OFFCRNBR | NUMBER(6) | Assigned officer/relationship manager number |
| 9 | ANB_ID | VARCHAR(4) | Bank/institution identifier (always 'ANB') |
| 10 | DATELASTMAINT | DATE | Date record was last updated in source system |
Sample Data:
| ACCTNBR | MJACCTTYPCD | CURRACCTSTATCD | BRANCHORGNBR | OPENDATE | DATEMAT | PRODCD | OFFCRNBR | ANB_ID | MAINT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | D | A | 101 | 2019-03-15 | NULL | CHK01 | 4521 | ANB | 2025-07-01 |
| 200891 | L | A | 205 | 2021-08-22 | 2031-08-22 | HELOC | 3310 | ANB | 2025-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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | RTXNNBR | NUMBER(12) | PK — Unique transaction sequence number |
| 3 | RTXNTYPCD | VARCHAR(4) | Transaction type (DEP, WDL, XFR, FEE, INT) |
| 4 | TRANAMT | NUMBER(15,2) | Transaction amount (+credit, −debit) |
| 5 | ORIGPOSTDATE | DATE | Original posting date |
| 6 | EFFDATE | DATE | Effective date (when funds available) |
| 7 | RTXNCD | VARCHAR(4) | Transaction code (more granular than type) |
| 8 | DESCR | VARCHAR(60) | Transaction description / memo |
| 9 | SRCCD | VARCHAR(4) | Source channel (ATM, POS, ACH, TEL, BRN) |
| 10 | DATELASTMAINT | DATE | Date record was last maintained |
Sample Data:
| ACCTNBR | RTXNNBR | RTXNTYPCD | TRANAMT | ORIGPOSTDATE | EFFDATE | RTXNCD | DESCR | SRCCD |
|---|---|---|---|---|---|---|---|---|
| 100234 | 987654001 | DEP | 5,000.00 | 2025-07-01 | 2025-07-01 | DCHK | Payroll Direct Dep | ACH |
| 100234 | 987654002 | WDL | -200.00 | 2025-07-02 | 2025-07-02 | WATM | ATM Withdrawal | ATM |
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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | RTXNNBR | NUMBER(12) | FK → DNA_RTXN (which transaction) |
| 3 | SUBACCTNBR | NUMBER(4) | Sub-account number (1=primary, 2=interest) |
| 4 | AMT | NUMBER(15,2) | Running balance after this transaction |
| 5 | PREVBALAMT | NUMBER(15,2) | Balance before this transaction posted |
| 6 | HOLDAMT | NUMBER(15,2) | Hold amount (pending funds not yet released) |
| 7 | AVAILBALAMT | NUMBER(15,2) | Available balance (AMT minus holds) |
| 8 | BALCATCD | VARCHAR(4) | Balance category code (LEDG, AVAIL, MEMO) |
| 9 | ANB_ID | VARCHAR(4) | Bank/institution identifier |
| 10 | DATELASTMAINT | DATE | Date this record was last maintained |
Sample Data:
| ACCTNBR | RTXNNBR | SUBACCT | AMT | PREVBALAMT | HOLDAMT | AVAILBALAMT | BALCATCD | ANB_ID | MAINT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | 987654001 | 1 | 42,350.00 | 37,350.00 | 0.00 | 42,350.00 | LEDG | ANB | 2025-07-01 |
| 100234 | 987654002 | 1 | 42,150.00 | 42,350.00 | 0.00 | 42,150.00 | LEDG | ANB | 2025-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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | EFFDATE | DATE | Effective/snapshot date |
| 3 | BOOKBALANCE | NUMBER(15,2) | Book balance as of that date |
| 4 | NOTEINTRATE | NUMBER(7,4) | Interest rate on that date |
| 5 | PRODUCT | VARCHAR(30) | Product code/name for the account |
| 6 | AVAILBALANCE | NUMBER(15,2) | Available balance (after holds/floats) |
| 7 | ACCRINTEREST | NUMBER(15,2) | Accrued interest not yet posted |
| 8 | OFFCRNBR | NUMBER(6) | Assigned officer number on that date |
| 9 | ACCTSTATCD | VARCHAR(2) | Account status as of snapshot date |
| 10 | ANB_ID | VARCHAR(4) | Bank/institution identifier |
Sample Data:
| ACCTNBR | EFFDATE | BOOKBALANCE | NOTEINTRATE | PRODUCT | AVAILBAL | ACCRINT | OFFCRNBR | STAT | ANB_ID |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | 2025-07-01 | 42,350.00 | 0.0150 | Premier Chk | 42,350.00 | 5.28 | 4521 | A | ANB |
| 100234 | 2025-07-02 | 42,150.00 | 0.0150 | Premier Chk | 42,150.00 | 10.56 | 4521 | A | ANB |
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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | EFFDATE | DATE | Effective/snapshot date (same as COMMON) |
| 3 | YTDAMTINT | NUMBER(15,2) | Year-to-date interest earned |
| 4 | OVERDRAFTBAL | NUMBER(15,2) | Current overdraft balance (0 if none) |
| 5 | NOTEAVAIL | NUMBER(15,2) | Available balance (after holds) |
| 6 | MTDAMTINT | NUMBER(15,2) | Month-to-date interest earned |
| 7 | LASTDEPAMT | NUMBER(15,2) | Last deposit amount |
| 8 | LASTDEPDATE | DATE | Date of last deposit |
| 9 | LASTWDLAMT | NUMBER(15,2) | Last withdrawal amount |
| 10 | LASTWDLDATE | DATE | Date of last withdrawal |
Sample Data:
| ACCTNBR | EFFDATE | YTDAMTINT | OVERDRAFT | NOTEAVAIL | MTDAMTINT | LASTDEPAMT | LASTDEPDT | LASTWDLAMT | LASTWDLDT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | 2025-07-01 | 312.45 | 0.00 | 42,350.00 | 5.28 | 5,000.00 | 2025-07-01 | 200.00 | 2025-06-28 |
| 200891 | 2025-07-01 | 45.00 | 150.00 | 1,200.00 | 2.10 | 500.00 | 2025-06-15 | 800.00 | 2025-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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | SUBACCTNBR | NUMBER(4) | Sub-account (1=principal, 2=interest, 3=escrow) |
| 3 | EFFDATE | DATE | Date of the balance |
| 4 | BALAMT | NUMBER(15,2) | Balance amount for this sub-account |
| 5 | PREVBALAMT | NUMBER(15,2) | Previous day's balance |
| 6 | BALCHGAMT | NUMBER(15,2) | Daily change amount (BALAMT − PREVBALAMT) |
| 7 | BALTYPCD | VARCHAR(4) | Balance type code (LEDG, AVAIL, COLL) |
| 8 | MINBALAMT | NUMBER(15,2) | Minimum balance during the day |
| 9 | ANB_ID | VARCHAR(4) | Bank/institution identifier |
| 10 | DATELASTMAINT | DATE | Last maintenance/update date |
Sample Data:
| ACCTNBR | SUBACCT | EFFDATE | BALAMT | PREVBALAMT | BALCHGAMT | BALTYPCD | MINBALAMT | ANB_ID | MAINT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | 1 | 2025-07-01 | 42,350.00 | 37,350.00 | 5,000.00 | LEDG | 37,350.00 | ANB | 2025-07-01 |
| 100234 | 2 | 2025-07-01 | 15.84 | 10.56 | 5.28 | LEDG | 10.56 | ANB | 2025-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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (primary account) |
| 2 | COMBOACCTNBR | VARCHAR(20) | The linked/related account number |
| 3 | COMBORELCD | VARCHAR(4) | Relationship type (JNT=Joint, LNK=Linked, BEN=Beneficiary) |
| 4 | COMBOSEQNBR | NUMBER(4) | Sequence number for ordering relationships |
| 5 | COMBOSTATCD | VARCHAR(2) | Combo status (A=Active, I=Inactive) |
| 6 | COMBODESC | VARCHAR(40) | Description of the relationship |
| 7 | COMBOSTARTDATE | DATE | Date relationship was established |
| 8 | COMBOENDDATE | DATE | Date relationship ended (NULL if active) |
| 9 | ANB_ID | VARCHAR(4) | Bank/institution identifier |
| 10 | DATELASTMAINT | DATE | Last maintenance/update date |
Sample Data:
| ACCTNBR | COMBOACCTNBR | COMBORELCD | SEQ | STAT | COMBODESC | STARTDATE | ENDDATE | ANB_ID | MAINT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | 100235 | LNK | 1 | A | Linked Savings | 2019-03-15 | NULL | ANB | 2025-01-10 |
| 100234 | 200891 | JNT | 2 | A | Joint HELOC | 2021-08-22 | NULL | ANB | 2025-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
| # | Column | Type | Description |
|---|---|---|---|
| 1 | ACCTNBR | VARCHAR(20) | FK → DNA_ACCT (which account) |
| 2 | FIRSTNM | VARCHAR(40) | Customer first name |
| 3 | LASTNM | VARCHAR(40) | Customer last name |
| 4 | ADDR1 | VARCHAR(60) | Street address line 1 |
| 5 | ADDR2 | VARCHAR(60) | Street address line 2 (suite, apt, etc.) |
| 6 | CITY | VARCHAR(30) | City name |
| 7 | STATE | VARCHAR(2) | State abbreviation (TX, OK, NM, CO) |
| 8 | ZIPCD | VARCHAR(10) | ZIP code (5 or 9 digit) |
| 9 | ADDRTYPCD | VARCHAR(4) | Address type (PRIM=Primary, MAIL=Mailing, BUS=Business) |
| 10 | DATELASTMAINT | DATE | Last maintenance/update date |
Sample Data:
| ACCTNBR | FIRSTNM | LASTNM | ADDR1 | ADDR2 | CITY | STATE | ZIPCD | ADDRTYPCD | MAINT |
|---|---|---|---|---|---|---|---|---|---|
| 100234 | John | Smith | 4520 Main Street | Suite 3 | Amarillo | TX | 79101 | PRIM | 2024-11-05 |
| 100234 | John | Smith | PO Box 1234 | NULL | Amarillo | TX | 79105 | 2024-11-05 |
4. Why KPIs Are Tied to Data Modeling
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)
| KPI | Source Table | Key Columns |
|---|---|---|
| Total Active Accounts | DNA_ACCT | CURRACCTSTATCD, PRODCD |
| New Accounts Opened (per month) | DNA_ACCT | OPENDATE |
| Average Balance by Product | DNA_WH_ACCTCOMMON | BOOKBALANCE, PRODUCT |
| Dormant Account Rate | DNA_ACCT | CURRACCTSTATCD = 'D' |
| Maturity Schedule (CDs/Loans) | DNA_ACCT | DATEMAT, PRODCD |
Domain 2: Transaction Analytics (Operations)
| KPI | Source Table | Key Columns |
|---|---|---|
| Daily Transaction Volume | DNA_RTXN | ORIGPOSTDATE, RTXNTYPCD |
| Average Transaction Size | DNA_RTXN | TRANAMT by RTXNTYPCD |
| Channel Distribution (ATM vs POS vs ACH) | DNA_RTXN | SRCCD |
| Fee Revenue | DNA_RTXN | RTXNTYPCD = 'FEE', TRANAMT |
| Deposit vs Withdrawal Ratio | DNA_RTXN | RTXNTYPCD IN ('DEP','WDL') |
Domain 3: Digital Banking (Architect Level)
| KPI | Source Table | Key Columns |
|---|---|---|
| Digital Login Volume (logins/day/month) | DETAILEDUSERLOGIN | LoginDateTime, Channel |
| Digital Adoption Rate (% customers using digital) | USERACTIVITY | UserId, ActivityType |
| Transfer Volume (count & $ transferred) | BOOKTRANSFERACTIVITY | Amount, Date |
Domain 4: Data Quality / Operations
| KPI | Source | Notes |
|---|---|---|
| Data Load Freshness (hours since last load) | Audit columns | ADT_LOAD_DATE across all Bronze tables |
| dbt Test Pass Rate (% tests passing) | EDW_PROD.TEST_AUDIT | 3,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
| Concept | What You Learned |
|---|---|
| Star Schema | DNA_ACCT is the central dimension; all other tables radiate outward via ACCTNBR |
| Fact vs Dimension | DNA_RTXN (75M rows) is the fact table; DNA_ACCT is the dimension |
| Grain | Each table has a clear grain (1 row = 1 account, 1 transaction, 1 daily snapshot, etc.) |
| Foreign Keys | Every child table uses ACCTNBR as FK back to the central dimension |
| 1:1 vs 1:Many | DNA_RTXN → DNA_RTXNBAL is 1:1; all others are 1:Many from DNA_ACCT |
| Snapshot Tables | DNA_WH_ACCTCOMMON and DNA_WH_ACCTDEPOSIT capture daily snapshots for trend analysis |
| KPIs from Models | KPIs are only possible if the data model captures the right grain and attributes |