CW

Snowflake Advanced Task Patterns

Free Lesson

Advertisement

Snowflake Advanced Task Patterns

Advanced task patterns in Snowflake enable complex workflow orchestration with dependencies, error handling, conditional logic, and dynamic SQL execution.

Architecture Overview

<svg width="800" height="500" viewBox="0 0 800 500" xmlns="http://www.w3.org/2000/svg">
  <defs>
    <linearGradient id="taskGrad" x1="0%" y1="0%" x2="100%" y2="0%">
      <stop offset="0%" style="stop-color:#9B59B6;stop-opacity:1" />
      <stop offset="100%" style="stop-color:#AF7AC5;stop-opacity:1" />
    </linearGradient>
  </defs>

  <text x="400" y="30" text-anchor="middle" font-size="18" font-weight="bold" fill="#333">Advanced Task Workflow Architecture</text>
  <rect x="30" y="60" width="150" height="60" rx="10" fill="#3498DB" opacity="0.9"/>
  <text x="105" y="85" text-anchor="middle" font-size="11" fill="white" font-weight="bold">Entry Task</text>
  <text x="105" y="100" text-anchor="middle" font-size="9" fill="white">CRON: 0 6 * * *</text>
  <path d="M180 90 L250 90" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <circle cx="230" cy="90" r="15" fill="#F39C12" opacity="0.9"/>
  <text x="230" y="94" text-anchor="middle" font-size="10" fill="white" font-weight="bold">IF</text>
  <path d="M245 75 L300 50" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <rect x="300" y="30" width="150" height="50" rx="10" fill="#2ECC71" opacity="0.9"/>
  <text x="375" y="55" text-anchor="middle" font-size="10" fill="white" font-weight="bold">Task A: Process Data</text>
  <text x="375" y="70" text-anchor="middle" font-size="9" fill="white">WHEN condition = TRUE</text>
  <path d="M245 105 L300 130" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <rect x="300" y="110" width="150" height="50" rx="10" fill="#E74C3C" opacity="0.9"/>
  <text x="375" y="135" text-anchor="middle" font-size="10" fill="white" font-weight="bold">Task B: Handle Error</text>
  <text x="375" y="150" text-anchor="middle" font-size="9" fill="white">WHEN condition = FALSE</text>
  <path d="M450 55 L520 90" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <path d="M450 135 L520 90" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <circle cx="500" cy="90" r="15" fill="#9B59B6" opacity="0.9"/>
  <text x="500" y="94" text-anchor="middle" font-size="10" fill="white" font-weight="bold">J</text>
  <path d="M515 90 L580 90" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <rect x="580" y="65" width="150" height="50" rx="10" fill="#1ABC9C" opacity="0.9"/>
  <text x="655" y="90" text-anchor="middle" font-size="10" fill="white" font-weight="bold">Task C: Validate</text>
  <path d="M655 115 L655 180" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <circle cx="655" cy="170" r="15" fill="#F39C12" opacity="0.9"/>
  <text x="655" y="174" text-anchor="middle" font-size="10" fill="white" font-weight="bold">F</text>

  <path d="M640 175 L550 200" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <rect x="450" y="180" width="150" height="50" rx="10" fill="#3498DB" opacity="0.9"/>
  <text x="525" y="205" text-anchor="middle" font-size="10" fill="white" font-weight="bold">Task D1: Notify</text>

  <path d="M670 175 L730 200" stroke="#333" stroke-width="2" fill="none" marker-end="url(#arrowTask)"/>
  <rect x="680" y="180" width="90" height="50" rx="10" fill="#E67E22" opacity="0.9"/>
  <text x="725" y="205" text-anchor="middle" font-size="10" fill="white" font-weight="bold">Task D2</text>
  <rect x="30" y="250" width="740" height="120" rx="10" fill="url(#taskGrad)" opacity="0.9"/>
  <text x="400" y="275" text-anchor="middle" font-size="14" fill="white" font-weight="bold">Task History & Monitoring</text>

  <rect x="50" y="290" width="160" height="60" rx="8" fill="white" opacity="0.9"/>
  <text x="130" y="315" text-anchor="middle" font-size="10" fill="#333">Execution History</text>
  <text x="130" y="330" text-anchor="middle" font-size="9" fill="#666">Run times, status</text>

  <rect x="230" y="290" width="160" height="60" rx="8" fill="white" opacity="0.9"/>
  <text x="310" y="315" text-anchor="middle" font-size="10" fill="#333">Error Tracking</text>
  <text x="310" y="330" text-anchor="middle" font-size="9" fill="#666">Failures, retries</text>

  <rect x="410" y="290" width="160" height="60" rx="8" fill="white" opacity="0.9"/>
  <text x="490" y="315" text-anchor="middle" font-size="10" fill="#333">Performance</text>
  <text x="490" y="330" text-anchor="middle" font-size="9" fill="#666">Duration, credits</text>

  <rect x="590" y="290" width="160" height="60" rx="8" fill="white" opacity="0.9"/>
  <text x="670" y="315" text-anchor="middle" font-size="10" fill="#333">Dependencies</text>
  <text x="670" y="330" text-anchor="middle" font-size="9" fill="#666">DAG visualization</text>
  <rect x="30" y="390" width="230" height="90" rx="10" fill="#27AE60" opacity="0.85"/>
  <text x="145" y="415" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Scheduling Patterns</text>
  <text x="145" y="435" text-anchor="middle" font-size="10" fill="white">CRON expressions</text>
  <text x="145" y="450" text-anchor="middle" font-size="10" fill="white">Event-driven triggers</text>
  <text x="145" y="465" text-anchor="middle" font-size="10" fill="white">Dependency chains</text>

  <rect x="280" y="390" width="230" height="90" rx="10" fill="#3498DB" opacity="0.85"/>
  <text x="395" y="415" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Error Handling</text>
  <text x="395" y="435" text-anchor="middle" font-size="10" fill="white">TRY/CATCH blocks</text>
  <text x="395" y="450" text-anchor="middle" font-size="10" fill="white">Automatic retries</text>
  <text x="395" y="465" text-anchor="middle" font-size="10" fill="white">Alert on failure</text>

  <rect x="530" y="390" width="240" height="90" rx="10" fill="#E74C3C" opacity="0.85"/>
  <text x="650" y="415" text-anchor="middle" font-size="12" fill="white" font-weight="bold">Dynamic Operations</text>
  <text x="650" y="435" text-anchor="middle" font-size="10" fill="white">Dynamic SQL execution</text>
  <text x="650" y="450" text-anchor="middle" font-size="10" fill="white">Parameterized queries</text>
  <text x="650" y="465" text-anchor="middle" font-size="10" fill="white">Conditional logic</text>

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

