알림과 스케줄링

-- 1. 실패 주문 수 모니터링
-- 조건: failed_orders > 100 이면 TRIGGERED
SELECT COUNT(*) AS failed_orders
FROM production.ecommerce.orders
WHERE status = 'FAILED'
  AND order_date >= CURRENT_TIMESTAMP() - INTERVAL 1 HOUR;

-- 2. 파이프라인 지연 감지
-- 조건: stale_tables > 0 이면 TRIGGERED
SELECT COUNT(*) AS stale_tables
FROM information_schema.tables
WHERE table_schema = 'gold'
  AND TIMESTAMPDIFF(HOUR, last_altered, CURRENT_TIMESTAMP()) > 4;

-- 3. 비용 급증 감지
-- 조건: pct_of_last_week > 150 이면 TRIGGERED (50% 이상 급증)
WITH today AS (
    SELECT SUM(usage_quantity) AS dbus
    FROM system.billing.usage
    WHERE usage_date = CURRENT_DATE()
),
last_week AS (
    SELECT SUM(usage_quantity) AS dbus
    FROM system.billing.usage
    WHERE usage_date = CURRENT_DATE() - 7
)
SELECT ROUND(today.dbus / NULLIF(last_week.dbus, 0) * 100, 1) AS pct_of_last_week
FROM today, last_week;

-- 4. 데이터 품질 이상 감지
-- 조건: null_ratio > 5 이면 TRIGGERED (NULL 비율 5% 초과)
SELECT
    ROUND(COUNT_IF(customer_id IS NULL) * 100.0 / COUNT(*), 2) AS null_ratio
FROM production.ecommerce.orders
WHERE order_date >= CURRENT_DATE() - INTERVAL 1 DAY;

{
    "alert_id": "alert-12345",
    "alert_name": "Failed Orders Alert",
    "alert_state": "TRIGGERED",
    "alert_condition": {
        "column": "failed_orders",
        "operator": ">",
        "threshold": 100
    },
    "query_result": {
        "failed_orders": 157
    },
    "alert_url": "https://workspace.databricks.com/sql/alerts/12345",
    "triggered_at": "2025-01-15T09:30:00Z"
}

예시: 지역별 매출 보고서
- 스케줄 1: region = 'APAC', 수신자: APAC 팀
- 스케줄 2: region = 'EMEA', 수신자: EMEA 팀
- 스케줄 3: region = 'NA', 수신자: NA 팀

-- SQL Editor에서 새 쿼리 생성
-- 이름: "Failed Orders Monitor"
SELECT
    COUNT(*) AS failed_count,
    MAX(order_date) AS latest_failure
FROM production.ecommerce.orders
WHERE status = 'FAILED'
  AND order_date >= CURRENT_TIMESTAMP() - INTERVAL 1 HOUR;