Anomaly Detection

AnoFox Tabular provides 6 anomaly detection functions spanning 2 statistical methods (Z-Score with O(n) single-pass computation, IQR with O(n log n) sorting) and 4 ML methods (Isolation Forest with Extended IF and SCiForest variants supporting up to 500 trees and 10,000 sample size, multivariate Isolation Forest, DBSCAN density-based clustering, and OutlierTree for explainable anomaly detection with human-readable explanations). OutlierTree is uniquely valuable because it provides context-aware outlier explanations -- for example, identifying that a salary is anomalous specifically for a Junior Developer role, not just anomalous in absolute terms.

Statistical and machine learning methods for outlier detection.

Quick Reference

Function	Description	SQL Signature
anofox_tab_zscore	Z-score outlier	(table, column, threshold) -> TABLE
anofox_tab_iqr	IQR outlier	(table, column, iqr_multiplier) -> TABLE
anofox_tab_isolation_forest	Univariate ML	(table, column, ...) -> TABLE
anofox_tab_isolation_forest_mv	Multivariate ML	(table, columns, ...) -> TABLE
anofox_tab_dbscan	Density-based	(table, column, eps, min_pts, mode) -> TABLE
anofox_tab_outlier_tree	Explainable outliers	(table, columns, mode) -> TABLE
anofox_tab_metric_anomaly_iqr	Daily-metric anomaly over time	(table, date_column, ...) -> TABLE

---

Statistical Methods (2)

anofox_tab_zscore

Detect outliers using Z-score method.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Table to analyze
column_name	VARCHAR	Yes	-	Column to analyze
threshold	DOUBLE	No	3.0	Z-score threshold

Method: z = (value - mean) / std_dev, anomaly = |z| > threshold

Thresholds:

Threshold	% in Distribution	Outliers
2.0	95%	5% of data
2.5	98.8%	1.2% of data
3.0	99.7%	0.3% of data

Example

SELECT * FROM anofox_tab_zscore('transactions', 'amount', 3.0)
WHERE is_outlier = true;

anofox_tab_iqr

Detect outliers using Interquartile Range.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Table to analyze
column_name	VARCHAR	Yes	-	Column to analyze
iqr_multiplier	DOUBLE	No	1.5	IQR multiplier

Method:

• IQR = Q3 - Q1
• lower = Q1 - iqr_multiplier * IQR
• upper = Q3 + iqr_multiplier * IQR
• anomaly = value < lower OR value > upper

Example

SELECT * FROM anofox_tab_iqr('transactions', 'amount', 1.5);

Pros over Z-score:

• Robust to extreme outliers
• Works with any distribution
• Doesn't require normality assumption

---

Isolation Forest (Enhanced)

Industry-grade anomaly detection with advanced capabilities.

Core Capabilities

• Categorical Support - Auto-detect VARCHAR columns with random subset splitting
• Extended IF (ndim) - Hyperplane splits for diagonal/curved anomaly patterns (multivariate)
• Density Scoring - Alternative metric based on points-to-volume ratio (multivariate)
• Sample Weights - Weighted sampling for imbalanced datasets (multivariate)
• SCiForest - Information-gain guided splitting (multivariate)

anofox_tab_isolation_forest

Univariate Isolation Forest for single column anomaly detection.

Full Signature

anofox_tab_isolation_forest(
    table_name VARCHAR,
    column_name VARCHAR,
    n_trees BIGINT,           -- 1-500, default 100
    sample_size BIGINT,       -- 1-10000, default 256
    contamination DOUBLE,     -- 0.0-0.5, default 0.1
    output_mode VARCHAR,      -- 'summary' or 'scores', default 'summary'
    seed BIGINT               -- Random seed for reproducible results
) -> TABLE

Parameters

Parameter	Range	Default	Description
n_trees	1-500	100	Number of isolation trees
sample_size	1-10000	256	Subsample size per tree
contamination	0.0-0.5	0.1	Expected anomaly fraction
output_mode	-	'summary'	'summary' or 'scores'
seed	-	-	Random seed for reproducibility

Contamination guidance:

Value	Expectation	Use Case
0.01	1% anomalies	High quality data
0.05	5% anomalies	Normal data
0.10	10% anomalies	Messy data

Examples

-- Basic usage
SELECT * FROM anofox_tab_isolation_forest(
    'sales_data', 'amount', 100, 256, 0.1, 'scores'
) WHERE is_anomaly = true;

-- Reproducible run with a fixed seed
SELECT * FROM anofox_tab_isolation_forest(
    'transactions', 'amount', 100, 256, 0.1, 'scores', 42
);

anofox_tab_isolation_forest_mv

Multivariate Isolation Forest for multiple column anomaly detection. Takes the same parameters as the univariate variant (column_name becomes column_names, comma-separated), plus the advanced Extended IF / SCiForest options.

Full Signature

