비용 모니터링과 체크리스트

SELECT
  usage_date,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY usage_date
ORDER BY usage_date;

SELECT
  sku_name,
  SUM(usage_quantity) AS total_dbus,
  ROUND(SUM(usage_quantity) / (SELECT SUM(usage_quantity) FROM system.billing.usage
    WHERE usage_date >= current_date() - INTERVAL 30 DAYS) * 100, 1) AS pct
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY sku_name
ORDER BY total_dbus DESC;

SELECT
  usage_metadata.custom_tags['Team'] AS team,
  sku_name,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY total_dbus DESC;

SELECT
  usage_metadata.cluster_id,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE
  usage_date >= current_date() - INTERVAL 30 DAYS
  AND usage_metadata.cluster_id IS NOT NULL
GROUP BY 1
ORDER BY total_dbus DESC
LIMIT 10;

SELECT
  usage_date,
  HOUR(usage_start_time) AS hour_of_day,
  DAYOFWEEK(usage_date) AS day_of_week,
  SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE
  usage_date >= current_date() - INTERVAL 30 DAYS
  AND (DAYOFWEEK(usage_date) IN (1, 7)  -- 주말
       OR HOUR(usage_start_time) NOT BETWEEN 8 AND 20)  -- 야간
GROUP BY 1, 2, 3
HAVING SUM(usage_quantity) > 10
ORDER BY dbus DESC;

SELECT
  CASE
    WHEN sku_name LIKE '%SERVERLESS%' THEN 'Serverless'
    ELSE 'Classic'
  END AS compute_type,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE
  usage_date >= current_date() - INTERVAL 30 DAYS
  AND billing_origin_product IN ('JOBS', 'SQL', 'ALL_PURPOSE')
GROUP BY 1;

WITH daily AS (
  SELECT
    usage_date,
    SUM(usage_quantity) AS dbus
  FROM system.billing.usage
  WHERE usage_date >= current_date() - INTERVAL 60 DAYS
  GROUP BY usage_date
)
SELECT
  d1.usage_date,
  d1.dbus AS today_dbus,
  d2.dbus AS last_week_dbus,
  ROUND((d1.dbus - d2.dbus) / d2.dbus * 100, 1) AS change_pct
FROM daily d1
LEFT JOIN daily d2 ON d1.usage_date = d2.usage_date + INTERVAL 7 DAYS
WHERE d1.usage_date >= current_date() - INTERVAL 30 DAYS
ORDER BY d1.usage_date DESC;

SELECT
  date_trunc('month', usage_date) AS month,
  billing_origin_product,
  SUM(usage_quantity) AS monthly_dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - INTERVAL 180 DAYS
GROUP BY 1, 2
ORDER BY month, monthly_dbus DESC;

SELECT
  identity_metadata.run_as AS user_email,
  billing_origin_product,
  SUM(usage_quantity) AS total_dbus
FROM system.billing.usage
WHERE
  usage_date >= current_date() - INTERVAL 30 DAYS
  AND identity_metadata.run_as IS NOT NULL
GROUP BY 1, 2
ORDER BY total_dbus DESC
LIMIT 20;

WITH stats AS (
  SELECT
    AVG(daily_dbus) AS avg_dbus,
    STDDEV(daily_dbus) AS stddev_dbus
  FROM (
    SELECT usage_date, SUM(usage_quantity) AS daily_dbus
    FROM system.billing.usage
    WHERE usage_date BETWEEN current_date() - INTERVAL 60 DAYS
                         AND current_date() - INTERVAL 7 DAYS
    GROUP BY usage_date
  )
)
SELECT
  b.usage_date,
  SUM(b.usage_quantity) AS daily_dbus,
  s.avg_dbus,
  ROUND((SUM(b.usage_quantity) - s.avg_dbus) / s.avg_dbus * 100, 1) AS deviation_pct
FROM system.billing.usage b
CROSS JOIN stats s
WHERE b.usage_date >= current_date() - INTERVAL 7 DAYS
GROUP BY b.usage_date, s.avg_dbus, s.stddev_dbus
HAVING SUM(b.usage_quantity) > s.avg_dbus + 2 * s.stddev_dbus
ORDER BY b.usage_date;

# 태그 표준 체계 예시
required_tags = {
  "Team": "data-engineering",       # 팀명
  "Project": "customer-360",        # 프로젝트명
  "Environment": "production",      # 환경 (dev/staging/prod)
  "CostCenter": "CC-1234",          # 비용 센터 코드
  "Owner": "user@company.com"       # 책임자
}

// Compute Policy로 태그 강제 적용
{
  "custom_tags.Team": {
    "type": "fixed",
    "value": "data-engineering"
  },
  "custom_tags.CostCenter": {
    "type": "regex",
    "pattern": "CC-\\d{4}"
  },
  "custom_tags.Environment": {
    "type": "allowlist",
    "values": ["dev", "staging", "prod"]
  }
}

-- 일일 비용이 임계값을 초과하면 알림 트리거
-- Databricks SQL Alert로 설정
SELECT
  usage_date,
  SUM(usage_quantity) AS daily_dbus,
  1000 AS threshold_dbus  -- 임계값: 1,000 DBU/일
FROM system.billing.usage
WHERE usage_date = current_date() - INTERVAL 1 DAY
GROUP BY usage_date
HAVING SUM(usage_quantity) > 1000;