How SQL alerts work

A SQL alert runs a query against a warehouse connection on a schedule and applies conditions to the result. If you can express the question as SQL, you can alert on it.

This is the most capable alerting in the product. It is also the only kind where you write the measurement yourself, so it has the most to explain.

The shape

  ┌─ your query ──────────────────────────────┐
  │ SELECT subject, lag_min, drift_pct, ...   │
  │ FROM ...                                  │
  └────────────────┬──────────────────────────┘
                   │ rows
                   ▼
        ┌── per-column conditions ──┐
        │ lag_min   outside 1…60    │  ← each with its own threshold,
        │ drift_pct gt 2.5          │    severity, alert and state
        └────────────┬──────────────┘
                     │
      guards first ──┴── then measurements
                     │
              ┌── one alert per row ──┐   or   ┌── one rolled-up alert ──┐
              │ (grouped rules)       │        │ (notify_mode: summary)  │
              └───────────────────────┘        └─────────────────────────┘

What makes it different from other alerting

One query, many conditions. Before per-column conditions existed, alerting on four columns of one query meant four rules holding four copies of the same SQL — the query ran four times, and editing it meant editing it four times, of which the fourth is the one somebody forgets.

Creating a rule

Data Warehouse → SQL Alerts → New rule. Five steps:

1. Write the query — what to measure.
2. Preview it — see the real result set before setting any condition.
3. Set conditions — per column, with thresholds.
4. Choose schedule and window — when it runs.
5. Route it — where the page lands.

Then test it before you rely on it.

Guards run first

A condition can be marked a guard. Guards are evaluated before measurements, and if a guard fails the measurements are not reported at all.

This is how you stop a stale table producing a page about a metric that was never refreshed:

SELECT
    dateDiff('minute', max(_ingested_at), now()) AS mirror_lag_min,  -- guard
    countIf(status = 'failed')                   AS failures          -- measurement
FROM warehouse.payments
WHERE created_at > now() - INTERVAL 1 HOUR

With mirror_lag_min as a guard (outside 0…30), a mirror that has stopped replicating reports "the data is 4 hours old" rather than "failures dropped to zero" — which is the same reading and the opposite conclusion.

Rules that cannot evaluate

A rule whose query errors, times out, or returns a shape the conditions cannot be applied to raises a fault, not silence.

Faults are reported on transition, not on every run. A misconfigured rule that runs every five minutes should produce one message, not one every five minutes forever — which is exactly the failure mode this product exists to prevent.

Safety

Queries are validated before they run:

• the first token must be SELECT, WITH, EXPLAIN, DESCRIBE, DESC or SHOW
• write verbs (INSERT, UPDATE, DELETE, UPSERT, MERGE, REPLACE) are refused in statement position
• each run has a two-minute timeout, so one slow query cannot hold up every other rule

Reference