ai_detect_anomalies function

Applies to: Databricks SQL

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

ai_detect_anomalies() is a table-valued function that identifies anomalous values in time series data. It uses the history preceding each value to estimate the expected value and prediction interval, then assigns an anomaly score.

Requirements

• A Pro or Serverless SQL warehouse. This function is not available on a Classic SQL warehouse.
• Enroll your workspace in the Predictive AI Functions preview. See Manage Databricks previews.

Syntax

SQL

ai_detect_anomalies(
  observed,
  time_col,
  value_col
  [, group_col]
  [, anomaly_threshold]
  [, covariate_col]
  [, holiday_region]
  [, only_last_n]
  [, smoothing]
)

Arguments

• observed is the table-valued input that contains the time series data.
• time_col is a STRING naming a DATE or TIMESTAMP column in observed. Timestamps must be unique within each time series.
• value_col is a STRING naming the numeric column to analyze. You can analyze one value column per invocation.
• group_col (optional) is a STRING or an ARRAY<STRING> naming columns that identify separate time series. Each group is analyzed independently. If omitted, the full input is treated as one time series.
• anomaly_threshold (optional) is a DOUBLE between 0 and 1 that controls when a value is flagged as anomalous. A row is anomalous when its anomaly_score is greater than this threshold. The default is 0.95. Lower values flag more anomalies, while higher values flag fewer anomalies.
• covariate_col (optional) is a STRING or an ARRAY<STRING> naming numeric or string columns whose values can help explain the time series, such as price, promotions, or weather.
• holiday_region (optional) is a STRING containing a country code, such as 'US'. When specified, the function accounts for holiday effects in the expected values.
• only_last_n (optional) limits the returned and scored rows while retaining earlier rows as historical context. Specify an integer from 1 through 8,192 to analyze only the most recent number of rows in each series, or specify a DATE or TIMESTAMP cutoff to analyze rows at or after that value. Limiting the scoring range can change anomaly scores because the function can make fewer predictions over different forecast windows. Omit this argument to analyze the full series.
• smoothing (optional) controls whether short gaps between anomalies are included in the same anomalous episode. When TRUE, the function marks gaps of up to three rows between flagged anomalies as anomalous. This can reduce fragmented anomaly episodes but can also flag normal values inside those gaps. Smoothing changes only is_anomaly; it does not change expected values, bounds, or anomaly scores. The default is TRUE.

Returns

The function returns the input time, group, and value columns, followed by these columns:

Column	Type	Description
expected	DOUBLE	The expected value based on the preceding history.
lower_bound	DOUBLE	The lower bound of the prediction interval.
upper_bound	DOUBLE	The upper bound of the prediction interval.
anomaly_score	DOUBLE	A score from 0 through 1 that indicates how strongly the observed value differs from the prediction.
is_anomaly	BOOLEAN	Whether the row is anomalous based on anomaly_threshold and smoothing.

Column	Type	Description
expected	DOUBLE	The expected value based on the preceding history.
lower_bound	DOUBLE	The lower bound of the prediction interval.
upper_bound	DOUBLE	The upper bound of the prediction interval.
anomaly_score	DOUBLE	A score from 0 through 1 that indicates how strongly the observed value differs from the prediction.
is_anomaly	BOOLEAN	Whether the row is anomalous based on anomaly_threshold and smoothing.

The first eight rows of each time series provide historical context. For these rows, expected, lower_bound, upper_bound, and anomaly_score are NULL, and is_anomaly is FALSE.

Examples

The following example generates a time series with an injected spike and identifies its anomalies:

SQL

WITH observed AS (
  SELECT
    date_add(DATE '2026-01-01', CAST(id AS INT)) AS ds,
    CASE
      WHEN id = 45 THEN 200.0
      ELSE 100.0 + 5.0 * sin(id / 3.0)
    END AS metric
  FROM range(60)
)
SELECT *
FROM ai_detect_anomalies(
  TABLE(observed),
  time_col => 'ds',
  value_col => 'metric'
)
ORDER BY ds

The following example analyzes each device independently, accounts for temperature and United States holidays, and returns only the most recent 100 rows from each device series:

SQL

SELECT *
FROM ai_detect_anomalies(
  TABLE(device_metrics),
  time_col => 'event_time',
  value_col => 'metric',
  group_col => 'device_id',
  anomaly_threshold => 0.98,
  covariate_col => 'temperature',
  holiday_region => 'US',
  only_last_n => 100,
  smoothing => FALSE
)
ORDER BY device_id, event_time

Limitations

The following limitations apply during Beta:

• Each time series can contain at most 8,192 input rows. A query with a longer series fails.
• Each time series must contain at least one finite observed value.
• Timestamps must be unique within each time series.
• You can analyze one value column per invocation.
• Error messages are delivered through the Python UDTF engine and can contain Python traceback information. The end of the traceback contains the actual error message.