Build a Fraud Detection Model in Minutes with BigQuery ML

Traditionally, shipping a fraud-detection classifier meant exporting operational tables to object storage, spinning up a Python training cluster, wrestling with class imbalance, and wiring up separate explainability libraries before a single analyst could score a transaction.

With Google BigQuery ML (BQML) and federated EXTERNAL_QUERY connections to Cloud Spanner, data engineers and analysts can train, evaluate, and generate Shapley-based feature attributions for an account-fraud classifier in pure Standard SQL—without moving sensitive financial records outside BigQuery’s governance boundary. Full SQL and sample data are available in github.com/datasherlock/bqml-demo.

Step 1: Federated Preparation from Cloud Spanner + Stratified Splitting

Suppose live login and transaction events land in a transactional Cloud Spanner table (account_activity_raw). Using a Cloud Resource connection, we query Spanner directly via EXTERNAL_QUERY, cast raw string attributes into strong BigQuery types, and assign a classifier split value to partition 80% training, 10% evaluation, and 10% holdout inference:

CREATE OR REPLACE TABLE `demo_dataset.account_activity_cleansed` AS
SELECT
  user_id,
  PARSE_TIMESTAMP('%c', timestamp) AS activity_ts,
  location,
  device_type,
  ip_address,
  CAST(transaction_amount AS NUMERIC) AS transaction_amount,
  transaction_type,
  CAST(successful_login AS BOOL) AS successful_login,
  CAST(unusual_activity AS BOOL) AS unusual_activity,
  RAND() AS classifier
FROM EXTERNAL_QUERY(
  "my_spanner_db.us-central1.demo-spanner-conn",
  "SELECT * FROM account_activity_raw"
);

-- Verify our 80 / 10 / 10 train-eval-predict split distribution
SELECT
  CASE
    WHEN classifier < 0.8 THEN 'training'
    WHEN classifier BETWEEN 0.8 AND 0.9 THEN 'evaluation'
    ELSE 'prediction'
  END AS split_bucket,
  COUNT(*) AS row_count
FROM `demo_dataset.account_activity_cleansed`
GROUP BY split_bucket;

Step 2: Train an Imbalance-Aware Logistic Regression Model

Fraud datasets are inherently skewed—legitimate activity vastly outnumbers fraudulent takeovers. Setting auto_class_weights = TRUE instructs BigQuery ML to weight minority-class fraud labels inversely proportional to their frequency so the model does not achieve a misleading 99% accuracy simply by predicting FALSE on every row:

CREATE OR REPLACE MODEL `demo_dataset.fraud_model`
OPTIONS (
  model_type = 'LOGISTIC_REG',
  auto_class_weights = TRUE,
  data_split_method = 'NO_SPLIT',
  input_label_cols = ['unusual_activity'],
  max_iterations = 15
) AS
SELECT
  * EXCEPT(user_id, activity_ts, ip_address, classifier)
FROM `demo_dataset.account_activity_cleansed`
WHERE classifier < 0.8;

Notice how we exclude high-cardinality identifiers (user_id, ip_address, raw activity_ts) via * EXCEPT(...) so the model generalizes across behavioral signals (transaction_amount, transaction_type, device_type, location, successful_login) rather than memorizing historical IDs.

Step 3: Evaluate Precision, Recall & ROC AUC

Next, we score the unseen 10% evaluation slice with ML.EVALUATE to inspect precision, recall, F1 score, log loss, and ROC AUC:

SELECT *
FROM ML.EVALUATE(
  MODEL `demo_dataset.fraud_model`,
  (
    SELECT * EXCEPT(user_id, activity_ts, ip_address, classifier)
    FROM `demo_dataset.account_activity_cleansed`
    WHERE classifier BETWEEN 0.8 AND 0.9
  )
);

Step 4: Explainable Predictions with ML.EXPLAIN_PREDICT

In regulated banking and payments workflows, a black-box risk score is not enough—fraud investigators need to know which exact features pushed an account over the alert threshold. ML.EXPLAIN_PREDICT returns the top contributing features and their attribution weights inline alongside each prediction, making it trivial to audit false positives and false negatives:

SELECT *
FROM ML.EXPLAIN_PREDICT(
  MODEL `demo_dataset.fraud_model`,
  (
    SELECT * EXCEPT(user_id, activity_ts, ip_address, classifier)
    FROM `demo_dataset.account_activity_cleansed`
    WHERE classifier > 0.9
  ),
  STRUCT(3 AS top_k_features)
)
WHERE predicted_unusual_activity != unusual_activity;

From zero Python boilerplate to explainable fraud scoring in under five minutes—and when your feature engineering matures, you can swap model_type = 'LOGISTIC_REG' to 'BOOSTED_TREE_CLASSIFIER' with a one-line SQL change!

Originally published on Medium / Dev Genius — Build a Fraud Detection Model in Minutes with BigQuery ML. Companion SQL & sample dataset: github.com/datasherlock/bqml-demo.