Data warehouse/SQL alerts/Writing the query

Writing the query

What shape the result set must be, how columns are interpreted, and the mistakes that produce a rule that never fires.

The two shapes

One row, several columns — a scalar rule

sql

SELECT
    count()                                      AS orders,
    countIf(status = 'failed')                   AS failed,
    round(100.0 * countIf(status = 'failed') / count(), 2) AS failure_pct
FROM warehouse.orders
WHERE created_at > now() - INTERVAL 15 MINUTE

Each numeric column can carry its own condition. One alert per condition.

One row per subject — a grouped rule

sql

SELECT
    subject,                       -- the key
    count()      AS volume,
    avg(latency) AS avg_latency
FROM warehouse.mail_message_logs
WHERE created_at > now() - INTERVAL 1 HOUR
GROUP BY subject

Turn on Group by and name the key column. Each row becomes its own alert with its own clocks, recovery and history. See Per-row alerts.

How columns are read

Column type	Used as
Numeric	A value a condition can be applied to
Text	Context — shown in the alert; can be the group key or a direction
Date / timestamp	Context. Not a value

Important

A numeric column is one that parses as a number in full. "2026-09-09 00:00-14:00" is text, not the year 2026. This matters: an earlier version parsed leading digits and silently treated window labels as measurements.

If a rule needs a number and several numeric columns are present, it will not guess — it asks you which one. Guessing produces a rule that alerts confidently on the wrong column.

Naming columns

Column names are what the alert message says. AS lag_min reads better than AS c2, and six months later it is the difference between an alert somebody acts on and one they have to go and look up.

Text columns come through as labels and are shown in the alert body — up to three of them, so a drift alert can say which window it measured without you adding it to the message template.

Time windows in the query

The rule's evaluation window is a setting; the query's WHERE clause is yours. They should agree.

sql

-- Rule window: 15 minutes.  Query window: 15 minutes.  Agreed.
WHERE created_at > now() - INTERVAL 15 MINUTE

A query covering an hour on a rule that runs every five minutes is not wrong, but it means twelve consecutive evaluations see mostly the same data — so For is measuring the same event repeatedly rather than a sustained one.

Things that produce a rule that never fires

Mistake	Symptom
Query returns no rows when healthy	Nothing to evaluate — the rule looks fine and can never fire. Return a row with `0` instead.
Condition on a text column	No numeric value to compare
`Group by` on without a key column	Rows cannot be told apart
A range condition with only one bound	Matches nothing. The editor marks this in red
Window shorter than the data's arrival lag	Always zero — add a freshness guard

Tip

Prefer countIf(...) over WHERE ... HAVING count() > 0. A query that returns no rows when everything is healthy cannot distinguish "healthy" from "the query is broken".

Preview before you save

Preview run executes the query and shows the real result set — columns, types, and the first rows — plus what each configured condition would decide against it.

It works before any condition is set, so you can look at the shape first and write conditions against what you actually have rather than what you expected.

Limits

Limit	Value
Query timeout	2 minutes
Rows evaluated per run	2000
Rollup lines in one message	25, and 2400 characters

A grouped query returning more than 2000 rows is reporting a shape, not a set of subjects — aggregate it further.

Where this behaviour lives: backend/internal/integration/dwh_runner.go, backend/internal/domain/dwh_metrics.go. If the code and this page disagree, the code is right — please fix the page.

Part of Data warehouse — Querying the warehouse, and alerting on what the query returns.