Can you write an SQL statement to schedule a job that creates a task?

Question

Assisting AI · Accepted Answer

In Snowflake, you can create a task that itself creates another task by using a stored procedure or a SQL script. Here’s a simple example that creates a child task when the parent runs:

```sql
-- Parent task that runs every hour
CREATE OR REPLACE TASK parent_task
  WAREHOUSE = my_wh
  SCHEDULE = 'USING CRON 0 * * * * UTC'
AS
  CALL create_child_task();

-- Stored procedure to create the child task
CREATE OR REPLACE PROCEDURE create_child_task()
RETURNS STRING
LANGUAGE SQL
AS
$$
  CREATE OR REPLACE TASK child_task
    WAREHOUSE = my_wh
    SCHEDULE = 'USING CRON 30 * * * * UTC'
  AS
    INSERT INTO audit_log (event, ts) VALUES ('child task created', CURRENT_TIMESTAMP());
  RETURN 'Child task created';
$$;
```

The parent task triggers the stored procedure, which creates the child task. The child task runs at 30 minutes past every hour. This demonstrates how to programmatically schedule jobs using SQL in Snowflake.

Can you write an SQL statement to schedule a job that creates a task?

💡 Model Answer

🎤 Get questions like this answered in real-time