Tasks for scheduling SQL in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

Task Scheduling Frequency Behavior

You create a Snowflake task with a schedule set to run every 10 minutes. The task runs a SQL statement that inserts data into a table. What happens if the previous run of the task is still executing when the next scheduled time arrives?

A The new run is skipped and will not start until the next scheduled time after the current run finishes.

B The new run starts immediately in parallel with the current run.

C The task scheduler throws an error and disables the task.

D The new run queues and waits until the current run finishes, then starts immediately.

💡 Hint

Think about how Snowflake handles overlapping task executions.

Snowflake tasks do not run concurrently. If a scheduled run time occurs while the previous run is still executing, that run is skipped. The task waits for the next scheduled time to run again.

❓ Configuration

intermediate

Correct Syntax for Creating a Scheduled Task

Which of the following SQL statements correctly creates a Snowflake task that runs every hour and executes a simple insert statement?

A CREATE TASK hourly_task WAREHOUSE = mywh SCHEDULE = '1 HOUR' AS INSERT INTO my_table VALUES (CURRENT_TIMESTAMP());

B CREATE TASK hourly_task WAREHOUSE = mywh SCHEDULE = 'USING CRON * * * * * UTC' AS INSERT INTO my_table VALUES (CURRENT_TIMESTAMP());

C CREATE TASK hourly_task WAREHOUSE = mywh SCHEDULE = 'USING CRON 0 0 * * * UTC' AS INSERT INTO my_table VALUES (CURRENT_TIMESTAMP());

D CREATE TASK hourly_task WAREHOUSE = mywh SCHEDULE = 'USING CRON 0 * * * * UTC' AS INSERT INTO my_table VALUES (CURRENT_TIMESTAMP());

💡 Hint

Check the cron expression for running every hour at minute zero.

The cron expression '0 * * * * UTC' runs the task every hour at minute zero. Option D uses the correct syntax and cron expression.

❓ Architecture

advanced

Designing a Task Chain for Data Pipeline

You want to design a Snowflake task chain where Task A loads raw data, Task B transforms it, and Task C aggregates results. Each task should start only after the previous one completes successfully. Which architecture best achieves this?

A Create three independent scheduled tasks with the same schedule time.

B Create one task that runs all three SQL statements sequentially in a single script.

C Create Task A with a schedule, then create Task B and Task C as tasks triggered by the completion of the previous task.

D Create Task A with a schedule, and Task B and Task C scheduled with a delay to allow previous tasks to finish.

💡 Hint

Think about how Snowflake tasks can trigger other tasks.

Snowflake supports task dependencies where one task triggers another upon successful completion. This ensures sequential execution without overlap.

❓ security

advanced

Task Execution Permissions

Which role must own a Snowflake task to ensure it can execute SQL statements that modify tables in a specific schema?

A The role that owns the task must have EXECUTE privilege on the task only.

B The role that owns the task must have OWNERSHIP on the target schema and tables.

C Any role with USAGE privilege on the warehouse only.

D Any role with SELECT privilege on the tables.

💡 Hint

Consider what privileges are needed to modify data in tables.

The task owner role must have ownership or appropriate privileges on the schema and tables to perform data modifications during task execution.

✅ Best Practice

expert

Handling Task Failures Gracefully

You have a critical Snowflake task that runs daily to update reports. Sometimes it fails due to transient errors like network issues. What is the best practice to ensure the task recovers and completes successfully without manual intervention?

A Configure the task with a retry mechanism using a separate monitoring script to rerun failed tasks.

B Create a task chain with a downstream task that checks the previous task's success and reruns it if failed.

C Use a task with a schedule and a conditional SQL statement that retries the operation internally on failure.

D Manually monitor the task daily and rerun it if it fails.

💡 Hint

Think about automation and monitoring outside the task itself.

Snowflake tasks do not have built-in retry logic. Best practice is to use external monitoring and automation to detect failures and rerun tasks as needed.