Snowflake a working procedure is not being successfully executed when calling it within a scheduled task

Question

I am having a fully running procedure on snowflake with no errors at all when calling it:

call ADD_MONTHLY_OBSERVATION_VALUES('@test_azure_blob_stg/Monthly_Report.csv', 'GENERIC_CSV_FORMAT');

I wanted to wrap this command into a task and schedule it to a specific time like so:

CREATE OR REPLACE TASK ADD_MONTHLY_OBSERVATION_VALUES_TASK
    WAREHOUSE = 'DEV_WH'
    TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
    //SCHEDULE = 'USING CRON 0 6-7 * * SUN,MON,TUE,WED,THU Asia/Dacca'
    //Schedule for each minute
    SCHEDULE = 'USING CRON * * * * * UTC'
AS
    call ADD_MONTHLY_OBSERVATION_VALUES('@test_azure_blob_stg/Monthly_Report.csv', 'GENERIC_CSV_FORMAT');

And then I resumed the task to work:

ALTER TASK ADD_MONTHLY_OBSERVATION_VALUES_TASK RESUME;

When I checked the history of the task:

STATE: SUCCEEDED

ERROR_CODE: NULL

ERROR_MESSAGE: NULL

QUERY_START_TIME: 2021-01-27 16:00:06.198 -0800

COMPLETED_TIME: 2021-01-27 16:00:24.902 -0800

RETURN_VALUE: NULL

Actually the procedure will return a string DONE when everything have been successfully added/updated.

When running:

show tasks;

The procedure's state is started.

Why the procedure is not being executed when called using a task?

There was new data uploaded to the staged file from Azure, so the procedure should detect the new submissions and start the insert process.

Marcel Marcel · Accepted Answer · 2021-01-28T09:11:00

If you run the procedure outside the task, you have to use the task owner role to ensure correct testing. If your procedure works outside the task with task owner privileges, it should also work within the task.

So... I assume there is a problem with your access/rights management. SQL statements executed by the task can only operate on Snowflake objects on which the role has the required privileges. --> You have to grant more privileges on the objects within your procedure to your task owner role.

See more here: https://docs.snowflake.com/en/sql-reference/sql/create-task.html

Snowflake a working procedure is not being successfully executed when calling it within a scheduled task

1 Answers