BigQuery Anomaly Detection: How to Do It in SQL, and Where It Stops

BigQuery does anomaly detection natively through BigQuery ML. You train a model, then call ML.DETECT_ANOMALIES against it, and BigQuery flags the rows that do not fit. Four model types back it: ARIMA_PLUS for time series, KMEANS for distance-from-cluster, PCA for reconstruction error, and AUTOENCODER for the same idea on higher-dimensional data. It runs in SQL, it needs no external service, and it is genuinely good at finding a spike in a metric. What it does not do is watch your whole warehouse for you, tell you which pipeline caused the anomaly, or alert anyone. This guide covers both halves: how to run BigQuery ML anomaly detection, and where it stops.

Last updated July 2026.

Does BigQuery have anomaly detection?

Yes. BigQuery ML includes anomaly detection built into standard SQL, so you do not export data or stand up a separate service. You create or reuse a model, then run ML.DETECT_ANOMALIES, and BigQuery returns a boolean is_anomaly column plus a score for every input row. The method depends on the model behind it. A time-series model flags points outside a forecast band. A clustering or dimensionality-reduction model flags points that sit too far from normal structure. Because it is SQL, you can schedule it, join the results to anything, and put the output straight into a table or a view.

The important framing: anomaly detection in BigQuery is a query you run on data you already have, not a monitoring system that watches tables and pages a human. Those are different jobs, and conflating them is where teams get surprised later.

BigQuery ML anomaly detection, the four ways

Each model type answers a different shape of question.

For most data teams the time-series path is the one that matters, because the question is usually "did this metric move in a way it should not have." Here is the whole flow for a daily metric.

First, train an ARIMA_PLUS model on the history:

CREATE OR REPLACE MODEL analytics.orders_forecast OPTIONS(model_type='ARIMA_PLUS', time_series_timestamp_col='order_date', time_series_data_col='order_count') AS SELECT order_date, order_count FROM analytics.daily_orders;

Then detect anomalies against it, with a contamination threshold that controls sensitivity:

SELECT * FROM ML.DETECT_ANOMALIES(MODEL analytics.orders_forecast, STRUCT(0.02 AS anomaly_prob_threshold) ) WHERE is_anomaly = TRUE;

The anomaly_prob_threshold is the dial. Lower it and you catch more, including more noise. Raise it and you catch only the loud events. There is no universally correct value; you tune it against a period you already understand.

For the non-time-series models the pattern is the same shape: CREATE MODEL with KMEANS, PCA, or AUTOENCODER, then ML.DETECT_ANOMALIES with a contamination value that sets the expected fraction of anomalies. Writing these queries by hand is where a plain-English-to-SQL tool that turns a question into a warehouse query earns its keep, because the syntax around model options is fiddly and easy to get subtly wrong.

What about BigQuery outlier detection without a model?

If you do not want to train a model, you can get a long way with statistics in plain SQL. A rolling z-score catches most single-metric spikes: compute a trailing mean and standard deviation over a window, then flag any point more than three standard deviations out. It is crude, it assumes a roughly normal distribution, and it has no concept of weekly seasonality, so it will page you every Monday if Mondays are quiet. That last part is exactly why the ARIMA_PLUS path exists: it models the seasonality you would otherwise hand-code and get wrong for a quarter.

