Successfully created a task in Snowflake, but it does not show up when running "show tasks"

Question

I am new to Snowflake and am trying to create my first task.

CREATE TASK task_update_table
WAREHOUSE = "TEST"
SCHEDULE = 'USING CRON 0 5 * * * America/Los_Angeles'
AS
INSERT INTO "TEST"."WEB"."SOME_TABLE" (ID,VALUE1,VALUE2,VALUE3)
WITH CTE AS
(SELECT 
ID
,VALUE1
,VALUE2
,VALUE3
FROM OTHER_TABLE
WHERE ID NOT IN (SELECT ID FROM "TEST"."WEB"."SOME_TABLE")
)
SELECT      
ID,VALUE1,VALUE2,VALUE3
FROM CTE

I got a message that the task was created successfully

"Task task_update_table successfully created"

I then try to run show tasks in schema SHOW TASKS IN "TEST"."WEB" and get 0 rows as a result. What am I doing wrong? why is the task not showing?

I did all of this under sysadmin and was using the same warehouse, db and schema.

Rich Murnane Rich Murnane · Accepted Answer · 2020-07-02T11:01:31

There are some limitations around show commands that might be blocking you, particularly "SHOW commands only return objects for which the current user’s current role has been granted the necessary access privileges".

https://docs.snowflake.com/en/sql-reference/sql/show.html#general-usage-notes

I suspect the task was created by a different role (therefore owned by a different role), or perhaps it was created in different database or schema.

To find it, I'd recommend running the following using a role such as ACCOUNTADMIN.

show tasks in account;
SELECT *
FROM (
    SELECT *
    FROM   TABLE(RESULT_SCAN(LAST_QUERY_ID())))
WHERE "name" = 'TASK_UPDATE_TABLE';

While testing and learning in Snowflake, it is critical you set your session "context" correctly, using commands like this:

USE ROLE my_role_here;
USE WAREHOUSE my_warehouse_here;
USE DATABASE my_database_here;
USE SCHEMA my_schema_here;

Doing those four commands, or setting defaults for them for your user will help you tremendously when learning.

I hope this helps...Rich

Successfully created a task in Snowflake, but it does not show up when running "show tasks"

1 Answers