anofox_tab_isolation_forest_mv(
    table_name VARCHAR,
    column_names VARCHAR,     -- Comma-separated column names
    n_trees BIGINT,           -- 1-500, default 100
    sample_size BIGINT,       -- 1-10000, default 256
    contamination DOUBLE,     -- 0.0-0.5, default 0.1
    output_mode VARCHAR,      -- 'summary' or 'scores', default 'summary'
    seed BIGINT,              -- Random seed for reproducible results
    ndim BIGINT,              -- 1-N, default 1 (Extended IF)
    coef_type VARCHAR,        -- 'uniform' or 'normal'
    scoring_metric VARCHAR,   -- 'depth', 'density', or 'adj_depth'
    weight_column VARCHAR,    -- Column for sample weights (NULL = uniform)
    ntry BIGINT,              -- 1-100, default 1 (SCiForest)
    prob_pick_avg_gain DOUBLE -- 0.0-1.0, default 0.0
) -> TABLE

Additional Parameters

Parameter	Range	Default	Description
ndim	1-N	1	Hyperplane dimensions (Extended IF)
coef_type	-	'uniform'	'uniform' or 'normal'
scoring_metric	-	'depth'	'depth', 'density', 'adj_depth'
weight_column	-	NULL	Sample weight column name
ntry	1-100	1	Split candidates (SCiForest)
prob_pick_avg_gain	0.0-1.0	0.0	Gain-based selection probability

Examples

-- Basic multivariate
SELECT * FROM anofox_tab_isolation_forest_mv(
    'customer_events', 'purchase_amount, session_duration, page_views',
    100, 256, 0.1, 'scores'
) ORDER BY anomaly_score DESC LIMIT 10;

-- Extended IF with density scoring
SELECT * FROM anofox_tab_isolation_forest_mv(
    'transactions', 'amount, quantity, duration',
    100, 256, 0.05, 'scores', 42,
    3,          -- ndim
    'normal',   -- coef_type
    'density'   -- scoring_metric: density-based
);

-- SCiForest with gain-based selection
SELECT * FROM anofox_tab_isolation_forest_mv(
    'events', 'value, duration', 100, 256, 0.05, 'scores', 42,
    1, 'uniform', 'depth',
    NULL,       -- weight_column
    10,         -- ntry: evaluate 10 candidates
    0.5         -- prob_pick_avg_gain: 50% gain-based
);

---

DBSCAN Clustering

anofox_tab_dbscan

Detect outliers using Density-Based Clustering.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Table to analyze
column_name	VARCHAR	Yes	-	Column to analyze
eps	DOUBLE	No	0.5	Neighborhood radius
min_pts	BIGINT	No	5	Min points for dense region
output_mode	VARCHAR	No	'summary'	'summary' or 'clusters'

Point Classifications:

• CORE - Dense region centers (low anomaly score)
• BORDER - Cluster edges (moderate anomaly score)
• NOISE - Isolated outliers (high anomaly score: 1.0)

Example

SELECT * FROM anofox_tab_dbscan(
    'transactions', 'amount', 10.0, 5, 'clusters'
) WHERE point_type = 'NOISE';

anofox_tab_dbscan_mv

Multivariate DBSCAN for multiple columns (comma-separated column_names).

SELECT * FROM anofox_tab_dbscan_mv(
    'customer_events', 'amount, frequency',
    0.5, 10, 'clusters'
) WHERE point_type = 'NOISE';

---

OutlierTree (Explainable Anomaly Detection)

Unlike Isolation Forest which provides anomaly scores, OutlierTree generates human-readable explanations for why specific values are outliers based on conditional distributions.

Key Features

• Detects outliers in context (e.g., "salary is high for a Junior Developer")
• Returns natural language explanations with statistical backing
• Uses robust statistics (median + MAD) resistant to outliers
• Supports both numeric and categorical columns

anofox_tab_outlier_tree

Signatures

-- Simple (3 params, uses defaults)
anofox_tab_outlier_tree(table_name, columns, output_mode) -> TABLE

-- Full (9 params)
anofox_tab_outlier_tree(
    table_name VARCHAR,
    columns VARCHAR,
    output_mode VARCHAR,
    max_depth INTEGER,
    max_perc_outliers DOUBLE,
    min_size_numeric INTEGER,
    min_size_categ INTEGER,
    z_norm DOUBLE,
    z_outlier DOUBLE
) -> TABLE

Parameters

Parameter	Type	Default	Description
table_name	VARCHAR	required	Source table name
columns	VARCHAR	required	Comma-separated column names
output_mode	VARCHAR	'summary'	'summary' or 'outliers'
max_depth	INTEGER	4	Maximum tree depth
max_perc_outliers	DOUBLE	0.01	Max fraction of outliers per cluster
min_size_numeric	INTEGER	25	Min cluster size for numeric targets
min_size_categ	INTEGER	75	Min cluster size for categorical targets
z_norm	DOUBLE	2.67	Z-threshold for confidence intervals
z_outlier	DOUBLE	8.0	Z-threshold for outlier flagging

Output Modes

Summary Mode Returns:

Column	Type	Description
status	VARCHAR	'pass' or 'fail'
total_rows	BIGINT	Rows analyzed
outlier_count	BIGINT	Outliers detected
columns_analyzed	INTEGER	Columns analyzed
clusters_evaluated	BIGINT	Clusters evaluated
message	VARCHAR	Summary message

