Tasks for scheduling SQL in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a scheduled task that runs every hour.

Snowflake

CREATE OR REPLACE TASK hourly_task
  WAREHOUSE = my_warehouse
  SCHEDULE = [1]
AS
  SELECT CURRENT_TIMESTAMP();

Drag options to blanks, or click blank then click option'

A'1 MINUTE'

B'1 DAY'

C'1 HOUR'

D'10 MINUTES'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '1 MINUTE' runs the task too frequently.

Using '1 DAY' runs the task too rarely.

The schedule string '1 HOUR' sets the task to run every hour.

2fill in blank

medium

Complete the code to enable the scheduled task after creation.

Snowflake

ALTER TASK hourly_task [1];

Drag options to blanks, or click blank then click option'

ARESUME

BSTART

CSUSPEND

DPAUSE

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUSPEND prevents the task from running.

PAUSE is not a valid command for tasks.

The RESUME command activates the task so it can run on schedule.

3fill in blank

hard

Fix the error in the task creation by completing the missing clause.

Snowflake

CREATE OR REPLACE TASK daily_task
  WAREHOUSE = my_warehouse
  SCHEDULE = [1]
AS
  CALL my_procedure();

Drag options to blanks, or click blank then click option'

A'1 DAY'

B'1 MINUTE'

C'1 WEEK'

D'1 MONTH'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '1 MINUTE' runs the task too often.

Using '1 WEEK' or '1 MONTH' runs it less frequently than daily.

The schedule '1 DAY' runs the task once every day, which fits the daily_task name.

4fill in blank

hard

Fill both blanks to create a task that runs every 15 minutes and uses the correct warehouse.

Snowflake

CREATE OR REPLACE TASK quarter_hour_task
  WAREHOUSE = [1]
  SCHEDULE = [2]
AS
  SELECT CURRENT_DATE();

Drag options to blanks, or click blank then click option'

Amy_warehouse

B'15 MINUTES'

C15 MINUTES

Ddefault_warehouse

Attempts:

3 left

💡 Hint

Common Mistakes

Putting quotes around warehouse name.

Using wrong schedule interval.

Use the correct warehouse name and the schedule string '15 MINUTES' for a 15-minute interval.

5fill in blank

hard

Fill all three blanks to create a task that runs weekly, uses the correct warehouse, and calls the right procedure.

Snowflake

CREATE OR REPLACE TASK weekly_task
  WAREHOUSE = [1]
  SCHEDULE = [2]
AS
  CALL [3]();

Drag options to blanks, or click blank then click option'

Aanalytics_warehouse

B'1 WEEK'

Cweekly_refresh

Ddaily_refresh

Attempts:

3 left

💡 Hint

Common Mistakes

Using daily procedure name for weekly task.

Wrong warehouse name.

Incorrect schedule string.

The warehouse is 'analytics_warehouse', schedule is '1 WEEK' for weekly runs, and the procedure called is 'weekly_refresh'.

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