Core Banking Analytics data model
Kimball warehouse for core banking: transaction-grain ledger events, daily account-balance snapshots, an accumulating loan snapshot, and card authorizations over conformed customer/account/branch/product dimensions. KYC and account identifiers are masked.
Retail banking analytics covering customer accounts, ledger transactions, daily balance snapshots, the loan lifecycle, and card authorizations — a Kimball star schema with conformed customer, account, branch, and product dimensions and regulated PII (tax ID, account number) classified and masked. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 12
- tables
- 104
- columns
- 18
- relationships
- 10
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Daily balance snapshot separate from transactions
Balances are semi-additive and required daily for regulatory and liquidity reporting independent of transaction volume.
Accumulating snapshot for loans
A loan has well-defined milestones (origination, maturity, payoff) and a slowly-changing outstanding balance — an accumulating snapshot fits.
Masking on tax ID and account number
GLBA and PCI require restricting access to account numbers and SSNs; they are classified restricted and masked.
Tables in the Core Banking Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (4)
fct_transactions
Grain: one row per account transaction
Transaction-grain fact — the core ledger event.
| Column | Type | Keys |
|---|---|---|
| transaction_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| customer_key | bigint | |
| branch_key | bigint | |
| transaction_type_key | bigint | |
| channel_key | bigint | |
| merchant_key | bigint | |
| transaction_id | varchar | |
| amount | decimal | |
| balance_after | decimal | |
| currency | varchar | |
| is_international | boolean |
fct_account_balance_snapshot
Grain: one row per account per day (periodic snapshot)
Daily balance snapshot — semi-additive.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| account_key | bigint | |
| customer_key | bigint | |
| closing_balance | decimal | |
| available_balance | decimal | |
| interest_accrued | decimal | |
| is_overdrawn | boolean | |
| days_overdrawn | smallint |
fct_loan
Grain: one row per loan (accumulating snapshot)
Accumulating snapshot tracking the loan lifecycle.
| Column | Type | Keys |
|---|---|---|
| loan_key | bigint | PK |
| account_key | bigint | |
| customer_key | bigint | |
| product_key | bigint | |
| origination_date_key | bigint | |
| maturity_date_key | bigint | |
| loan_number | varchar | |
| principal_amount | decimal | |
| interest_rate | decimal | |
| term_months | smallint | |
| outstanding_balance | decimal | |
| monthly_payment | decimal | |
| loan_status | varchar | |
| days_past_due | integer |
fct_card_authorization
Grain: one row per card authorization
| Column | Type | Keys |
|---|---|---|
| authorization_key | bigint | PK |
| date_key | bigint | |
| account_key | bigint | |
| merchant_key | bigint | |
| channel_key | bigint | |
| authorization_id | varchar | |
| authorization_amount | decimal | |
| is_approved | boolean | |
| decline_reason | varchar | |
| is_fraud_flagged | boolean |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_business_day | boolean |
dim_customer
Grain: one row per customer per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| tax_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | PII |
| customer_segment | varchar | |
| kyc_status | varchar | |
| risk_rating | varchar | |
| onboarding_date | date | |
| is_active | boolean |
dim_account
Grain: one row per account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_number | varchar | PII |
| account_type | varchar | |
| product_key | bigint | |
| currency | varchar | |
| open_date | date | |
| close_date | date | |
| account_status | varchar |
dim_branch
Grain: one row per branch
| Column | Type | Keys |
|---|---|---|
| branch_key | bigint | PK |
| branch_id | varchar | BK |
| branch_name | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| region | varchar | |
| manager_name | varchar | PII |
dim_product
Grain: one row per banking product
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| product_name | varchar | |
| product_category | varchar | |
| interest_rate | decimal | |
| monthly_fee | decimal |
dim_merchant
Grain: one row per card merchant
| Column | Type | Keys |
|---|---|---|
| merchant_key | bigint | PK |
| merchant_id | varchar | BK |
| merchant_name | varchar | |
| mcc | varchar | |
| merchant_category | varchar | |
| country | varchar |
Lookup tables (2)
dim_transaction_type
Grain: one row per transaction type
| Column | Type | Keys |
|---|---|---|
| transaction_type_key | bigint | PK |
| type_code | varchar | BK |
| type_name | varchar | |
| direction | varchar |
dim_channel
Grain: one row per channel
| Column | Type | Keys |
|---|---|---|
| channel_key | bigint | PK |
| channel_code | varchar | BK |
| channel_name | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dim_account.product_key → dim_product (many-to-one)
- fct_transactions.date_key → dim_date (many-to-one)
- fct_transactions.account_key → dim_account (many-to-one)
- fct_transactions.customer_key → dim_customer (many-to-one)
- fct_transactions.branch_key → dim_branch (many-to-one)
- fct_transactions.transaction_type_key → dim_transaction_type (many-to-one)
- fct_transactions.channel_key → dim_channel (many-to-one)
- fct_transactions.merchant_key → dim_merchant (many-to-one)
- fct_account_balance_snapshot.date_key → dim_date (many-to-one)
- fct_account_balance_snapshot.account_key → dim_account (many-to-one)
- fct_account_balance_snapshot.customer_key → dim_customer (many-to-one)
- fct_loan.account_key → dim_account (many-to-one)
- fct_loan.customer_key → dim_customer (many-to-one)
- fct_loan.product_key → dim_product (many-to-one)
- fct_card_authorization.date_key → dim_date (many-to-one)
- fct_card_authorization.account_key → dim_account (many-to-one)
- fct_card_authorization.merchant_key → dim_merchant (many-to-one)
- fct_card_authorization.channel_key → dim_channel (many-to-one)
Related templates
All templates →- Kimball
AML & Financial-Crime Surveillance
Banking & Financial Services
- Kimball
Capital Markets Trading & Investment
Banking & Financial Services
- 3NF
Investment Management Platform — IBOR/ABOR (3NF)
Banking & Financial Services
- Data Vault 2.0
Investment Management EDW — Aladdin-style (Data Vault 2.0)
Banking & Financial Services
From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.