Why pipelines automate data freshness in Snowflake - See It in Action

Why pipelines automate data freshness

📖 Scenario: You work in a company that uses Snowflake to store and analyze sales data. The sales data is updated daily, and you want to make sure the reports always show the latest information without manual updates.

🎯 Goal: Build a simple Snowflake pipeline that automates data freshness by loading new sales data daily and updating a summary table automatically.

📋 What You'll Learn

Create a table to hold raw sales data

Create a configuration variable for the data load schedule

Write a SQL command to load new data into the raw sales table

Create a task that runs daily to refresh the summary table automatically

💡 Why This Matters

🌍 Real World

Automating data freshness ensures reports and dashboards always show the latest information without manual work.

💼 Career

Data engineers and cloud architects use pipelines and tasks in Snowflake to maintain reliable, up-to-date data for business decisions.

1

Create the raw sales data table

Create a table called raw_sales with columns sale_id (integer), sale_date (date), and amount (number).

Snowflake

-- Your code here to create the raw_sales table

Hint

Use CREATE OR REPLACE TABLE followed by the table name and column definitions.

2

Set the data load schedule variable

Create a variable called data_load_schedule and set it to 'USING CRON 0 0 * * * UTC' to run daily at midnight UTC.

Snowflake

CREATE OR REPLACE TABLE raw_sales (
  sale_id INT,
  sale_date DATE,
  amount NUMBER
);

-- Your code here to create the data_load_schedule variable

Hint

Use SET to create a session variable with the cron schedule string.

3

Write the SQL command to load new sales data

Write a SQL command called load_new_sales that inserts new rows into raw_sales from a stage called @sales_stage using COPY INTO raw_sales.

Snowflake

CREATE OR REPLACE TABLE raw_sales (
  sale_id INT,
  sale_date DATE,
  amount NUMBER
);

SET data_load_schedule = 'USING CRON 0 0 * * * UTC';

-- Your code here to write the COPY INTO command named load_new_sales

Hint

Use CREATE OR REPLACE TASK with the schedule variable and COPY INTO to load data.

4

Create a task to refresh the summary table automatically

Create a task called refresh_summary that runs daily using the data_load_schedule variable and updates a summary table daily_sales_summary by aggregating raw_sales data.

Snowflake

CREATE OR REPLACE TABLE raw_sales (
  sale_id INT,
  sale_date DATE,
  amount NUMBER
);

SET data_load_schedule = 'USING CRON 0 0 * * * UTC';

CREATE OR REPLACE TASK load_new_sales
  WAREHOUSE = my_warehouse
  SCHEDULE = $data_load_schedule
AS
COPY INTO raw_sales
FROM @sales_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

-- Your code here to create the refresh_summary task

Hint

Use CREATE OR REPLACE TASK with a MERGE INTO statement to update the summary table.

Practice

1. What is the main reason to use tasks in Snowflake for data freshness?

easy

A. To automate data updates without manual steps

B. To store data permanently without changes

C. To manually refresh data when needed

D. To delete old data automatically

Solution

1. Step 1: Understand task purpose

   Tasks are designed to automate data updates, so they run without manual work.

2. Step 2: Identify automation benefit

   Automating data updates keeps data fresh and ready without needing manual refreshes.

3. Final Answer:

   To automate data updates without manual steps -> Option A

4. Quick Check:

   Automation means no manual steps [OK]

Hint: Tasks run automatically to keep data fresh [OK]

Common Mistakes:

• Thinking tasks only store data
• Assuming manual refresh is needed
• Confusing deletion with freshness

2. Which syntax correctly creates a Snowflake task that runs every hour?

easy

A. CREATE TASK my_task SCHEDULE = '1 HOUR' AS COPY INTO my_table FROM @my_stage;

B. CREATE TASK my_task SCHEDULE = 'USING CRON 0 * * * *' AS COPY INTO my_table FROM @my_stage;

C. CREATE TASK my_task SCHEDULE = '60 MINUTES' AS COPY INTO my_table FROM @my_stage;

D. CREATE TASK my_task SCHEDULE = 'EVERY HOUR' AS COPY INTO my_table FROM @my_stage;

