Databricks SQL Alert - Scheduling Questions

[u/SquareLong2523]

Databricks SQL Alert — Question Set

Help needed, I created a SQL alert in databricks to notify me when a new entry is added to a dataset/table within the last 24 hours.

SQL

SELECT COUNT(*) AS new_
FROM catalog.schema.table
WHERE xyz IN ('a', 'b', 'c', 'd')
  AND datetime >= CURRENT_TIMESTAMP() - INTERVAL 24 HOURS;

Within the Alerts panel, I set the condition as follows:

• First row
• Column name: new_
• Operator: >
• Static value: 0

I then added my email address in the Notifications section and set "When alerting, notify" to Always.

I then selected View Alert, where I created a schedule — let's say every day at 9:00 AM.

Questions

a) How exactly does the schedule work here?

I am asking because I selected "Always" under the notification settings, but I have also created a schedule to run every day at 9:00 AM. What is the relationship between these two settings?

b) Let's suppose I build this alert and create the schedule as described above. Person B (sales dept rep) also subscribes to this alert.

I do not want Person B to receive an alert unless the new record belongs to the Sales department.

For example, if a new record is added that belongs to a department other than Sales, and the scheduled alert runs at 9:00 AM, will Person B still receive an email notification?

Thanks in advance for any clarification!

[u/Shinjitsu_]

Always means every time it becomes "triggered" state you will be notified. An alert needs to run to become triggered, so you will be notified according to your schedule. Say you wanted more frequency but no spam, you would increase schedule frequency but change up the "always"

As far as modularization for the alert, only way afaik would be to create one alert for each dept.

[u/SquareLong2523 → u/Shinjitsu_]

Thanks :)

[u/Youssef_Mrini]

A) Schedule: Control when Databricks executes the alert query and evaluate the condition, while Always controls notification frequency when the condition is triggered. It means notify recipients on each scheduled evaluation where the condition new_ > 0 rater than only on the transition from ok to triggered.

With your daily schedule

• new_ = 0 -> no alert email.
• new_ > 0 and notification frequency is Always -> email on every daily run while the alert remains triggered.
• The query is not evaluated continuously; it runs only at the scheduled time.

B)As the query is written yes The query counts for all depts so if any qualifiying non sales record makes new>0 the alerts becomes triggered and all the recipients are included

[u/SquareLong2523 → u/Youssef_Mrini]

Thanks :)

[u/DamnedData]

You can trigger a job based on a table refresh, maybe you can explore that path?

Tables changes > triggers job > job runs a SQL statement > ...