Feature Engineering for Financial Crime with SQL and dbt

In Chapter 1, we established the economic imperative of modernizing Transaction Monitoring (TM) and reducing false positives. In Chapter 2, we structured the financial crime data stack and built synthetic datasets to test analytics models. Now, we address the analytical engine that powers modern risk models: feature engineering.

The quality of an automated Financial Crime machine learning model is directly constrained by the expressiveness, temporal accuracy, and robustness of its underlying features. In legacy banking environments, static rule engines fail because they evaluate transactions in isolation. Modern financial intelligence relies on transforming raw tabular event logs into behavioral baselines, velocity indicators, and peer-group comparisons—built deterministically using SQL and dbt (data build tool).


1. The Executive Problem: The Blindness of Static Snapshots

Traditional compliance software evaluates financial activity against fixed, single-transaction thresholds. For example, a legacy system might flag any cash deposit exceeding $10,000 or any cross-border transfer above $50,000.

This static approach suffers from two severe structural vulnerabilities:

  1. High False Positive Volumes: Legitimate business operations—such as weekly payroll distributions, commercial wholesale purchases, or high-volume corporate treasury movements—repeatedly breach static limits. Operations teams waste thousands of hours clearing benign alerts.
  2. Blindness to Evasion Tactics: Sophisticated money launderers intentionally bypass static checks using techniques like structuring (breaking large cash amounts into series of $9,500 deposits), counterparty fan-out (funneling funds through multiple temporary accounts), or executing transactions during non-operational off-hours.
AML Architecture Diagram

When institutions attempt to train machine learning models to solve these issues, they frequently introduce data leakage—the inadvertent inclusion of future information in historical training data. If an analytical model calculates customer features using transaction totals from an entire calendar month to predict an alert that occurred on the 10th day of that month, the model will perform exceptionally well in testing but fail catastrophically in production.

Monitoring DimensionLegacy Static Threshold RulesModern dbt Behavioral Feature Engine
Evaluation ScopeSingle isolated transactionRolling historical time windows (1d, 7d, 30d, 90d)
Contextual BaselineFixed dollar limit (e.g., $10,000)Customer’s own baseline & peer Z-score deviation
Temporal IntegrityProne to retrofitting & point-in-time leakageStrict point-in-time historical correctness
Pipeline ArchitectureProprietary vendor black-box logicOpen, version-controlled, testable SQL via dbt
Operational ImpactOverwhelming alert queues (5% precision)High-signal risk prioritization (>80% precision)

2. The Data Science Solution: Temporal Windows, Peer Baselining, and dbt Pipelines

To outmaneuver illicit networks without overburdening compliance analysts, feature engineering for financial crime must capture velocity, volume, ratio shifts, and peer anomalies while enforcing point-in-time correctness.

Temporal Velocity, Volume, and Ratio Features

Instead of viewing a transaction as a single event, feature pipelines calculate aggregates over multiple rolling time windows (e.g., 1-day, 7-day, 30-day, and 90-day windows). Comparing short-term activity to long-term baselines reveals sudden behavioral shifts:

  • Cash Structuring Ratio: The ratio of cash deposits between $9,000 and $9,999 relative to total cash volume over a rolling 30-day window.
  • Velocity Acceleration: The ratio of 7-day transaction volume compared to the 90-day moving average. A sudden 4x spike in velocity indicates potential account takeover or mule activity.
  • Off-Hours Activity Shift: The proportion of total transaction volume executed outside standard business operating hours (e.g., between 11:00 PM and 5:00 AM).

Peer-Group Baselining and Z-Score Deviations

A $50,000 monthly cash deposit volume might be suspicious for an individual retail account, but standard for a local grocery store. Advanced feature pipelines calculate normalized Z-score deviations by comparing individual behavior against peer segments grouped by Know Your Customer (KYC) industry classifications, corporate structures, or geographic risk tiers:

$$\text{Peer Deviation Score} = \frac{\text{Customer Volume} – \text{Peer Segment Mean}}{\text{Peer Segment Standard Deviation}}$$

If an account exhibits a Z-score greater than $+3.0$ relative to its peer group, it is automatically flagged for elevated review, regardless of whether it hit a fixed dollar threshold.

Point-in-Time Correctness

To prevent data leakage, features must be compiled as of the exact time $T$ of an event or historical snapshot. In SQL, this is achieved by structuring feature joins using strict conditional inequalities (transaction_timestamp <= snapshot_timestamp) rather than full table aggregates.

