Snowflake Tasks



Today we take a look at a newish feature in Snowflake called Tasks. Tasks allows you to create a SQL script or procedure and schedule it to run on your Snowflake instance. The Snowflake task engine is a CRON variant and should look familar syntactically if you are an avid linux user. CRON or CRONTAB is the linux version of windows task schedule. It is extermly simplified in regards to how it runs a job. It supports a few parameters and points to a .sh or other script file. The paramters control the frequency of the job being run, days of week and time.



Model Results



Snowflake has recently introduced this functionality so lets take a quick look at how to create our first task on our Snowflake instance.



First lets create a table to store some data into. This will be the table targeted in our Snowflake task.

CREATE DATABASE MYTASKEXAMPLES;

USE DATABASE MYTASKEXAMPLES;

CREATE OR REPLACE TABLE LOGTABLE
(
LogDT datetime
)



Next lets create and run a insert statment.

INSERT INTO LOGTABLE(LogDT) VALUES(CURRENT_TIMESTAMP);



Now lets create the task which will use the SQL and the statement.



CREATE OR REPLACE TASK MINUTEINSERT
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON * * * * * America/New_York'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
AS
INSERT INTO LOGTABLE(LogDT) VALUES(CURRENT_TIMESTAMP);





This will create the task in Snowflake, but it will be initated in a suspended state. To turn the command on we must alter the task.

ALTER TASK MINUTEINSERT RESUME;



Now all we need to do is wait and scan the table, here we can see every minute the task has ran and inserted our data.



SELECT * FROM LOGTABLE



RESULTS



Turn off the task so it does not run up compute charges!

ALTER TASK MINUTEINSERT SUSPEND; 



This is an extremly basic example, I will do some more complicated implementations of Snowflake tasks using SnowPipe and stored procedures soon. You can also do preceding steps, notifications and many other nifty things with Snowflake tasks.

Ian Fogelman

Ian Fogelman

My name is Ian Fogelman. I like to develop data driven solutions with SQL Server, Python, .NET and predictive analytics.