Databricks Community

Pavel_Sinkevich · a week ago

Hi everyone,

We manage SQL alerts with Declarative Automation Bundles and like having them in version control. One thing we'd like to improve: the alert's SQL query has to be written inline in the YAML resource definition (query_text).

The problem

Our alert queries often run to 40+ lines, with CTEs, several joins and comments. Embedding them in YAML has some drawbacks:

No SQL syntax highlighting, formatting or linting in the IDE or workspace editor
You can't easily run or debug the query on its own (in the SQL editor, a notebook, etc.) without copying it out of the YAML
Code review is harder because SQL changes are mixed in with configuration changes
The YAML files become long, and indentation inside the | block can be easy to break

Current options and their limitations

file_path on the alert resource accepts a .dbalert.json file, not a plain .sql file. The query is stored as a single escaped JSON string, which is harder to read than inline YAML. As far as we can tell, bundle variables such as ${var.environment} are also not substituted inside it.
Passing the query through --var or BUNDLE_VAR_* works, but every deploy then needs a wrapper script, and it breaks plain databricks bundle deploy from the workspace UI.
Putting the query in a variable default in a separate YAML file helps a bit, but the SQL is still embedded in YAML.

Proposal

Allow alerts to reference a .sql file directly, similar to how jobs reference SQL files in sql_task.file. For example:

resources:
  alerts:
    my_alert:
      display_name: "my alert"
      query_file: ../src/alerts/my_alert.sql
      warehouse_id: ${var.warehouse_id}
      evaluation:
        ...

Ideally:

The path is resolved relative to the YAML file, like other bundle paths
Bundle variable interpolation (${var.*}, ${bundle.target}) is applied to the file contents, so the same query can target different catalogs per environment
query_text and query_file are mutually exclusive, and bundle validate gives a clear error if both are set

Benefits

SQL stays as real SQL, with full editor support, and can be run and tested on its own
SQL logic is kept separate from alert configuration (schedule, evaluation, notifications)
It works the same way as other bundle resources that already reference external files (notebooks, SQL tasks, dashboards)

Thanks!

ronald59pepper · a week ago

This is a well-thought-out proposal that addresses a common pain point when managing complex SQL queries in Declarative Automation Bundles, aligning alerts with how other resources already handle external files. Adding native support for a query_file parameter would significantly improve IDE support, code reviews, and overall maintainability for teams managing SQL alerts at scale.

Empower Retirement

Jen_Evangelista · a week ago

Before adding a wrapper script, I'd look at defining the alerts in Python. Bundles accept Python resource definitions next to YAML, and the databricks-bundles package has an Alert resource with query_text. A small loader can read the .sql file and pass its contents in, while the schedule and evaluation config stay where they are.

Two details matter. Resolve the .sql path from the loader module, not from the shell's working dir, and make sure the file ships with the bundle. For env-specific values, I'd read the bundle variables in the loader and substitute them myself, then check the deployed query, rather than assume ${var.*} inside the file gets resolved.

Your query_file request still makes sense. Python support needs CLI 0.275+ and a Python env (uv by default), which a YAML-only project may not want. I haven't tried this in your setup, so I'd check the resolved query for two targets and confirm the deploy still works from the UI you use today.

Docs: https://docs.databricks.com/aws/en/dev-tools/bundles/python

Pavel_Sinkevich · a week ago

Thanks for your suggestion! We considered this option, but decided against it for now. We are using hatch as Python project manager, and hatch is storing multiple virtual environments per project in an external location outside of the project folder. This setup works well for us, and we would prefer not to change it.

data_pulse · a week ago

@Pavel_Sinkevich

I tested this feature too using Databricks CLI 0.290.0. The results confirm that referencing standalone .sql files through a native query_file property is not supported in the tested CLI version.

Key findings

1. query_file is not supported

Using query_file: ./sql/check_amount.sql produced:

Warning: unknown field: query_file
Warning: required field "query_text" is not set

Interestingly, bundle validation returned exit code 0 despite these warnings, while deployment subsequently failed because query_text was missing.

2. file_path is not an alternative

The existing file_path property expects a serialized alert asset (.dbalert.json), not a standalone .sql file. Variable interpolation inside .dbalert.json is also explicitly rejected.

3. Variable interpolation is restricted for alert assets

Using ${var.*} placeholders inside .dbalert.json produced an explicit error indicating that variable interpolation is unsupported in alert asset files.

4. Verified workaround using bundle variables

I successfully deployed external SQL using: query_text: ${var.alert_query}

With PowerShell loading the SQL file into an environment variable:

