A banking analytics model couples an append-only transaction fact table with a daily periodic snapshot table to track account balances over time. Joint accounts and multi-holder relationships are resolved using a customer-account bridge table, with strict column-level security and data tokenization applied to protect sensitive financial records.
Banking transactions and daily balances
Banking analytics demands two core fact tables operating at distinct grains:
fact_transactions (grain: one row per financial ledger event) transaction_id (degenerate dimension) account_key origination_branch_key servicing_branch_key transaction_date_key settlement_date_key channel_key (atm, online_banking, pos, wire, branch) merchant_key transaction_type (debit, credit, transfer, fee) amount (positive for credit, negative for debit) running_ledger_sequence_number fact_account_daily_balances (periodic snapshot: one row per account per day) account_key date_key ending_balance_amount available_balance_amount total_credit_amount total_debit_amount transaction_count
Core analytical distinctions between these tables:
- Ledger transactions are strictly immutable and append-only. Corrections or transaction reversals generate new offsetting ledger rows rather than updating historical records.
- Transaction records distinguish between posting dates and effective settlement dates to handle weekend bank processing lags and clearinghouse delays accurately.
- Branches use role-playing dimension keys to separate the branch where an account was opened from the branch where a cash transaction physically occurred.
- Transaction fact tables also record spot exchange rates at the moment of execution for international wire transfers and foreign debit card transactions, allowing multi-currency transactions to convert into the domestic reporting currency while preserving local amounts for customer bank statements.
- Ending balance is semi-additive: it sums cleanly across accounts and branches for any given day, but summing balances across dates is mathematically invalid.
Account dimensions and joint holder bridges
Account attributes change over time as customers upgrade account tiers or reassign primary branches, requiring SCD Type 2 tracking in dim_account. In banking, account ownership is many-to-many: a single checking account can have multiple joint holders, and one customer can hold multiple credit cards, mortgages, and checking accounts. Resolve this relationship using bridge_account_customers:
account_key | customer_key | relationship_type | ownership_share_percent 1001 | 501 | Primary Holder | 50.00 1001 | 502 | Joint Holder | 50.00
Weighting ownership share allows financial analysts to allocate portfolio balances across customer demographics accurately without double counting assets.
Immutability and compliance controls
Banking data models must satisfy rigorous regulatory compliance, auditability, and data privacy standards such as SOX and BCBS 239. Financial ledgers prohibit hard deletions; any adjustment requires a verifiable journal entry that reconciles against general ledger trial balances. Personally Identifiable Information (PII) like tax identification numbers and full account numbers must never sit in plain text inside dimension tables. Store tokenized surrogates in analytical tables, keeping raw PII encrypted in vault databases accessible only through masked views with strict role-based access control.