sql-alerting-patterns

name	sql-alerting-patterns
description	Comprehensive guide for Databricks SQL Alerts V2 - config-driven alerting framework with SDK deployment, hierarchical job architecture (5 atomic + 1 composite), proactive EXPLAIN-based query validation, and partial success patterns. Use when setting up SQL alerts, creating alert configuration tables, deploying alerts via Databricks SDK (V2 dict-based or typed classes), or troubleshooting alert failures. Includes config-driven patterns, fully qualified table names (no parameters), severity-based routing, alert ID conventions, SQL query patterns (threshold, percentage change, anomaly detection), DataFrame-based config seeding, DAB job configuration, custom notification templates, Quartz cron schedules, and troubleshooting patterns.
clients	["ide_cli","genie_code"]
bundle_resource	jobs
deploy_verb	bundle_deploy
deploy_note	SQL Alerts V2 config-driven; alert-deployment jobs (5 atomic + 1 composite) deploy via `bundle deploy --target dev` (runDatabricksCli on Genie Code); alert config tables live in the per-user prefixed schema.
coverage	full
metadata	{"author":"prashanth subrahmanyam","version":"2.0","domain":"monitoring","role":"worker","pipeline_stage":7,"pipeline_stage_name":"observability","called_by":["observability-setup"],"standalone":true,"last_verified":"2026-08-30","volatility":"medium","upstream_sources":[]}

SQL Alerting: Config-Driven Framework for Databricks

Overview

This skill provides a comprehensive config-driven framework for setting up Databricks SQL Alerts using the V2 API, a Delta configuration table, and SDK deployment. It covers alert rule design, SQL query patterns, SDK integration (both V2 dict-based and typed classes), hierarchical job architecture, proactive query validation, partial success deployment, and troubleshooting workflows.

When to Use This Skill

• Setting up SQL alerts for Gold layer tables
• Creating alert configuration tables
• Deploying alerts via Databricks SDK (V2 or typed classes)
• Troubleshooting alert failures
• Implementing config-driven alerting (runtime updates without code changes)
• Creating severity-based notification routing
• Setting up hierarchical job architecture for alerting pipelines
• Validating alert queries before deployment

Core Principles

Principle 1: Config-Driven Alerting

Alert rules are stored in a Delta configuration table, not hardcoded. This enables:

• Runtime updates without code changes
• Centralized alert management
• Version history via Delta time travel
• Easy enable/disable without deployment

Principle 2: Hierarchical Job Architecture

⚠️ CRITICAL: Use atomic + composite jobs pattern for production alerting:

Layer 1 (Atomic - single notebook per job):
├── alerting_tables_setup_job.yml    → setup_alerting_tables.py
├── seed_all_alerts_job.yml          → seed_all_alerts.py
├── alert_query_validation_job.yml   → validate_alert_queries.py
├── notification_destinations_sync_job.yml → sync_notification_destinations.py
└── sql_alert_deployment_job.yml     → sync_sql_alerts.py

Layer 2 (Composite - orchestrates via run_job_task):
└── alerting_layer_setup_job.yml     → References all atomic jobs

Composite Job Pattern:

tasks:
  - task_key: setup_alerting_tables
    run_job_task:  # ✅ NOT notebook_task!
      job_id: ${resources.jobs.alerting_tables_setup_job.id}

  - task_key: deploy_sql_alerts
    depends_on:
      - task_key: validate_alert_queries
    run_job_task:
      job_id: ${resources.jobs.sql_alert_deployment_job.id}

Benefits: Test atomic jobs independently, debug failures at specific step, run subsets of pipeline.

Minimal Alternative: Two-Job Pattern

For simpler setups, a two-job separation still works:

1. Setup Job (alert_rules_setup_job): Creates/updates the alert_rules config table
2. Deploy Job (alert_deploy_job): Reads config table and creates/updates SQL Alerts via SDK

Why This Matters:

• Rules can be modified in Delta without redeploying alerts
• Dry-run capability for validation
• Clear separation between configuration and deployment

Principle 3: Fully Qualified Table Names

⚠️ CRITICAL: Databricks SQL Alerts (Public Preview) do NOT support parameters in queries.

-- ❌ WRONG: Parameterized query (NOT SUPPORTED)
SELECT * FROM ${catalog}.${schema}.fact_booking_daily

