The Night Everything Broke (And How Streams Saved Me)

It was 2 AM on a Tuesday. My phone was buzzing non-stop. Our nightly ETL job had failed—again. This time, it crashed after processing 6 hours of data, and we had to start over from scratch. The business needed fresh customer data by 8 AM for the morning reports.

I was running a classic batch process: every night at midnight, truncate the target table, reload everything from source, rebuild all the aggregations. It worked fine when we had 100,000 customers. But we’d grown to 5 million customers, and the full reload was taking 8 hours.

That’s when I discovered Streams and Tasks in Snowflake. Within a week, I rebuilt the entire pipeline:

  • No more full reloads (only process changes)
  • No more manual scheduling (Tasks handled it)
  • No more 8-hour batch windows (incremental updates took 10 minutes)
  • No more 2 AM phone calls (built-in retry logic)

This guide is everything I wish someone had shown me that night. We’ll build real pipelines—not toy examples—starting from simple automation and working up to complex patterns like SCD Type 2.

What Are Streams and Tasks? (The Simple Explanation)

Before diving into code, let’s understand what these actually do:

Streams are like security cameras for your tables. They watch what changes (inserts, updates, deletes) and create a “change log” you can query. Think of it as automatic change data capture (CDC) built into Snowflake.

Tasks are scheduled SQL jobs. They’re like cron jobs but smarter—they can depend on other tasks, run only when data is available, and auto-retry on failure.

Together? Magic. Streams detect changes, Tasks process them automatically.

The old way:

SQL 7 lines

SQL example — read the query, then copy it into your warehouse.

-- Run this manually or via cron at 2 AMTRUNCATE TABLE customer_summary;INSERT INTO customer_summary SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_spentFROM ordersGROUP BY customer_id;-- Takes hours, processes everything, fails if interrupted

The new way:

SQL 9 lines

SQL example — read the query, then copy it into your warehouse.

-- Stream watches for changesCREATE STREAM order_changes ON TABLE orders;-- Task processes only changes, runs automaticallyCREATE TASK update_customer_summary    SCHEDULE = '5 MINUTE'    WHEN SYSTEM$STREAM_HAS_DATA('order_changes')AS    -- Process only changed orders (10 seconds instead of 8 hours!)    MERGE INTO customer_summary ...

Let’s build this properly.

Part 1: Understanding Streams (Change Data Capture)

Creating Your First Stream

