Practical Analysis of a Databricks-Based Bank Transaction Analysis and Risk Monitoring Platform

This article analyzes a practical end-to-end bank transaction analysis and risk monitoring platform project built on Databricks, covering six stages: data engineering, feature engineering, SQL analysis, interactive dashboards, AI intelligent querying, and machine learning risk prediction. The project processes over 10,000 transaction records from approximately 5,000 customers, aiming to establish a modern bank risk control system that meets business analysis needs and identifies potential risk signals. It addresses issues such as the inability of traditional reporting systems to support real-time monitoring and intelligent decision-making, while tackling challenges like class imbalance in synthetic datasets, providing actionable business insights and practical启示.

Project Background

In the digital finance era, banks face challenges of data scale and risk. Traditional reporting systems cannot meet the needs of real-time monitoring and intelligent decision-making. This project builds a comprehensive solution integrating multiple technologies, processing 10,000+ transaction records from 5,000 customers. Its core goal is to establish a modern risk control system for business analysis and risk identification.

Dataset Composition

The synthetic dataset includes dimensions such as customer information (ID, age, city), transaction data (date, amount, merchant category), account type, credit card information, reward points, loan information, and risk signals.

Technical Architecture

Centered on Databricks, it uses Apache Spark/PySpark for data processing, Delta Tables for storage, Databricks Dashboards for visualization, Genie AI for natural language queries, and Spark ML & Scikit-Learn to support machine learning modules, ensuring large-scale data processing capability and enterprise-level reliability.

Data Cleaning Steps

1. Column name standardization: Remove spaces and replace special characters with underscores; 2. Data type validation: Ensure fields use appropriate types; 3. Null value and duplicate detection: Identify data issues; 4. Abnormal field recoding: Convert -1/other values to 0/1 encoding; 5. Date processing: Parse dates and extract year-month, then save cleaned data as Delta tables.

Feature Engineering

• Credit utilization rate: Credit card balance / credit limit, divided into four levels: low/medium/high/over-limit;
• Reward points bucketing: Divided into low/medium/high tiers based on average points;
• Transaction amount range: Divided into four intervals: <100, 100-499, 500-999, ≥1000;
• Risk label engineering: Customers with an average anomaly rate ≥0.5 are marked as high-risk, resulting in a class imbalance of 94% low-risk and 6% high-risk.

SQL Analysis

• Transaction trends: Aggregate transaction count, total amount, and average amount by month;
• Geographic and account type analysis: Group by account type and city to count transaction numbers and amounts;
• Relationship between utilization and anomaly rate: Calculate average anomaly rate for each utilization interval, verifying that higher utilization correlates with higher risk;
• Correlation between reward points and risk: Discover that customers with low rewards have the highest average risk.

Interactive Dashboards

• Transaction dashboard: Displays average transaction amount trend, total transaction volume trend, city vs account type comparison, and transaction amount distribution;
• Loan dashboard: Displays loan status distribution and approval/rejection trends;
• Risk dashboard: Displays the relationship between anomaly rate and utilization, the relationship between reward points and utilization, and average risk by reward bucket.

Databricks Genie AI Assistant

• Semantic layer configuration: Customize metrics like average risk and total transaction count, and filters like city and loan status;
• Query examples: Answer questions about branch anomaly rates, high-risk customers, and high-value high-risk customers;
• Benchmark testing: Compare manually written SQL with Genie-generated SQL to evaluate accuracy.

Machine Learning Applications

• Feature set: Average transaction amount, transaction count, total amount, credit utilization rate, credit card balance, reward points, loan amount, interest rate;
• Attempt 1: Logistic regression, almost only predicts low risk with zero recall;
• Attempt 2: Weighted logistic regression, AUC around 0.56 with slight improvement;
• Attempt 3: Isolation Forest (unsupervised), marks about 250 abnormal customers with F1 score around 0.08, providing a complementary risk perspective.

1. Positive correlation between utilization and risk: The higher the credit utilization rate, the higher the anomaly rate;
2. High rewards do not equal low risk: Customers with high reward points are not necessarily low-risk—those with high utilization and high rewards are a high-value but potentially high-risk group;
3. Loan behavior affects risk: Customers with active loans or large loans have different abnormal characteristics;
4. Transaction scale distribution: The vast majority of transactions are ≥1000 USD, indicating the customer group is dominated by high-value businesses;
5. Low-reward customers have the highest risk: Customers in the low-reward bucket have the highest average risk.

Project Insights

• Importance of data engineering: Data cleaning and feature engineering are the foundation of analysis, and risk label engineering is the main limitation of the model;
• Class imbalance handling: Use comprehensive metrics like AUC and F1 to avoid being misled by accuracy;
• Supervised vs unsupervised: Unsupervised methods (e.g., Isolation Forest) can provide complementary perspectives, especially when true labels are lacking;
• Value of AI assistants: Genie lowers the threshold for analysis but requires benchmark testing to ensure reliability;
• Business insights: Data often challenges intuition—for example, low-reward customers have the highest risk—reflecting the value of data-driven decision-making.

This project provides practical references for building modern bank risk control systems, showing the complete process from raw data to business insights.