Snowflake task that runs exactly once

Strange as it may sound, a task that will run exactly one might sometimes save you. In my situation it is a hack for missing features in a terraform for Snowflake provider.

According to the Snowflake documentation, a task can only run regularly on schedule, or not run at all.

To get a task run exactly once, it needs to suspend (or drop) itself. With a little help from stored procedures it is perfectly doable.

create or replace procedure destroy_task_test()
returns string null
language javascript
as
$$
// 1. do what the task needs to do
// …

// 2. drop the task
var sql_command = `DROP TASK SELF_DESTROY_TASK;`;
var sql_statement = snowflake.createStatement({sqlText: sql_command});
sql_statement.execute();
$$

Then create the suicidal task:

create task SELF_DESTROY_TASK schedule = '1 minute'
as
call destroy_task_test();

alter task SELF_DESTROY_TASK resume;

Now run SHOW TASKS and watch your task disappear after a minute;

Instead of DROP TASK you may want to use ALTER TASK <task name> SUSPEND.