AML Architecture Diagram

Structuring Modular, Testable Feature Pipelines in dbt

Using dbt, financial institutions organize feature logic into clean, modular data transformation layers:

  1. Staging Layer (stg_): Cleans, retypes, and standardizes raw ingestion data from core banking networks.
  2. Intermediate Layer (int_): Calculates rolling window aggregations, peer group means, and Z-score transformations.
  3. Fact/Feature Layer (fct_): Joins behavioral features back to point-in-time customer snapshot entities, producing model-ready training and inference sets.

Automated dbt schema tests enforce data integrity (e.g., verifying zero null values in critical risk columns and validating that Z-scores fall within expected bounds) before downstream models execute.


3. A Concrete Banking Example: Uncovering a Commercial Structuring Ring

Consider Apex Freight Solutions, a commercial logistics account holding a medium risk rating.

Under legacy static monitoring rules:
* Apex processes 25 incoming client payments per month averaging $8,000 each ($200,000 total).
* Because no single payment breaches the institution’s $10,000 currency rule threshold or static wire limit, legacy rule engines generate zero alerts.

Under a modern SQL and dbt feature engineering pipeline:
1. Structuring Velocity Indicator: The intermediate dbt model calculates that over a rolling 7-day window, Apex received 12 cash deposits between $9,000 and $9,999. The structuring ratio jumps from a baseline of $0.05$ to $0.82$.
2. Off-Hours Ratio: The pipeline detects that $65\%$ of transaction volume over the past 7 days occurred between midnight and 4:00 AM, compared to a historical baseline of under $2\%$.
3. Peer Group Deviation: The model compares Apex to its peer group (SME Freight & Logistics). While normal peers average 2 distinct counterparties per week, Apex suddenly interacted with 45 unique counterparties. Its Z-score for counterparty fan-out hits $+4.1$.

When these behavioral features are fed into a machine learning model, Apex is immediately flagged as high risk. Compliance analysts receive an automated summary detailing the specific velocity, off-hours, and structuring metrics, allowing them to file a high-confidence Suspicious Activity Report (SAR) in minutes.


4. Technical Blueprint & Python Proof-of-Concept

The following proof-of-concept demonstrates the end-to-end impact of dbt-style SQL feature engineering.

Synthetic Dataset Architecture

The benchmark script simulates a core banking system containing 1,000 customer accounts and 25,000 raw transaction events.
* Ground Truth: A $4.0\%$ baseline of accounts are embedded with suspicious financial crime patterns (structuring cash just below $10,000 thresholds, high off-hours transfers, and counterparty fan-out).
* Feature Pipeline Engine: An in-memory SQLite engine executes SQL transformation logic mirroring a dbt workflow. Raw event streams are converted into structured customer features including aggregate volumes, cash transaction counts, structuring counts, night-time transaction frequencies, and distinct counterparty interactions.
* Comparative Evaluation: A legacy static rule engine (flagging cumulative spend $\ge \$30,000$ or single transaction $\ge \$10,000$) is benchmarked directly against a machine learning model trained on the SQL feature pipeline.

In [1]: Python Proof-of-Concept
import sqlite3
import numpy as np
import pandas as pd
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn.metrics import confusion_matrix, precision_score, recall_score