$env:BUNDLE_VAR_alert_query = Get-Content -LiteralPath sql/check_amount.sql -Raw

This preserved multiline SQL and resolved bundle variable references.

Related GitHub issue

An enhancement request has also been opened in the official Databricks CLI repository for this same functionality: https://github.com/databricks/cli/issues/5620

The workaround works, but native query_file support would simplify SQL maintenance, version control and CI/CD. Hopefully this feature can be prioritized and included in upcoming releases.

VladTamas · a week ago

Short answer: You can get "SQL in a .sql file" today, without waiting for a query_file field, by defining the alerts with Python for Declarative Automation Bundles [1]. A small load_resources() function reads each .sql file and passes its contents as query_text. Bundle variables like ${var.catalog} inside the file are still substituted, because the string goes through the same interpolation as YAML values. I tested this on Databricks CLI v1.22.1 with databricks-bundles 1.22.1: one file in src/alerts/, deployed to dev, read samples.tpch.orders, and bundle validate -t prod rendered it as workspace.tpch.orders. Your proposal is still worth keeping as a feature request, because this workaround adds a Python step to the bundle.

1. What the alert resource accepts today

query_text is a required string, and file_path is "the local file path of the alert asset" (the .dbalert.json format you mention) [2]. There's no field that takes a plain .sql file.
So the SQL has to arrive as a string. The question is only who builds that string, and Python resources can do it at deploy time [1].

2. Load every .sql file as an alert

Add the Python section to databricks.yml and keep your variables in YAML:

python:
  venv_path: .venv
  resources:
    - 'resources:load_resources'

variables:
  catalog:
    default: samples
  warehouse_id:
    default: <warehouse-id>

targets:
  dev:
    mode: development
    default: true
  prod:
    variables:
      catalog: workspace

Put the queries in src/alerts/*.sql as real SQL, with variables where the catalog changes:

-- Open orders placed before the cutoff date
WITH open_orders AS (
  SELECT o_orderkey, o_orderdate
  FROM ${var.catalog}.tpch.orders
  WHERE o_orderstatus = 'O'
)
SELECT count(*) AS late_orders
FROM open_orders
WHERE o_orderdate < DATE'1998-07-01'

In resources/__init__.py, create one alert per file (install databricks-bundles in .venv first [1][3]):

from pathlib import Path
from databricks.bundles.core import Bundle, Resources
from databricks.bundles.alerts import Alert

SQL_DIR = Path(__file__).parent.parent / "src" / "alerts"

def load_resources(bundle: Bundle) -> Resources:
    resources = Resources()
    for sql_file in sorted(SQL_DIR.glob("*.sql")):
        resources.add_resource(sql_file.stem, Alert.from_dict({
            "display_name": sql_file.stem,
            "query_text": sql_file.read_text(),
            "warehouse_id": "${var.warehouse_id}",
            "evaluation": {
                "comparison_operator": "GREATER_THAN",
                "source": {"name": "late_orders"},
                "threshold": {"value": {"double_value": 0}},
            },
            "schedule": {
                "quartz_cron_schedule": "0 0 7 * * ?",
                "timezone_id": "Europe/Bucharest",
                "pause_status": "PAUSED",
            },
        }))
    return resources

Check what each target will deploy before you deploy it:

databricks bundle validate -t prod -o json   # query_text shows workspace.tpch.orders
databricks bundle deploy -t dev

In my test the deployed dev alert's query_text (read back with GET /api/2.0/alerts/<id>) had the comment, the CTE and samples.tpch.orders, so the formatting survives.

3. How this maps to your wish list

Real SQL with editor support: yes. The file is plain SQL. To run it on its own, replace ${var.catalog} with a catalog name or use a SQL parameter.
Path relative to the bundle: yes, through Path(__file__), so it doesn't depend on the working directory.
${var.*} and ${bundle.target} in the file: yes. Substitution happens after Python returns the resource.
databricks bundle deploy from the workspace UI: bundles in the workspace support resources defined in Python [4]. If the workspace has serverless egress control, use a private PyPI mirror for the packages [4].
Evaluation per alert: in the loop above every alert uses the same rule. If they differ, keep a small dict of evaluations keyed by file name, or put the evaluation in YAML and only load query_text in Python.

What I'd do

Start with one alert. Run bundle validate -o json for each target and diff the query_text, then move the rest over. Keep your forum request open: a native query_file (resolved relative to the YAML, with variable interpolation, and mutually exclusive with query_text) would remove the Python step, and it would match how sql_task.file already works.