payments-risk-warehouse

Links: Source on GitHub · Live dbt docs and lineage graph

The problem

Before a payments company can build fraud models or trustworthy risk reporting, it needs a solid data foundation: clean models of its core entities, metric definitions everyone agrees on, features that don’t quietly leak the future into training data, and tests that catch bad data before it reaches a decision.

This project builds that foundation end to end for a peer-to-peer payments product, on public, synthetic data: a dimensional warehouse for analysts and a point-in-time-correct feature table for a fraud model.

The data

• Transactions: PaySim, a synthetic mobile-money dataset: 6,362,620 transactions over 31 days, of which 8,213 (0.13%) are fraud, all of them transfers or cash-outs.
• Accounts, devices, logins and disputes, which PaySim lacks, are generated by a seeded Python script keyed to PaySim’s own accounts and transactions. Every value is derived from an MD5 hash of a seed and a key, so re-runs produce identical data even though DuckDB runs SQL in parallel.
• A planted signal, on purpose and labelled as such: fraudulent senders get more failed logins and more often use a device shared with other fraud accounts. That proves the feature pipeline works end to end; it says nothing about real-world fraud. Everything else in the generator is independent of the label.

Architecture

PaySim CSV + generated entities
  → Python ingestion → DuckDB raw schema
  → dbt staging (rename, cast, clean)
  → intermediate (velocity, login, device and dispute features)
  → marts: a star schema (core) and an ML feature table (ml)

The core star schema has three facts and four dimensions: fct_transactions (incremental), fct_logins and fct_disputes; dim_account (6.9M customers plus 2.2M merchants, so every receiver key resolves), dim_device, dim_date and dim_transaction_type. The live docs show every model, column and test, and the lineage graph.

Modeling decisions

Point-in-time features, enforced and proven. Every feature uses only data from before its transaction. PaySim timestamps are hourly and transactions within an hour have no reliable order, so windows end at the previous hour. Even “how many other accounts used this device” counts only logins before the transaction. Each feature group records the latest event it read, and a dbt test fails if any is not earlier. I checked the tests by breaking the model on purpose: letting a window include the current hour failed the leakage test on all 6.4M rows, and a brute-force recount disagreed on 4.2M.

Know when a “great” column is a leak. PaySim’s balance columns nearly reveal the label: fraud empties the sender’s account exactly 97.7% of the time, against 0.0% for legitimate transfers. But the after-balance isn’t known when a payment is scored, and the dataset’s own author warns that fraudulent transactions were cancelled in the simulation. So no balance column reaches the feature table, and a test checks the table’s real columns to keep it that way.

Incremental where it matters, verified. fct_transactions loads only transactions newer than the latest already loaded. That’s correct only because the source file is in time order, which I checked rather than assumed. Deleting the last 362,620 rows and re-running brought them back in 0.25 s, with 0 rows differing from a full build.

Money in decimals. PaySim balances are often off by exactly one cent. In floating point that reads as 0.0100000000093, just over a 0.01 tolerance, and a float-based check misclassified about 680k transactions as balance mismatches.

Verify the documentation. Kaggle describes the legacy fraud flag as “transfers over 200.000”. That rule would flag 409,110 transfers; the flag fires on 16. The docs now describe what the data shows.

Data quality

• 17 models, 154 tests: keys, relationships across every fact and dimension, row-count reconciliation between layers, accepted values, ranges, and the leakage tests.
• A full build on 6.4M transactions takes about 8 seconds.
• CI runs the whole pipeline (ingestion, dbt build, all tests) on a committed sample on every pull request; main is protected, and the docs site republishes on every merge.

Results

Simple yes/no rules scored against the fraud label, on the full data. The base rate is 0.129%.

• The legacy rule is never wrong but misses 99.8% of fraud.
• The “perfect” balance rule is the trap: it’s leakage, which is exactly why balances are excluded.
• The real signal is on the receiving side. Transfers to a receiver with no activity in the prior week catch 4,093 of the 4,097 fraudulent transfers. Sender history is useless in PaySim: 99.85% of senders appear only once.
• Planted signals came back as configured, and unplanted ones showed nothing: account age and foreign-IP logins don’t separate fraud from legitimate transactions, which is evidence the generator doesn’t leak the label by accident.

These are rules, not a trained model. The aim was to show that the warehouse produces trustworthy features and where the signal is.

What’s next

• Orchestration with Prefect, with dependency checks and SLA alerts.
• Data observability with Elementary.
• A Snowflake target provisioned with Terraform. The SQL mostly uses dbt’s cross-database macros, and the few DuckDB-specific functions are marked in comments.
• An AI triage agent that classifies failed dbt tests and suggests fixes using the Claude API.

Stack: Python 3.12 with uv · dbt Core 1.12 · DuckDB · dbt_utils · dbt_expectations · GitHub Actions · GitHub Pages.