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.

Practice

1. What is the main purpose of a TASK in Snowflake?

easy

A. To create user accounts

B. To store large amounts of data

C. To automate running SQL statements on a schedule

D. To manage network security

Solution

1. Step 1: Understand the role of TASK in Snowflake

   A TASK is designed to run SQL commands automatically at set times.

2. Step 2: Compare TASK purpose with other options

   Storing data, creating users, and managing security are handled by other Snowflake features, not TASKS.

3. Final Answer:

   To automate running SQL statements on a schedule -> Option C

4. Quick Check:

   Task = automate SQL schedule [OK]

Hint: Tasks run SQL automatically on schedule [OK]

Common Mistakes:

• Confusing TASK with data storage
• Thinking TASK creates users
• Assuming TASK manages security

2. Which of the following is the correct syntax to create a simple task that runs every hour in Snowflake?

easy

A. CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'USING CRON 0 * * * * UTC' AS SELECT CURRENT_TIMESTAMP();

B. CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'EVERY 1 HOUR' AS SELECT CURRENT_TIMESTAMP();

C. CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'CRON 0 0 * * *' AS SELECT CURRENT_TIMESTAMP();

D. CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'RUN EVERY HOUR' AS SELECT CURRENT_TIMESTAMP();

Solution

1. Step 1: Identify correct cron syntax for hourly schedule

   The cron expression '0 * * * * UTC' means at minute 0 of every hour in UTC time.

2. Step 2: Check Snowflake task syntax

   Snowflake requires SCHEDULE = 'USING CRON ...' format, so CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'USING CRON 0 * * * * UTC' AS SELECT CURRENT_TIMESTAMP(); matches this correctly.

3. Final Answer:

   CREATE TASK my_task WAREHOUSE = my_wh SCHEDULE = 'USING CRON 0 * * * * UTC' AS SELECT CURRENT_TIMESTAMP(); -> Option A

4. Quick Check:

   Use 'USING CRON' with correct cron string [OK]

Hint: Use 'USING CRON' with proper cron syntax for schedules [OK]

Common Mistakes:

• Omitting 'USING CRON' keyword
• Using invalid cron expressions
• Trying unsupported schedule keywords

3. Given the following task creation:

CREATE TASK daily_task WAREHOUSE = wh1 SCHEDULE = 'USING CRON 0 0 * * * UTC' AS INSERT INTO logs VALUES (CURRENT_TIMESTAMP());

What happens if you do NOT run ALTER TASK daily_task RESUME; after creation?

medium

A. The task runs immediately once and then stops

B. The task runs only when manually triggered

C. The task runs continuously without stopping

D. The task will not run automatically until resumed

Solution

1. Step 1: Understand task state after creation

   By default, a newly created task is in a suspended state and does not run automatically.

2. Step 2: Effect of not resuming the task

   Without running ALTER TASK ... RESUME, the task stays suspended and will not execute on schedule.

3. Final Answer:

   The task will not run automatically until resumed -> Option D

4. Quick Check:

   New tasks are suspended until resumed [OK]

Hint: Always resume tasks after creation to enable scheduling [OK]

Common Mistakes:

• Assuming tasks run immediately after creation
• Thinking tasks run continuously without control
• Believing tasks run only manually by default

4. You wrote this task creation statement:

CREATE TASK my_task WAREHOUSE = wh1 SCHEDULE = 'USING CRON 60 * * * * UTC' AS SELECT 1;

When you try to resume the task, it fails. What is the problem?

medium

A. The warehouse name is missing

B. The cron expression has an invalid minute value

C. The SQL statement is invalid

D. The task name is not allowed

Solution

1. Step 1: Check the cron expression

   Cron minute values must be between 0 and 59. '60' is invalid.

2. Step 2: Validate other parts

   Warehouse is specified, SQL is valid, and task name is allowed, so no issues there.

3. Final Answer:

   The cron expression has an invalid minute value -> Option B

4. Quick Check:

   Cron minutes must be 0-59 [OK]

Hint: Cron minutes must be 0-59, not 60 [OK]

Common Mistakes:

• Using 60 as a minute in cron
• Ignoring cron syntax errors
• Assuming warehouse or SQL errors cause failure

5. You want to create a task that runs a cleanup SQL every day at 3:30 AM UTC and uses warehouse 'clean_wh'. Which of the following is the correct way to create and enable this task?

hard

A. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME;

B. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'EVERY DAY AT 3:30' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME;

C. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 3 30 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME;

D. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * *' AS DELETE FROM temp_table;

Solution

1. Step 1: Verify cron expression for 3:30 AM UTC

   The correct cron is '30 3 * * * UTC' meaning minute 30, hour 3.

2. Step 2: Check task creation and enabling

   Task must be created with warehouse, schedule, SQL, and then resumed with ALTER TASK ... RESUME.

3. Step 3: Compare options

   CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME; uses correct cron, warehouse, SQL, and resumes the task. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'EVERY DAY AT 3:30' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME; uses invalid schedule syntax. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 3 30 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME; swaps hour and minute. CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * *' AS DELETE FROM temp_table; misses RESUME command.

4. Final Answer:

   CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME; -> Option A

5. Quick Check:

   Correct cron + resume task = CREATE TASK cleanup_task WAREHOUSE = clean_wh SCHEDULE = 'USING CRON 30 3 * * * UTC' AS DELETE FROM temp_table; ALTER TASK cleanup_task RESUME; [OK]

Hint: Use correct cron and always resume task after creation [OK]

Common Mistakes:

• Swapping hour and minute in cron
• Forgetting to resume the task
• Using unsupported schedule syntax