Retail Banking - Data Models

Retail Banking - Data Models

Retail Banking services offered by a financial institution for individual consumers and serve the following functions:

Deposits Accounts – Surplus from individual customers as savings and pay interest.

Loan / Credit Accounts – Banks offer loans or credit to individual customers and earn interest.

Cash management – Banks offers variety of services to manage and transact their money. For instance – ATM, Cards, UPI, Online Transfers etc.

Note – Retail banking is vast, and in this blog series am trying to narrow down and build a use case so that I can explain in detail the architecture, design principles, data model, data engineering, governance and visualization concepts E2E

Retail Banking in Data perspective

Accounts Details AccountDM.png

Customer Details CustomerDM.png

Transaction Details TransactionDM.png

Assume Mr. and Mrs. Dhoni walks to nearby bank, below are step by step process:

Bank verifies the customers, request for identity documents, does background checks and generates CIF (Customer Information File). Understand the customer needs and recommends products as per needs of customer, for instance,

Mr. DhoniA1Savings Account
Mr. DhoniA2Term Deposit
Mrs. DhoniA2Term Deposit
Mrs. DhoniA3Savings Account

As per above table,

  • Mr and Mrs Dhoni have opened a personal saving account for day-to-day transactional activities.
  • Mr and Mrs Dhoni have opened a joint term deposit account, to lock an amount of money for an agreed length of time (the ‘term’) to get a guaranteed rate of interest for the term selected. From data perspective, we understand that Customer Account is many to many relationship

Mr. Dhoni now wants to transfer money to his friend Mr. Sachin hence adds a payee. Payee is related with Mr Dhoni.

Mr Dhoni performs the following tasks,

  1. Deposits cash into his newly created account “A1”
  2. Performs a payment from his account to newly added payee.
  3. Payee, can be to account details (or) mobile (or) email-id etc.

Payment table captures from customer, from account, payee and amount transferred details.

Both cash deposit and account to account transfer are two separate transactions, entries are made in Transaction table.

While going through data model, understand, considering below points :

  • Please keep in mind retail banking is a vast subject. Have tried my best to make data model easy to understand and almost look complete from data engineer perspective.

  • Customer can originate through mobile app, customer care (telephonic), branch, web site, or others (broker) etc.

  • Data is stored in database and data modelling plays a critical part in data management, governance, and intelligence. Have defined normalized, simple, well defined, and organized data model.

  • Relationship between the tables. Well defined primary and foreign keys.

  • Audit fields for monitoring activities and compliance.

  • For CUSTOMER, limiting my scope to only “INDIVIDUALS”. Other possible CUSTOMER_PARTY_TYPE “ORGANIZATION”, “VISITOR” etc. and the data model can expand by addition of new customer party.

  • For ACCOUNT, limiting my scope to only “SAVINGS”, “PERSONAL LOAN”, “CREDIT ACCOUNT” (Credit card account) and “MORTGAGE” (Home Loan).

  • Finally, the Transaction and Product are other entities.

I understand the content is theoretical but please spend sometime and understand. In upcoming episodes when we start tasks technically, good understanding of source system and requirements will make it easier and the process enjoyable.

Upcoming episode, we will play architect role build data pipeline and understand, how the data flows from source data model (OLTP) to data lake and then into data warehouse (OLAP). Also, will define real time use cases from perspective of KYC, marketing and campaign and Anti-money Laundering teams which is common to most of banks.

In case you have missed out on my previous episodes refer to

Community and Social Footprints :

Did you find this article valuable?

Support Cloudnloud Tech Community by becoming a sponsor. Any amount is appreciated!