Alerts

Use SQL editor to define alerts to notify you when a field returned by a scheduled query meets a threshold. You can use alerts to monitor your business or integrate alerts with tools to start workflows such as user onboarding or support tickets. Alerts complement scheduled queries, but their criteria are checked after every execution.

Important

  • Alerts leveraging queries with parameters use the default value specified in the SQL editor for each parameter.

View and organize alerts

Alerts can be viewed in one of two ways:

  • New alerts can now be viewed in the workspace browser by clicking Workspace Icon Workspace in the sidebar. These alerts are viewable, by default, in the Home folder. Users can organize alerts into folders in the workspace browser along with other Databricks objects. To view and organize currently existing alerts in the workspace browser, users (or admins) must migrate them into the workspace browser. For information about the Databricks SQL workspace browser, including migration, see Workspace browser.

  • All alerts, regardless of creation time or migration status, can be viewed by clicking Alerts Icon Alerts in the sidebar.

Important

Starting on July 10, 2023, Databricks will force-migrate all Databricks SQL content (dashboards, queries, alerts) to the workspace browser. Visit My Alerts and look for any un-migrated alerts, which will have a checkbox on the lefthand side. When a box is checked, a Migrate button will appear that allows you to migrate multiple assets at a time. If no action is taken, your alerts will be moved to your user folder. Workspace admins should ensure that objects without a valid active owner are updated to have one. Starting on September 10, 2023, we will delete all unmigrated objects without a valid owner.

By default, objects are sorted in reverse chronological order. You can reorder the list by clicking the column headings.

  • Name shows the string name of each alert.

  • State shows whether the alert status is TRIGGERED, OK, or UNKNOWN.

  • Last Updated shows the last updated time or date.

  • Created at shows the date and time the alert was created.

    • TRIGGERED means that on the most recent execution, the Value column in your target query met the Condition and Threshold you configured. If your alert checks whether “cats” is above 1500, your alert will be triggered as long as “cats” is above 1500.

    • OK means that on the most recent query execution, the Value column did not meet the Condition and Threshold you configured. This doesn’t mean that the Alert was not previously triggered. If your “cats” value is now 1470, your alert will show as OK.

    • UNKNOWN means Databricks SQL does not have enough data to evaluate the alert criteria. You will see this status immediately after creating your Alert and until the query has executed. You will also see this status if there was no data in the query result or if the most recent query result doesn’t include the Value Column you configured.

Create an alert

