TD_OutlierFilterTransform

Aug 31

Outliers can distort analysis, skew machine learning models, and introduce noise into downstream processing. The TD_OutlierFilterFit and TD_OutlierFilterTransform functions provide a simple way to identify outlier thresholds and remove anomalous values from your data using metadata-driven processing.

In this example, we'll create a small dataset containing mostly normal values alongside a couple of obvious outliers, fit percentile-based thresholds, apply the transformation, and validate the results.

TD_OutlierFilterTransform Syntax

TD_OutlierFilterTransform supports two execution patterns:

PARTITION BY ANY for applying a single set of fitted outlier thresholds across the entire dataset.

  
    TD_OutlierFilterTransform (
    ON { table | view | (query) } AS InputTable PARTITION BY ANY
    ON { table | view | (query) } AS FitTable DIMENSION
)

PARTITION BY Key for applying different outlier thresholds to different groups.

  
    TD_OutlierFilterTransform (
    ON { table | view | (query) } AS InputTable PARTITION BY group_column
    ON { table | view | (query) } AS FitTable PARTITION BY group_column
)

In this example, we'll create a sample dataset containing both normal observations and obvious outliers, fit percentile-based thresholds, and then filter the dataset.

Build the Input Dataset

We'll start by creating a small table containing ten normal observations and two intentional outliers.

The outliers are:

amount = 450.00
amount = 12.00
distance_km = 45.00
distance_km = 0.20

These values sit far outside the range of the remaining records.

  
    CREATE MULTISET TABLE demo_outlier_input AS (
    SELECT 1 AS id,
           CAST(98.00 AS DECIMAL(18,2)) AS amount,
           CAST(10.10 AS DECIMAL(18,2)) AS distance_km
    ...
) WITH DATA;

  
  

Fit Outlier Thresholds

Next, use TD_OutlierFilterFit to analyse the selected columns and determine the thresholds that define acceptable values.

In this example:

The amount and distance_km columns are analysed.
A percentile-based approach is used.
Values below the 5th percentile and above the 95th percentile are treated as potential outliers.

  
    SELECT *
FROM TD_OutlierFilterFit(
    ON demo_outlier_input AS InputTable
    OUT TABLE FitTable(demo_outlier_fit)
    USING
        TargetColumns('amount', 'distance_km')
        OutlierMethod('PERCENTILE')
        LowerPercentile(0.05)
        UpperPercentile(0.95)
) AS dt;

  
  

The generated metadata is stored in the table, demo_outlier_fit. This table contains the lower and upper bounds that will be used during transformation.

Apply TD_OutlierFilterTransform

Once the fit metadata has been created, it can be applied to the source data using TD_OutlierFilterTransform.

Rows falling outside the learned thresholds are removed from the result set.

  
    CREATE MULTISET TABLE demo_outlier_filtered AS (
    SELECT *
    FROM TD_OutlierFilterTransform(
        ON demo_outlier_input AS InputTable PARTITION BY ANY
        ON demo_outlier_fit AS FitTable DIMENSION
    ) AS dt
) WITH DATA;

  
  

In this example:

PARTITION BY ANY applies a single global outlier model.
DIMENSION indicates that the fit table contains metadata shared by all rows.

Review the Filtered Results

The transformed table can now be queried to verify that the outliers have been removed.

  
    SELECT *
FROM demo_outlier_filtered
ORDER BY id;

TD_Outlierfiltertransform.sql

James Armitage