Isn't this just a row count alert with extra steps?

It's a different question. A fixed row count alert asks whether the count crossed one number you picked. An anomaly ratio asks whether today looks like the last week, which adapts as the table's normal volume drifts, instead of a threshold you have to keep adjusting.

Can one Watch catch both a spike and a drop?

Not in a single Watch. A Condition is one comparison, Greater than or Less than, so a two-sided check (too low or too high) means two Watches on the same query, one for each direction.

What happens on the first few days, before there's a week of history?

The trailing average has little to compare against, so the ratio is unstable and the Watch may fire on noise. Give it a week of real data before trusting the alert, or start the Watch with a wide Condition and tighten it once you've seen normal values.

Is this real statistical anomaly detection?

No. It's a ratio against a trailing average, not a model with confidence intervals or seasonality. It catches an obviously abnormal day without needing a platform, but a genuinely noisy or seasonal table will need a wider tolerance or a purpose-built tool.

Row Count Anomaly Detection with SQL, No Platform Required.

Why a fixed threshold isn't always the right question

A plain row count Watch answers "did the count change at all, or cross this number." That's the right question for a table with a steady, predictable volume. It's the wrong question for a table whose normal count drifts, week over week, or day of week to day of week, where a fixed threshold either fires constantly or misses a real problem because the number you picked stopped being the right line months ago.

Before you start

A query you can turn into a single ratio or percent-change number, and QueryFlow Pipelines.

Steps

Write a query that returns one number: today's count divided by a trailing average, or the day-over-day percent change.
Run it, then click Watch this.
Confirm the Connection and SQL, and choose A value as what to watch, then pick the Column.
Set a Condition, Greater than or Less than, around the ratio that means the count isn't normal.
Set Check every, choose Destinations, and Save.

A worked example: today vs. the trailing 7-day average

On a BigQuery events table with a daily ingestion job, compute today's row count as a fraction of the average of the last seven days:

SELECT SAFE_DIVIDE(
  (SELECT COUNT(*) FROM `my-gcp-project.raw.events` WHERE DATE(_PARTITIONTIME) = CURRENT_DATE()),
  (SELECT AVG(daily_count) FROM (
    SELECT DATE(_PARTITIONTIME) AS day, COUNT(*) AS daily_count
    FROM `my-gcp-project.raw.events`
    WHERE DATE(_PARTITIONTIME) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
    GROUP BY day
  ))
) AS ratio_to_7day_avg;

Watch A value on ratio_to_7day_avg. A value below 0.5 means today's count is less than half of a typical day, worth a second look even though it isn't zero. A value above 1.5 means today is running half again as high as normal, which might be a real spike or a duplicate load. Since a Watch takes one Condition, set up two: one with Less than 0.5, one with Greater than 1.5, both on the same query.

Check it worked

Run the query a few times over a normal week first and note what the ratio actually looks like, most days should land close to 1.0. Use Run preview before saving to confirm the current value, then Check now from the Watches list after saving to confirm a real check fires.

Troubleshooting

If you see	Fix
Ratio comes back NULL	SAFE_DIVIDE returns NULL when the average is 0, usually the first few days before there's a full week of history to average.
Alert fires every Monday	A weekly cycle (lower weekend volume) can look like an anomaly to a flat threshold. Widen the Condition, or build the trailing average from the same weekday only.
No alert despite an obvious problem	Check the Condition direction, Less than won't catch a spike, and confirm Check every has actually elapsed since the last check.

Choosing a window length

Seven days is a reasonable default because it naturally spans a full weekly cycle, weekday and weekend volume both get represented in the average. A table with a stronger monthly pattern, an invoicing table that spikes at month-end, needs a longer window or a same-period comparison instead, or the trailing average will read the monthly spike itself as the anomaly.

A second worked example, on Databricks

SELECT
  (SELECT COUNT(*) FROM catalog.gold.orders WHERE order_date = current_date()) /
  NULLIF((SELECT AVG(daily_count) FROM (
    SELECT order_date, COUNT(*) AS daily_count
    FROM catalog.gold.orders
    WHERE order_date BETWEEN date_sub(current_date(), 8) AND date_sub(current_date(), 1)
    GROUP BY order_date
  )), 0) AS ratio_to_7day_avg;

Same idea, Databricks dialect: NULLIF stands in for BigQuery's SAFE_DIVIDE to avoid a divide-by-zero error rather than a NULL result, and the table reference uses Unity Catalog's three-part catalog.schema.table naming instead of a project-qualified BigQuery path.

When a plain threshold is still the better fit

If a table's daily count barely moves week to week, a plain row count Watch with Changed or a fixed Increased by/Decreased by is simpler to reason about and doesn't need a ratio query. Reach for the trailing-average version specifically when the table's normal volume genuinely varies and a single fixed number would need constant retuning.

A note on false positives around launches and holidays

A real marketing push, a holiday, or a one-off launch will genuinely break the pattern the trailing average expects, and the Watch will fire correctly even though nothing is actually broken. That's expected, not a bug in the approach: an anomaly ratio tells you the day looked unusual, it doesn't know why. Treat a fired Watch during a known event as a prompt to glance and confirm, not as a false alarm to silence permanently.

Catch a row count that doesn't look like the others.