SQL Alerts, Scheduled Queries & AI/BI Dashboards

The one-liner

Alert simulator — drag today's order count

▸ condition: alert when orders_today = 0 (pipeline failure)

How it works — deep dive

1 · Scheduled queries — Quartz cron on a warehouse

Mechanism: attach a schedule to a saved query; at each fire time it runs on a SQL Warehouse as the owner. Databricks uses 6-field Quartz cron (sec min hour day month dow), not 5-field Unix cron. Trade-off: cadence = freshness and cost.

# Quartz cron is 6 fields. "every day at 06:00":
schedule:
  quartz_cron_schedule: "0 0 6 * * ?"   # sec min hour day month dow
  timezone_id: America/New_York

2 · SQL Alerts — condition on a query result → notify

Mechanism: an alert points at a query, defines an evaluation (a comparison on a result column vs a threshold), and on a schedule checks it — if met, sends to notification destinations. Trade-off: evaluates only when scheduled (cadence = detection lag); condition-only, not arbitrary logic.

-- The alert's backing query returns ONE row/column to compare.
-- Condition (set in the alert): WHEN orders_today = 0 → notify on-call.
SELECT count(*) AS orders_today
FROM prod.sales.orders
WHERE order_date = current_date();

3 · Alerts & schedules as code — Asset Bundle

Mechanism: the alert bundle resource (SQL alert v2) declares the query, evaluation, schedule, and warehouse — versioned and promoted by CI/CD. Trade-off: the bundle is the source of truth; UI edits get reverted on the next deploy.

# databricks.yml — verified keys (alert v2 resource)
resources:
  alerts:
    orders_zero_alert:
      display_name: orders_zero_alert
      query_text: "SELECT count(*) AS orders_today FROM prod.sales.orders WHERE order_date = current_date()"
      evaluation:
        comparison_operator: EQUAL      # EQUAL / GREATER_THAN / LESS_THAN …
        source: { name: orders_today }  # result column to compare
        threshold: { value: { double_value: 0 } }
      schedule:
        quartz_cron_schedule: "0 0 6 * * ?"
        timezone_id: America/New_York
      warehouse_id: ${var.warehouse_id}

4 · AI/BI Dashboards — datasets, visuals, filters, subscriptions

Mechanism: datasets (queries) feed the canvas; visualizations + parameters/filters cross-filter widgets; author by hand or with Genie (NL), then publish with scheduled refresh + email/Slack subscriptions. Trade-off: runs on a warehouse (cold-start latency); refresh runs as the publisher — mind permissions/cost. Successor to retired legacy/Redash dashboards.

-- A dashboard DATASET is a query, usually parameterized so filters work:
SELECT region, sum(amount) AS revenue
FROM prod.sales.orders
WHERE order_date BETWEEN :start_date AND :end_date
GROUP BY region ORDER BY revenue DESC;

5 · Dashboards as code — bundle the .lvdash.json

Mechanism: an AI/BI Dashboard serializes to *.lvdash.json; the dashboard bundle resource references that file + a warehouse. databricks bundle generate dashboard exports an existing one. Trade-off: the JSON is generated — edit in the UI then re-export, don't hand-edit.

# databricks.yml — verified keys (dashboard resource)
resources:
  dashboards:
    revenue_overview:
      display_name: "Revenue Overview"
      file_path: ../src/revenue_overview.lvdash.json
      warehouse_id: ${var.warehouse_id}

# export an existing dashboard, then deploy:
# databricks bundle generate dashboard --existing-path "/Workspace/.../Revenue Overview"
# databricks bundle deploy -t prod

Three tools — click each

Why it matters

• DE doesn't stop at gold tables — stakeholders need monitoring & visibility. Alerts catch problems automatically; dashboards make data usable.
• "Alert when revenue drops / a pipeline's row count is 0?" → SQL Alert on a scheduled query.
• Shipping dashboards/alerts as code (versioned, promoted dev→prod) is what makes a workspace enterprise-grade.

Uses, edge cases & limitations

• Uses: data-quality/SLA alerting, daily KPI emails, executive dashboards, self-serve NL exploration via Genie, version-controlled BI via bundles.
• Edge cases: alerts evaluate only when scheduled (detection lag = cadence); dashboards/refresh run as the owner (permission/cost; cold-start latency); schedules are 6-field Quartz, not Unix cron.
• Limitation: alerts trigger on a query-result condition, not arbitrary logic; legacy/Redash dashboards are retired — use AI/BI Dashboards.

Common gotchas

• ❌ Expecting an alert to fire instantly — it fires on its schedule (mind the lag).
• ❌ Writing a 5-field Unix cron (0 6 * * *) — Databricks wants 6-field Quartz (0 0 6 * * ?).
• ❌ Building on legacy dashboards — retired; use AI/BI Dashboards.
• ❌ Forgetting scheduled refresh/queries run as the owner (permission/cost).
• ❌ No notification destination → an alert that never tells anyone.
• ❌ Hand-editing the generated .lvdash.json instead of editing in the UI and re-running bundle generate dashboard.