Snowflake Alerting and Notifications

Snowflake provides built-in alerting capabilities through ALERT objects, enabling proactive monitoring and automated responses to data and system conditions.

Architecture Overview

<svg width="800" height="450" viewBox="0 0 800 450" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="alertGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#E74C3C;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#EC7063;stop-opacity:1" />
    </linearGradient>
    <linearGradient id="notiGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#F39C12;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#F7DC6F;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Snowflake Alerting & Notifications</text>
  <rect x="30" y="60" width="200" height="150" rx="10" fill="#6C5CE7" opacity="0.9"/>
  <text x="130" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Data Sources</text>
  <text x="130" y="110" text-anchor="middle" font-size="10" fill="white">Query Results</text>
  <text x="130" y="125" text-anchor="middle" font-size="10" fill="white">Table Data</text>
  <text x="130" y="140" text-anchor="middle" font-size="10" fill="white">Metrics</text>
  <text x="130" y="155" text-anchor="middle" font-size="10" fill="white">Logs</text>
  <text x="130" y="175" text-anchor="middle" font-size="10" fill="white">System Status</text>
  <path d="M230 135 L270 135" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowAlert)"/>
  <rect x="270" y="60" width="200" height="150" rx="10" fill="url(#alertGrad)" opacity="0.9"/>
  <text x="370" y="85" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Alert Engine</text>
  <text x="370" y="110" text-anchor="middle" font-size="11" fill="white">Condition Evaluation</text>
  <text x="370" y="130" text-anchor="middle" font-size="11" fill="white">Threshold Checks</text>
  <text x="370" y="150" text-anchor="middle" font-size="11" fill="white">Pattern Detection</text>
  <text x="370" y="170" text-anchor="middle" font-size="11" fill="white">Rate Limiting</text>
  <text x="370" y="190" text-anchor="middle" font-size="11" fill="white">State Management</text>
  <path d="M470 135 L510 135" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowAlert)"/>
  <rect x="510" y="60" width="260" height="150" rx="10" fill="url(#notiGrad)" opacity="0.9"/>
  <text x="640" y="85" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Notification Channels</text>

  <rect x="525" y="100" width="110" height="40" rx="5" fill="white" opacity="0.9"/>
  <text x="580" y="125" text-anchor="middle" font-size="10" fill="#333">Email</text>
  <rect x="645" y="100" width="110" height="40" rx="5" fill="white" opacity="0.9"/>
  <text x="700" y="125" text-anchor="middle" font-size="10" fill="#333">Slack</text>
  <rect x="525" y="150" width="110" height="40" rx="5" fill="white" opacity="0.9"/>
  <text x="580" y="175" text-anchor="middle" font-size="10" fill="#333">Webhook</text>
  <rect x="645" y="150" width="110" height="40" rx="5" fill="white" opacity="0.9"/>
  <text x="700" y="175" text-anchor="middle" font-size="10" fill="#333">Teams</text>
  <rect x="30" y="230" width="740" height="200" rx="10" fill="#27AE60" opacity="0.85"/>
  <text x="400" y="255" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Alert Categories</text>

  <rect x="50" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="135" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Data Quality</text>
  <text x="135" y="315" text-anchor="middle" font-size="9" fill="#666">Null rate > threshold</text>
  <text x="135" y="330" text-anchor="middle" font-size="9" fill="#666">Schema drift</text>
  <text x="135" y="345" text-anchor="middle" font-size="9" fill="#666">Freshness lag</text>
  <text x="135" y="360" text-anchor="middle" font-size="9" fill="#666">Volume anomalies</text>
  <text x="135" y="375" text-anchor="middle" font-size="9" fill="#666">Business rule violations</text>

  <rect x="240" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="325" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Performance</text>
  <text x="325" y="315" text-anchor="middle" font-size="9" fill="#666">Query duration > SLA</text>
  <text x="325" y="330" text-anchor="middle" font-size="9" fill="#666">Queue wait time</text>
  <text x="325" y="345" text-anchor="middle" font-size="9" fill="#666">Spillage to disk</text>
  <text x="325" y="360" text-anchor="middle" font-size="9" fill="#666">Credit consumption</text>
  <text x="325" y="375" text-anchor="middle" font-size="9" fill="#666">Warehouse utilization</text>

  <rect x="430" y="275" width="170" height="130" rx="8" fill="white"/>
  <text x="515" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Security</text>
  <text x="515" y="315" text-anchor="middle" font-size="9" fill="#666">Failed logins</text>
  <text x="515" y="330" text-anchor="middle" font-size="9" fill="#666">Privilege escalation</text>
  <text x="515" y="345" text-anchor="middle" font-size="9" fill="#666">Unusual access patterns</text>
  <text x="515" y="360" text-anchor="middle" font-size="9" fill="#666">Data export attempts</text>
  <text x="515" y="375" text-anchor="middle" font-size="9" fill="#666">Policy violations</text>

  <rect x="620" y="275" width="140" height="130" rx="8" fill="white"/>
  <text x="690" y="295" text-anchor="middle" font-size="11" fill="#333" font-weight="bold">Operational</text>
  <text x="690" y="315" text-anchor="middle" font-size="9" fill="#666">Task failures</text>
  <text x="690" y="330" text-anchor="middle" font-size="9" fill="#666">Pipeline delays</text>
  <text x="690" y="345" text-anchor="middle" font-size="9" fill="#666">Stage issues</text>
  <text x="690" y="360" text-anchor="middle" font-size="9" fill="#666">Integration errors</text>
  <text x="690" y="375" text-anchor="middle" font-size="9" fill="#666">Resource limits</text>

  <defs>
    <marker id="arrowAlert" markerWidth="10" markerHeight="10" refX="9" refY="3" orient="auto" markerUnits="strokeWidth">
      <path d="M0,0 L0,6 L9,3 z" fill="#333"/>
    </marker>
  </defs>
