75 ) Banking data model - explaination

 

Step 1: The Client's Existing System and Tables

In my previous banking project, the client was using separate legacy systems for different financial products and customer operations:

• Core Banking System (DB2 / Oracle): This handled daily customer accounts, deposits, and withdrawals. The main tables were:

  • Customer_Master: Stored customer ID, personal details, KYC info, and contact details.

  • Account_Master: Stored account numbers, account types (Savings, Current, Fixed Deposit), opening dates, and current balances.

  • Branch_Master: Stored branch IDs, branch names, and regional locations.

• Loan Management System (SQL Server): This handled all lending operations (Home Loans, Personal Loans, Car Loans). The main tables were:

  • Loan_Application: Stored loan IDs, requested amounts, approval status, and credit scores.

  • Loan_Repayment_Schedule: Stored monthly installment due dates, principal amounts, and interest breakdowns.

Step 2: The Problems They Faced

Because customer deposits and loan information were trapped in separate systems, the bank faced major operational blocks:

• Data Silos: The bank couldn't easily calculate a customer's "Total Relationship Value" (seeing how much money a customer has in savings versus how much debt they owe on loans in a single view).

• Slow Regulatory and Risk Reporting: Generating compliance reports (like loan default trends or monthly deposit inflows) required running slow, manual queries across DB2 and SQL Server, often leading to delayed submissions to regulators.

• No Historical Tracking: If a customer's credit score changed or their loan interest rate was modified, the old systems overwrote the previous values, preventing risk teams from running predictive trend analysis.

Step 3: The Client's Expectation and Why

• The Goal: They wanted a centralized data warehouse that combined customer accounts, loans, and transaction activity into a single source of truth.

• The Reason: They needed to track daily deposits, monitor non-performing assets (NPAs/loan defaults), and analyze cross-selling opportunities (e.g., offering pre-approved loans to customers with high savings balances) without slowing down live transactional systems.

Step 4: The Solution Design I Proposed (Star Schema)

1. Defined the Business Process

We focused our core analytical areas on Account Transactions (deposits/withdrawals) and Loan Disbursements/Repayments.

2. Designed the Dimension Tables (Context)

• Dim_Customer: Tracked customer demographic changes over time using SCD Type 2 (e.g., income level, address).

• Dim_Account: Categorized accounts into Savings, Checking, or Corporate.

• Dim_Branch: Linked transactions back to specific bank branches and regions.

• Dim_Date: Standardized timeline for daily, monthly, and fiscal year financial reporting.

3. Designed the Fact Tables (Numbers & Events)

• Fact_Account_Transactions: Stored every deposit, withdrawal, and transfer along with transaction amounts and fees.

• Fact_Loan_Payments: Stored scheduled vs. actual loan payments, principal amounts paid, interest collected, and default flags.

Interview Questions and Answers: Banking Project Data Model

Q1: Can't the bank run these risk and compliance reports directly on the Core Banking OLTP system?

Answer:

No. Core Banking systems (OLTP) are optimized for ultra-fast, single-row locking operations—like a customer withdrawing cash at an ATM or swiping a debit card. Running heavy analytical queries across millions of historical transactions to calculate monthly loan defaults or regulatory risk metrics would lock tables and cause severe latency or downtime for customers at bank branches and online portals. A Data Warehouse isolates analytical workloads completely from live production systems.

Q2: How do you handle account balance updates in a banking data warehouse? Are balances additive?

Answer:

Account balances are Semi-Additive facts. You can add up account balances across different branches for a single specific day to get the total bank liquidity, but you cannot add a month-end balance across 30 days of the year (as that would artificially inflate the number). To handle this, we store daily snapshots of account balances in a snapshot fact table rather than transaction-level logs.

Q3: What if you have to add a new regulatory compliance column or dimension later? Does it break historical reports?

Answer:

Adding a new dimension attribute (like adding a "Customer Risk Rating" category) doesn't break historical reports as long as it's added as a new column with default or historical fallback values for past records. If we need to add a brand-new metric that requires uncaptured historical transaction data, we document the gap clearly and ensure the ETL pipeline populates it moving forward.

Q4: How do you handle late-arriving transactions, such as a wire transfer or check clearance that posted late?

Answer:

We rely on transaction effective dates rather than system load dates. When a late-arriving transaction is processed during the nightly ETL run, our pipeline reads its actual effective date, places it into the corresponding historical date partition in the fact table, and triggers an incremental recalculation for that specific period's aggregates.

Q5: What is a "Factless Fact Table" in a banking context? Give an example.

Answer:

A factless fact table tracks events or conditions where no monetary metric is measured. In banking, we used a factless fact table for Customer KYC Compliance Tracking (Fact_Customer_KYC_Check). It recorded which customers completed their annual identity verification, on what date, and at which branch, allowing compliance teams to report on audit completion rates without dealing with financial amounts.

Q6: How do you manage Slowly Changing Dimensions (SCD) for a customer whose credit score or income bracket changes frequently?

Answer:

We use SCD Type 2 for critical attributes like income brackets or credit risk tiers. When a customer's credit score changes, we close out the old dimension record by updating an expiration date and setting an active flag to false, then insert a new row with the updated score and a new surrogate key. This ensures historical loan performance reports accurately reflect the customer's risk profile at the exact time the loan was issued.

Q7: How do you handle missing dimension keys for transactions coming from newly opened branches or new product types?

Answer:

We use a Default or "Unknown" Member pattern (assigned an ID of -1). If a transaction arrives for a newly created branch code that hasn't synced into the data warehouse dimension table yet, the ETL pipeline assigns the -1 default key instead of failing. This keeps the data pipeline running smoothly, prevents data loss, and flags an alert for the data engineering team to update the master dimension table.

Q8: How do you prevent duplicate transaction records from entering the banking data warehouse?

Answer:

Banking data must be 100% accurate. We prevent duplicates through:

1. Staging Layer Cleansing: Using unique transaction hashes and window functions (ROW_NUMBER()) during the staging phase to drop exact technical duplicates.

2. Primary/Composite Keys: Enforcing strict unique constraints on natural business keys in the data warehouse (such as combining Transaction_Reference_Number, Account_ID, and Timestamp).

Q9: How do you handle data ingestion frequencies that vary across sources (e.g., real-time fraud monitoring vs. monthly loan summaries)?

Answer:

We use a tiered ingestion architecture:

• Micro-Batch/Streaming: For high-priority operational metrics like fraud detection or ATM withdrawals, data flows through streaming pipelines (like Kafka) into near-real-time staging layers.

• Batch/Scheduled Loads: For standard accounting summaries, end-of-day balances, and monthly loan performance metrics, we run scheduled ETL jobs overnight using partition-based loads to optimize compute resources.

Q10: If a business stakeholder asks to merge two different legacy bank databases that use conflicting ID formats, how do you resolve it during data modeling?

Answer:

We resolve this during the staging and transformation phase by creating a Master Data Management (MDM) mapping layer or lookup table. If System A uses customer ID CUST-101 and System B uses B_101 for the same individual, our ETL transformation script maps both natural keys to a single, unified surrogate key (Customer_Key) before loading them into the dimensional data warehouse.