Tasks for scheduling SQL in Snowflake - Commands & Configuration

Introduction

Scheduling SQL queries helps automate repetitive database tasks like data updates or reports. Snowflake Tasks let you run SQL statements on a set schedule without manual work.

When you want to refresh a summary table every hour automatically.

When you need to run daily data cleanup jobs without logging in.

When you want to automate sending reports by running queries at night.

When you want to chain multiple SQL commands to run one after another.

When you want to reduce manual errors by automating routine SQL tasks.

Commands

This command creates a task named 'my_hourly_task' that runs every hour at minute 0 using a specified warehouse. It inserts the current timestamp and a count from 'source_table' into 'my_table'.

Terminal

CREATE OR REPLACE TASK my_hourly_task
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  INSERT INTO my_table SELECT CURRENT_TIMESTAMP, COUNT(*) FROM source_table;

Expected Output

Task MY_HOURLY_TASK created.

→

WAREHOUSE - Specifies the compute resource to run the task.

→

SCHEDULE - Defines when the task runs using a cron expression.

This command starts the task so it begins running on its schedule.

Terminal

ALTER TASK my_hourly_task RESUME;

Expected Output

Task MY_HOURLY_TASK resumed.

This command shows details about the task to confirm it exists and is active.

Terminal

SHOW TASKS LIKE 'my_hourly_task';

Expected Output

name           | database_name | schema_name | owner | comment | schedule          | state  

MY_HOURLY_TASK | MY_DB        | PUBLIC      | USER  |         | CRON 0 * * * * UTC | started

This command checks the recent run history of the task to see if it executed successfully.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY()) WHERE NAME = 'MY_HOURLY_TASK' ORDER BY SCHEDULED_TIME DESC LIMIT 5;

Expected Output

NAME           | SCHEDULED_TIME       | STATE   | ERROR

MY_HOURLY_TASK | 2024-06-01 12:00:00 | SUCCEEDED | NULL

MY_HOURLY_TASK | 2024-06-01 11:00:00 | SUCCEEDED | NULL

MY_HOURLY_TASK | 2024-06-01 10:00:00 | SUCCEEDED | NULL

Key Concept

If you remember nothing else from this pattern, remember: Snowflake Tasks automate SQL by running queries on a schedule using a warehouse.

Common Mistakes

Creating a task but forgetting to resume it.

The task stays paused and never runs on schedule.

Always run ALTER TASK task_name RESUME after creating the task.

Using an invalid cron expression in the SCHEDULE clause.

The task creation fails or the schedule does not work as expected.

Use valid cron syntax and test schedules carefully.

Not specifying a warehouse for the task.

The task cannot run because it has no compute resource.

Always specify WAREHOUSE when creating a task.

Summary

Create a task with CREATE TASK specifying warehouse and schedule.

Start the task with ALTER TASK ... RESUME to enable scheduled runs.

Check task status with SHOW TASKS and recent runs with TASK_HISTORY.