UPI Fraud Detection — Lambda Architecture on Databricks

A production-grade data engineering portfolio project implementing the Lambda architecture on Databricks Free Edition, using synthetic UPI (Unified Payments Interface) transaction data to demonstrate both batch and real-time fraud detection pipelines.

---

Architecture overview

                        ┌─────────────────────┐
                        │   Data generator    │
                        │  500K UPI txns CSV  │
                        └────────┬────────────┘
                                 │
              ┌──────────────────┴──────────────────┐
              │                                     │
              ▼                                     ▼
   ┌─────────────────────┐             ┌─────────────────────┐
   │    BATCH LAYER      │             │    SPEED LAYER      │
   │                     │             │                     │
   │  Bronze (Auto       │             │  Stream producer    │
   │  Loader + Delta)    │             │  (simulated feed)   │
   │         ↓           │             │         ↓           │
   │  Silver (SCD Type 2 │             │  Structured         │
   │  MERGE + cleanse)   │             │  Streaming          │
   │         ↓           │             │  (sliding windows,  │
   │  Gold (merchant     │             │   watermarks,       │
   │  daily, city        │             │   foreachBatch)     │
   │  hourly aggs)       │             │         ↓           │
   └─────────┬───────────┘             │  Fraud alerts Delta │
             │                         └──────────┬──────────┘
             │                                    │
             └──────────────┬─────────────────────┘
                            ▼
                 ┌─────────────────────┐
                 │   SERVING LAYER     │
                 │                     │
                 │  UNION batch Gold   │
                 │  + stream alerts    │
                 │  → unified view     │
                 │  → 4 dashboards     │
                 └─────────────────────┘

---

Tech stack

Layer	Technology
Platform	Databricks Free Edition (Serverless)
Storage format	Delta Lake
Batch ingestion	Auto Loader (cloudFiles)
Batch transform	PySpark, Delta MERGE
Streaming	Spark Structured Streaming
Orchestration	%run notebook chaining
Data governance	Unity Catalog Volumes
Visualisation	Matplotlib
Language	Python 3 / PySpark

---

Project structure

lambda-upi-pipeline/
├── 00_data_generator.py      # Generates 500K synthetic UPI transactions
├── 01_bronze_ingestion.py    # Auto Loader → Delta Bronze + audit columns
├── 02_silver_transform.py    # SCD Type 2 MERGE, cleansing, derived columns
├── 03_gold_aggregation.py    # Merchant daily + city hourly Gold tables
├── 04_stream_producer.py     # Simulates live UPI transaction stream
├── 05_stream_consumer.py     # Structured Streaming fraud detection
├── 06_serving_analysis.py    # Lambda serving layer + dashboard charts
└── 00_master_pipeline.py     # Batch orchestrator using %run chaining

---

Data model

Synthetic UPI transaction schema

Column	Type	Description
transaction_id	string	UUID, primary key
txn_timestamp	timestamp	Event time (used for watermarks)
sender_upi_id	string	e.g. user0042@okaxis
receiver_upi_id	string	e.g. merchant012@ybl
amount	double	Transaction amount in INR
txn_type	string	P2P or P2M
bank_name	string	HDFC, SBI, ICICI, AXIS etc.
device_id	string	Device fingerprint
status	string	SUCCESS / FAILED / PENDING
city	string	Bangalore, Mumbai, Delhi etc.

Unity Catalog structure

main.lambda_upi
├── Tables
│   ├── bronze_transactions       # Raw Delta + audit columns
│   ├── silver_transactions       # Cleansed facts + derived columns
│   ├── silver_dim_sender         # SCD Type 2 sender dimension
│   ├── gold_merchant_daily       # Merchant aggregates per day
│   ├── gold_city_hourly          # City × hour transaction heatmap
│   └── streaming_fraud_alerts    # Real-time high-risk alerts
└── Volumes
    ├── raw_data/                 # Source CSV files
    ├── streaming_input/          # Live JSON transaction feed
    └── checkpoints/              # Streaming state checkpoints

---

Key engineering patterns

1. Medallion architecture (Bronze → Silver → Gold)

