How to Detect Anomalies in Snowflake with SQL

To detect anomalies in Snowflake with SQL, create a model with CREATE SNOWFLAKE.ML.ANOMALY_DETECTION over training data that has a timestamp column and a target column, then call the DETECT_ANOMALIES method on that model against newer data. The result set flags each row with IS_ANOMALY alongside a forecast and a prediction interval. The whole thing is about fifteen lines of SQL, and the parts that decide whether it is useful are the prediction interval you choose and the amount of history you train on.

This is a walkthrough of the working SQL, the parameters that matter, and the documented limits that catch teams out on the first real table. If you are further back and comparing approaches rather than writing queries, the buyer level comparison of every native route lives on our Snowflake anomaly detection page.

The SQL, end to end

Start with training data. The function needs a timestamp column with granularity no finer than one second and a target column holding the number you care about. A daily row count per table is a good first series, because it is cheap to compute and it catches partial loads.

CREATE OR REPLACE VIEW orders_daily_volume AS
SELECT
  DATE_TRUNC('DAY', created_at)::TIMESTAMP_NTZ AS ts,
  COUNT(*)                                     AS row_count
FROM production.public.orders
WHERE created_at < DATEADD('DAY', -1, CURRENT_TIMESTAMP())
GROUP BY 1;

Then create the model. LABEL_COLNAME set to an empty string is what makes this unsupervised, which is what you want when you have no labeled history of past incidents, and that is nearly everyone.

CREATE OR REPLACE SNOWFLAKE.ML.ANOMALY_DETECTION orders_volume_model(
  INPUT_DATA      => SYSTEM$REFERENCE('VIEW', 'orders_daily_volume'),
  TIMESTAMP_COLNAME => 'ts',
  TARGET_COLNAME    => 'row_count',
  LABEL_COLNAME     => ''
);

Now score newer data. Note the word newer, because it is load bearing in a way the syntax does not advertise.

CALL orders_volume_model!DETECT_ANOMALIES(
  INPUT_DATA => SYSTEM$REFERENCE('VIEW', 'orders_recent_volume'),
  TIMESTAMP_COLNAME => 'ts',
  TARGET_COLNAME    => 'row_count',
  CONFIG_OBJECT     => {'prediction_interval': 0.9999}
);

That returns one row per input row with the columns SERIES, TS, Y, FORECAST, LOWER_BOUND, UPPER_BOUND, IS_ANOMALY, PERCENTILE, and DISTANCE. Y is what actually happened, FORECAST is what the model expected, and IS_ANOMALY is true when the value fell outside the interval.

Why your first run flags one percent of everything

The default prediction_interval is 0.99. That sounds like a safe, conservative setting and it is the single biggest source of confusion with this feature. A 99 percent prediction interval means that by construction roughly one percent of rows fall outside it and come back flagged. Run it with defaults against a ten million row table and you get something in the region of one hundred thousand rows with IS_ANOMALY set to true.

Nothing is broken when that happens. A prediction interval is doing exactly what it says. Snowflake documentation recommends setting the value very close to 1.0, and suggests 0.9999 or even closer, which is why the example above uses it rather than the default. The right number depends on how volatile the series normally is, so a stable daily row count tolerates a tighter interval than a marketing metric that legitimately spikes.

The operational question the SQL cannot answer is who picks that number for each of your tables, and who revisits it when a series changes character after a migration. One threshold chosen once by whoever wrote the query is a threshold nobody maintains. That is manageable across ten series and it is not manageable across a warehouse, which is the practical reason automated data anomaly detection tools derive tolerances from each table history instead of asking a person to nominate one.

How much training data does Snowflake anomaly detection need?

Two rows will train a model, which is a trap rather than a feature. The numbers that matter are further down the documentation: you need at least 12 rows per time series before results stop being naive, and at least 60 rows before the model can produce non linear results. On a daily series that means roughly two months of history before the model can represent anything more interesting than a straight line.

Train on three weeks of daily data and you will get a model that runs, returns plausible looking output, and has no real idea what your weekly seasonality looks like. It will flag every Monday. Give it a few months, or switch to an hourly series where 60 rows arrives in under three days.

For multi series training there is a ceiling too: five million rows per series on a standard warehouse. Beyond that, or when you pass five or more exogenous variables, Snowflake recommends a Snowpark optimized warehouse. One detail worth knowing before you plan capacity is that a larger warehouse size does not improve training time.

Can you detect anomalies in past data?