BigQuery also ships data profiling and data quality scans in Dataplex, which compute column statistics and let you set rule-based checks. Those are assertion-style checks (this column's null rate must be below X), not learned anomaly detection, and the two are complementary. Use scans for the rules you can state, and ML anomaly detection for the deviations you cannot.

Where BigQuery ML anomaly detection stops

The gap is not accuracy. BigQuery ML will happily tell you that yesterday's order count was anomalous. The gap is everything around that fact.

It is one table, one metric, one query. You wrote a model for daily orders. You have 300 other tables. Each one needs its own model, its own schedule, its own threshold, and its own maintenance when the schema or the seasonality shifts. Anomaly detection that you have to author per metric covers the metrics somebody had time to author, which is never all of them.

It does not tell you why. An anomaly flag says the number moved. It does not say the upstream Fivetran sync paused, or that a dbt model changed a join, or that a source switched cents to dollars. You still have to trace the cause by hand, and the trace is usually the slow part of an incident.

It does not tell you what broke downstream. A flagged anomaly on a staging table means nothing until you know it feeds three dashboards the finance team reads at 9am. Without lineage, an anomaly is a fact with no blast radius attached.

It does not alert anyone. ML.DETECT_ANOMALIES returns rows. Turning those rows into a Slack message a human sees, deduplicated so one upstream break does not fire 40 times, routed by severity, is a whole system you build and run yourself.

None of this is a criticism of the feature. It is a boundary. BigQuery ML gives you the detection primitive. A monitoring layer is what turns primitives into an operational practice, which is the subject of continuous anomaly detection across every table rather than one metric at a time.

When to use which

Use BigQuery ML anomaly detection directly when you have a specific, high-value metric, a team fluent in SQL and ML options, and the appetite to own the models and thresholds. A revenue forecast with anomaly flags, built once and watched closely, is a great use of ARIMA_PLUS. It is precise, it lives in your warehouse, and it costs only the compute to train and score.

Reach for a monitoring platform when the problem is coverage and operations rather than a single metric. If you want freshness, volume, schema, and distribution watched across every table without writing a model each time, with the cause and the downstream impact attached and an alert in Slack when something breaks, that is a different tool. Dataobservability connects a read-only role to BigQuery, learns each table's normal behavior automatically, and pages you with the blast radius already mapped, from 99 dollars a month. Many teams run both: BigQuery ML for the two or three metrics that deserve a bespoke model, and continuous monitoring for the long tail of tables nobody has time to model by hand.

Frequently asked questions

Does BigQuery have built-in anomaly detection?

Yes. BigQuery ML provides anomaly detection through the ML.DETECT_ANOMALIES function, which works with ARIMA_PLUS time-series models, KMEANS clustering, PCA, and AUTOENCODER models. It runs in standard SQL with no external service, returns an is_anomaly flag and a score per row, and can be scheduled. It detects deviations; it does not alert, explain causes, or map downstream impact on its own.

How do I detect anomalies in BigQuery?

Create a model that fits your data (an ARIMA_PLUS model for a metric over time, or KMEANS, PCA, or AUTOENCODER for multivariate rows), then call ML.DETECT_ANOMALIES against that model with a threshold controlling sensitivity. Filter where is_anomaly = TRUE to get the flagged rows. For a quick single-metric check without a model, a rolling z-score in SQL works but ignores seasonality.

What is the difference between ML.DETECT_ANOMALIES and a data quality scan?

A Dataplex data quality scan runs rule-based assertions you define, such as a null rate below a threshold or values inside an accepted set. ML.DETECT_ANOMALIES learns a model of normal and flags statistical deviation you did not have to define in advance. Scans catch the rules you can state; anomaly detection catches the departures you cannot. Most teams use both.

How much does BigQuery anomaly detection cost?

BigQuery ML anomaly detection costs BigQuery compute: the bytes processed to train the model plus the bytes scanned each time you run detection. For one metric on modest history it is cheap. Run per-model detection across hundreds of tables on a schedule and the scan cost adds up, which is one reason metadata-first monitoring platforms exist. There is no separate license fee for the ML functions themselves.

Can BigQuery anomaly detection replace a data observability tool?

For a single high-value metric, yes. Across a warehouse, no. BigQuery ML gives you the detection primitive for one model at a time, but it does not provide automatic coverage of every table, root-cause context, downstream lineage, or alerting. A data observability platform adds those operational layers on top of detection, which is what makes broken data something a team catches in minutes rather than discovers in a meeting.