-- ✅ CORRECT: Fully qualified table names embedded in query
SELECT * FROM wanderbricks_dev.gold.fact_booking_daily

Pattern: Use f-strings at rule creation time to embed catalog/schema:

rev_001_query = f"""
SELECT ...
FROM {catalog}.{gold_schema}.fact_booking_daily
WHERE ...
"""

Principle 4: Severity-Based Notification Routing

Alerts are categorized by severity with different notification strategies:

Severity	Icon	Action Required	Notification Speed
CRITICAL	🔴	Immediate	Real-time (email + Slack)
WARNING	🟡	Investigate soon	Batched (email)
INFO	🟢	Informational	Daily digest

Principle 5: Proactive Query Validation

⚠️ CRITICAL: Run EXPLAIN on all alert queries before deployment to catch column/table errors early.

def validate_alert_query(spark, alert_id: str, query: str) -> tuple:
    """Validate query using EXPLAIN."""
    try:
        spark.sql(f"EXPLAIN {query}")
        return (alert_id, True, None)
    except Exception as e:
        if "UNRESOLVED_COLUMN" in str(e):
            return (alert_id, False, "Column not found")
        elif "TABLE_OR_VIEW_NOT_FOUND" in str(e):
            return (alert_id, False, "Table not found")
        return (alert_id, False, f"Error: {str(e)[:100]}")

Create a dedicated validation job (alert_query_validation_job) that tests all alert queries before deployment. This catches:

• Missing tables (renamed/dropped)
• Missing columns (schema evolution)
• SQL syntax errors
• Permission issues

Principle 6: Partial Success Tolerance

⚠️ CRITICAL: Allow deployment to succeed if ≥90% of alerts deploy successfully. Don't fail the entire deployment for a single alert issue.

# Allow job to succeed if ≥90% of alerts deploy successfully
if success_rate >= 90:
    print(f"⚠️ Partial success: {success_rate:.0f}% ({success_count}/{total})")
else:
    raise RuntimeError(f"Too many failures: {len(errors)} errors")

Principle 7: DataFrame-Based Config Seeding

⚠️ CRITICAL: Never use SQL INSERT for seeding alert configurations. Use DataFrame instead.

Problem: SQL INSERT with replace("'", "''") breaks LIKE patterns:

-- Original: WHERE sku_name LIKE '%ALL_PURPOSE%'
-- After INSERT escaping: WHERE sku_name LIKE %ALL_PURPOSE% (quotes lost!)

Solution: Use DataFrame with explicit schema:

# ✅ CORRECT: DataFrame handles escaping automatically
rows = [(alert_id, alert_name, alert_query, ...)]
df = spark.createDataFrame(rows, schema)
df.write.mode("append").saveAsTable(cfg_table)

Quick Reference

Alert ID Convention

Format: <DOMAIN>-<NUMBER>-<SEVERITY>

Components:

• DOMAIN: Business domain (3-4 chars): REV, ENG, PROP, HOST, CUST, COST, SECURITY, PERF
• NUMBER: Sequential within domain (3 digits, zero-padded)
• SEVERITY: CRIT, WARN, or INFO

Examples:

• REV-001-CRIT → Revenue domain, alert #1, critical severity
• ENG-003-WARN → Engagement domain, alert #3, warning severity
• PROP-004-INFO → Property domain, alert #4, informational
• COST-001-CRIT → Cost domain, alert #1, critical severity
• SECURITY-003-WARN → Security domain, alert #3, warning
• PERF-005-INFO → Performance domain, alert #5, informational

Alert Rules Configuration Table Schema

Required Columns for SDK Deployment:

Column	SDK Field	Required	Notes
alert_query	query_text	✅	Full SQL query
condition_column	AlertOperandColumn.name	✅	Column to check
condition_operator	AlertConditionOperator	✅	>, <, =, etc.
condition_threshold	AlertOperandValue.string_value	✅	Threshold value
schedule_cron	cron_schedule	✅	Quartz format
schedule_timezone	cron_timezone	✅	IANA timezone

See alert-patterns.md for complete schema definition.

SQL Query Patterns

1. Threshold Comparison - Alert when metric crosses threshold
2. Percentage Change from Baseline - Alert when metric deviates from historical average
3. Statistical Anomaly Detection (Z-Score) - Alert when metric is statistically unusual
4. Count-Based Alert - Alert when count is below threshold
5. Informational Summary - Always triggers for daily/weekly summaries