</svg>

Alert Creation

Basic Alert

-- Create alert for data quality
CREATE OR REPLACE ALERT data_quality_alert
  WAREHOUSE = compute_wh
  SCHEDULE = '5 MINUTE'
  IF (SELECT COUNT(*) FROM my_table WHERE column_name IS NULL) > 100
THEN
  CALL SYSTEM$SEND_EMAIL(
    'my_integration',
    'admin@company.com',
    'Data Quality Alert',
    'More than 100 null values detected in my_table.column_name'
  );
END IF;

Complex Alert with Multiple Conditions

CREATE OR REPLACE ALERT comprehensive_alert
  WAREHOUSE = compute_wh
  SCHEDULE = '10 MINUTE'
AS
BEGIN
  -- Check multiple conditions
  LET null_count := (SELECT COUNT(*) FROM customer_data WHERE email IS NULL);
  LET stale_count := (SELECT COUNT(*) FROM metrics WHERE updated_at < DATEADD(day, -1, CURRENT_TIMESTAMP()));
  LET error_rate := (SELECT COUNT(*) FROM logs WHERE level = 'ERROR') / COUNT(*);
  
  -- Alert if any condition met
  IF (:null_count > 50 OR :stale_count > 1000 OR :error_rate > 0.05) THEN
    CALL SYSTEM$SEND_SLACK_MESSAGE(
      'my_slack_integration',
      '#data-alerts',
      '⚠️ Data Quality Alert\n' ||
      'Null emails: ' || :null_count || '\n' ||
      'Stale records: ' || :stale_count || '\n' ||
      'Error rate: ' || ROUND(:error_rate * 100, 2) || '%'
    );
  END IF;
END;

Notification Integrations

Email Integration

CREATE OR REPLACE NOTIFICATION INTEGRATION email_integration
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AWS_SNS
  DIRECTION = OUTBOUND
  AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-east-1:123456789:my-topic'
  AWS_SNS_ROLE_ARN = 'arn:aws:iam::123456789:role/snowflake-sns-role';

Slack Integration

CREATE OR REPLACE NOTIFICATION INTEGRATION slack_integration
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AWS_SNS
  DIRECTION = OUTBOUND
  AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-east-1:123456789:slack-topic';

Alert Monitoring

-- View alert history
SELECT
  alert_name,
  alert_condition,
  last_triggered,
  last_action,
  state
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
ORDER BY last_triggered DESC;

-- Alert performance
SELECT
  alert_name,
  COUNT(*) as trigger_count,
  AVG(execution_time_ms) as avg_execution_ms,
  MAX(last_triggered) as most_recent
FROM TABLE(INFORMATION_SCHEMA.ALERT_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY 1
ORDER BY trigger_count DESC;

Alert Patterns