How to show full query results in a SQL alert's notification?

SRJDB
New Contributor III

Monday

I have a SQL alert; the code behind it looks like this:

SELECT COLUMN_1, COLUMN_2, COUNT(*) AS TOTAL, 1 AS FORCE_TRIGGER
FROM MY_TABLE
GROUP BY COLUMN_1, COLUMN_2

The idea is that a scheduled job updates my_table and then runs the alert. The alert triggers when MIN(FORCE_TRIGGER) > 0 (in other words, each time it runs). It then sends me a notification via email with the query results included via the variable @QUERY_RESULT_TABLE.

The alert runs successfully and sends me an email. However, in it I only get a single row of query results.

Does anyone know how to get the full query results to display in the email please?

Labels:
0 Kudos

balajij8
Esteemed Contributor II

Monday

@SRJDB 

The underlying issue is on how Databricks SQL processes aggregation functions in the alert trigger criteria. When you configure the alert condition using an aggregate like MIN, MAX, AVG or SUM, the alert engine runs an evaluation pass that collapses the entire result set into a single scalar row. That single-row context gets passed into the result table variable in your notification template, which is why email only renders one record.

To preserve the complete table in your notification, switch the alert condition setting from MIN to FIRST_ROW. Using FIRST_ROW instructs the alert to evaluate the trigger criteria against the first record's value while keeping the full query result set intact. The notification variable will then render all returned rows from your query up to the limit of 100 rows.

AbhilashNagilla
Databricks Employee
Databricks Employee

Monday

You are getting one row because of the MIN in the alert condition rather than anything in the notification template. Setting an aggregation rewrites the query before it runs. From Alert query patterns: "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", and "those variables will only display the final, post-aggregation query result." So with MIN on FORCE_TRIGGER, what executes is shaped like WITH q AS (<your query>) SELECT MIN(FORCE_TRIGGER) FROM q, which returns exactly one row, and that single row is all @QUERY_RESULT_TABLE has to render.

To keep the always-fires pattern and still get every row:

  1. Set the condition to First row on FORCE_TRIGGER, keeping > 0. Create an alert describes the choice as "You can set an alert condition on the first value of a column in the query result, or you can select to set an aggregation across all rows of a single column, such as SUM or AVERAGE", and the Alerts API reference states that when aggregation is not set, "the behavior is equivalent to using First row." With no aggregation there is no CTE rewrite, so the full result set reaches the template. Your FORCE_TRIGGER column is 1 on every row, so a first-row check still fires on every run, and it does not matter which row the evaluator picks.

  2. Plan for the 100-row cap. Create an alert documents QUERY_RESULT_TABLE as "The query result HTML table (string). Results are limited to the first 100 rows. Only email notification destinations can render HTML." If your grouped result can exceed 100 rows, adding an explicit ORDER BY makes it predictable which rows land in the email.

Worth confirming with Test condition in the alert editor before the next scheduled run, since that shows you the evaluation without waiting for the schedule.

View solution in original post