See alert-patterns.md for detailed SQL examples.

SDK Setup (Critical!)

⚠️ This is the #1 operational gotcha. The SDK must be upgraded at runtime for V2 API support:

# Databricks notebook source
# MAGIC %pip install --upgrade databricks-sdk>=0.40.0 --quiet

# COMMAND ----------

# MAGIC %restart_python

# COMMAND ----------

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

V2 API Payload Structure

alert_dict = {
    "display_name": "[CRITICAL] Alert Name",
    "query_text": "SELECT column FROM catalog.schema.table WHERE condition",
    "warehouse_id": "warehouse-id",
    "schedule": {
        "quartz_cron_schedule": "0 0 * * * ?",  # Every hour
        "timezone_id": "America/Los_Angeles",
        "pause_status": "UNPAUSED"
    },
    "evaluation": {
        "source": {"name": "column_name", "aggregation": "SUM"},
        "comparison_operator": "GREATER_THAN",
        "threshold": {"value": {"double_value": 1000}},
        "empty_result_state": "OK",
        "notification": {
            "notify_on_ok": False,
            "subscriptions": [{"user_email": "[e-mail]"}]
        }
    }
}

alert_v2 = AlertV2.from_dict(alert_dict)
ws.alerts_v2.create_alert(alert_v2)

See sdk-api-reference.md for complete V2 API details.

Comparison Operators

Operator	API Value
>	GREATER_THAN
>=	GREATER_THAN_OR_EQUAL
<	LESS_THAN
<=	LESS_THAN_OR_EQUAL
=	EQUAL
!=	NOT_EQUAL
IS NULL	IS_NULL

Aggregation Types

SUM, COUNT, COUNT_DISTINCT, AVG, MEDIAN, MIN, MAX, STDDEV, FIRST (null in API)

Critical Rules

Rule 1: Fully Qualified Table Names Only

❌ WRONG: Parameterized queries (NOT SUPPORTED)

SELECT * FROM ${catalog}.${schema}.fact_booking_daily

✅ CORRECT: Fully qualified names embedded at rule creation

alert_query = f"""
SELECT * FROM {catalog}.{gold_schema}.fact_booking_daily
WHERE check_in_date = DATE_ADD(CURRENT_DATE(), -1)
"""

Rule 2: Query Must Return Rows Only When Condition Met

❌ WRONG: Returns rows always

SELECT rate FROM ... WHERE rate IS NOT NULL

✅ CORRECT: Only returns rows when threshold crossed

SELECT rate FROM ... HAVING rate > 15

Rule 3: Always Include alert_message Column

✅ CORRECT: Human-readable notification content

SELECT
    cancellation_rate,
    'CRITICAL: Cancellation rate at ' || cancellation_rate || '%' as alert_message
FROM ...
HAVING cancellation_rate > 15

Rule 4: Use NULLIF for Division

✅ CORRECT: Prevent division by zero errors

ROUND(SUM(cancellation_count) / NULLIF(SUM(booking_count), 0) * 100, 1) as cancellation_rate

Rule 5: Use DataFrame for Config Seeding (Never SQL INSERT)

⚠️ CRITICAL: SQL INSERT with string escaping breaks LIKE patterns in alert queries. See Principle 7.

# ❌ WRONG: SQL INSERT loses quotes in LIKE patterns
spark.sql(f"INSERT INTO {table} VALUES ('{alert_query.replace(chr(39), chr(39)+chr(39))}')")

# ✅ CORRECT: DataFrame handles escaping automatically
rows = [(alert_id, alert_name, alert_query, ...)]
df = spark.createDataFrame(rows, schema)
df.write.mode("append").saveAsTable(cfg_table)

Rule 6: No CHECK Constraints or DEFAULT Values in DDL

⚠️ Unity Catalog limitation: CHECK constraints and DEFAULT values are not supported in DDL. Validate in code instead.

# ❌ WRONG: CHECK constraints in DDL (not supported)
# severity STRING NOT NULL CHECK (severity IN ('CRITICAL', 'WARNING', 'INFO'))

# ✅ CORRECT: Validate in code
assert rule["severity"] in ("CRITICAL", "WARNING", "INFO"), f"Invalid severity: {rule['severity']}"

Rule 7: Validate Queries with EXPLAIN Before Deployment

⚠️ CRITICAL: Run EXPLAIN on all alert queries before deployment. See Principle 5.