Anomaly Detection in SQL: Flag Unusual Transactions Without Labels

Objective

The hardest part of fraud detection is not the model. It is that you do not have labels. Nobody has gone through last quarter's transactions and marked which were fraudulent, and by the time a chargeback tells you, the money has gone.

Anomaly detection needs no labels. You hand it the shape of normal traffic and it reports what does not fit. This recipe trains an isolation forest on a transactions table and scores every row, with no target column anywhere in the statement.

Step 1: Transactions with a handful of odd ones

Two hundred transactions. Most are modest, daytime, domestic. A few are large, at 3am, and cross-border — the combination that matters, rather than any one feature alone.

-- Drop first so the recipe is re-runnable.
DROP TABLE IF EXISTS recipe_anom_tx;

CREATE TABLE recipe_anom_tx (
  tx_id         INT PRIMARY KEY,
  amount        DOUBLE,
  hour_of_day   INT,
  cross_border  INT
);

INSERT INTO recipe_anom_tx (tx_id, amount, hour_of_day, cross_border) VALUES
  (1,27,10,0), (2,34,11,0), (3,41,12,0), (4,48,13,0), (5,55,14,0),
  (6,62,15,0), (7,69,16,0), (8,76,17,0), (9,83,18,0), (10,90,19,0),
  (11,97,9,0), (12,104,10,0), (13,111,11,0), (14,118,12,0), (15,125,13,0),
  (16,132,14,0), (17,139,15,0), (18,146,16,0), (19,153,17,0), (20,160,18,0),
  (21,167,19,0), (22,174,9,0), (23,181,10,0), (24,188,11,0), (25,195,12,0),
  (26,22,13,0), (27,29,14,0), (28,36,15,0), (29,43,16,0), (30,50,17,0),
  (31,57,18,0), (32,64,19,0), (33,71,9,0), (34,78,10,0), (35,85,11,0),
  (36,92,12,0), (37,99,13,0), (38,106,14,0), (39,113,15,0), (40,2920,3,1),
  (41,127,17,0), (42,134,18,0), (43,141,19,0), (44,148,9,0), (45,155,10,0),
  (46,162,11,0), (47,169,12,0), (48,176,13,0), (49,183,14,0), (50,190,15,0),
  (51,197,16,0), (52,24,17,0), (53,31,18,0), (54,38,19,0), (55,45,9,0),
  (56,52,10,0), (57,59,11,0), (58,66,12,0), (59,73,13,0), (60,80,14,0),
  (61,87,15,0), (62,94,16,0), (63,101,17,0), (64,108,18,0), (65,115,19,0),
  (66,122,9,0), (67,129,10,0), (68,136,11,0), (69,143,12,0), (70,150,13,0),
  (71,157,14,0), (72,164,15,0), (73,171,16,0), (74,178,17,0), (75,185,18,0),
  (76,192,19,0), (77,199,9,0), (78,26,10,0), (79,33,11,0), (80,2540,3,1),
  (81,47,13,0), (82,54,14,0), (83,61,15,0), (84,68,16,0), (85,75,17,0),
  (86,82,18,0), (87,89,19,0), (88,96,9,0), (89,103,10,0), (90,110,11,0),
  (91,117,12,0), (92,124,13,0), (93,131,14,0), (94,138,15,0), (95,145,16,0),
  (96,152,17,0), (97,159,18,0), (98,166,19,0), (99,173,9,0), (100,180,10,0),
  (101,187,11,0), (102,194,12,0), (103,21,13,0), (104,28,14,0), (105,35,15,0),
  (106,42,16,0), (107,49,17,0), (108,56,18,0), (109,63,19,0), (110,70,9,0),
  (111,77,10,0), (112,84,11,0), (113,91,12,0), (114,98,13,0), (115,105,14,0),
  (116,112,15,0), (117,119,16,0), (118,126,17,0), (119,133,18,0), (120,3060,3,1),
  (121,147,9,0), (122,154,10,0), (123,161,11,0), (124,168,12,0), (125,175,13,0),
  (126,182,14,0), (127,189,15,0), (128,196,16,0), (129,23,17,0), (130,30,18,0),
  (131,37,19,0), (132,44,9,0), (133,51,10,0), (134,58,11,0), (135,65,12,0),
  (136,72,13,0), (137,79,14,0), (138,86,15,0), (139,93,16,0), (140,100,17,0),
  (141,107,18,0), (142,114,19,0), (143,121,9,0), (144,128,10,0), (145,135,11,0),
  (146,142,12,0), (147,149,13,0), (148,156,14,0), (149,163,15,0), (150,170,16,0),
  (151,177,17,0), (152,184,18,0), (153,191,19,0), (154,198,9,0), (155,25,10,0),
  (156,32,11,0), (157,39,12,0), (158,46,13,0), (159,53,14,0), (160,2680,3,1),
  (161,67,16,0), (162,74,17,0), (163,81,18,0), (164,88,19,0), (165,95,9,0),
  (166,102,10,0), (167,109,11,0), (168,116,12,0), (169,123,13,0), (170,130,14,0),
  (171,137,15,0), (172,144,16,0), (173,151,17,0), (174,158,18,0), (175,165,19,0),
  (176,172,9,0), (177,179,10,0), (178,186,11,0), (179,193,12,0), (180,20,13,0),
  (181,27,14,0), (182,34,15,0), (183,41,16,0), (184,48,17,0), (185,55,18,0),
  (186,62,19,0), (187,69,9,0), (188,76,10,0), (189,83,11,0), (190,90,12,0),
  (191,97,13,0), (192,104,14,0), (193,111,15,0), (194,118,16,0), (195,125,17,0),
  (196,132,18,0), (197,139,19,0), (198,146,9,0), (199,153,10,0), (200,3200,3,1);

