# Find outliers

Shape / inspect

A variant of [Check data quality](https://meridian.online/operations/check-data-quality).

Find values far from the rest of their column by interquartile range or z-score, and flag, remove or reject them.

Page: <https://meridian.online/operations/check-data-quality/find-outliers>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

find outliers by the interquartile range and by z-score, and flag, remove or reject them

```sql
CREATE TEMP TABLE readings AS SELECT * FROM (VALUES
  (1, 10), (2, 11), (3, 11), (4, 12), (5, 12), (6, 13), (7, 10), (8, 12), (9, 11), (10, 9), (11, 20), (12, 60)
) t(id, v);
-- the quartiles and the mean and standard deviation are taken over the whole column, then each row is tested
CREATE TEMP TABLE flagged AS
SELECT id, v,
       v < q1 - 1.5 * (q3 - q1) OR v > q3 + 1.5 * (q3 - q1) AS outlier_iqr,
       abs(v - mean) / sd > 2 AS outlier_z
FROM (SELECT *, quantile_cont(v, 0.25) OVER () AS q1, quantile_cont(v, 0.75) OVER () AS q3,
             avg(v) OVER () AS mean, stddev_samp(v) OVER () AS sd FROM readings);
CREATE TEMP TABLE kept AS SELECT id, v FROM flagged WHERE NOT outlier_iqr;
CREATE TEMP TABLE rejected AS SELECT id, v FROM flagged WHERE outlier_iqr;
SELECT * FROM flagged ORDER BY id;
SELECT CASE WHEN ((SELECT list(id ORDER BY id) FROM flagged WHERE outlier_iqr) = [11, 12] AND (SELECT list(id ORDER BY id) FROM flagged WHERE outlier_z) = [12] AND (SELECT count(*) FROM kept) = 10 AND (SELECT list(id ORDER BY id) FROM rejected) = [11, 12] AND (SELECT count(*) FROM flagged) = 12) THEN 'as expected' ELSE error('the interquartile range should flag readings 11 and 12, the z-score above 2 only reading 12, and the removed and rejected tables should split 10 and 2') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Duckle: [`Outlier Detection`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25)
- KNIME Analytics Platform: [`Numeric Outliers`](https://api.hub.knime.com/nodes/*KJTmGCLiAl3tpEcj?details=full) (read 2026-09-27)
- KNIME Analytics Platform: [`Numeric Outliers (Apply)`](https://api.hub.knime.com/nodes/*Nx_LJNtSi3lhr1Ok?details=full) (read 2026-09-27)
- RapidMiner Studio: [`Tukey Test`](https://docs.rapidminer.com/latest/studio/operators/extensions/Operator%20Toolbox/outliers/tukey_test.html) (read 2026-09-27)
- SQLMesh: [`z_score`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/audits.md) (read 2026-09-27)