Add SQL conditions to job triggers

Use a SQL condition to check your data before a triggered job run starts. When the trigger fires, Databricks runs a saved SQL query on a SQL warehouse that you choose. The run starts only if the query result meets the condition. For example, run a job only when an upstream table has finished loading or a data quality check passes, without adding a separate checker task to the job.

Beta

This feature is in Beta. To use it, a workspace admin must turn on SQL conditions for triggers from the Previews page. See Manage Databricks previews.

Requirements

You must have CAN MANAGE on the job to configure its triggers.
You need a saved query that doesn't use query parameters. You must have access to the query and be able to run it on the SQL warehouse that you select.
The job's Run as identity must be able to open the saved query, have CAN USE on the SQL warehouse, and have read access to every table that the query reads. The condition query runs as this identity.

How SQL conditions work

The trigger decides when to check. The SQL condition decides whether to start a run. After the trigger fires, Databricks runs the query in read-only mode. A run starts only when both the trigger and the SQL condition are satisfied.

You can add one SQL condition to a scheduled, table update, file arrival, model update, or job completion trigger. Continuous triggers don't support SQL conditions.

Choose how Databricks evaluates the query result:

Condition type	API value	Starts a run when	Query requirements
Query returns rows	`QUERY_RETURNS_ROWS`	The result contains at least one row.	None. Zero rows means that the condition isn't met.
Result value changes	`RESULT_VALUE_CHANGES`	The result differs from the value at the previous check.	Must return exactly one row and one column. The value must be 1 MiB or smaller.

Condition type	API value	Starts a run when	Query requirements
Query returns rows	`QUERY_RETURNS_ROWS`	The result contains at least one row.	None. Zero rows means that the condition isn't met.
Result value changes	`RESULT_VALUE_CHANGES`	The result differs from the value at the previous check.	Must return exactly one row and one column. The value must be 1 MiB or smaller.

For Result value changes, Databricks runs the query in the background right after you save the trigger and stores the result as a baseline. This check doesn't start a run. Later checks start a run when the result differs from the stored value. Changing the query, SQL warehouse, or condition type on the trigger resets the baseline, and Databricks stores a new one in the same way. Pausing the trigger or changing other trigger settings can also reset it. Editing the text of the saved query doesn't reset it.

Each check works as follows:

A check, including SQL warehouse startup, times out after 5 minutes. If a check times out, no run starts, and the trigger checks again at its next evaluation.
A trigger runs at most one check at a time. If the trigger is due while a check is still running, Databricks skips that evaluation instead of queuing it.
If the query has more than one statement, only the result of the last statement is used.
SQL conditions apply only to runs that the trigger starts. Run now and other manual runs don't check the SQL condition.

tip

For Query returns rows, make sure that the query returns zero rows when the data isn't ready. A query like SELECT count(*) > 100 FROM main.sales.orders returns one row even when the count is 100 or less, so the condition is met. Filter with WHERE or HAVING instead:

SQL
SELECT 1
FROM main.sales.orders
WHERE order_date = current_date()
HAVING count(*) > 100

Add a SQL condition to a trigger

In the workspace sidebar, click Jobs & Pipelines, then open the job.
In Schedules & Triggers, click Add trigger, or edit an existing trigger.
Select the Trigger type and configure the trigger.
Expand Advanced, then click Add SQL condition.
Select the SQL query and the SQL warehouse.
Under Trigger when, select Query returns rows or Result value changes.
Click Save.

Test trigger checks that the job can use the saved query. It confirms that the query exists, isn't in the trash, and has no parameters, but it doesn't run the query. To see the query result, run the query in the SQL editor.

To remove a condition, edit the trigger, expand Advanced, and click Remove SQL condition.

Configure with the API, CLI, or bundles

Set sql_condition with sql_query_id, warehouse_id, and trigger_mode. For a scheduled trigger that uses a cron expression, set it on schedule. For other trigger types, set it on trigger, next to the trigger type settings. With the Multiple Triggers Beta, set it on each entry in triggers.

REST API
CLI
Bundles

Save this request as update-job.json and send it to POST /api/2.2/jobs/update in the Jobs API. Replace the IDs with your job, saved query, and SQL warehouse IDs.

