@MarkV
Appreciate you sharing the details. There are a few approaches depending on whether you want to use the REST API, the Python SDK, SQL, or the CLI. Here is a breakdown.
BACKGROUND
When you create a schedule for a query in the Databricks SQL editor, it creates a Lakeflow Job under the hood with a SQL task. So to programmatically access the schedules of your scheduled queries, you primarily work with the Jobs API.
APPROACH 1: JOBS REST API (Recommended)
The Jobs API lets you list all jobs and inspect their triggers (schedules). You can expand task details to see which ones are SQL query tasks.
List all jobs with their task details:
GET /api/2.1/jobs/list?expand_tasks=true
Then look at each job's schedule field and sql_task in the tasks array. Here is a Python example using the Databricks SDK:
from databricks.sdk import WorkspaceClient
w = WorkspaceClient()
for job in w.jobs.list(expand_tasks=True):
settings = job.settings
if settings and settings.schedule:
# Check if any task is a SQL task
tasks = settings.tasks or []
for task in tasks:
if task.sql_task is not None:
print(f"Job: {settings.name}")
print(f" Query ID: {task.sql_task.query.query_id}")
print(f" Schedule: {settings.schedule.quartz_cron_expression}")
print(f" Timezone: {settings.schedule.timezone_id}")
print(f" Paused: {settings.schedule.pause_status}")
print()
The schedule object contains:
- quartz_cron_expression: The cron schedule (Quartz format)
- timezone_id: The timezone for the schedule
- pause_status: Whether the schedule is PAUSED or UNPAUSED
APPROACH 2: SYSTEM TABLES (SQL Query)
If you prefer SQL and have system tables enabled, you can query the Lakeflow jobs system table directly. This is useful for reporting across your entire workspace.
SELECT
j.job_id,
j.name,
j.trigger_type,
j.trigger,
j.paused,
j.creator_user_name,
j.change_time
FROM system.lakeflow.jobs j
WHERE j.trigger_type = 'CRON'
AND j.delete_time IS NULL
ORDER BY j.change_time DESC
Note: The trigger, trigger_type, paused, and creator_user_name columns were added in late 2024 and may not be populated for jobs that have not been modified since then.
To correlate these with specific SQL queries, you would need to cross-reference with the Jobs API to inspect the task types, since the job_tasks system table does not currently expose the task type (SQL vs. notebook vs. Python, etc.).
APPROACH 3: DATABRICKS CLI
You can also use the Databricks CLI to list jobs with their schedules:
databricks jobs list --expand-tasks --output json
Then filter the JSON output for jobs containing sql_task entries. For example, piping through jq:
databricks jobs list --expand-tasks --output json | jq '.jobs[] | select(.settings.tasks[]?.sql_task != null) | {name: .settings.name, schedule: .settings.schedule, query_id: .settings.tasks[].sql_task.query.query_id}'
APPROACH 4: LEGACY QUERIES API (Deprecated)
The older queries API at /api/2.0/preview/sql/queries used to include schedule information directly in the query object. However, this API is deprecated and Databricks recommends migrating to the Jobs API approach described above. If you still need it:
GET /api/2.0/preview/sql/queries
Be aware this will eventually be removed, so plan to move to the Jobs API.
A NOTE ON PERMISSIONS
Query schedules created from the SQL editor may not be visible to all users. Schedule permissions operate independently from query permissions. If you are an admin, you should be able to see all jobs via the Jobs API. If you are a non-admin user, you will only see jobs you own or have permissions on.
REFERENCES
- Schedule a query: https://docs.databricks.com/en/sql/user/queries/schedule-query.html
- Jobs API (list jobs): https://docs.databricks.com/api/workspace/jobs/list
- Lakeflow Jobs system tables: https://docs.databricks.com/en/admin/system-tables/jobs.html
- Latest DBSQL API info: https://docs.databricks.com/en/sql/dbsql-api-latest.html
- Databricks SDK for Python: https://databricks-sdk-py.readthedocs.io/en/latest/workspace/jobs/jobs.html
- Job scheduling and triggers: https://docs.databricks.com/en/jobs/scheduled.html
Hope this helps. Let me know if you have follow-up questions.
* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.
* This reply used an agent system I built to research and draft this response based on the wide set of documentation I have available and previous memory. I personally review the draft for any obvious issues and for monitoring system reliability and update it when I detect any drift, but there is still a small chance that something is inaccurate, especially if you are experimenting with brand new features.
