Snowflake SQL Compilation error on task creation with declare block

Question

When trying to create a task(from a Snowflake worksheet) with two declared variables of type VARCHAR, an error is given for an <EOF> on the line when declaring the first variable. I am unsure of why this happens since in the examples in Snowflakes documentation on task creation the same syntax to create a variable is used.

Here is the query.

create or replace task TEST_STORED_PROC_TASK
    warehouse = COMPUTE_XS
    schedule = '1 minute'
AS
DECLARE
    delete_query VARCHAR;
    insert_query VARCHAR;
BEGIN
    delete_query := 'DELETE FROM TEST_TASK_TBL_DEST
            USING TEST_STORED_PROC_TASK_STREAM
            WHERE TEST_TASK_TBL_DEST.DATA_INDEX = TEST_STORED_PROC_TASK_STREAM.DATA_INDEX
                  AND TEST_STORED_PROC_TASK_STREAM.METADATA$ISUPDATE;';
    insert_query := 'INSERT INTO TEST_TASK_TBL_DEST (DATA_INDEX ,COMP_ID ,ACCOUNT_ID ,COMP_VERSION ,NAME ,DESCRIPTION ,OBJECT_DICT ,ACTION_TYPE ,CONNECTOR_TYPE ,OUTPUT_PROFILE ,   PARAMETER_PROFILE)
              WITH DEDUPED_COMP AS (
                SELECT DATA_INDEX,COMP_ID,ACCOUNT_ID,COMP_VERSION,NAME, DESCRIPTION, PROPERTIES,  ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
                FROM TEST_STORED_PROC_TASK_STREAM
                WHERE METADATA$ACTION = \'INSERT\'
                QUALIFY 1 = ROW_NUM
              ),
              GROUPED_PROPS AS (
                SELECT ANY_VALUE(DATA_INDEX) AS DATA_INDEX, COMP_ID, ANY_VALUE(ACCOUNT_ID) AS ACCOUNT_ID, ANY_VALUE(COMP_VERSION) AS COMP_VERSION,
                       ANY_VALUE(NAME) AS NAME, ANY_VALUE(DESCRIPTION) AS DESCRIPTION, OBJECT_AGG(DISTINCT XMLGET(PROPS.Value,\'Name\'):"$"::string,
                       XMLGET(PROPS.Value,\'Value\'):"$"::VARIANT) AS OBJECT_DICT
                FROM DEDUPED_COMP,
                LATERAL FLATTEN(INPUT=>PROPERTIES, MODE=> \'ARRAY\', OUTER=>TRUE) PROPS
                GROUP BY COMP_ID
              )
              SELECT *, OBJECT_DICT:"action-type"::string AS ACTION_TYPE, OBJECT_DICT:"connector-type"::string AS CONNECTOR_TYPE,
                     OBJECT_DICT:"output-profile"::string AS OUTPUT_PROFILE, OBJECT_DICT:"parameter-profile"::string AS PARAMETER_PROFILE
              FROM GROUPED_PROPS;
            ';
    call RUN_TASK_QUERIES(ARRAY_CONSTRUCT(:delete_query, :insert_query));
END;

Here is the error

SQL compilation error: syntax error line 6 at position 25 unexpected '<EOF>'.

Answer 1

The code is executed without error when using Snowsight.

If the ClassicUI is used then wrapping with EXECUTE IMMEDIATE $$ ... $$ will compile it:

create or replace task TEST_STORED_PROC_TASK
    warehouse = COMPUTE_XS
    schedule = '1 minute'
AS
EXECUTE IMMEDIATE $$
DECLARE
    delete_query VARCHAR;
    insert_query VARCHAR;
BEGIN
    delete_query := 'DELETE FROM TEST_TASK_TBL_DEST
            USING TEST_STORED_PROC_TASK_STREAM
            WHERE TEST_TASK_TBL_DEST.DATA_INDEX = TEST_STORED_PROC_TASK_STREAM.DATA_INDEX
                  AND TEST_STORED_PROC_TASK_STREAM.METADATA$ISUPDATE;';
    insert_query := 'INSERT INTO TEST_TASK_TBL_DEST (DATA_INDEX ,COMP_ID ,ACCOUNT_ID ,COMP_VERSION ,NAME ,DESCRIPTION ,OBJECT_DICT ,ACTION_TYPE ,CONNECTOR_TYPE ,OUTPUT_PROFILE ,   PARAMETER_PROFILE)
              WITH DEDUPED_COMP AS (
                SELECT DATA_INDEX,COMP_ID,ACCOUNT_ID,COMP_VERSION,NAME, DESCRIPTION, PROPERTIES,  ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
                FROM TEST_STORED_PROC_TASK_STREAM
                WHERE METADATA$ACTION = \'INSERT\'
                QUALIFY 1 = ROW_NUM
              ),
              GROUPED_PROPS AS (
                SELECT ANY_VALUE(DATA_INDEX) AS DATA_INDEX, COMP_ID, ANY_VALUE(ACCOUNT_ID) AS ACCOUNT_ID, ANY_VALUE(COMP_VERSION) AS COMP_VERSION,
                       ANY_VALUE(NAME) AS NAME, ANY_VALUE(DESCRIPTION) AS DESCRIPTION, OBJECT_AGG(DISTINCT XMLGET(PROPS.Value,\'Name\'):"$"::string,
                       XMLGET(PROPS.Value,\'Value\'):"$"::VARIANT) AS OBJECT_DICT
                FROM DEDUPED_COMP,
                LATERAL FLATTEN(INPUT=>PROPERTIES, MODE=> \'ARRAY\', OUTER=>TRUE) PROPS
                GROUP BY COMP_ID
              )
              SELECT *, OBJECT_DICT:"action-type"::string AS ACTION_TYPE, OBJECT_DICT:"connector-type"::string AS CONNECTOR_TYPE,
                     OBJECT_DICT:"output-profile"::string AS OUTPUT_PROFILE, OBJECT_DICT:"parameter-profile"::string AS PARAMETER_PROFILE
              FROM GROUPED_PROPS;
            ';
    call RUN_TASK_QUERIES(ARRAY_CONSTRUCT(:delete_query, :insert_query));
END;
$$;