No, and this is the constraint most likely to break an evaluation plan. Snowflake documents that anomalies can only be detected in test data, never in the data used for training, and that every test timestamp must be greater than the training timestamps. Detection is strictly forward looking.

The consequence is that you cannot train a model today and point it at last quarter to see what it would have caught, which is the most natural way anyone would want to validate a detector before trusting it. It also removes the retrospective question you want answered in the days after an incident, which is how long the problem had been running before somebody noticed. Answering that needs stored metric history, which is a different architecture from an on demand scoring function.

How fast is it?

Inference runs at approximately one second per 100 rows, and the documentation is explicit that this is independent of warehouse size. You cannot buy your way out of it by sizing up. On that stated rate, scoring a million rows works out on the order of ten thousand seconds, which is close to three hours.

Training is a separate story and scales more comfortably: a standard XL warehouse handles roughly 100 time series of 100,000 rows each in about 211 seconds, and a Snowpark optimized XL handles around 1000 series of 100,000 rows in about 831 seconds.

The practical read is that this function belongs on aggregated metrics, not raw rows. Score one daily row count per table, not ten million individual orders. That is the same design conclusion you arrive at independently if you ever build warehouse wide monitoring yourself.

Supervised or unsupervised?

Pass a Boolean column to LABEL_COLNAME and training becomes supervised. The purpose is narrower than it sounds: it mainly stops the model overfitting to outliers that are already sitting in your training window, because you are telling it which historical points were genuinely abnormal.

It does not turn the function into a classifier that learns your incident taxonomy. And it requires something most teams do not have, which is a maintained record of which timestamps were bad. Worth remembering that the incidents that cost the most are usually the ones nobody labeled, because nobody knew they happened. Unsupervised is the honest default.

What you cannot change

Three limits are worth knowing before you design around this function. You cannot choose or adjust the algorithm, which is a gradient boosting machine using differencing, auto regressive lags, rolling averages, and calendar variables generated from the timestamp. You cannot override trend, seasonality, or seasonal amplitude, and season length is tied to input frequency, using 24 for hourly data and 7 for daily. And models are immutable: there is no updating one in place.

That last one has a consequence that only appears in production. If a series changes, you create a new model, so continuous operation means a schedule that creates model objects on a rolling basis and something that drops the old ones. Models also cannot be cloned or shared across roles or accounts, so if you validate in a development account and promote to production, you do not promote the model, you retrain it there. The thing you tested is not the thing that runs.

What you still have to build around it

The SQL above is a detector. A monitoring system is a detector plus everything that turns a result set into someone taking action:

• A schedule, usually a task, running per series
• A results table, so output persists past the query
• State, so the same ongoing anomaly does not alert on every single run
• A per series threshold somebody owns and revisits
• A rolling retrain strategy, because models cannot be updated in place
• An ownership map from table to team
• Routing into Slack or PagerDuty, plus acknowledgement and resolution
• Monitoring of the monitoring, so you find out when it silently stops running

None of that is difficult. All of it is work, and it is the work that gets left out of the estimate. This is a familiar shape to anyone who has stood up infrastructure alerting, where the detection part is a script that checks an endpoint every thirty seconds and the hard part is everything around it: deduplication, escalation, and making sure the right person is woken. Data monitoring has the same structure with two differences that matter. The cardinality is thousands of tables rather than dozens of hosts, and there is no equivalent of a server being down, because a table can be perfectly fresh, perfectly sized, and still hold wrong numbers.

When the native function is the right answer

If you have five to ten important series, an engineer who is comfortable maintaining this, and no ambition to cover the whole warehouse, SNOWFLAKE.ML.ANOMALY_DETECTION plus a scheduled task is a genuinely good choice. It is well documented, it runs where the data already is, and you should not pay anyone for it.

If you want rule based checks rather than statistical ones, the other native route is data metric functions, which cover null counts, duplicates, row counts, freshness, and schema changes on a schedule. Those need Enterprise Edition and cap out at 50,000 associations per account, and we walk through the SQL and the credit cost in the guide to data quality checks in Snowflake.

The line moves when coverage becomes the goal rather than a shortlist. At that point you are operating a system rather than running a query, and the maintenance lands on your most senior data engineer, taken directly out of the pipeline work they were hired for. That is the comparison worth making explicitly, and it is why Snowflake data observability starts from the full table list, learns a baseline per table, and routes what fires to whoever owns the dataset, from 99 dollars a month with a 14 day trial that needs no card.