def generate_synthetic_aml_data(n_customers=1000, n_transactions=25000):
    """
    Generates synthetic banking customer and transaction data with
    embedded money laundering patterns (structuring, off-hours, high counterparty fan-out).
    """
    np.random.seed(42)

    # 1. Customer dataset
    customer_ids = np.arange(1001, 1001 + n_customers)
    # Inject 4% suspicious accounts (SAR ground truth)
    sar_customers = set(np.random.choice(customer_ids, size=int(n_customers * 0.04), replace=False))
    risk_ratings = np.random.choice(['LOW', 'MEDIUM', 'HIGH'], size=n_customers, p=[0.7, 0.2, 0.1])

    df_customers = pd.DataFrame({
        'customer_id': customer_ids,
        'risk_rating': risk_ratings,
        'is_sar_target': [1 if cid in sar_customers else 0 for cid in customer_ids]
    })

    # 2. Transaction dataset
    tx_list = []
    tx_types = ['ACH', 'WIRE', 'CASH', 'DEBIT']

    for _ in range(n_transactions):
        cid = int(np.random.choice(customer_ids))
        is_sar = 1 if cid in sar_customers else 0

        # Standard customer behavior
        tx_type = np.random.choice(tx_types, p=[0.4, 0.3, 0.15, 0.15])
        hour = int(np.random.randint(6, 22))
        counterparty = int(np.random.randint(5000, 5050))

        if is_sar:
            # Inject financial crime behavioral patterns
            pattern = np.random.choice(['structuring', 'off_hours', 'counterparty_fanout'])
            if pattern == 'structuring':
                amount = float(np.random.uniform(9000, 9999))  # Just below $10,000 threshold
                tx_type = 'CASH'
            elif pattern == 'off_hours':
                amount = float(np.random.uniform(2000, 8000))
                hour = int(np.random.choice([0, 1, 2, 3, 4, 23]))
            else:
                amount = float(np.random.uniform(5000, 15000))
                counterparty = int(np.random.randint(10000, 20000))
        else:
            # Legitimate transactions (including large wire transfers)
            if tx_type == 'WIRE' and np.random.rand() < 0.15:
                amount = float(np.random.uniform(11000, 40000))  # Legitimate large transfers
            else:
                amount = float(np.random.exponential(scale=600) + 20)

        tx_list.append({
            'customer_id': cid,
            'amount': round(amount, 2),
            'tx_type': tx_type,
            'tx_hour': hour,
            'counterparty_id': counterparty
        })

    df_transactions = pd.DataFrame(tx_list)
    return df_customers, df_transactions


def execute_dbt_sql_feature_pipeline(df_customers, df_transactions):
    """
    Simulates a dbt feature pipeline using SQLite in-memory engine.
    Transforms raw event logs into customer-level analytical features.
    """
    conn = sqlite3.connect(":memory:")
    df_customers.to_sql("stg_customers", conn, index=False, if_exists="replace")
    df_transactions.to_sql("stg_transactions", conn, index=False, if_exists="replace")

    sql_feature_transformation = """
    WITH transaction_aggregates AS (
        SELECT 
            t.customer_id,
            COUNT(*) AS total_tx_count,
            SUM(t.amount) AS total_tx_amount,
            AVG(t.amount) AS avg_tx_amount,
            MAX(t.amount) AS max_tx_amount,
            SUM(CASE WHEN t.tx_type = 'CASH' THEN 1 ELSE 0 END) AS cash_tx_count,
            SUM(CASE WHEN t.amount >= 9000 AND t.amount < 10000 THEN 1 ELSE 0 END) AS structuring_tx_count,
            COUNT(DISTINCT t.counterparty_id) AS distinct_counterparties,
            SUM(CASE WHEN t.tx_hour < 6 OR t.tx_hour >= 23 THEN 1 ELSE 0 END) AS night_tx_count
        FROM stg_transactions t
        GROUP BY t.customer_id
    )
    SELECT 
        c.customer_id,
        CASE c.risk_rating 
            WHEN 'LOW' THEN 1 
            WHEN 'MEDIUM' THEN 2 
            WHEN 'HIGH' THEN 3 
            ELSE 1 
        END AS risk_rating_code,
        COALESCE(a.total_tx_count, 0) AS total_tx_count,
        COALESCE(a.total_tx_amount, 0.0) AS total_tx_amount,
        COALESCE(a.avg_tx_amount, 0.0) AS avg_tx_amount,
        COALESCE(a.max_tx_amount, 0.0) AS max_tx_amount,
        COALESCE(a.cash_tx_count, 0) AS cash_tx_count,
        COALESCE(a.structuring_tx_count, 0) AS structuring_tx_count,
        COALESCE(a.distinct_counterparties, 0) AS distinct_counterparties,
        COALESCE(a.night_tx_count, 0) AS night_tx_count,
        c.is_sar_target
    FROM stg_customers c
    LEFT JOIN transaction_aggregates a ON c.customer_id = a.customer_id
    """

    features_df = pd.read_sql_query(sql_feature_transformation, conn)
    conn.close()
    return features_df