• Bronze — raw ingestion with zero transformation. Adds _ingested_at, _source_file, and _batch_id audit columns. Schema-on-read via Auto Loader.
• Silver — cleansed, deduplicated, enriched. Applies SCD Type 2 on the sender dimension to track bank_name and device_id changes over time. Adds is_fraud_candidate flag and time-derived columns.
• Gold — business-level aggregates. Two purpose-built tables: merchant daily summary (fraud rate, success rate, volume) and city hourly heatmap (for operational dashboards).

2. SCD Type 2 with Delta MERGE

Tracks slowly changing attributes on the sender dimension. When a sender changes their bank or device, the old record is expired (is_current = false, effective_end set) and a new record is inserted. This preserves full history for audit and ML feature engineering.

dim_table.alias("target").merge(
    df_sender_latest.alias("source"),
    "target.sender_upi_id = source.sender_upi_id AND target.is_current = true"
).whenMatchedUpdate(
    condition="target.bank_name != source.bank_name OR target.device_id != source.device_id",
    set={"is_current": False, "effective_end": current_timestamp()}
).whenNotMatchedInsert(values={...}).execute()

3. Spark Structured Streaming with sliding windows

Fraud signals computed over 5-minute sliding windows (sliding every 1 minute):

Signal	Logic	Pattern detected
velocity_flag	txn count > 3 in window	Card skimming / account abuse
amount_spike_flag	max amount > ₹49,000	Just-under-limit fraud
new_device_flag	unique devices > 1 in window	Account takeover
fraud_score	sum of all flags (0–3)	Composite risk score

Only alerts with fraud_score ≥ 2 are persisted — reduces false positives.

4. Lambda serving layer

Batch Gold views and streaming alert views are unified with unionByName into a single serving layer. Downstream consumers (dashboards, ML models) query one view without awareness of which layer produced the data.

5. Auto Loader for incremental ingestion

Uses cloudFiles format with schema inference and evolution. Tracks processed files via checkpoint, making re-runs fully idempotent — no duplicate ingestion on retry.

---

Results

Metric	Value
Total transactions processed	500,000
Unique senders	500
Unique merchants	200
Fraud candidates (batch)	~15,000 (3%)
High-risk alerts (streaming)	33
Top fraud score	3 / 3
Highest flagged amount	₹98,724
Highest activity city	Pune

---

How to run

Prerequisites

• Databricks Free Edition account
• Serverless compute enabled

Setup

1. Create a Databricks Free Edition workspace
2. Create a folder: Workspace → lambda-upi-pipeline
3. Import all notebooks into the folder
4. Attach notebooks to Serverless compute

Run order

Batch layer (run once):

00_master_pipeline   ← runs stages 1–4 automatically

Or run individually:

00_data_generator  →  01_bronze_ingestion  →  02_silver_transform  →  03_gold_aggregation

Speed layer (run in sequence):

1. Run 04_stream_producer  (wait for completion)
2. Run 05_stream_consumer  (processes all produced files)

Serving layer:

06_serving_analysis  ← generates unified view + 4 dashboard charts

---

Interview talking points

• Why Lambda over Kappa? Lambda lets each layer optimise independently — batch for accuracy and history, streaming for low latency. Kappa simplifies the codebase but requires Kafka topic retention for historical replay, which adds operational complexity. For a fraud detection use case where both historical pattern analysis and real-time intervention matter, Lambda is the right fit.

• Why SCD Type 2 over overwrite? Fraud models need point-in-time correctness — knowing which device a user had at the time of a transaction is critical for model training and audit. Overwriting loses that history.

• Why foreachBatch over direct Delta write? foreachBatch gives full control over micro-batch processing — we can filter by fraud score, deduplicate, and apply conditional write logic before persisting. A direct writeStream to Delta would write all rows without this control.

• Why approx_count_distinct over countDistinct in streaming? Exact distinct counts in streaming require maintaining all seen values in state — expensive at scale. approx_count_distinct uses HyperLogLog with ~2% error, which is acceptable for fraud signal detection and dramatically reduces state size.

• How would you productionise this? Replace the file-based stream source with Kafka (Confluent Cloud or MSK). Add Airflow or Databricks Workflows for orchestration. Add Great Expectations or Delta constraints for data quality gates between layers. Enable Unity Catalog column-level access controls for PII fields (sender_upi_id, device_id). Add OPTIMIZE and ZORDER on txn_date and sender_upi_id for query performance.

---

Author

Aswin P.M. Senior Data Engineer — CFRA Research aswinpm.com · github.com/aswinpm