Feature Request: Please add SQL Query and SQL Alert support to Asset Bundle

[litan1106]

Hi, please add SQL query and SQL alert support to the asset bundle in addition to the job, and pipeline type.

With sql_task, it makes more sense to use alert creation and query creation. The asset bundle provides the best DEVOPS experience while keeping everything in source control.

[pietern]

Thanks for creating the issue.

We do not plan to add these in the next ~2 months, but we will consider it beyond that.

Let's leave the issue open to gather +1s from other folks who would like to see this happen.

[Lonache]

Frequently we build (materialized) views in conjunction with jobs, and the refresh cycles are easiest to set up if this is done in the same repo and coordinated. I would, if I could, write idempotent SQL queries for the views, and I'd like to use sql_task syntax that @litan1106 suggested.

This is something I think a lot of us could use - or would use. Or should use. I mean, creating the views by hand would be possible. But it's messy to be doing some things by hand and others not.

@pietern We're all onboard with following best DevOps practice, right?

[pietern]

@Lonache The SQL queries and alerts that @litan1106 is referring to are proper resources.

If you're looking to manage SQL queries in text files, you can do that today with the sql_file_task.

You could check out the default-sql bundle template for an example on how to structure this.

To update views you can use CREATE OR REPLACE in the query text.

[litan1106]

@Lonache The SQL queries and alerts that @litan1106 is referring to are proper resources.

If you're looking to manage SQL queries in text files, you can do that today with the sql_file_task.

You could check out the default-sql bundle template for an example on how to structure this.

To update views you can use CREATE OR REPLACE in the query text.

even if we can use the sql_file_task to create the view for the SQL alert, we still want to create the SQL alert with the asset bundle so we can deploy all at once instead of manually create alert in the UI.

[Escoto]

Is there any timeline already to incorporate SQL Alerts into Bundles?..
My team is evaluating if we need to invest time into Terraform or is this already in the roadmap?

[pietern]

There is no timeline for SQL Alerts yet. First, we're working on adding support for AI/BI dashboards.

We'll definitely consider adding this and I agree it makes sense to do so. It is a matter of prioritization.

[hashemi-soroush]

Can you share the timeline on the dashboards, @pietern ?

[koopatroopa599]

Agreed this would be a great addition.

Based on some of the comments around using the sql_task, I've added a bit of a workaround to create alerts/notifications that are configured in asset bundles in case anyone else finds it useful.

An SQL file can be added to the asset bundle that raises errors on a condition - e.g.:

IF(
    Column = Condition,
    "Data is OK",
    raise_error('Alert has been triggered')
) AS Alert
FROM example_table

Then a scheduled job runs on our SQL warehouse - running that SQL file. If the SQL query raises an error, the webook notification gets activated and we get a notification.

resources:
  jobs:
    expectation:
      name: Expectation
      schedule:
        quartz_cron_expression: cron_expression
        timezone_id: some_timezone
        
      webhook_notifications:
        on_failure:
          - id: ${var.teams_channel_id}


      tasks:
        - task_key: sql_alert_file
          sql_task:
            warehouse_id: ${var.sql_warehouse}
            file:
              path: ../../src/sql_alert_file.sql

This isn't quite as nice as using proper databricks alert resources, but lets us configure similar alert notifications without doing anything in the UI.

[Philippe-Neveux]

Hello, I'm also interested into managing my SQL queries and alerts with Databricks Asset Bundle. Because it's not possible to manage them with DAB, I do it the Databricks python SDK for the moment. Here's an example:

Create the SQL Query (Databricks resource) from an SQL File

from databricks.sdk import WorkspaceClient
from databricks.sdk.service import sql

w = WorkspaceClient()
srcs = w.data_sources.list()

with open("/path/to/query.sql", 'r') as file:
    sql_query = file.read()

query = w.queries.create(query=sql.CreateQueryRequestQuery(
    display_name=f'my_query',
    warehouse_id=srcs[0].warehouse_id,
    description="My Query",
    query_text=sql_query,
    parent_path="/path/where/I/want/to/put/my/query"
  )
)

Then I create the alert

alert = w.alerts.create(
  alert=sql.CreateAlertRequestAlert(
    condition=sql.AlertCondition(
      operand=sql.AlertConditionOperand(
        column=sql.AlertOperandColumn(name="column_to_check")
      ),
      op=sql.AlertOperator.GREATER_THAN_OR_EQUAL,
      threshold=sql.AlertConditionThreshold(
          value=sql.AlertOperandValue(
            double_value=0.8
          )
      )
    ),
    display_name=f'name-of-my-alert',
    query_id=query.id, # Here's the query that I just created
    parent_path="/path/where/I/want/to/put/my/alert"
  )
)

I finally, i schedule a job to run my alert

from databricks.sdk.service import jobs
from databricks.sdk.service import compute

srcs = w.data_sources.list()

job = w.jobs.create(
    name=f'name-of-my-alert',# Keep the same names logic as your own project
    tasks=[
        jobs.Task(
            description="Description of my alert",
            sql_task=jobs.SqlTask(
                warehouse_id=srcs[0].warehouse_id,
                alert=jobs.SqlTaskAlert(alert_id=alert.id) # Id of the alert that I just created
            ),
            task_key="run_alert",
            timeout_seconds=0
        )
    ],
    schedule=jobs.CronSchedule(
        quartz_cron_expression="0 */15 * * * ?",  # Run every 15 minutes
        timezone_id="UTC"
    )
)

No sure if it's the best way to do manage alert through code, but I let that code here if it can help :)

[VOVELEE]

hey, I am also interested in this functionality. Is there any update on it?

[Ansssss]

We've been moving away from Terraform (toward DABs) for deploying Databricks resources, it would be great if DABs could handle alerts too.