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

Need a 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

Need a 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

Need a 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

Need a hint?

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