Shape / inspect · All operations

Find outliers

A variant of Check data quality

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

Status

In arcform

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.
  • The statement checks its own result and raises an error when it is wrong: “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”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

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

Duckle (1)
  • Outlier Detectionread 2026-09-25

    route IQR or z-score outliers of a numeric column to reject

KNIME Analytics Platform (2)
  • Numeric Outliersread 2026-09-27

    Flag numeric outliers by interquartile-range rule and remove or replace them

  • Numeric Outliers (Apply)read 2026-09-27

    Treat outliers in a table using bounds computed by Numeric Outliers

RapidMiner Studio (1)
  • Tukey Testread 2026-09-27

    Flag outliers per numeric column by Tukey's fences around the quartiles

SQLMesh (1)
  • z_scoreread 2026-09-27

    Audit: No row has an absolute z-score above a threshold

Background

An outlier is an observation that appears to deviate markedly from other observations in the sample.[1] Tukey's fences mark as an outlier any observation more than k times the interquartile range below the lower quartile or above the upper quartile.[2] The Z-score of an observation gives how many sample standard deviations it lies from the sample mean.[1]

When to use it

An outlier may indicate bad data, but it may also be random variation or something scientifically interesting, and an outlying observation is typically not simply deleted.[1] Removing a data point solely because it is an outlier is a controversial practice, often frowned upon by many scientists, as it typically invalidates statistical results.[2] In large samples a small number of outliers is to be expected, and is not due to any anomalous condition.[2] Flag outliers first and look at them; remove or reject only those with a known cause, such as a unit error or a test record. (Meridian's view)

Using Z-scores to identify possible outliers can be misleading, particularly for small samples, because the maximum Z-score is at most (n-1)/sqrt(n).[1] Masking can occur when too few outliers are specified in a test, as the additional outliers may influence the test statistic enough that no points are declared outliers.[1] Outlier tests that assume an approximately normal distribution can report an outlier that is in fact due to the data not being normal.[1]

For discrete distributions there is no universal agreement on selecting the quartile values.[3] In DuckDB, quantile_cont returns an interpolated quantile, the method listed as Type 7 in Hyndman and Fan (1996).[4] Record which quartile method set the fences, so a result can be compared fairly with one from another tool. (Meridian's view)

The method

John Tukey proposed the interquartile range test, with k = 1.5 marking an outlier and k = 3 marking data that is far out.[2] Iglewicz and Hoaglin recommend a modified Z-score built from the median and the median absolute deviation, labelling values above 3.5 in absolute value as potential outliers.[1]

Iglewicz and Hoaglin distinguish outlier labelling, which flags potential outliers for investigation, from accommodation with robust techniques and from formal identification.[1] Grubbs' test is recommended for a single outlier, and the Generalized Extreme Studentized Deviate test when the exact number of outliers is not known.[1] Data that follow an approximately lognormal distribution can be transformed to normality by taking logarithms before an outlier test is applied.[1]

The two common approaches to excluding outliers are trimming, which discards them, and Winsorising, which replaces them with the nearest non-suspect data.[2] Write the flagged rows to a table of their own rather than deleting them, so the decision can be revisited when the cause is known. (Meridian's view)

Sources

  1. [1]1.3.5.17. Detection of Outlierssecondary source
  2. [2]Outlier - Wikipediasecondary source
  3. [3]Quartile - Wikipediasecondary source
  4. [4]Aggregate Functions – DuckDBdocumentation