Outliers Mode Returns:

Column	Type	Description
row_id	BIGINT	Row index (1-indexed)
column_name	VARCHAR	Column with outlier
outlier_value	VARCHAR	The anomalous value
cluster_mean	DOUBLE	Mean in cluster
cluster_sd	DOUBLE	SD in cluster
z_score	DOUBLE	Robust z-score
lower_bound	DOUBLE	Lower CI bound
upper_bound	DOUBLE	Upper CI bound
conditions	VARCHAR	JSON array of split conditions
explanation	VARCHAR	Human-readable explanation
outlier_score	DOUBLE	Rarity score

Examples

-- Summary mode: quick pass/fail check
SELECT * FROM anofox_tab_outlier_tree('employees', 'department,salary', 'summary');

-- Outliers mode: get detailed explanations
SELECT row_id, column_name, outlier_value, explanation
FROM anofox_tab_outlier_tree('employees', 'job_title,salary,years_exp', 'outliers');

-- Returns explanations like:
-- "Value 150000 for column 'salary' is unusually high (expected: 52333 +/- 7413)
--  when job_title = 'Junior Developer'"

-- With custom parameters for small datasets
SELECT * FROM anofox_tab_outlier_tree(
    'test_data', 'category,value', 'outliers',
    4,          -- max_depth
    0.5,        -- max_perc_outliers
    3,          -- min_size_numeric
    2,          -- min_size_categ
    2.67,       -- z_norm
    3.0         -- z_outlier
);

---

Metric-level anomaly detection over time

While the checks above flag individual anomalous rows, anofox_tab_metric_anomaly_iqr (alias metric_anomaly_iqr) flags anomalous days: it computes a daily metric and checks whether the reference date's value is an outlier relative to the trailing window.

anofox_tab_metric_anomaly_iqr

anofox_tab_metric_anomaly_iqr(
    table_name VARCHAR,
    date_column VARCHAR,
    metric_column VARCHAR,    -- Optional: AVG of this column per day (default: row count)
    window_days BIGINT,       -- Trailing baseline window, default 30
    k DOUBLE,                 -- IQR multiplier, default 1.5
    mode VARCHAR,             -- 'both' (default), 'upper', or 'lower'
    reference_date DATE       -- Default: latest date in the data
) -> TABLE

Method: Computes a daily metric (row count, or AVG of metric_column), then flags the reference date when its value falls outside Q1/Q3 ± k·IQR of the trailing window_days-day window. This is distinct from the row-level anofox_tab_iqr check, which flags individual rows.

Returns: status, reference_date, metric_value, q1, q3, lower_bound, upper_bound, baseline_days, mode, message

Examples

-- Is today's order volume anomalous vs. the last 30 days?
SELECT * FROM anofox_tab_metric_anomaly_iqr('orders', 'order_date');

-- Daily average basket value, 14-day baseline, only flag drops
SELECT * FROM anofox_tab_metric_anomaly_iqr(
    'orders', 'order_date', 'basket_value', 14, 1.5, 'lower'
);

Tip: Pointed at a persisted run_checks result table ('dq_results', 'run_ts', 'value'), it detects anomalies in your check history over time. See Checks for the check-suite runner.

---

Method Comparison

Method	Type	Speed	Interpretation	Use Case
Z-Score	Statistical	Fast	Easy	Single column, normal dist
IQR	Statistical	Fast	Easy	Single column, robust
Isolation Forest	ML	Medium	Hard	Multiple columns, complex patterns
Extended IF	ML	Medium	Hard	Diagonal/curved anomalies
DBSCAN	ML	Slow	Medium	Density-based clusters
OutlierTree	ML	Medium	Easy	Explainable, contextual outliers

---

Decision Tree

How many features?
+-- ONE column
|   +-- Normal distribution? -> Z-Score
|   +-- Any distribution? -> IQR
|
+-- MULTIPLE columns
    +-- Need explanations? -> OutlierTree
    +-- Detect complex patterns? -> Extended IF (ndim > 1)
    +-- Find density clusters? -> DBSCAN
    +-- Maximize detection? -> Isolation Forest

---

Performance Characteristics

Function	Speed	Notes
Z-Score	O(n)	Single pass
IQR	O(n log n)	Sorting for quartiles
Isolation Forest	O(n log n)	Tree construction
Extended IF	O(n log n)	Additional hyperplane calc
DBSCAN	O(n^2)	Distance calculations
OutlierTree	O(n log n)	Tree building + scoring

The Isolation Forest implementation in AnoFox goes beyond the standard algorithm with 3 advanced modes: Extended IF uses multi-dimensional hyperplane splits (configurable via ndim) to detect diagonal and curved anomaly boundaries that standard axis-aligned splits miss. SCiForest adds information-gain guided splitting (configurable via ntry and prob_pick_avg_gain) for more targeted anomaly detection. Density scoring provides an alternative metric based on points-to-volume ratio instead of tree depth. All 3 modes are accessible through the same function signature with optional parameters.

---