Snowflake Data Warehouse Automation

Snowflake enables comprehensive automation of data warehouse operations through tasks, streams, alerts, and monitoring capabilities that reduce manual intervention.

Architecture Overview

<svg width="800" height="450" viewBox="0 0 800 450" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="autoGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#FF6B6B;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#FF8E8E;stop-opacity:1" />
    </linearGradient>
    <linearGradient id="monGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#4ECDC4;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#7EDDD6;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Snowflake Warehouse Automation Architecture</text>
  <rect x="30" y="60" width="120" height="120" rx="10" fill="#6C5CE7" opacity="0.9"/>
  <text x="90" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Sources</text>
  <text x="90" y="110" text-anchor="middle" font-size="10" fill="white">APIs</text>
  <text x="90" y="125" text-anchor="middle" font-size="10" fill="white">Files</text>
  <text x="90" y="140" text-anchor="middle" font-size="10" fill="white">DBs</text>
  <text x="90" y="155" text-anchor="middle" font-size="10" fill="white">Streams</text>
  <path d="M150 120 L190 120" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowAuto)"/>
  <rect x="190" y="60" width="140" height="120" rx="10" fill="#F39C12" opacity="0.9"/>
  <text x="260" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Ingestion</text>
  <text x="260" y="110" text-anchor="middle" font-size="10" fill="white">Snowpipe</text>
  <text x="260" y="125" text-anchor="middle" font-size="10" fill="white">COPY INTO</text>
  <text x="260" y="140" text-anchor="middle" font-size="10" fill="white">Streams</text>
  <text x="260" y="155" text-anchor="middle" font-size="10" fill="white">External Fn</text>
  <path d="M330 120 L370 120" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowAuto)"/>
  <rect x="370" y="60" width="140" height="120" rx="10" fill="url(#autoGrad)" opacity="0.9"/>
  <text x="440" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Processing</text>
  <text x="440" y="110" text-anchor="middle" font-size="10" fill="white">Tasks</text>
  <text x="440" y="125" text-anchor="middle" font-size="10" fill="white">Stored Procs</text>
  <text x="440" y="140" text-anchor="middle" font-size="10" fill="white">UDFs</text>
  <text x="440" y="155" text-anchor="middle" font-size="10" fill="white">Pipelines</text>
  <path d="M510 120 L550 120" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowAuto)"/>
  <rect x="550" y="60" width="120" height="120" rx="10" fill="#29B5E8" opacity="0.9"/>
  <text x="610" y="85" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Serving</text>
  <text x="610" y="110" text-anchor="middle" font-size="10" fill="white">Views</text>
  <text x="610" y="125" text-anchor="middle" font-size="10" fill="white">Shares</text>
  <text x="610" y="140" text-anchor="middle" font-size="10" fill="white">APIs</text>
  <text x="610" y="155" text-anchor="middle" font-size="10" fill="white">Alerts</text>
  <rect x="30" y="210" width="740" height="100" rx="10" fill="url(#monGrad)" opacity="0.9"/>
  <text x="400" y="235" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Monitoring & Governance Layer</text>

  <rect x="50" y="250" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="120" y="277" text-anchor="middle" font-size="10" fill="#333">Query History</text>

  <rect x="210" y="250" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="280" y="277" text-anchor="middle" font-size="10" fill="#333">Access History</text>

  <rect x="370" y="250" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="440" y="277" text-anchor="middle" font-size="10" fill="#333">Resource Monitors</text>

  <rect x="530" y="250" width="140" height="45" rx="8" fill="white" opacity="0.9"/>
  <text x="600" y="277" text-anchor="middle" font-size="10" fill="#333">Alerts & Notifications</text>
  <rect x="30" y="330" width="230" height="100" rx="10" fill="#9B59B6" opacity="0.85"/>
  <text x="145" y="355" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Task Automation</text>
  <text x="145" y="375" text-anchor="middle" font-size="10" fill="white">Cron scheduling</text>
  <text x="145" y="390" text-anchor="middle" font-size="10" fill="white">Event-driven triggers</text>
  <text x="145" y="405" text-anchor="middle" font-size="10" fill="white">Task dependencies</text>
  <text x="145" y="420" text-anchor="middle" font-size="10" fill="white">Error recovery</text>

  <rect x="290" y="330" width="230" height="100" rx="10" fill="#E74C3C" opacity="0.85"/>
  <text x="405" y="355" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Data Quality</text>
  <text x="405" y="375" text-anchor="middle" font-size="10" fill="white">Schema validation</text>
  <text x="405" y="390" text-anchor="middle" font-size="10" fill="white">Null checks</text>
  <text x="405" y="405" text-anchor="middle" font-size="10" fill="white">Freshness monitoring</text>
  <text x="405" y="420" text-anchor="middle" font-size="10" fill="white">Business rules</text>

  <rect x="550" y="330" width="220" height="100" rx="10" fill="#27AE60" opacity="0.85"/>
  <text x="660" y="355" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Self-Service</text>
  <text x="660" y="375" text-anchor="middle" font-size="10" fill="white">Data catalog</text>
  <text x="660" y="390" text-anchor="middle" font-size="10" fill="white">Usage analytics</text>
  <text x="660" y="405" text-anchor="middle" font-size="10" fill="white">Cost allocation</text>
  <text x="660" y="420" text-anchor="middle" font-size="10" fill="white">Self-provisioning</text>

  <defs>
    <marker id="arrowAuto" 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>

