Snowflake CRON Task not inserting data into table

Issue

This Content is from Stack Overflow. Question asked by CodeM

  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') ));



Solution

Check the Answers

This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?