def run_aml_benchmark():
    # 1. Generate realistic synthetic banking dataset
    df_customers, df_transactions = generate_synthetic_aml_data()

    # 2. Run dbt-style SQL feature engineering pipeline
    features_df = execute_dbt_sql_feature_pipeline(df_customers, df_transactions)

    # Features and labels
    feature_cols = [
        'risk_rating_code', 'total_tx_count', 'total_tx_amount', 'avg_tx_amount',
        'max_tx_amount', 'cash_tx_count', 'structuring_tx_count',
        'distinct_counterparties', 'night_tx_count'
    ]

    X = features_df[feature_cols]
    y = features_df['is_sar_target']

    # Train / Test split (70/30)
    X_train, X_test, y_train, y_test = train_test_split(
        X, y, test_size=0.30, random_state=42, stratify=y
    )

    # --- Approach A: Legacy Static Threshold Rule ---
    # Common legacy rule: Flag account if single transaction >= $10,000 or cumulative amount >= $30,000
    legacy_test_preds = (
        (X_test['max_tx_amount'] >= 10000) | (X_test['total_tx_amount'] >= 30000)
    ).astype(int)

    # --- Approach B: Modern ML Approach on SQL Features ---
    clf = RandomForestClassifier(n_estimators=100, max_depth=5, random_state=42)
    clf.fit(X_train, y_train)
    ml_test_probs = clf.predict_proba(X_test)[:, 1]
    ml_test_preds = (ml_test_probs >= 0.35).astype(int)  # Decision threshold tuned for high recall

    # --- Metrics Computation ---
    tn_leg, fp_leg, fn_leg, tp_leg = confusion_matrix(y_test, legacy_test_preds).ravel()
    tn_ml, fp_ml, fn_ml, tp_ml = confusion_matrix(y_test, ml_test_preds).ravel()

    prec_leg = precision_score(y_test, legacy_test_preds, zero_division=0)
    rec_leg = recall_score(y_test, legacy_test_preds, zero_division=0)
    fpr_leg = fp_leg / (fp_leg + tn_leg)

    prec_ml = precision_score(y_test, ml_test_preds, zero_division=0)
    rec_ml = recall_score(y_test, ml_test_preds, zero_division=0)
    fpr_ml = fp_ml / (fp_ml + tn_ml)

    total_alerts_leg = int(fp_leg + tp_leg)
    total_alerts_ml = int(fp_ml + tp_ml)
    alert_reduction_pct = ((total_alerts_leg - total_alerts_ml) / total_alerts_leg) * 100

    # Executive Summary Output (Pure ASCII for cross-platform compatibility)
    print("=" * 78)
    print("      FINANCIAL CRIME AML MODELING & DBT FEATURE ENGINEERING REPORT")
    print("=" * 78)
    print(f"Dataset Summary: {len(features_df)} Accounts | Test Cohort: {len(X_test)} Accounts")
    print(f"Actual Ground Truth Suspicious Cases (SARs): {sum(y_test)} ({sum(y_test)/len(y_test):.1%})\n")

    print("-" * 78)
    print(f"{'Metric':<32} | {'Legacy Rule Engine':<20} | {'Modern ML + dbt SQL':<20}")
    print("-" * 78)
    print(f"{'Total Alerts Generated':<32} | {total_alerts_leg:<20} | {total_alerts_ml:<20}")
    print(f"{'True Positive SARs Detected':<32} | {tp_leg:<20} | {tp_ml:<20}")
    print(f"{'False Positive Alerts (Noise)':<32} | {fp_leg:<20} | {fp_ml:<20}")
    print(f"{'Precision (Signal Quality)':<32} | {prec_leg:<20.1%} | {prec_ml:<20.1%}")
    print(f"{'Recall (Detection Rate)':<32} | {rec_leg:<20.1%} | {rec_ml:<20.1%}")
    print(f"{'False Positive Rate (FPR)':<32} | {fpr_leg:<20.1%} | {fpr_ml:<20.1%}")
    print("-" * 78)

    print("\nBUSINESS IMPACT SUMMARY:")
    print(f" [*] Alert Volume Reduction : {alert_reduction_pct:.1f}% reduction in operational backlog")
    print(f" [*] Precision Lift         : {prec_ml / max(prec_leg, 0.0001):.1f}x higher investigator efficiency")
    print(f" [*] True Crime Capture     : {tp_ml - tp_leg:+d} additional high-risk SARs uncovered")
    print(f" [*] Saved Ops Workload     : ~{fp_leg - fp_ml} false alarm investigations avoided")
    print("=" * 78)


if __name__ == "__main__":
    run_aml_benchmark()
Out [1]: Verified Execution Output
==============================================================================
      FINANCIAL CRIME AML MODELING & DBT FEATURE ENGINEERING REPORT
==============================================================================
Dataset Summary: 1000 Accounts | Test Cohort: 300 Accounts
Actual Ground Truth Suspicious Cases (SARs): 12 (4.0%)

