Credit Risk Analytics with Early Warning Signals | Python, MySQL
For this project, I worked on a credit risk analytics pipeline using Lending Club loan data. The goal was to move beyond simple loan reporting and build a workflow that can identify risky loans early, classify portfolio risk clearly, and support business-style monitoring.
This project combines Python for large-file processing and MySQL for risk logic, signal engineering, and portfolio-level analysis. I wanted this project to feel close to how a real financial analytics or risk team would work with raw loan data in practice.
Project goal
In lending, it is not enough to only know whether a loan has already defaulted. Financial institutions also need to identify warning signs before a borrower becomes seriously delinquent.
That is the main idea behind this project.
I built a pipeline to answer questions like:
Which loans are already in default or late stages?
Which current loans show early warning signs of stress?
How can the portfolio be grouped into meaningful risk buckets?
Where is risk concentrated in terms of exposure and borrower profile?
Instead of treating this as only a data cleaning exercise, I approached it like a mini production-style analytics project.
Dataset and processing approach
The raw dataset was a large Lending Club loan file with borrower, loan, repayment, and credit profile information. Since the file was large, I used chunked ingestion in Python to make the process more scalable and memory-friendly.
In the Python cleaning step, I selected only the columns needed for analysis, such as:
loan amount and funded amount
interest rate and installment
loan status and term
annual income and employment length
credit utilization and revolving balance
FICO ranges
payment history and outstanding principal
I also standardized important fields before loading the data into SQL:
converted date columns into proper datetime format
cleaned percentage columns like interest rate and revolving utilization
extracted the numeric value from loan term
converted IDs and numeric columns into usable formats
This produced a clean core dataset called loan_core.csv, which became the base for the rest of the analysis.
Building the core loan table
After cleaning the file in Python, I created a structured SQL table called loan_core and loaded the processed CSV into MySQL.
This stage was important because it separated raw file handling from analytical logic. Once the clean dataset was in SQL, I could focus on business rules, flags, and portfolio reporting more clearly.
The idea was to create a strong base table first, then build risk layers on top of it step by step.
Risk labeling logic
The next step was to create a clear risk framework using loan status.
I built a table called loan_core_risk where each loan was classified into a simplified risk view. I added two main fields:
default_flag → whether the loan should be treated as defaulted
risk_state → a broader status grouping such as DEFAULT, LATE, PAID, CURRENT, or OTHER
This helped convert messy business statuses into a more standardized structure. For example, loans marked as Charged Off or Default were mapped into the default category, while grace-period and late-payment statuses were grouped under late risk.
This step made the portfolio much easier to analyze because instead of working with many raw loan status values, I could work with a smaller and more meaningful risk classification.
Early warning signal engineering
One of the most important parts of the project was building early warning signals.
The idea here was to detect financial stress before the loan becomes a full default case. I created a new SQL table called loan_core_ews and engineered three main warning signals:
1. High utilization flag
This flag identifies borrowers using a high percentage of their revolving credit. If revolving utilization is above 80%, it may suggest financial pressure or reduced borrowing capacity.
2. Low credit buffer flag
This signal checks whether the borrower has very little available revolving credit left. I calculated the difference between total revolving credit limit and revolving balance. If the remaining credit buffer is very low, it can be a warning sign.
3. High leverage flag
This measures whether the loan amount is high relative to annual income. If a borrower’s loan amount is too large compared to income, repayment stress may be more likely.
Then I combined these conditions into a composite early_warning_flag. If any of these warning conditions were present, the loan was marked as having an early warning signal.
This part of the project reflects real-world credit monitoring, where lenders try to identify stress patterns before loans move into serious delinquency.
Risk bucketing strategy
After generating both default labels and early warning signals, I created the final risk segmentation table: loan_core_bucketed.
In this step, each loan was assigned into one of four portfolio risk buckets:
LOW → no warning signals and not in default
MEDIUM → early warning present, but not currently late or defaulted
HIGH → loan is already late
DEFAULT → confirmed default cases
This bucketing made the portfolio much more actionable. Instead of only checking raw flags, I could now summarize the book of business in a way that supports management reporting and prioritization.
For example:
LOW-risk loans may only need regular monitoring
MEDIUM-risk loans may need preventive attention
HIGH-risk loans may require urgent review
DEFAULT loans represent realized risk
This is a practical way to translate raw data into decisions.
Portfolio-level analysis
Once the buckets were created, I used SQL aggregation to summarize the portfolio.
I calculated measures such as:
number of loans in each bucket
percentage of total portfolio
total loan exposure
outstanding principal
total defaults
average utilization
average leverage
These metrics help show not only how many risky loans exist, but also how much money is tied to each risk level. That makes the analysis more useful from a financial risk perspective.
For example, a bucket may contain fewer loans but still represent high exposure because of larger loan amounts or outstanding balances.
Why this project matters
What I like about this project is that it connects data engineering, analytics, and business thinking together.
It is not just about loading a CSV and writing a few queries. It shows a full workflow:
ingesting a large raw file efficiently
selecting useful columns for analysis
standardizing messy values
building a clean SQL base table
applying business-friendly risk logic
engineering early warning indicators
segmenting the portfolio into actionable risk buckets
summarizing exposure at portfolio level
This makes the project feel much closer to real work in banking, lending, risk analytics, or analytics engineering.
Tools used
I used:
Python (pandas) for chunked ingestion and data standardization
MySQL for risk logic, signal engineering, and portfolio reporting
GitHub for project organization and reproducibility
Using both Python and SQL made the project stronger because each tool handled the part it is best suited for.
Final thoughts
This project helped me think more like a credit risk analyst and analytics engineer. I was able to work with a large loan dataset, create structured risk definitions, and build an early warning system that supports proactive monitoring.
For my portfolio, I wanted this project to show that I can do more than basic SQL or simple dashboarding. I can take raw financial data, clean it properly, apply business logic, and turn it into a risk monitoring workflow that is useful for decision-making.
This project is a good example of how analytics can be used not just to describe what already happened, but also to flag what may happen next.
Comments