DAG Workflow

-- Create task DAG
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_1 AS INSERT INTO t3 SELECT 3;
CREATE OR REPLACE TASK task_4 WAREHOUSE = wh AFTER task_2 AND task_3 AS INSERT INTO t4 SELECT 4;

-- Resume all tasks
ALTER TASK task_1 RESUME;
ALTER TASK task_2 RESUME;
ALTER TASK task_3 RESUME;
ALTER TASK task_4 RESUME;

Conditional Branching

CREATE OR REPLACE TASK conditional_task
  WAREHOUSE = compute_wh
  SCHEDULE = '1 HOUR'
AS
BEGIN
  -- Check condition
  LET record_count := (SELECT COUNT(*) FROM staging_table);
  
  IF (:record_count > 0) THEN
    -- Process data
    INSERT INTO target_table
    SELECT * FROM staging_table;
    
    -- Send success notification
    CALL SYSTEM$SEND_EMAIL(
      'my_integration',
      'admin@company.com',
      'Data Loaded',
      'Successfully loaded ' || :record_count || ' records'
    );
  ELSE
    -- Log no data
    INSERT INTO task_log (task_name, status, message, timestamp)
    VALUES ('conditional_task', 'SKIPPED', 'No records to process', CURRENT_TIMESTAMP());
  END IF;
END;

Dynamic SQL Tasks

CREATE OR REPLACE TASK dynamic_etl_task
  WAREHOUSE = compute_wh
  SCHEDULE = 'USING CRON 0 2 * * * America/New_York'
AS
BEGIN
  -- Get list of tables to process
  LET tables := (SELECT ARRAY_AGG(table_name) FROM metadata_table WHERE active = TRUE);
  
  FOR i IN 0 TO ARRAY_SIZE(:tables) - 1 DO
    LET table_name := :tables[i];
    
    -- Dynamic SQL
    LET sql := 'INSERT INTO target.' || :table_name ||
               ' SELECT * FROM source.' || :table_name ||
               ' WHERE processed = FALSE';
    
    EXECUTE IMMEDIATE :sql;
    
    -- Log execution
    INSERT INTO task_log (task_name, table_name, status, timestamp)
    VALUES ('dynamic_etl', :table_name, 'SUCCESS', CURRENT_TIMESTAMP());
  END FOR;
END;

Error Handling Patterns

CREATE OR REPLACE TASK resilient_task
  WAREHOUSE = compute_wh
  SCHEDULE = '5 MINUTE'
AS
BEGIN
  -- Try block
  BEGIN
    INSERT INTO target_table
    SELECT * FROM source_table WHERE processed = FALSE;
    
    -- Update source as processed
    UPDATE source_table SET processed = TRUE WHERE processed = FALSE;
  EXCEPTION
    WHEN OTHER THEN
      -- Log error
      INSERT INTO error_log (task_name, error_code, error_message, timestamp)
      VALUES ('resilient_task', SQLCODE, SQLERRM, CURRENT_TIMESTAMP());
      
      -- Send alert
      CALL SYSTEM$SEND_EMAIL(
        'alert_integration',
        'ops@company.com',
        'Task Failed: resilient_task',
        'Error: ' || SQLERRM
      );
  END;
END;

Task Monitoring

-- Task execution history
SELECT
  name,
  state,
  completed_time,
  scheduled_time,
  error_code,
  error_message,
  DATEDIFF(second, scheduled_time, completed_time) as duration_seconds
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP()),
  TASK_NAME => 'resilient_task'
))
ORDER BY scheduled_time DESC;

-- Task dependency analysis
SELECT
  t.name as task_name,
  t.state,
  t.schedule,
  t.allow_overlapping_execution,
  t.error_integration
FROM TABLE(INFORMATION_SCHEMA.TASKS()) t
ORDER BY t.name;

Use task dependencies to create complex workflows. Snowflake automatically handles parallel execution and failure propagation. Monitor task history for performance optimization and error tracking.

Advanced Patterns

PatternImplementationUse Case
DAGMultiple AFTER dependenciesComplex ETL
Fan-outMultiple tasks after oneParallel processing
Fan-inMultiple tasks before oneAggregation
ConditionalIF/ELSE in stored procDecision trees
DynamicEXECUTE IMMEDIATESchema-aware
  • Task dependencies enable complex workflow orchestration
  • Conditional branching allows decision-based execution
  • Dynamic SQL supports schema-aware processing
  • TRY/CATCH provides resilient error handling
  • Task history enables monitoring and debugging

Advertisement

←44 Snowflake Alerting46 Snowflake Stored Procedures Advanced→

Need Expert Snowflake Help?

Get personalized warehouse optimization, data modeling, or Snowflake platform consulting.

Contact Us →View Services

Advertisement