Creating Your First Stream: excerpt of this SQL example. This is a shortened excerpt of a 19-line script.
-- Setup: Create sample source table
CREATE OR REPLACE TABLE customers (
    customer_id INTEGER,
    customer_name STRING,
    email STRING,
    status STRING,
    created_date DATE,
    updated_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
…

The remaining 11 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

How Streams Capture Changes

Now let’s make some changes and see what the stream captures:

How Streams Capture Changes: excerpt of this SQL example. This is a shortened excerpt of a 14-line script.
-- Make various changes
UPDATE customers SET status = 'INACTIVE' WHERE customer_id = 1;
INSERT INTO customers VALUES (4, 'Alice Williams', '[email protected]', 'ACTIVE', '2024-04-05', CURRENT_TIMESTAMP());
DELETE FROM customers WHERE customer_id = 3;
-- Query the stream
SELECT 
    customer_id,
    customer_name,
…

The remaining 6 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Output:

SQL 5 lines

SQL example — read the query, then copy it into your warehouse.

customer_id | customer_name    | status   | METADATA$ACTION | METADATA$ISUPDATE1           | John Doe         | INACTIVE | INSERT          | TRUE1           | John Doe         | ACTIVE   | DELETE          | TRUE4           | Alice Williams   | ACTIVE   | INSERT          | FALSE3           | Bob Johnson      | ACTIVE   | DELETE          | FALSE

Understanding the output:

  1. UPDATE appears as DELETE (old value) + INSERT (new value) with METADATA$ISUPDATE = TRUE
  2. INSERT appears as INSERT with METADATA$ISUPDATE = FALSE
  3. DELETE appears as DELETE with METADATA$ISUPDATE = FALSE

Stream Consumption (Critical Concept)

Here’s something that confused me for weeks: streams are consumed when you read from them in a DML operation.

SQL 9 lines

SQL example — read the query, then copy it into your warehouse.

-- Query stream (doesn't consume)SELECT * FROM customer_changes;-- Stream still has data-- Use stream in INSERT (consumes!)INSERT INTO customer_backupSELECT * FROM customer_changes;-- Query stream againSELECT * FROM customer_changes;-- Returns 0 rows - stream was consumed!

Important patterns:

SQL 7 lines

SQL example — read the query, then copy it into your warehouse.

-- If you need the data multiple times, capture it firstCREATE TEMPORARY TABLE changes_snapshot ASSELECT * FROM customer_changes;-- Now use snapshot multiple timesINSERT INTO target1 SELECT * FROM changes_snapshot;INSERT INTO target2 SELECT * FROM changes_snapshot;-- Stream is only consumed once

Part 2: Understanding Tasks (Automation)

Creating Your First Task

Creating Your First Task: excerpt of this SQL example. This is a shortened excerpt of a 23-line script.
-- Simple task that runs on schedule
CREATE OR REPLACE TASK hello_world_task
    WAREHOUSE = my_wh
    SCHEDULE = '5 MINUTE'
AS
    INSERT INTO task_logs 
    VALUES ('Hello from task!', CURRENT_TIMESTAMP());
-- Tasks are created in SUSPENDED state
…

The remaining 15 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Conditional Execution (Run Only When Needed)

Conditional Execution (Run Only When Needed): excerpt of this SQL example. This is a shortened excerpt of a 16-line script.
-- Create task that only runs when stream has data
CREATE OR REPLACE TASK process_customer_changes
    WAREHOUSE = etl_wh
    SCHEDULE = '5 MINUTE'
    WHEN SYSTEM$STREAM_HAS_DATA('customer_changes')
AS
    INSERT INTO customer_history
    SELECT 
…

The remaining 8 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Why this is powerful:

  • Task checks every 5 minutes
  • Only runs if stream has data
  • Warehouse only spins up when needed
  • Zero cost if no changes

Task Dependencies (Building Pipelines)

Task Dependencies (Building Pipelines): excerpt of this SQL example. This is a shortened excerpt of a 49-line script.
-- Create a pipeline: raw → staging → production
-- Task 1: Load raw data
CREATE OR REPLACE TASK load_raw_data
    WAREHOUSE = etl_wh
    SCHEDULE = '10 MINUTE'
AS
    COPY INTO raw_orders
    FROM @s3_stage/orders/
…

The remaining 41 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Task dependency diagram:

Snippet 5 lines

Code example — copy the snippet, then match it to your project.

load_raw_data (every 10 min)    ↓stage_data (after load_raw_data completes)    ↓aggregate_to_prod (after stage_data completes)

Part 3: Real Use Case #1 – Simple Incremental Load

Scenario: Load customer orders from source system, keep only net changes.

Part 3: Real Use Case #1 – Simple Incremental Load: excerpt of this SQL example. This is a shortened excerpt of a 62-line script.
-- Source table (simulates external system)
CREATE OR REPLACE TABLE source_orders (
    order_id INTEGER,
    customer_id INTEGER,
    product_name STRING,
    amount DECIMAL(10,2),
    order_date DATE,
    updated_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
…

The remaining 54 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Why this works:

  • Stream captures all changes
  • Task runs only when changes exist
  • MERGE handles both new and updated records
  • Fully automated, zero manual intervention

Part 4: Real Use Case #2 – SCD Type 2 (The Big One)

This is the use case everyone asks about. Slowly Changing Dimensions Type 2 tracks full history of changes.

Business requirement: Track complete history of customer data changes over time.

Step 1: Create SCD2 Table Structure

Step 1: Create SCD2 Table Structure: excerpt of this SQL example. This is a shortened excerpt of a 52-line script.
-- Dimension table with SCD2 pattern
CREATE OR REPLACE TABLE dim_customer_scd2 (
    customer_key INTEGER AUTOINCREMENT,        -- Surrogate key
    customer_id INTEGER,                       -- Natural key
    customer_name STRING,
    email STRING,
    phone STRING,
    address STRING,
…

The remaining 44 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Step 2: Create Stream on Source

SQL 1 line

SQL example — read the query, then copy it into your warehouse.

CREATE OR REPLACE STREAM source_customers_stream ON TABLE source_customers;

Step 3: Build SCD2 Processing Logic

This is the complex part. We need to:

  1. Identify what changed
  2. Expire old records (set end date, is_current = FALSE)
  3. Insert new versions
Step 3: Build SCD2 Processing Logic: excerpt of this SQL example. This is a shortened excerpt of a 60-line script.
CREATE OR REPLACE TASK process_customer_scd2
    WAREHOUSE = etl_wh
    SCHEDULE = '10 MINUTE'
    WHEN SYSTEM$STREAM_HAS_DATA('source_customers_stream')
AS
BEGIN
    -- Step 1: Expire old records for changed customers
    UPDATE dim_customer_scd2
…

The remaining 52 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Step 4: Test SCD2 Processing

Step 4: Test SCD2 Processing: excerpt of this SQL example. This is a shortened excerpt of a 23-line script.
-- Test 1: Update customer address (should create new version)
UPDATE source_customers 
SET address = '999 New Street', city = 'San Francisco', state = 'CA'
WHERE customer_id = 1;
-- Wait 10 minutes (or manually run: EXECUTE TASK process_customer_scd2;)
-- Check results - should see 2 versions of customer 1
SELECT 
    customer_key,
…

The remaining 15 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Step 4: Test SCD2 Processing: excerpt of this SQL example (part 2). This is a shortened excerpt of a 25-line script.
-- Test 2: Update multiple attributes
UPDATE source_customers 
SET 
    email = '[email protected]',
    phone = '555-9999',
    status = 'INACTIVE'
WHERE customer_id = 2;
-- Check history
…

The remaining 17 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Step 5: Query Historical Data

Now the payoff—querying data as it was at any point in time:

Step 5: Query Historical Data: excerpt of this SQL example. This is a shortened excerpt of a 38-line script.
-- Current state (easy)
SELECT * FROM dim_customer_scd2 WHERE is_current = TRUE;
-- State as of specific date
SELECT 
    customer_id,
    customer_name,
    address,
    city,
…

The remaining 30 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Part 5: Real Use Case #3 – Multi-Table Pipeline

Scenario: Process orders → update customer metrics → trigger alerts

Part 5: Real Use Case #3 – Multi-Table Pipeline: excerpt of this SQL example. This is a shortened excerpt of a 98-line script.
-- Source tables
CREATE OR REPLACE TABLE raw_orders (
    order_id INTEGER,
    customer_id INTEGER,
    amount DECIMAL(10,2),
    order_date DATE,
    status STRING,
    inserted_at TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP()
…

The remaining 90 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Part 6: Error Handling and Monitoring

Handling Task Failures

Handling Task Failures: excerpt of this SQL example. This is a shortened excerpt of a 20-line script.
-- Task with error handling
CREATE OR REPLACE TASK robust_processing
    WAREHOUSE = etl_wh
    SCHEDULE = '10 MINUTE'
AS
BEGIN
    -- Use TRY-CATCH pattern
    INSERT INTO processing_log VALUES ('Starting process', CURRENT_TIMESTAMP(), NULL);
…

The remaining 12 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Monitoring Task Execution

Monitoring Task Execution: excerpt of this SQL example. This is a shortened excerpt of a 33-line script.
-- Create monitoring view
CREATE OR REPLACE VIEW task_monitoring AS
SELECT 
    name as task_name,
    state,
    schedule,
    warehouse_name,
    error_code,
…

The remaining 25 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Stream Lag Monitoring

Stream Lag Monitoring: excerpt of this SQL example. This is a shortened excerpt of a 27-line script.
-- Check if streams are falling behind
SELECT 
    table_name,
    stream_name,
    SYSTEM$STREAM_GET_TABLE_TIMESTAMP(stream_name) as stream_position,
    CURRENT_TIMESTAMP() as current_time,
    DATEDIFF(minute, 
        SYSTEM$STREAM_GET_TABLE_TIMESTAMP(stream_name), 
…

The remaining 19 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Part 7: Performance Optimization

Tip 1: Minimize Stream Scans

Tip 1: Minimize Stream Scans: excerpt of this SQL example. This is a shortened excerpt of a 17-line script.
-- Bad: Scanning stream multiple times
CREATE TASK inefficient_task AS
BEGIN
    INSERT INTO table1 SELECT * FROM my_stream WHERE condition1;
    INSERT INTO table2 SELECT * FROM my_stream WHERE condition2;
END;
-- Problem: Stream scanned twice (expensive!)
-- Good: Scan once, use temp table
…

The remaining 9 lines stay in the interactive article so this page remains a written walkthrough rather than a raw SQL dump.

Tip 2: Use Clustering for Large Streams

Snippet 3 lines

Code example — copy the snippet, then match it to your project.

-- If stream source table is large, cluster itALTER TABLE large_source_table CLUSTER BY (date_column);-- Improves stream performance significantly

Tip 3: Right-Size Warehouses

SQL 10 lines

SQL example — read the query, then copy it into your warehouse.

-- Use smaller warehouses for simple tasksCREATE TASK simple_aggregation    WAREHOUSE = X_SMALL_WH  -- Don't waste credits    SCHEDULE = '5 MINUTE'AS ...;-- Use larger for complex processingCREATE TASK complex_transformations    WAREHOUSE = LARGE_WH    SCHEDULE = '1 HOUR'AS ...;

Tip 4: Task Scheduling Strategy

SQL 3 lines

SQL example — read the query, then copy it into your warehouse.

-- Stagger tasks to avoid warehouse contentionCREATE TASK task_1 SCHEDULE = 'USING CRON 0 * * * * UTC' AS ...;   -- Every hour at :00CREATE TASK task_2 SCHEDULE = 'USING CRON 15 * * *

Question this article answers

The short answer first. Open it to read the working note.

What Are Streams and Tasks? (The Simple Explanation)?

Before diving into code, let's understand what these actually do: Streams are like security cameras for your tables. They watch what changes (inserts, updates, deletes) and create a "change log" you can query. Think of it as automatic change data capture (CDC) built into Snowflake.