1. If try to insert into table without task then it's successful, but with task not inserting data into table.
2. Task status is succeed but table is empty.
3. Task is scheduled to run every 5 min with CRON

I dont know where it's going wrong.

use database test_db;
use schema test_Schema;
use warehouse test_wh;

--to get the past one year date when I execute the code
set l_last_control_dt =

(
select
ifnull( dateadd( hour, -4, max( start_time ) ), dateadd( month, -13, current_timestamp() ) ) as last_control_dt
from
test_db.test_Schema;.warehouse_metering_history
);

----count of rows where start time from warehouse_metering_history greater than l_last_control_dt 
set l_row_count =

(
select count(*)
from
snowflake.account_usage.warehouse_metering_history where
s.start_time >= to_timestamp( $l_last_control_dt )
);


CREATE  or replace TASK task_load_warehouse_metering_tbl
WAREHOUSE = test_wh
SCHEDULE  = 'USING CRON */5 * * * * UTC'
AS
INSERT into
    test_db.test_schema.warehouse_metering_tbl
(
select
         current_account()                  as account_name
        ,current_region()                   as region_name
        ,s.start_time
        ,s.end_time
        ,s.warehouse_id
        ,s.warehouse_name
        ,s.credits_used
        ,s.credits_used_compute
        ,s.credits_used_cloud_services
        ,'warehouse_metering_history'
        ,getvariable('L_ROW_COUNT')
        ,to_timestamp( sysdate() )
    from
        snowflake.account_usage.warehouse_metering_history s
    where
        s.start_time >= to_timestamp( getvariable('L_LAST_CONTROL_DT') ));

Task succeed status:

After running task for 3 times

CodePudding user response：

Session variable is availble only for a session it was created in.

SQL variables are private to a session. When a Snowflake session is closed, all variables created during the session are dropped. This means that no one can access user-defined variables that have been set in another session, and when the session is closed, these variables expire.

Task is executed using its own session, thus:

where  s.start_time >= to_timestamp( getvariable('L_LAST_CONTROL_DT') ));

is a comparison against NULL value:

where s.start_time >= NULL

which is ALWAYS not true, 0 rows selected.

Quick test on a completely new session:

SELECT getvariable('L_LAST_CONTROL_DT') 
-- NULL

That could be spotted easier when accessing a session variable with $:

SELECT $L_LAST_CONTROL_DT
-- Error: Session variable '$L_LAST_CONTROL_DT' does not exist (line 1)

Solution using Snowflake Scripting block and block variables:

CREATE  or replace TASK task_load_warehouse_metering_tbl
WAREHOUSE = test_wh
SCHEDULE  = 'USING CRON */5 * * * * UTC'
AS
DECLARE
    l_last_control_dt TIMESTAMP := (
       SELECT 
           ifnull( dateadd( hour, -4, max( start_time ) ),
                   dateadd( month, -13, current_timestamp() ) ) as last_control_dt
       FROM test_db.test_Schema.warehouse_metering_history);

   l_row_count INTEGER;
BEGIN
   l_row_count := (SELECT COUNT(*)
                   FROM snowflake.account_usage.warehouse_metering_history
                   WHERE s.start_time >= :l_last_control_dt);

   INSERT INTO test_db.test_schema.warehouse_metering_tbl
   -- (col_name1, col_name2, col_name3, ...)
   SELECT
         current_account()                  as account_name
        ,current_region()                   as region_name
        ,s.start_time
        ,s.end_time
        ,s.warehouse_id
        ,s.warehouse_name
        ,s.credits_used
        ,s.credits_used_compute
        ,s.credits_used_cloud_services
        ,'warehouse_metering_history'
        ,:l_row_count 
        ,to_timestamp( sysdate() )
    FROM  snowflake.account_usage.warehouse_metering_history s
    WHERE s.start_time >= :l_last_control_dt;
END;