Skip to main content

Set up alerts based on anomaly detection

This page describes how to set up alerts based on the results of an anomaly detection scan. For an introduction to anomaly detection, see Anomaly detection.

To configure a Databricks SQL alert on the anomaly detection output results table, follow these steps. For more details about how to set up an alert, see Databricks SQL alerts.

important

By default, only account admins can access the system table system.data_quality_monitoring.table_results. If other users need to configure alerts, make sure to grant appropriate access.

  1. Click Alerts in the sidebar and click Create alert.

  2. Enter the following text in the query editor:

    SQL
    WITH rounded_data AS (
    SELECT
       DATE_TRUNC('HOUR', event_time) AS evaluated_at,
       CONCAT(catalog_name, '.', schema_name, '.', table_name) AS full_table_name,
       status,
       MAX(downstream_impact.num_queries_on_affected_tables) AS impacted_queries,
       MAX(freshness.commit_freshness.predicted_value) AS commit_expected,
       MAX(freshness.commit_freshness.last_value) AS commit_actual,
       MAX(completeness.daily_row_count.min_predicted_value) AS completeness_expected,
       MAX(completeness.daily_row_count.last_value) AS completeness_actual
    FROM system.data_quality_monitoring.table_results
    GROUP BY ALL
    )
    SELECT
       evaluated_at,
       full_table_name,
       status,
       commit_expected,
       commit_actual,
       completeness_expected,
       completeness_actual,
       impacted_queries
    FROM rounded_data
    WHERE
       evaluated_at >= current_timestamp() - INTERVAL 6 HOURS
       -- enter the minimum number of table violations before the alert is triggered
       AND impacted_queries > :min_tables_affected
       AND status = 'Unhealthy';
    note

    For legacy beta jobs, existing alert configuration should replace system.data_quality_monitoring.table_results with <catalog>.<schema>._quality_monitoring_summary.

  3. Run the query.

  4. Using the alert editor on the right side of the screen, configure the alert condition. For details, see Create an alert.

    Configure the trigger condition

  5. (Optional) To customize the email template, open the Advanced tab on the right side of the screen, and check Customize template to open the template editor.

    An example custom email template is shown below. For more information about custom templates, see Advanced settings.

    Html
    <h4>The following tables are failing quality checks in the last hour</h4>

    <table>
      <tr>
        <td>
          <table>
            <tr>
              <th>Table</th>
              <th>Expected Staleness</th>
              <th>Actual Staleness</th>
              <th>Expected Row Volume</th>
              <th>Actual Row Volume</th>
              <th>Impact (queries)</th>
            </tr>
            {{#QUERY_RESULT_ROWS}}
            <tr>
              <td>{{full_table_name}}</td>
              <td>{{commit_expected}}</td>
              <td>{{commit_actual}}</td>
              <td>{{completeness_expected}}</td>
              <td>{{completeness_actual}}</td>
              <td>{{impacted_queries}}</td>
            </tr>
            {{/QUERY_RESULT_ROWS}}
          </table>
        </td>
      </tr>
    </table>

Now, you have an alert that triggers based on the downstream impact of the quality issue, helping you debug the table that triggered the alert.

Last updated on