sql-alerting-patterns

# 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:** ```yaml 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. ```sql -- ❌ 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: ```python 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. ```python 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. ```python # 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: ```sql -- Original: WHERE sku_name LIKE '%ALL_PURPOSE%' -- After INSERT escaping: WHERE sku_name LIKE %ALL_PURPOSE% (quotes lost!) ``` **Solution:** Use DataFrame with explicit schema: ```python # ✅ 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](references/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](references/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: ```python # 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 ```python 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": "user@company.com"}] } } } alert_v2 = AlertV2.from_dict(alert_dict) ws.alerts_v2.create_alert(alert_v2) ``` See [sdk-api-reference.md](references/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) ```sql SELECT * FROM ${catalog}.${schema}.fact_booking_daily ``` **✅ CORRECT:** Fully qualified names embedded at rule creation ```python 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 ```sql SELECT rate FROM ... WHERE rate IS NOT NULL ``` **✅ CORRECT:** Only returns rows when threshold crossed ```sql SELECT rate FROM ... HAVING rate > 15 ``` ### Rule 3: Always Include alert_message Column **✅ CORRECT:** Human-readable notification content ```sql 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 ```sql 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. ```python # ❌ 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. ```python # ❌ 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. ```python