Scheduling Snowpark Jobs via Tasks
Once you have written your Python ELT pipeline and registered it as a Snowpark Stored Procedure, it sits in Snowflake waiting to be called. To fully automate your pipeline so you can close your laptop and sleep, you must schedule the stored procedure using a Snowflake Task.
Topics Covered
- Calling a Python Procedure from a Task
- Passing Parameters
- Creating a Snowpark Task DAG
- Monitoring Task History
1. Calling a Python Procedure from a Task
Let's assume we have previously registered a Snowpark Stored Procedure named ARCHIVE_LOGS_SPROC. We want this to run automatically every night at 2:00 AM IST (Indian Standard Time).
(To create tasks, you need the EXECUTE TASK privilege. To verify your current role, run SELECT CURRENT_ROLE();)
USE ROLE SYSADMIN;
-- Create the Task
CREATE OR REPLACE TASK DAILY_LOG_ARCHIVE_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = 'USING CRON 0 2 * * * Asia/Kolkata'
AS
-- Call the Snowpark procedure and pass the required parameters
CALL ARCHIVE_LOGS_SPROC('SYSTEM_LOGS', 30);
-- Tasks are created in a 'Suspended' state by default. You MUST resume it.
ALTER TASK DAILY_LOG_ARCHIVE_TASK RESUME;
2. Creating a Snowpark Task DAG
In complex Data Engineering pipelines, you rarely have just one step. Usually, you have an Extract step, followed by a Transform step, followed by an Aggregation step.
Snowflake Tasks allow you to build a DAG (Directed Acyclic Graph). A downstream task will automatically wait for its upstream parent task to finish before it starts executing.
Let's build a 3-step Python pipeline.
-- Step 1: The Root Task (Scheduled)
CREATE OR REPLACE TASK EXTRACT_RAW_JSON_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE = '60 MINUTE' -- Runs every hour
AS
CALL LOAD_RAW_JSON_SPROC();
-- Step 2: The Transform Task (Dependent on Step 1)
CREATE OR REPLACE TASK CLEAN_JSON_DATA_TASK
WAREHOUSE = COMPUTE_WH
AFTER EXTRACT_RAW_JSON_TASK -- This creates the dependency link
AS
CALL CLEAN_DATA_SPROC();
-- Step 3: The Aggregation Task (Dependent on Step 2)
CREATE OR REPLACE TASK UPDATE_EXECUTIVE_DASHBOARD_TASK
WAREHOUSE = COMPUTE_WH
AFTER CLEAN_JSON_DATA_TASK
AS
CALL UPDATE_DASHBOARD_SPROC();
-- IMPORTANT: You must resume them in reverse order (Children first, Root last)
ALTER TASK UPDATE_EXECUTIVE_DASHBOARD_TASK RESUME;
ALTER TASK CLEAN_JSON_DATA_TASK RESUME;
ALTER TASK EXTRACT_RAW_JSON_TASK RESUME;
3. Monitoring Snowpark Task Execution
Once your Python pipeline is running automatically, you need to monitor it. If the Python code inside CLEAN_DATA_SPROC throws an exception (e.g., an IndexError or a Snowpark SnowflakeSQLException), the Task will fail.
You check the status of tasks using the TASK_HISTORY table function.
-- View the history of the root task over the last 7 days
SELECT
NAME AS TASK_NAME,
STATE,
SCHEDULED_TIME,
COMPLETED_TIME,
ERROR_MESSAGE
FROM table(information_schema.task_history(
task_name=>'EXTRACT_RAW_JSON_TASK',
scheduled_time_range_start=>dateadd('day', -7, current_timestamp())
))
ORDER BY SCHEDULED_TIME DESC;
If the STATE is FAILED, the ERROR_MESSAGE column will contain the exact Python stack trace generated by your Snowpark Stored Procedure, making debugging very straightforward.
4. Best Practices
- Idempotency: Ensure the Python code inside your stored procedure is idempotent. If the task fails halfway through and automatically retries an hour later, it should not duplicate data. Use
.write.mode("overwrite")or handleMERGElogic carefully. - Error Alerts: Do not rely on manually checking
TASK_HISTORY. Create a Snowflake Alert (or use an Error Integration) to automatically send an email or Slack message if theSTATEinTASK_HISTORYequalsFAILED. - Session State: Remember that a Snowflake Task spins up a brand new Python environment and Snowpark Session every time it runs. Variables do not persist across task runs.
Summary
- A Task is the scheduling and orchestration engine for Snowpark Stored Procedures.
- Use
SCHEDULEto define when the root task runs (using minutes or CRON). - Use
AFTERto link multiple Snowpark procedures together into a DAG. - Query
TASK_HISTORYto view the execution status and Python error messages.