Alert query patterns

This page covers SQL authoring techniques that go beyond a single-column threshold check.

Alert aggregations

An aggregation on an alert works by modifying the original SQL of the Databricks SQL query attached to the alert. The alert wraps the original query text in a common table expression (CTE) and performs a wrapping aggregation query on it to aggregate the query result.

As an example, a SUM aggregation on an alert attached to a query with text SELECT 1 AS column_name means that whenever the alert is refreshed, the modified SQL that runs would be: WITH q AS (SELECT 1 AS column_name) SELECT SUM(column_name) FROM q.

This means that the original query result (pre-aggregated) cannot be shown in an alert custom body (with parameters such as QUERY_RESULT_ROWS and QUERY_RESULT_COLS) whenever there is an aggregation on an alert. Instead, those variables will only display the final, post-aggregation query result.

Alert on multiple columns

To set an alert based on multiple columns of a query, your query can implement the alert logic and return a Boolean value for the alert to trigger on. For example:

SELECT CASE WHEN drafts_count > 10000 AND archived_count > 5000 THEN 1 ELSE 0 END
FROM (
SELECT sum(CASE WHEN is_archived THEN 1 ELSE 0 END) AS archived_count,
sum(CASE WHEN is_draft THEN 1 ELSE 0 END) AS drafts_count
FROM queries) data

This query returns 1 when drafts_count > 10000 and archived_count > 5000. Then you can configure the alert to trigger when the value is 1.

Alert on metric views

You can set an alert on a metric view to get notified when a metric value falls outside an expected range. Alerts on metric views work the same as alerts on any other dataset. In the alert's query, reference the metric view by its fully qualified name.