3
0
During this post we will discuss about Serverless TASKS in snowflake. Snowflake tasks allow users to schedule the execution of a SQL statement. Before the Serverless task in place, creation of TASK requires the WAREHOUSE parameter or in other words Warehouse was tightly coupled with the task definition. User have to manually configure warehouse size, idle policy, which can be time-consuming and require Analysis. Snowflake has addressed this concern and introduce Serverless TASK by making the warehouse optional. The Serverless model for tasks enables you to rely on compute resources managed by Snowflake instead of user-managed virtual warehouses. Deciding warehouse size and then optimize it for maximum utilization/efficiency are handled by the task execution infrastructure itself.
Omit the WAREHOUSE parameter to allow Snowflake to manage the compute resources for the task.
Still we have an option, can alternatively manage the compute resources for individual tasks by specifying an existing virtual warehouse when creating the task
Optional Parameters:
WAREHOUSE: Omit this parameter to use Snowflake-managed compute resources for runs of this task. Also referred to as Serverless tasks.
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE : Specifies the size of the compute resources to provision for the first run of the task. Once task history is available for Snowflake, Snowflake ignores this parameter setting. Based on the TASK_HISTORY, Snowflake will use that metadata to effectively “right-size” the compute resource for all subsequent executions.
Note: Both parameters can not go together, If a WAREHOUSE = string parameter value is specified, then setting this parameter produces a user error.
In case of Serverless compute model, Snowflake bills your account based on the actual compute resource usage. Customer-manage virtual warehouses, which consume credits when active, and may sit idle or be overutilize.
Privileges:
Privileges:
Grant 'EXECUTE MANAGED TASK' on account level to the role.
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE <<ROLE_NAME>>
Serverless Task creates in below way:
create or replace task TASK_DEMO
SCHEDULE = '1 minute'
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'SMALL'
AS insert into TASK_DEMO values ('9999999','I101',100)
Query the serverless_task_history table to verify if credits are consumed by Serverless task executions and consumption.
select * from table(information_schema.serverless_task_history(date_range_start=>dateadd(h, -12, current_timestamp())));
Verify the TASK runs inside the task_history:
select * from table(information_schema.task_history(scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
result_limit => 5,
task_name=>'TASK_DEMO'));
Important observation:
Initially Snowflake executes the task on SMALL warehouse (define by : USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE) and once the task history is available ,it has used metadata to determine the right sizing warehouse. Based on this Snowflake consider X-SMALL for subsequent runs
