Snowflake SQL Task Demonstration

Introduction

This document provides a comprehensive guide to working with Snowflake tasks. It includes SQL code examples, detailed instructions, and best practices for creating, managing, and executing tasks in Snowflake.

---

Setting Up the Environment

Clean Slate Setup

Recreate the ECOMMERCEDB.ECOMMERCESCHEMA. Ensure no previous tables, views, functions, etc., exist.

---

Creating Base Tables

Sales Table

CREATE OR REPLACE TABLE SALES (
  sale_id INT IDENTITY(1,1) PRIMARY KEY,
  product_name VARCHAR(100),
  category VARCHAR(50),
  region VARCHAR(50),
  quantity INT,
  price DECIMAL(10, 2),
  sale_date DATE
);

Asia Sales Table

CREATE OR REPLACE TABLE ASIA_SALES(
  sale_id INT IDENTITY(1,1) PRIMARY KEY,
  product_name VARCHAR(100),
  category VARCHAR(50),
  region VARCHAR(50),
  quantity INT,
  price DECIMAL(10, 2),
  sale_date DATE
);

Daily Sales Summary Table

CREATE OR REPLACE TABLE DAILY_SALES_SUMMARY (
  summary_date DATE,
  total_daily_sales DECIMAL(12,2),
  total_daily_quantity INT
);

---

Inserting Data into Base Tables

Initial Data for Sales Table

INSERT INTO SALES (product_name, category, region, quantity, price, sale_date)
VALUES
('Laptop', 'Electronics', 'North America', 2, 750.00, '2024-07-19'),
('Smartphone', 'Electronics', 'Europe', 1, 799.99, '2024-07-19'),
('Tablet', 'Electronics', 'Asia', 3, 299.99, '2024-07-20'),
('Laptop', 'Electronics', 'North America', 1, 750.00, '2024-07-22'),
('Smartphone', 'Electronics', 'Europe', 2, 799.99, '2024-07-22'),
('Blender', 'Home Appliances', 'Asia', 1, 99.99, '2024-07-21'),
('Microwave', 'Home Appliances', 'North America', 1, 199.99, '2024-07-21'),
('Toaster', 'Home Appliances', 'Europe', 2, 49.99, '2024-07-18');

---

Creating and Managing Tasks

Scheduled Task: Track Asia Sales

CREATE OR REPLACE TASK TRACK_ASIA_SALES
  SCHEDULE = '3 minutes'
AS
  MERGE INTO ASIA_SALES AS target
  USING (
    SELECT sale_id, product_name, category, region, quantity, price, sale_date
    FROM SALES
    WHERE region = 'Asia'
  ) AS source
  ON target.sale_id = source.sale_id
  WHEN NOT MATCHED THEN 
    INSERT (sale_id, product_name, category, region, quantity, price, sale_date)
    VALUES (source.sale_id, source.product_name, source.category, source.region, source.quantity, source.price, source.sale_date);

Activating and Verifying the Task

ALTER TASK TRACK_ASIA_SALES RESUME;
SHOW TASKS;
DESCRIBE TASK TRACK_ASIA_SALES;

---

Advanced Tasks

Scheduled Task with Virtual Warehouse

CREATE OR REPLACE TASK SUMMARIZE_DAILY_SALES
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 0 4 * * * America/New_York'
AS
  MERGE INTO DAILY_SALES_SUMMARY AS target
  USING (
    SELECT 
      sale_date AS summary_date,
      SUM(price * quantity) AS total_sales,
      SUM(quantity) AS total_quantity
    FROM SALES
    GROUP BY sale_date
  ) AS source
  ON target.summary_date = source.summary_date
  WHEN MATCHED THEN
    UPDATE SET
      target.total_daily_sales = source.total_sales,
      target.total_daily_quantity = source.total_quantity
  WHEN NOT MATCHED THEN
    INSERT (summary_date, total_daily_sales, total_daily_quantity)
    VALUES (source.summary_date, source.total_sales, source.total_quantity);

---

Task Graphs and Dependencies

Weekly Sales Summary Task

CREATE OR REPLACE TASK SUMMARIZE_WEEKLY_SALES
  WAREHOUSE = COMPUTE_WH
  AFTER SUMMARIZE_DAILY_SALES
AS
  MERGE INTO WEEKLY_SALES_SUMMARY AS target
  USING (
    SELECT 
      DATE_TRUNC('week', sale_date) AS summary_week,
      SUM(price * quantity) AS total_weekly_sales,
      SUM(quantity) AS total_weekly_quantity
    FROM SALES
    GROUP BY DATE_TRUNC('week', sale_date)
  ) AS source
  ON target.summary_week = source.summary_week
  WHEN MATCHED THEN
    UPDATE SET
      target.total_weekly_sales = source.total_weekly_sales,
      target.total_weekly_quantity = source.total_weekly_quantity
  WHEN NOT MATCHED THEN
    INSERT (summary_week, total_weekly_sales, total_weekly_quantity)
    VALUES (source.summary_week, source.total_weekly_sales, source.total_weekly_quantity);

---

Cleaning Up and Recap

Dropping Tasks and Tables

DROP TASK SUMMARIZE_DAILY_SALES;
DROP TASK SUMMARIZE_WEEKLY_SALES;
DROP TASK SUMMARIZE_MONTHLY_SALES;
TRUNCATE TABLE DAILY_SALES_SUMMARY;
TRUNCATE TABLE WEEKLY_SALES_SUMMARY;
TRUNCATE TABLE MONTHLY_SALES_SUMMARY;

---

Final Notes

This document covers fundamental and advanced task management in Snowflake, including examples of creating base tables, inserting data, and configuring both scheduled and event-based tasks. For additional details, refer to the Snowflake documentation.