The goal
Reproduce, end to end, the analytics-engineering workflow a financial-crime data team uses: take raw transaction data, model it into reliable, tested datasets, engineer fraud signals, score transactions, and measure how well the scoring works, honestly. Everything runs on the free BigQuery sandbox and free dbt and Looker Studio tiers, so it is fully reproducible at zero cost.
The stack
| Layer | Tool |
|---|---|
| Warehouse | Google BigQuery (EU) |
| Transformation | dbt Core |
| Visualisation | Looker Studio |
| Cloud | Google Cloud Platform |
The pipeline
A layered dbt project ending in a star schema, staging cleans and renames, an intermediate layer engineers features, a mart scores risk, and the fact and dimensions form the dimensional model.
↓
stg_transactions , clean, renamed staging (+ tests)
↓
int_transactions_features , balance-error features; fraud-bearing types only
↓
mart_suspicious_activity , rule-based scoring + is_suspicious flag
↓
fct_transactions , central fact
└─ dim_time · dim_accounts · dim_transaction_types
The signal
The intermediate model filters to the two fraud-bearing types, TRANSFER and CASH_OUT, and engineers the balance-error features: for a clean transaction, money is conserved and the balance error is roughly zero; a large error means the balances don't reconcile, which is the core fraud tell in PaySim. These are per-transaction signals, which work here because account-based signals like velocity don't, PaySim accounts barely repeat, a data-quality reality worth stating rather than hiding.
The mart scores each transaction against three transparent rules into a risk_score of 0–3, flagging is_suspicious at 2 or above: a zero destination balance before and after, an origin account drained to empty, and an amount over a tunable threshold.
Data quality & testing
Every model is tested; dbt build runs models and tests in dependency order. Three test types are used, not_null on required fields, accepted_values so categories and flags only ever hold valid values, and relationships for referential integrity so every foreign key resolves to its dimension with no orphans. This mirrors the auditability a regulated bank requires: any figure in a report traces through tested models back to raw data.
Evaluation, the honest part
The is_suspicious flag was evaluated against the real fraud label with a confusion matrix, then tuned by moving the threshold.
| Threshold | Recall | Precision | False alarms |
|---|---|---|---|
≥ 2 | 83% | 13% | 45,760 |
≥ 3 | 31% | 99.4% | 16 |
≥2 the screen catches roughly five of every six frauds but over-flags heavily, right for a first-line screen where humans triage the noise. Tightening to ≥3 collapses false alarms from 45,760 to 16 but misses most fraud, right for an auto-action system. The threshold is a business decision trading the cost of missed fraud against the cost of analyst time. Two different risk appetites, two different settings. The pipeline ships at ≥2 as the first-line default.The dashboard
Fraud occurs only in TRANSFER (~16%) and CASH_OUT (~4.5%); the other three types carry none. The destination-side balance error is the strongest single signal, fraudulent transactions leave the receiving account unreconciled.
Limitations & scope
- Synthetic data, PaySim is generated, not real; this shows modelling and tooling, not detection rates.
- Inflated fraud prevalence from keeping all fraud rows when subsampling.
- Relative time, not calendar time, supports hour-of-day, not day-of-week or seasonality.
- Rule-based, not ML, three transparent rules, chosen for interpretability and auditability.
- Looker Studio, not LookML: the dashboards are built in Looker Studio, reading straight from the dbt marts. LookML, Looker's modelling and semantic layer, would be the production step on top of this, and that layer sits outside this project's scope.