Hey there, let's break down your problem step by step:

First off, your current syntax is not valid — that's exactly why you're hitting the SQL compilation error: syntax error line 5 at position 9 unexpected 'DB' message. Snowflake's Task WHEN clause requires a boolean expression that returns TRUE or FALSE directly, and a CALL statement (used to execute stored procedures) doesn't fit this requirement.

How to fix this #

You have two solid options depending on whether your CHECK_STATUS logic needs to run procedural code (like DDL/DML) or not:

Option 1: Switch to a Boolean UDF (Recommended for read-only checks)

If your CHECK_STATUS only needs to run read-only logic (like checking for existing records), replace the stored procedure with a User-Defined Function (UDF) that returns a boolean value:

-- Create the boolean UDF first
CREATE OR REPLACE FUNCTION DB.SCHEMA.CHECK_STATUS(arg1 VARCHAR)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
    -- Insert your actual check logic here, e.g.:
    SELECT EXISTS(SELECT 1 FROM DB.SCHEMA.YOUR_TABLE WHERE your_column = arg1);
$$;

-- Now create the task with the UDF in the WHEN clause
CREATE OR REPLACE TASK SCHEMA.TASK1 
WAREHOUSE = 'YOUR_WAREHOUSE_NAME' 
SCHEDULE = '5 minute' 
WHEN DB.SCHEMA.CHECK_STATUS('arg1')
AS CALL DB.SCHEMA.ETL('arg1');

This works because the UDF directly returns a boolean value that the WHEN clause can evaluate.

Option 2: Use a Stored Procedure with a Status Table (For procedural logic)

If CHECK_STATUS needs to run procedural code (like DDL, data modifications, or complex workflows), you can have the stored procedure write its result to a dedicated status table, then query that table in the WHEN clause.

1. Modify the stored procedure to track status:

CREATE OR REPLACE PROCEDURE DB.SCHEMA.CHECK_STATUS(arg1 VARCHAR)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
    DECLARE
        task_status BOOLEAN;
    BEGIN
        -- Your existing check logic here, e.g.:
        SELECT EXISTS(SELECT 1 FROM DB.SCHEMA.YOUR_TABLE WHERE your_column = arg1) INTO task_status;

        -- Create/replace a status table (do this once if you haven't already)
        CREATE OR REPLACE TABLE IF NOT EXISTS DB.SCHEMA.TASK_STATUS (
            TASK_NAME VARCHAR(100),
            STATUS BOOLEAN,
            LAST_UPDATED TIMESTAMP
        );

        -- Update the status for your task
        INSERT OVERWRITE INTO DB.SCHEMA.TASK_STATUS
        VALUES ('TASK1', task_status, CURRENT_TIMESTAMP());

        RETURN task_status;
    END;
$$;

2. Create the task with a query in the WHEN clause:

CREATE OR REPLACE TASK SCHEMA.TASK1 
WAREHOUSE = 'YOUR_WAREHOUSE_NAME' 
SCHEDULE = '5 minute' 
WHEN (SELECT COALESCE(STATUS, FALSE) FROM DB.SCHEMA.TASK_STATUS WHERE TASK_NAME = 'TASK1')
AS 
BEGIN
    -- First refresh the status
    CALL DB.SCHEMA.CHECK_STATUS('arg1');
    -- Then run your ETL
    CALL DB.SCHEMA.ETL('arg1');
END;

The COALESCE handles the case where the status table has no entry yet, defaulting to FALSE to avoid unexpected runs.

Key Background #

Per Snowflake's CREATE TASK documentation, the WHEN clause must resolve to a boolean value. It evaluates this expression at each scheduled interval, and only runs the task's main logic when the result is TRUE. A CALL statement can't be used here because it's an execution command, not a boolean expression.

内容的提问来源于stack exchange，提问作者Sarde