Skip to main content

Databricks SQL alerts

Beta

This feature is in Beta.

This page provides step-by-step guidance for enabling and using Databricks SQL alerts to automate query execution, evaluate custom conditions, and deliver notifications when those conditions are met. With alerts, you can proactively monitor your business data and receive timely notifications whenever reported values fall outside of expected thresholds. When you schedule an alert, its associated query runs and the alert criteria are checked-regardless of any existing schedules on the underlying query. Additionally, you can access an alert history to review the results of past alert evaluations.

To learn how to work with legacy alerts instead, see What are legacy alerts?.

Enable alerts in your workspace​

For most customers, workspace admins can control workspace access to this preview from the Previews page. If you do not see this version of alerts in your workspace, contact your Databricks account team.

Enabling this preview does not impact your existing alerts; you can continue using both these and legacy alerts in parallel.

View and organize alerts​

Click the Alerts Icon Alerts in the sidebar to open the alerts listing page.

The alerts listing page.

  • The Alerts tab is open by default and shows all alerts in the workspace. You can type to filter by name. Click Only my alerts to show only alerts where you are the owner.

  • The Legacy alerts tab shows alerts that were created using the previous alerts system.

  • Name shows the string name of each alert.

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

  • Schedule shows the last updated time or date.

  • Owner shows the username of the person who owns the alert.

  • 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 indicates that Databricks SQL does not have enough data to evaluate the alert criteria. This status appears immediately after creating your alert and remains until the query has run. This status is also used if there was no data in the query result or if the most recent query result doesn’t include the Value Column you configured.
    • ERROR indicates that an error occurred during alert evaluation.

Create an alert​

The steps in this section walk you through how to create the alert shown in the following image. Use the labels and explanations to understand each component.

The create alert UI is shown with numbered labels that identify different parts.

  1. Data set: Write and test the query that you want to alert on.
  2. Condition: Select the value threshold that should trigger an alert notification. You can run the query to test the condition from this section.
  3. Schedule: Set a periodic schedule for the alert to run.
  4. Notifications: Indicate with users or notification destinations should be alerts when the threshold value falls outside of the expected range.
  5. Advanced: Use advanced settings to alert on special values and conditions.
important

During the Beta period, alerts do not support queries with parameters. See What are legacy alerts?.

Use the following steps to create an alert:

  1. Click Alerts Icon Alerts in the sidebar and click Create Alert.

  2. Copy and paste the following query in the Data set editor.

SQL
SELECT
  to_date(tpep_pickup_datetime) as date,
  SUM(fare_amount) as amount
FROM
  `samples`.`nyctaxi`.`trips`
GROUP BY
  ALL
ORDER BY
  1 DESC

  1. In the Condition field, set the conditions that should trigger the alert.

Condition field with applied settings as in the following text.

Apply the following settings as shown:

  • Select Sum as the data value to check. 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.
  • Select amount as the column to check.
  • Select > (greater than) as the logical operator to apply.
  • Set the threshold value to Static value 4000. Static value is selected by default. Enter 4000 into the value field.
  1. Click Test condition to preview the alert and test whether the alert would trigger with the current data. The provided settings should trigger the alert. You can change the threshold value to test further.
  2. Use the Schedule drop-down menus to set the alert so that it runs Every 5 minutes from 0 minutes past the hour. Optionally, select the Show cron syntax checkbox to edit the schedule in Quartz Cron Syntax.
  3. In the Notifications section, search for your user name and click on it when it appears.
  4. Click Create.

Your alert is saved and will run on the schedule you provided. The data used in this example is static, so if your Test condition returned as Triggered, it will again the next time it runs. You will receive an email notification as an alert. If the Test condition returned OK, no notification is sent.

Review alert details​

A page opens to show your alert details. Additionally, an alert history is shown with evaluation history details. Click the Run now button to run the alert query immeditately an return the result on this screen.

Click Edit in the top right corner of the page to continue editing the alert.

Advanced settings​

Click Advanced settings to apply the following settings:

  • Notify on ok: Send a notification when the alert returns as OK.

  • Notification frequency: Periodically send notifications on an alert until the status retunrs to OK.

  • Empty result state: Set a special status to return when the query returns no results.

  • Template: You can choose to send notifications using the default template or a custom template. The following list explains each template type:

    • 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_COLUMN: The alert column name (string).
        • 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 HTML table (string). Results are limited to the first 100 rows. Only email notification destinations can render HTML.
        • 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. Only email notification destinations can render HTML. 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.

Workspace admins can set up new notification destinations. See Manage notification destinations.

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:

SQL
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.

Configure alert permissions and transfer alert ownership​

For alert permission levels, see Alerts ACLs.

  1. In the sidebar, click Alerts.

  2. Click an alert.

  3. Click Share in the upper-right corner of the alert detail page to open the Sharing dialog.

    Manage alert permissions

  4. Search for and select the groups and users, and assign the permission level.

  5. Click Add.

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. Service principals and groups cannot be assigned ownership of a alert. You can also transfer ownership using the Permissions API.

  1. As a workspace admin, log in to your Databricks workspace.

  2. In the sidebar, click Alerts.

  3. Click an alert.

  4. Click the Share button at the top right to open the Sharing dialog.

  5. Click on the gear icon at the top right and click Assign new owner.

    Assign new owner

  6. Select the user to assign ownership to.

  7. Click Confirm.

Last updated on