Automating Report Data with Snowflake Tasks and Governed Access

1.Building repeatable refreshes with tasks

A report is only as current as the tables it reads. Tasks are how Snowflake keeps those tables current without anyone running SQL by hand. A task can run SQL statements or stored procedures. It runs either on a schedule or when an event happens, such as new data arriving in a stream.

For a fixed schedule, set the SCHEDULE parameter to an interval such as '60 MINUTES'. To run at particular times or on particular days, use a USING CRON expression with a time zone. Snowflake runs only one instance of a scheduled task at a time. If a run is still going when the next scheduled time arrives, that scheduled time is skipped.

CREATE TASK SCHEDULED_T1
  WAREHOUSE='COMPUTE_WH'
  SCHEDULE='60 MINUTES'
  AS SELECT 1;

Sometimes new data arrives at unpredictable times. In that case a triggered task is a better fit. It runs when SYSTEM$STREAM_HAS_DATA reports new rows in a stream, so nothing has to poll the source repeatedly, and data is processed as soon as it lands.

CREATE TASK triggered_task_stream
  WHEN SYSTEM$STREAM_HAS_DATA('orders_stream')
  AS
    INSERT INTO completed_promotions
    SELECT order_id, order_total, order_time, promotion_id
    FROM orders_stream;

Creating a task does not start it. Every new task starts out suspended. EXECUTE TASK runs it once, which is the way to test it. ALTER TASK … RESUME lets it follow its schedule or watch for events from then on. Any outside service that can sign in to your account and is allowed to run SQL can also call EXECUTE TASK. That lets an external pipeline start a Snowflake task.

Sources1

2.Serverless or user-managed compute

Every task needs compute. The WAREHOUSE parameter decides which kind. Leave it out and the task is serverless: Snowflake predicts the resources each run needs, based on recent runs, and assigns them automatically. You can limit the size with SERVERLESS_TASK_MIN_STATEMENT_SIZE and SERVERLESS_TASK_MAX_STATEMENT_SIZE. Include WAREHOUSE and the task is user-managed: it runs on a warehouse you choose and size yourself. For either model, the role that runs the task needs the global EXECUTE MANAGED TASK privilege.

Sources1

3.Chaining refresh steps and handling failures

A reporting refresh often has several steps: load the data, then build the aggregates, then clean up. A task graph runs those steps in order. Its root task sets the schedule. Each child task names its parents with AFTER. Children that share a parent run in parallel. A task with several parents waits until all of them finish. An optional finalizer task, created with FINALIZE, runs after everything else and can clean up or send notifications. All tasks in a graph must have the same owner and be in the same database and schema.

CREATE TASK task_c
   ?  task_a, task_b
  AS SELECT 1;

Unattended jobs need protection against failure. SUSPEND_TASK_AFTER_NUM_FAILURES automatically suspends a task after the given number of consecutive failed or timed-out runs. This stops a broken task from using credits over and over. Task graphs default to suspending after 10 consecutive failures. TASK_AUTO_RETRY_ATTEMPTS retries a failed task. It is off by default. Set on a root task, it retries the whole graph straight away when a child fails. To change a task inside a scheduled graph, suspend the root task first. A run already in progress will finish, and the child tasks don't need to be suspended one by one.

Sources21

4.Operationalizing data for report consumers

Once tasks keep the data fresh, consumers need a stable and governed way to read it. Two tools from the access-control docs handle this.

The first is a secure view. Create one with the SECURE keyword on CREATE VIEW. Only users granted the role that owns the view can see its definition. Other users who run SHOW VIEWS, call GET_DDL, or query the Information Schema VIEWS view do not get the definition. That lets you show curated metrics without revealing the logic and base tables behind them.

The second tool is a future grant, which handles a reporting schema that keeps gaining tables. A future grant sets privileges that are applied automatically to each new object of a given type as it is created. Pair it with a grant on ALL existing objects, and the BI role can read today's tables and every table added later.

-- Grant the SELECT privilege on all new tables in a schema to role R2
GRANT SELECT ON FUTURE TABLES IN SCHEMA s1 TO ROLE r2;

-- Grant the SELECT privilege on all existing tables in a schema to role R2
GRANT SELECT ON ALL TABLES IN SCHEMA s1 TO ROLE r2;

Sources34

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A task starts running on its schedule as soon as CREATE TASK succeeds.Why is that wrong?

   New tasks are created suspended. They only follow their schedule after ALTER TASK … RESUME. EXECUTE TASK runs them once.

   Covered in Building repeatable refreshes with tasks

2. 2.GRANT SELECT ON ALL TABLES IN SCHEMA also covers tables created after the grant.Why is that wrong?

   ALL covers only tables that exist when you run it. A FUTURE grant is what gives privileges automatically on new objects.

   Covered in Operationalizing data for report consumers

Practise it for real

Create an hourly refresh task, test it, and switch it on

1. 1.Run CREATE TASK SCHEDULED_T1 with WAREHOUSE='COMPUTE_WH' and SCHEDULE='60 MINUTES' AS SELECT 1;

   Why: Including WAREHOUSE makes this a user-managed task with a fixed hourly schedule.

   You should see: The task is created in a suspended state.

2. 2.Run EXECUTE TASK SCHEDULED_T1;

   Why: A single manual run checks the task before it goes live.

   You should see: One run of the task appears in the task history.

3. 3.Run ALTER TASK SCHEDULED_T1 RESUME;

   Why: Tasks only follow their schedule once they are resumed.

   You should see: The task now runs every 60 minutes.

4. 4.Run ALTER TASK SCHEDULED_T1 SET SUSPEND_TASK_AFTER_NUM_FAILURES = 3;

   Why: Automatic suspension stops a broken refresh from using credits again and again.

   You should see: After three consecutive failed or timed-out runs, the task suspends itself.