Executing Multiple Lines in a Snowflake Task

Question

I created the task below and am having trouble getting it to execute all lines. It looks like it just does the first delete from productweekly_upload then completes. Anyone have any ideas? This is my first time using tasks

CREATE OR REPLACE TASK WeeklySymphony_Load
  WAREHOUSE = UPLOADWAREHOUSE
  SCHEDULE = 'USING CRON 10 8 * * MON America/New_York'
as

--run every monday at 8:10 am 

delete from Productweekly_Upload;
delete from Factsweekly_Upload;
delete from Productweekly;
delete from Factsweekly;

copy into ProductWeekly_Upload
from @symphony_s3_stage/prasco_phast_it_prdct_wk_;

copy into FactsWeekly_Upload
from @symphony_s3_stage/prasco_phast_it_wk_;

insert into ProductWeekly
select * from ProductWeekly_Upload;

insert into FactsWeekly 
select * from FactsWeekly_Upload;

score 3 · Accepted Answer · answered Mar 16 '20 at 13:18

3

You can only execute 1 command in a TASK. If you want to create multiple steps, you can either wrap these into a stored procedure and call the SP from the TASK, or you can create each step as a TASK and make those dependencies, so they execute in order.

I recommend a read-through of this document:

https://docs.snowflake.com/en/user-guide/tasks-intro.html

answered Mar 16 '20 at 13:18

Mike Walton

6,595
2
11
22

Snowflake stored procedures look like a hot mess - I may take the easy way out and just create like 6 different tasks with one line of code each. – Jeff Mar 16 '20 at 14:48
Snowflake SPs are just Javascript. Easy. – Mike Walton Mar 16 '20 at 15:46

score 1 · Answer 2 · answered Apr 25 '22 at 10:11

If you want multiple statement, the 3 solutions are :

Stored proc
Chain multiple tasking AFTER statement
Use Procedural logic with AS DECLARE // BEGIN // END; block https://docs.snowflake.com/en/sql-reference/sql/create-task.html#procedural-logic-using-snowflake-scripting

Executing Multiple Lines in a Snowflake Task

2 Answers2