JSON
{
  "job_id": 9001,
  "new_settings": {
    "trigger": {
      "pause_status": "UNPAUSED",
      "table_update": {
        "table_names": ["main.sales.orders"],
        "condition": "ANY_UPDATED"
      },
      "sql_condition": {
        "sql_query_id": "fe25e731-92f2-4838-9fb2-1ca364320a3d",
        "warehouse_id": "5d409943fcd32c6f",
        "trigger_mode": "QUERY_RETURNS_ROWS"
      }
    }
  }
}

Save the request body from the REST API tab as update-job.json, then run:

Bash
databricks api post /api/2.2/jobs/update --json @update-job.json

For Declarative Automation Bundles, add the following settings to an existing job resource, alongside its tasks:

YAML
resources:
  jobs:
    orders_report:
      trigger:
        pause_status: UNPAUSED
        periodic:
          interval: 1
          unit: DAYS
        sql_condition:
          sql_query_id: fe25e731-92f2-4838-9fb2-1ca364320a3d
          warehouse_id: 5d409943fcd32c6f
          trigger_mode: RESULT_VALUE_CHANGES

Bundles can't create saved queries. Create the query in the workspace first, then reference its ID.

A sql_condition that you send to jobs/update replaces the current one as a whole, so include all of its fields. If you leave out sql_condition, the current condition stays. To update multiple triggers, see Add multiple triggers to a job.

Monitor SQL condition checks

For scheduled triggers, the trigger status on the job page shows whether the latest SQL condition check was satisfied. Click Query execution details to open the statement in query history.
To read the latest check that ran without an error, call the Get job API with include_trigger_state=true and read trigger_state.sql_condition. The latest_condition_evaluation_satisfied field shows whether that check met the condition, and latest_condition_evaluation_sql_statement_id identifies its statement in query history. A failed check doesn't change these fields. With Multiple Triggers, read trigger_details[].state.sql_condition.
For each run that a SQL condition started, the Launched field in the runs list and on the run details page shows a SQL tag. Click it, then click Query execution details to open that run's check in query history.
If a check fails for a reason that you can fix, the error appears in the trigger status on the job page. Databricks also sends the error to the job's failure notifications, including email recipients and webhook destinations. You get one notification when a working condition starts to fail, not one for every failed check. The error stays until the next successful check. See Add notifications on a job.

Each check runs as a SQL statement on your SQL warehouse. To review checks over time, query the query history system table for the warehouse.

Troubleshooting

Issue	Action
The job runs every time the trigger fires	For Query returns rows, change the query so that it returns zero rows when the data isn't ready.
Result value changes never starts a run	The baseline is the query result at the time you saved the trigger. A run starts only after the query result changes from that value.
The check times out	Make the query faster, or use a serverless SQL warehouse, which starts faster.
Add SQL condition doesn't appear	Expand Advanced. If the button is still missing, ask a workspace admin to enable SQL conditions for triggers.

Issue	Action
The job runs every time the trigger fires	For Query returns rows, change the query so that it returns zero rows when the data isn't ready.
Result value changes never starts a run	The baseline is the query result at the time you saved the trigger. A run starts only after the query result changes from that value.
The check times out	Make the query faster, or use a serverless SQL warehouse, which starts faster.
Add SQL condition doesn't appear	Expand Advanced. If the button is still missing, ask a workspace admin to enable SQL conditions for triggers.

Limitations

Each trigger supports one SQL condition.
Continuous triggers don't support SQL conditions.
Condition queries run only on SQL warehouses, in read-only mode.
A check, including SQL warehouse startup, times out after 5 minutes.
A trigger keeps only its latest check, not a full history of every check. To see earlier checks, use the SQL tag on runs that the condition started, or query history.
Checks run on your SQL warehouse and are billed as SQL warehouse usage. A check can start a stopped warehouse.
Triggers with SQL conditions count toward the workspace limit of 12,000 active triggers.

Requirements​

How SQL conditions work​

Add a SQL condition to a trigger​

Configure with the API, CLI, or bundles​

Monitor SQL condition checks​

Troubleshooting​

Limitations​