Follow these steps to create an alert on a single column of a query.

  1. Do one of the following:

    • Click New Icon New in the sidebar and select Alert.

    • Click Alerts Icon Alerts in the sidebar and click the + New Alert button.

    • Click Workspace Icon Workspace in the sidebar and click + Create Alert.

  2. Search for a target query.

    Target query

    To alert on multiple columns, you need to modify your query. See Alert aggregations.

  3. In the Trigger condition field, configure the alert.

    • The Value column drop-down controls which field of your query result is evaluated. Alert conditions can be set on the first value of a column in the query result, or you can choose to set an aggregation across all the rows of a single column, such as SUM or AVERAGE.

      Alert aggregations

    • The Operator drop-down controls the logical operation to be applied.

    • The Threshold value text input is compared against the Value column using the Condition you specify.

    Alert settings

  4. Click Preview alert to preview the alert and test whether the alert would trigger with the current data.

  5. In the When triggered, send notification field, select how many notifications are sent when your alert is triggered:

    • Just once: Send a notification when the alert status changes from OK to TRIGGERED.

    • Each time alert is evaluated: Send a notification whenever the alert status is TRIGGERED regardless of its status at the previous evaluation.

    • At most every: Send a notification whenever the alert status is TRIGGERED at a specific interval. This choice lets you avoid notification spam for alerts that trigger often.

    Regardless of which notification setting you choose, you receive a notification whenever the status goes from OK to TRIGGERED or from TRIGGERED to OK. The schedule settings affect how many notifications you will receive if the status remains TRIGGERED from one execution to the next. For details, see Notification frequency.

  6. In the Template drop-down, choose a template:

    • Use default template: Alert notification is a message with links to the Alert configuration screen and the Query screen.

    • Use custom template: Alert notification includes more specific information about the alert.

      1. A box displays, consisting of input fields for subject and body. Any static content is valid, and you can incorporate built-in template variables:

        • ALERT_STATUS: The evaluated alert status (string).

        • ALERT_CONDITION: The alert condition operator (string).

        • ALERT_THRESHOLD: The alert threshold (string or number).

        • ALERT_NAME: The alert name (string).

        • ALERT_URL: The alert page URL (string).

        • QUERY_NAME: The associated query name (string).

        • QUERY_URL: The associated query page URL (string).

        • QUERY_RESULT_TABLE: The query result table.

        • QUERY_RESULT_VALUE: The query result value (string or number).

        • QUERY_RESULT_ROWS: The query result rows (value array).

        • QUERY_RESULT_COLS: The query result columns (string array).

        An example subject, for instance, could be: Alert "{{ALERT_NAME}}" changed status to {{ALERT_STATUS}}.

      2. You can use HTML to format messages in a custom template. The following tags and attributes are allowed in templates:

        • Tags: <a>, <abbr>, <acronym>, <b>, <blockquote>, <body>, <br>, <code>, <div>, <em>, <h1>, <h2>, <h3>, <h4>, <h5>, <h6>, <head>, <hr>, <html>, <i>, <li>, <ol>, <p>, <span>, <strong>, <table>, <tbody>, <td>, <th>, <tr>, <ul>

        • Attributes: href (for <a>), title (for <a>, <abbr>, <acronym>)

      3. Click the Preview toggle button to preview the rendered result.

        Important

        The preview is useful for verifying that template variables are rendered correctly. It is not an accurate representation of the eventual notification content, as each notification destination can display notifications differently.

      4. Click the Save Changes button.

  7. In Refresh, set a refresh schedule. An alert’s refresh schedule is independent of the query’s refresh schedule.

    • If the query is a Run as owner query, the query runs using the query owner’s credential on the alert’s refresh schedule.

    • If the query is a Run as viewer query, the query runs using the alert creator’s credential on the alert’s refresh schedule.

  8. Click Create Alert.

  9. Choose a notification destination.

    Important

    If you skip this step you will not be notified when the alert is triggered.

    Notification destination

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.

Note

All trigger conditions related to aggregations are not supported by the API.

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.

Notification frequency

Databricks SQL sends notifications to your chosen notification destinations whenever it detects that the Alert status has changed from OK to TRIGGERED or vice versa. Consider this example where an Alert is configured on a query that is scheduled to run once daily. The daily status of the Alert appears in the following table. Prior to Monday the alert status was OK.

Day

Alert Status

Monday

OK

Tuesday

OK

Wednesday

TRIGGERED

Thursday

TRIGGERED

Friday

TRIGGERED

Saturday

TRIGGERED

Sunday

OK

If the notification frequency is set to Just Once, Databricks SQL sends a notification on Wednesday when the status changed from OK to TRIGGERED and again on Sunday when it switches back. It does not send alerts on Thursday, Friday, or Saturday unless you specifically configure it to do so because the Alert status did not change between executions on those days.

Configure alert permissions

To configure who can manage and run alerts, see Alert access control.

Transfer ownership of an alert

When you save an alert, you become the alert’s owner. If an alert’s owner is removed from a workspace, the alert no longer has an owner. A workspace admin user can transfer ownership of an alert to a different user. To transfer ownership by using the Databricks SQL UI:

  1. Open the alert.

  2. Click Share.

  3. Select a user, and then select Can Manage.

  4. Read the prompt. If you agree, click Confirm.

To transfer ownership by using the REST API, see Transfer ownership of an alert.

If the alert previously had an owner, that user no longer has the Can Manage permission on the alert, and the user to whom you gave the Can Manage permission is now the owner.