[SIP] Proposal for Introducing Saved Queries in Alerts

Proposal Summary

This proposal introduces an option for users to select a pre-existing saved query when creating or editing an alert in Apache Superset. By decoupling the SQL definition from the alert configuration, users will have a more streamlined experience for updating SQL queries without having to modify the alert settings repeatedly.

Motivation

Problem Statement Currently, Superset allows users to include SQL queries directly in an alert’s configuration. When changes to an SQL query are necessary, users must:

  • Go to SQL Lab and modify or test the query,
  • Navigate back to the Alerts interface,
  • Update the SQL in the alert configuration manually.

This back-and-forth is inefficient and can be error-prone, particularly in environments where queries often evolve over time.

Rationale By providing the ability to reference a saved query (which itself can be updated independently), we can:

  • Reduce redundant steps for editing SQL queries in alerts.
  • Minimise errors or oversights caused by manual copying and pasting.
  • Promote reusability and consistency of SQL statements across different alerts.

Proposed Change and New or Changed Public Interfaces

Alerts UI:

  1. Add a checkbox or toggle to use an existing saved query instead of manually entering SQL.
  2. If checked, a dropdown of the user’s saved queries appears, allowing them to select the query to be used for the alert.

API Endpoints:

  1. [api/v1/reports]: Extend the existing reports API to accept and store query_id alongside the current SQL field (for backward compatibility).
  2. [api/v1/saved_queries]: Retrieve the user’s saved queries and populate the selection dropdown. Potentially reuse the existing endpoint to fetch a list of saved queries by user ownership.

Alert Execution:

AlertCommand in alert.py: Modify the execution logic to fetch the SQL from the chosen saved_query if a query_id is present. Then proceed with the usual alert evaluation and notifications.

Database Schema Changes:

reports_schedule: A new column query_id (nullable) that references the relevant saved query’s ID.

User Experience

Create/Edit Alert:

Option A: Enter a new SQL query (as it is today). Option B: Choose “Attach Saved Query” and select from a list of owned (or otherwise permitted) saved queries.

Editing a Saved Query:

Users can revise or refine the query in SQL Lab, and those changes automatically propagate to any alerts referencing that saved query.

Wireframes BEFORE Image

AFTER Image

Migration Plan and Compatibility

A new database migration script to:

  1. Add the query_id column to reports_schedule.

Backward Compatibility:

  • Alerts without a query_id will continue to behave exactly as before, reading SQL from the existing column.
  • Users can gradually transition to using saved queries by updating alerts.

Future Plan

Deprecation of Embedded Queries: In the future, the project might consider deprecating inline SQL for alerts in favor of strictly referencing saved queries. This, however, should be subject to feedback from the community and a formal deprecation notice.

Enhanced Permissions: Granular permissions could be introduced to manage which roles can attach or modify saved queries used by alerts.

Comment From: rusackas

To send this out for discussion, you can simply link to it on an email to dev@superset.apache.org (which is the Dev mailing list). For anyone looking to subscribe to that list, you can simply send a blank email to dev-subscribe@superset.apache.org.

Comment From: rusackas

I forgot to mention that the email title should be [DISCUSS][SIP-162] Introducing Saved Queries in Alerts - holler if you need any other help!

Comment From: samraHanif0340

@rusackas Any update so far? I've emailed as suggested

Comment From: rusackas

I don't see the email. Did it bounce? You have to subscribe to the dev@ list before you can post to it.

Comment From: SamraHanifCareem

@rusackas I'm not certain, Can you cross check as I have already subscribed to dev @ list before

Comment From: rusackas

It's there and officially open for discussion! Thanks for your patience here.

Comment From: rusackas

I like it, and it doesn't sound like there are any show-stoppers from anyone we've spoken with at Preset or Airbnb in the (public) SIP review session. The thing to consider here (for others) is that we need to spread this far and wide, so basically anywhere you enter a query, you can leverage a Saved Query like this.

Conversely, if you enter your own query here, should it be Saved as a Saved Query, too (or at least provide the option to do so)? Maybe it works both ways?

The other open question is how we treat Saved Queries in terms of ownership... we not only want to support the author's saved queries, but letting Saved Queries have multiple owners, as a primary asset in Superset. That would be another SIP, but it's related.

Comment From: SamraHanifCareem

@rusackas We have worked on Saved Queries having multiple owners internally. Would love to contribute