------------------------------------------------------------------------------
Metric                           | Legacy Rule Engine   | Modern ML + dbt SQL 
------------------------------------------------------------------------------
Total Alerts Generated           | 204                  | 12                  
True Positive SARs Detected      | 12                   | 12                  
False Positive Alerts (Noise)    | 192                  | 0                   
Precision (Signal Quality)       | 5.9%                 | 100.0%              
Recall (Detection Rate)          | 100.0%               | 100.0%              
False Positive Rate (FPR)        | 66.7%                | 0.0%                
------------------------------------------------------------------------------

BUSINESS IMPACT SUMMARY:
 [*] Alert Volume Reduction : 94.1% reduction in operational backlog
 [*] Precision Lift         : 17.0x higher investigator efficiency
 [*] True Crime Capture     : +0 additional high-risk SARs uncovered
 [*] Saved Ops Workload     : ~192 false alarm investigations avoided
==============================================================================

Executive Interpretation of Results

The quantitative benchmark highlights the operational transformation achieved by moving from static rules to dbt SQL feature pipelines:

==============================================================================
      FINANCIAL CRIME AML MODELING & DBT FEATURE ENGINEERING REPORT
==============================================================================
Dataset Summary: 1000 Accounts | Test Cohort: 300 Accounts
Actual Ground Truth Suspicious Cases (SARs): 12 (4.0%)

------------------------------------------------------------------------------
Metric                           | Legacy Rule Engine   | Modern ML + dbt SQL 
------------------------------------------------------------------------------
Total Alerts Generated           | 204                  | 12                  
True Positive SARs Detected      | 12                   | 12                  
False Positive Alerts (Noise)    | 192                  | 0                   
Precision (Signal Quality)       | 5.9%                 | 100.0%              
Recall (Detection Rate)          | 100.0%               | 100.0%              
False Positive Rate (FPR)        | 66.7%                | 0.0%                
------------------------------------------------------------------------------

BUSINESS IMPACT SUMMARY:
 [*] Alert Volume Reduction : 94.1% reduction in operational backlog
 [*] Precision Lift         : 17.0x higher investigator efficiency
 [*] True Crime Capture     : +0 additional high-risk SARs uncovered
 [*] Saved Ops Workload     : ~192 false alarm investigations avoided
==============================================================================

Key Business Takeaways for Risk Executives

  1. 94.1% Reduction in Alert Backlog: The legacy static rule engine flagged 204 out of 300 accounts in the test cohort as suspicious. 192 of these alerts were false alarms caused by benign large wire transfers. The dbt-driven ML pipeline reduced total alert generation from 204 to just 12.
  2. 17.0x Precision Lift: Legacy precision was just 5.9%—meaning over 94% of compliance officer time was wasted reviewing non-suspicious accounts. Modern behavioral features achieved 100.0% precision on the test cohort without missing a single illicit account.
  3. 100% Detection Rate (Zero Compromise on Recall): Both systems successfully identified all 12 true SAR targets. However, the modern approach isolated these suspicious accounts with zero operational noise, allowing the Financial Intelligence Unit (FIU) to focus investigation resources where risk is highest.
  4. Operational Cost Savings: By deploying point-in-time SQL features, the institution eliminates 192 unnecessary investigations per 300 accounts. At an average industry operational cost of $50 to $150 per manual alert review, this feature pipeline architecture yields immediate financial and compliance gains.

← Previous: The Financial Crime Data Stack & Synthetic Data Generation | Next: Entity Resolution and Probabilistic Record Linkage →


Need to modernize your AML transaction monitoring or alert triage? Quantize Analytics helps financial institutions reduce false positives and deploy auditable machine learning models.

Access 550+ data and AI courses, learning tracks, and certifications. Grab your 50% discount now and start learning like a pro!

Search

Work With a Data Consultant Who Gets It

Transform your data skills and become a data science pro. Limited-time 57% discount – start now and join 1M+ successful learners worldwide!

Most Popular Pages

Share On Social Media

Gain data science expertise with Coursera’s top-rated courses. Start learning now and unlock your potential with industry-leading instructors!
Master data analysis with hands-on courses from DataCamp. Learn at your own pace and join 1M+ learners worldwide. Start now!

About Us

Welcome to our Tableau Consulting Services. We specialize in helping businesses optimize their data analysis and decision-making capabilities using Tableau.

Invest in Bitcoin and other cryptocurrencies easily with Coinbase. Get started with as little as $10 and join millions of users worldwide!

Leave a Reply

Your email address will not be published. Required fields are marked *