Step 2: Ground truth — how many odd rows are actually there

You cannot evaluate an unsupervised model without some notion of what you expect it to find. Here the planted outliers are identifiable by amount:

SELECT
  COUNT(*)                                        AS total_tx,
  SUM(CASE WHEN amount > 2000 THEN 1 ELSE 0 END)  AS large_tx,
  ROUND(AVG(amount), 0)                           AS avg_amount,
  ROUND(MAX(amount), 0)                           AS max_amount
FROM recipe_anom_tx;

Expected: 200 transactions, 5 of them over 2,000, averaging about 110 with a maximum near 3,000. Five in two hundred is 2.5% — a realistic rate, and low enough that a model which flags everything is obviously wrong.

Step 3: Train — note what is missing

CREATE EXPERIMENT recipe_anom_exp AS
  SELECT amount, hour_of_day, cross_border
  FROM recipe_anom_tx
WITH (
  task_type   = 'anomaly_detection',
  algorithms  = ['isolation_forest'],
  max_trials  = 5,
  random_seed = 42
);

There is no target_column. That is the whole point: nothing in this statement tells the engine which rows are fraudulent, because nothing in the data knows. The model learns the shape of the bulk of the traffic and measures distance from it.

For the same reason there is no validation_strategy. There are no labels to hold out, so the engine validates internally against its own fitted distribution.

Step 4: Read the result

{
  "status": "success",
  "best_score": 0.103,
  "score_basis": "internal_validation",
  "total_trials": 4,
  "trials_attempted": 4,
  "failed_trials": [],
  "ignored_options": []
}

score_basis: internal_validation is the field to notice. A supervised recipe shows cross_validation or holdout; here there are no labels to hold out, so the number is not an accuracy and must not be reported as one. It describes how cleanly the model separated dense regions from sparse ones on its own training data.

This is the honest limitation of unsupervised detection. You cannot know the precision without labels. What you get is a ranking, and the ranking is still valuable — it tells your reviewers where to look first.

Step 5: Score a known-odd and a known-ordinary transaction

DEPLOY MODEL recipe_anom_model FROM EXPERIMENT recipe_anom_exp;

PREDICT anomaly_score USING recipe_anom_model AS
  SELECT 2500 AS amount, 3  AS hour_of_day, 1 AS cross_border
  UNION ALL
  SELECT 45   AS amount, 14 AS hour_of_day, 0 AS cross_border;

Expected: the large 3am cross-border transaction scores about 0.78 and the ordinary afternoon one about 0.47. Higher means more anomalous.

Read those numbers carefully. The gap is real and in the right direction, but it is not 0.99 against 0.01. Isolation-forest scores are a relative ranking, not a probability of fraud, and 0.47 for a perfectly normal transaction is expected rather than alarming. Treating 0.5 as "half fraudulent" is the most common way these scores get misread.

Step 6: Rank the whole table for review

PREDICT anomaly_score USING recipe_anom_model AS
  SELECT amount, hour_of_day, cross_border
  FROM recipe_anom_tx;

Expected: 200 rows, each with a score. The planted large transactions should sit at the top when you sort by it. That ordered list is the deliverable — a review queue, not a verdict. Set the cut-off by how many cases your team can actually review in a day, which is a capacity decision rather than a modelling one.

Cleanup (Optional)

DROP TABLE IF EXISTS recipe_anom_tx;

Use it from your agent

• REST/SDK: POST /v1/query/execute with the CREATE EXPERIMENT text, then score new transactions as they arrive with the Step 5 form.
• Other algorithms: local_outlier_factor and one_class_svm are also available and make different assumptions — density versus boundary. Put all three in algorithms and let the search pick.
• Why in-DB: transactions are scored in the same statement that reads them, so there is no queue, no scoring service, and nothing to fall behind.

Key Concepts Learned

• No labels, no target_column. Unsupervised detection learns the shape of normal and measures distance from it.
• score_basis: internal_validation means the score is not an accuracy. There were no held-out labels; do not report it as precision.
• The output is a ranking, not a probability. 0.78 against 0.47 is a meaningful ordering; neither is a percentage chance of fraud.
• Set the threshold from review capacity. The model orders the queue; how far down you read is an operational choice.
• It is the combination that is anomalous, not any single feature. A large amount alone is a big sale; large plus 3am plus cross-border is a pattern.