Solution

1. Step 1: Check Snowflake task schedule syntax

   Snowflake uses CRON expressions for scheduling tasks, e.g., 'USING CRON 0 * * * *' means every hour at minute 0.

2. Step 2: Compare options

   Only CREATE TASK my_task SCHEDULE = 'USING CRON 0 * * * *' AS COPY INTO my_table FROM @my_stage; uses valid CRON syntax. Options A, B, and D use incorrect or unsupported schedule formats.

3. Final Answer:

   CREATE TASK my_task SCHEDULE = 'USING CRON 0 * * * *' AS COPY INTO my_table FROM @my_stage; -> Option B

4. Quick Check:

   Snowflake tasks use CRON for schedules [OK]

Hint: Snowflake task schedules use CRON syntax [OK]

Common Mistakes:

• Using plain time strings instead of CRON
• Writing 'EVERY HOUR' instead of CRON
• Confusing minutes and hours format

3. Given this task code:

CREATE TASK daily_update SCHEDULE = 'USING CRON 0 0 * * *' AS
COPY INTO sales FROM @sales_stage;

What happens when this task runs?

medium

A. It copies new data from sales_stage to sales table daily at midnight

B. It deletes all data from sales table daily at midnight

C. It copies data every minute from sales_stage to sales table

D. It runs only once when created

Solution

1. Step 1: Interpret CRON schedule

   'USING CRON 0 0 * * *' means the task runs daily at midnight (00:00 hours).

2. Step 2: Understand task action

   The task copies data from the stage 'sales_stage' into the 'sales' table each time it runs.

3. Final Answer:

   It copies new data from sales_stage to sales table daily at midnight -> Option A

4. Quick Check:

   CRON midnight + COPY INTO = daily data update [OK]

Hint: CRON 0 0 * * * means daily at midnight [OK]

Common Mistakes:

• Thinking it deletes data
• Assuming it runs every minute
• Believing it runs only once

4. You created a task but it never runs. Which fix will most likely solve this?

medium

A. Rename the task to a different name

B. Delete the task and recreate the table

C. Check if the task schedule uses valid CRON syntax

D. Run the COPY command manually every hour

Solution

1. Step 1: Identify common task issue

   If a task never runs, an invalid schedule is a common cause, especially if CRON syntax is wrong.

2. Step 2: Apply fix

   Correcting the schedule to valid CRON syntax enables the task to run automatically as expected.

3. Final Answer:

   Check if the task schedule uses valid CRON syntax -> Option C

4. Quick Check:

   Invalid schedule blocks task runs [OK]

Hint: Invalid CRON stops task from running [OK]

Common Mistakes:

• Deleting unrelated objects
• Running commands manually defeats automation
• Renaming does not fix schedule issues

5. You want to ensure your sales data is always fresh by running a task every 15 minutes. Which approach is best?

hard

A. Create a task without schedule and run it once daily

B. Manually run COPY commands every 15 minutes

C. Create a task with schedule 'USING CRON 15 * * * *' to run once per hour

D. Create a task with schedule 'USING CRON */15 * * * *' to automate data copy

Solution

1. Step 1: Understand CRON for 15-minute intervals

   'USING CRON */15 * * * *' means run every 15 minutes, which fits the requirement.

2. Step 2: Compare options for automation

   Create a task with schedule 'USING CRON */15 * * * *' to automate data copy automates the data copy every 15 minutes. Manually run COPY commands every 15 minutes is manual and not automated. Create a task with schedule 'USING CRON 15 * * * *' to run once per hour runs only once per hour, not every 15 minutes. Create a task without schedule and run it once daily runs once daily, too infrequent.

3. Final Answer:

   Create a task with schedule 'USING CRON */15 * * * *' to automate data copy -> Option D

4. Quick Check:

   CRON '*/15' means every 15 minutes [OK]

Hint: Use '*/15' in CRON for every 15 minutes [OK]

Common Mistakes:

• Choosing manual runs over automation
• Misunderstanding CRON syntax for intervals
• Setting schedule too infrequent