DATA PIPELINE CASE STUDY

Automated Transaction Monitoring Pipeline

Platform: Databricks Built with: SQL, PySpark, n8n Focus: KPI Monitoring, Risk Triggers, Automated Alerting

Project Overview

A cloud-based pipeline that processes 6.3 million mobile money transactions in Databricks, computes daily and weekly business KPIs, evaluates four explainable risk triggers using auditable business rules, and delivers automated reports and alerts to Telegram on a cron schedule. Built to demonstrate cloud scale data processing without requiring anyone to open a dashboard.

The dataset is PaySim, a synthetic mobile money dataset built from real aggregated transaction logs for fraud detection research. It has 6.3M transactions across 31 simulated days, five transaction types, and ground-truth fraud labels.

Note on Defensibility: This pipeline runs on a batch schedule, not streaming — a production equivalent would use Kafka and Databricks Structured Streaming, and the architecture here is designed to be compatible with that upgrade path. PaySim is synthetic, not live data: some patterns in the results below (like a single-day 100% fraud spike) are dataset artifacts, not real-world events, and are labeled as such rather than filtered out.

Pipeline Architecture

Four stages, moving from raw data to a delivered alert:

Ingestion (PySpark): A date offset is applied to the raw data, mapping the dataset's 31 simulated days onto a fixed window of real calendar dates so that cron-based triggers can query by actual date, treating each day as a live feed.
Modeling (Databricks SQL): Data is built into a star schema — fact_daily_transactions at the center, with dim_date, dim_account, dim_transaction_type, and a rolling account_balance_snapshot tracking each account's 30-day activity baseline.
KPIs & risk triggers (Databricks SQL): Daily and weekly KPIs are computed, and four rule based triggers are evaluated against the data (detailed below).
Orchestration (n8n): Three cron-triggered workflows query the Databricks SQL endpoint directly and deliver formatted alerts to Telegram.

Risk Triggers

Four explainable, rule-based triggers, evaluated daily and written to an alert_flags table. Results below are the actual validated outputs against PaySim:

Trigger	Condition	Result
Transaction spike	Account exceeds 3× its own 30-day average transaction count in 24 hours	0 rows — PaySim's max daily count per account is 2, making the threshold mathematically unreachable. Kept and documented as a dataset finding, not removed.
Transfer → cash-out chain	Same day transfer followed by a cash-out from the same account, in the top 5% of daily amounts	29 same day chains found; 1 exceeds ~$518K. Implemented as an explicit SQL rule, not a trained model, so it stays fully auditable.
Fraud rate breach	Daily fraud rate exceeds 1%	Fires on 11 of 31 days. Day 31 shows a 100% fraud rate across 274 transactions — a simulation artifact, kept and labeled rather than filtered.
Volume collapse	Daily volume drops more than 40% vs. the same day the prior week	Fires on ~10 of 31 days, reflecting genuine volume tapering in the dataset's second half.

The transfer → cash-out chain trigger, implemented in SQL:

-- Trigger: Transfer followed by same day cash-out from the same account,
        -- where the amount sits in the top 5% of daily transaction amounts
        SELECT
        t1.account_id,
        t1.transaction_date,
        t1.amount AS transfer_amount,
        t2.amount AS cashout_amount
        FROM transactions t1
        JOIN transactions t2
        ON t1.account_id = t2.account_id
        AND t1.transaction_date = t2.transaction_date
        AND t1.type = 'TRANSFER'
        AND t2.type = 'CASH_OUT'
        WHERE t1.amount >= (
        SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount)
        FROM transactions
        WHERE transaction_date = t1.transaction_date
        );

Transaction monitoring pipeline: PaySim data through PySpark ingestion, Databricks SQL modeling and triggers, to n8n and Telegram

PaySim data → ingestion → star schema → risk triggers → n8n → Telegram alerts

Automated Workflows

Three cron-triggered n8n workflows query the Databricks SQL endpoint and deliver formatted messages to Telegram:

Daily KPI Monitor: Runs every morning, formats the previous day's KPIs with day-over-day change indicators.
Weekly Performance Summary: Runs every Monday, delivers a week-over-week summary including transaction type mix and cash-out-to-cash-in ratio.
Emergency Alert Engine: Runs daily and checks alert_flags. If nothing is active it exits silently with no message sent; if a trigger is active, it sends a formatted alert naming the specific trigger.

Key Design Decisions

The spike trigger compares each account against its own 30-day average rather than a fixed company-wide cutoff — a power user making 50 transactions a day is normal; the same volume from an account that averages 2 is not. This mirrors how fraud teams actually reason about behavioral deviation.
The transfer → cash-out chain is an explicit SQL condition, not a trained classifier — if it fires, every account, amount, and timestamp is traceable without interpreting model weights.
Weekly volume is compared to the same day the prior week rather than the previous day, to avoid false alerts from routine weekday patterns.
Findings that look like errors are kept and documented rather than filtered out: the zero-row spike trigger and the day-31 100% fraud rate are both real characteristics of how PaySim generates data, not bugd. Hiding them would misrepresent what the data actually shows.

Known Limitations

The pipeline runs on Batch schedule, not a streaming architecture. A production version would use Kafka and Databricks Structured Streaming; this architecture is designed to be compatible with that upgrade.
n8n runs locally, so scheduled triggers only fire while the instance is active. A VPS or n8n Cloud deployment would allow unattended 24/7 operation.