How to schedule stored procedure via snowflake task ?

A task can execute a single SQL statement or a call to a stored procedure.

Tasks can be combined with table stream for continuous ELT workflows to process recently changed table rows.

Stored procedure & table used in this article for explanation.

Table Script:

CREATE OR REPLACE TABLE TABLE_SMP
(
Emp_Name VARCHAR,
Emp_ID VARCHAR,
Last_Inserted_Date TIMESTAMP
);

Stored Procedure:

CREATE OR REPLACE PROCEDURE Task_Scenario()
RETURNS VARIANT not null
LANGUAGE JAVASCRIPT
AS
$$
var response_as_json = {};
response_as_json[‘status’] = ‘Success’;
var str = ‘’;
var stmt = snowflake.createStatement({sqlText: “INSERT INTO TABLE_SMP VALUES (‘DB1’,’TB1',sysdate())”});
try
{
stmt.execute();
}
catch (err)
{
snowflake.execute({sqlText: “ROLLBACK WORK;”});
response_as_json[‘status’] = ‘Fail’;
response_as_json[‘error_message’] = err;
}
return response_as_json;
$$;

Use case of the Procedure: It used to insert the records in to a snowflake table (TABLE_SMP)

To know more about the stored procedure in snowflake click here.

TASK in snowflake:

Snowflake task has the ability to execute single SQL statements including calling a stored procedure.

Tasks in Snowflake may only be triggered by a schedule

DDL statements relevant to snowflake task are as follows:

1. CREATE TASK
2. ALTER TASK
3. DROP TASK
4. DESCRIBE TASK
5. SHOW TASKS

How to schedule a Snowflake Task?

The Snowflake task engine has a CRON and NONCRON variant scheduling mechanisms. The CRON variant should look familiar syntactically if you are an avid Linux user. CRON or CRONTAB is the Linux version of Windows task schedule. It is extremely simplified in regards to how it runs a job. It supports a few parameters and points to a SQL statement as part of the task definition. The parameters control the frequency of the job being run, days of week and time.

Here is a table with a few configurations and descriptions of CRON job timings to give you an idea of how it works.

* * * * * command(s)
- - - - -
| | | | |
| | | | ----- Day of week (0 - 7) (Sunday=0 or 7)
| | | ------- Month (1 - 12)
| | --------- Day of month (1 - 31)
| ----------- Hour (0 - 23)
------------- Minute (0 - 59)

Sample snowflake script using CRON notation:

Task we will be calling the procedure Task_Scenario() . The timestamp column in the table will allow us to see the schedule of the job executing.

Before Task Run: (Table: TABLE_SMP)

Task:

CREATE OR REPLACE TASK DEMO_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = ‘USING CRON */1 * * * * UTC’
AS
call Task_Scenario();

CREATION OF NEW TASK

Note: An important is that even though the task is created in Snowflake, which we can verify by running the show tasks command.

SHOW command used to display all the TASK

We can see the task state is “suspended” which means the task will not be triggering. To turn our task on, we issue an alter task command.

ALTER command to change the status of the TASK

Now that the task is on, we just have to wait 1 minute for the task to trigger.

Table details after 2 job run

To know more about the next schedule run details of the task execute below SQL statement,

select *
from table(information_schema.task_history())
order by scheduled_time;

To save on compute charges suspend the tasks.

ALTER TASK DEMO_TASK SUSPEND;

Will focus on NONCRON job in next article :)