Is there a way to force run a Snowflake's TASK now (before the next scheduled slot)?

Question

I have a task scheduled to run every 15 minutes:

CREATE OR REPLACE TASK mytask
  WAREHOUSE = 'SHARED_WH_MEDIUM'
  SCHEDULE = '15 MINUTE'
  STATEMENT_TIMEOUT_IN_SECONDS = 3600,
  QUERY_TAG = 'KLIPFOLIO'
AS
  CREATE OR REPLACE TABLE mytable AS
   SELECT * from  xxx; 
;

alter task mytask resume;

I see from the output of task_history() that the task is SCHEDULED:

select * from table(aftonbladet.information_schema.task_history(task_name => 'MYTASK')) order by scheduled_time;
QUERY_ID    NAME    DATABASE_NAME   SCHEMA_NAME QUERY_TEXT  CONDITION_TEXT  STATE   ERROR_CODE  ERROR_MESSAGE   SCHEDULED_TIME  COMPLETED_TIME  RETURN_VALUE
***     MYTASK  *** *** ***     SCHEDULED           2020-01-21 09:58:12.434 +0100       

but I want it to run right now without waiting for the SCHEDULED_TIME , is there any way to accomplish that?

Curriery

This is now possible! See this answer. – Erminois

Answer 1

Snowflake now supports running tasks manually. Just use the EXECUTE TASK command:

EXECUTE TASK manually triggers an asynchronous single run of a scheduled task (either a standalone task or the root task in a task tree) independent of the schedule defined for the task. A successful run of a root task triggers a cascading run of child tasks in the tree as their precedent task completes, as though the root task had run on its defined schedule.

Also, there is no need for the task in started mode. Even tasks in suspended mode can be executed manually.

Scherle

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Yuletide

Answer 2

There is no way currently to execute a task manually. You could, however, alter the task schedule to 1 minute, let it run, and then alter it back to 15 minutes, so that you're not waiting the full 15 minutes. I have seen this request multiple times, and there is an Idea on Lodge (https://community.snowflake.com/s/ideas) that you should upvote (search for 'Tasks' and I think it'll be one of the top ideas). Since Tasks are still in Public Preview, it's likely that these types of ideas will be reviewed and prioritized if they have a lot of votes.

Shalom

The Snowflake Idea is called A command to trigger a TASK (and its children) without having to schedule it has only 40 points (that is 4 votes as of 2020-01-21) – Curriery

What about also creating a stored procedure? – Obscurity

The problem with creating a stored procedure is that Snowflake might simultaneously execute both the procedure (manually) and the task (automatically). On the other hand, Snowflake will never execute the same task simultaneously -- see the documentation for the ALLOW_OVERLAPPING_EXECUTION parameter: "Snowflake ensures only one instance of a standalone task is running at a given time." – Erminois

Answer 3

To build on Mike's answer:

• You could have a task that executes every minute, but only if there's data on the stream!
• For this you can create a table and stream just to decide if the task will be triggered every minute or not.
• This root task should delete the data inserted in the stream to prevent the task running again.
• So then you can have dependent tasks that execute every time you bring data into the stream, but only when the stream has new data.
• This relies on the ability to run a task only when SYSTEM$STREAM_HAS_DATA()

-- stream so this task executes every minute, but only if there's new data
create table just_timestamps_stream_table(value varchar);
create stream just_timestamps_stream on table just_timestamps_stream_table;

-- https://docs.snowflake.com/en/user-guide/tasks-intro.html
create or replace task mytask_minute
  warehouse = test_small
  schedule = '1 MINUTE'
  when SYSTEM$STREAM_HAS_DATA('just_timestamps_stream')
as
-- consume stream so tasks doesn't execute again
delete from just_timestamps_stream_table;

-- the real task to be executed
create or replace task mytask_minute_child1
  warehouse = test_small
  after mytask_minute
as
insert into just_timestamps values(current_timestamp, 'child1');

Full example:

• https://github.com/fhoffa/snowflake_snippets/blob/main/stream_and_tasks/minimal.sql

Sprat