Task Automation

Scheduled Tasks

-- Daily ETL task
CREATE OR REPLACE TASK daily_etl_task
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON 0 2 * * * America/New_York'
  ERROR_INTEGRATION = slack_notification
AS
BEGIN
  -- Step 1: Load data
  COPY INTO raw_sales
  FROM @staging_area/sales/
  FILE_FORMAT = csv_format
  ON_ERROR = 'CONTINUE';

  -- Step 2: Transform
  INSERT INTO curated_sales
  SELECT
    id,
    TRIM(UPPER(customer_name)) as customer_name,
    CAST(amount AS DECIMAL(10,2)) as amount,
    order_date,
    CURRENT_TIMESTAMP() as processed_at
  FROM raw_sales
  WHERE order_date >= DATEADD(day, -1, CURRENT_DATE());

  -- Step 3: Validate
  IF (SELECT COUNT(*) FROM curated_sales WHERE amount < 0) > 0 THEN
    RAISE ERROR(10001, 'Negative amounts detected');
  END IF;
END;

Event-Driven Tasks

-- Task triggered by stream
CREATE OR REPLACE TASK stream_processor
  WAREHOUSE = compute_wh
  WHEN SYSTEM$STREAM_HAS_DATA('my_stream')
AS
  MERGE INTO target_table t
  USING my_stream s
  ON t.id = s.id
  WHEN MATCHED AND s.METADATA$ACTION = 'DELETE' THEN DELETE
  WHEN NOT MATCHED THEN INSERT *;

-- Task dependency chain
CREATE OR REPLACE TASK task_1 WAREHOUSE = wh AS INSERT INTO t1 SELECT 1;
CREATE OR REPLACE TASK task_2 WAREHOUSE = wh AFTER task_1 AS INSERT INTO t2 SELECT 2;
CREATE OR REPLACE TASK task_3 WAREHOUSE = wh AFTER task_2 AS INSERT INTO t3 SELECT 3;

Monitoring Queries

-- Task execution history
SELECT
  name,
  state,
  completed_time,
  scheduled_time,
  error_code,
  error_message
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY())
WHERE scheduled_time >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY scheduled_time DESC;

-- Query performance monitoring
SELECT
  query_text,
  execution_status,
  total_elapsed_time,
  rows_produced,
  bytes_scanned,
  warehouse_name
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
  START_TIME => DATEADD(hour, -24, CURRENT_TIMESTAMP())
))
WHERE total_elapsed_time > 10000
ORDER BY total_elapsed_time DESC;

Resource Monitoring

-- Create resource monitor
CREATE OR REPLACE RESOURCE MONITOR warehouse_monitor
  WITH CREDIT_QUOTA = 500
  FREQUENCY = MONTHLY
  START_TIMESTAMP = IMMEDIATELY
  NOTIFY_USERS = ('admin@company.com')
  TRIGGERS ON 75% DO NOTIFY
  TRIGGERS ON 90% DO SUSPEND
  TRIGGERS ON 100% DO SUSPEND_IMMEDIATELY;

-- Apply to warehouse
ALTER WAREHOUSE compute_wh SET RESOURCE_MONITOR = warehouse_monitor;

Automation Patterns