Tasks for scheduling SQL in Snowflake - Mini Project: Build & Apply

Tasks for scheduling SQL in Snowflake

📖 Scenario: You are a data engineer managing a Snowflake data warehouse. You want to automate a SQL query that refreshes a summary table every day without manual intervention.

🎯 Goal: Build a Snowflake task that runs a SQL statement daily to update a summary table.

📋 What You'll Learn

Create a SQL statement that refreshes the summary table

Create a Snowflake task with a daily schedule

Enable the task to run automatically

Verify the task configuration

💡 Why This Matters

🌍 Real World

Automating data refreshes in Snowflake saves time and reduces manual errors in data pipelines.

💼 Career

Data engineers and cloud architects often schedule tasks in Snowflake to maintain up-to-date data for analytics.

1

Create the SQL statement to refresh the summary table

Create a SQL statement called refresh_summary_sql that contains the exact text: INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE()

Snowflake

# Create a variable called refresh_summary_sql with the exact SQL statement
# Your code here

Need a hint?

Use a string variable to hold the SQL statement exactly as shown.

2

Create a Snowflake task with a daily schedule

Write a SQL command to create a task named daily_refresh_task that runs the SQL in refresh_summary_sql every day at 1 AM UTC. Use the schedule string 'USING CRON 0 1 * * * UTC' and the SQL statement from refresh_summary_sql as the task body.

Snowflake

refresh_summary_sql = "INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE()"
-- Create the task daily_refresh_task with the given schedule and SQL
# Your code here

Need a hint?

Use CREATE OR REPLACE TASK with the exact task name and schedule. Include the SQL statement inside the task.

3

Enable the Snowflake task to run automatically

Write a SQL command to enable the task named daily_refresh_task so it can run on schedule.

Snowflake

refresh_summary_sql = "INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE()"
CREATE OR REPLACE TASK daily_refresh_task
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 1 * * * UTC'
AS
  INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE();
-- Enable the task daily_refresh_task
# Your code here

Need a hint?

Use ALTER TASK daily_refresh_task RESUME; to enable the task.

4

Verify the task configuration

Write a SQL query to show the details of the task named daily_refresh_task using SHOW TASKS LIKE 'daily_refresh_task'.

Snowflake

refresh_summary_sql = "INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE()"
CREATE OR REPLACE TASK daily_refresh_task
  WAREHOUSE = my_warehouse
  SCHEDULE = 'USING CRON 0 1 * * * UTC'
AS
  INSERT INTO summary_table SELECT * FROM raw_data WHERE date = CURRENT_DATE();

ALTER TASK daily_refresh_task RESUME;
-- Show the task details
# Your code here

Need a hint?

Use SHOW TASKS LIKE 'daily_refresh_task'; to verify the task.