Snowflake Transformations for Data Engineers: Streams, Tasks, Dynamic Tables, MERGE, Stored Procedures, CDC Patterns, and Building Medallion Pipelines

Leave a Comment / Data Engineering, Snowflake

Table of Contents

In the previous post, we loaded raw data into Snowflake using stages, COPY INTO, and Snowpipe. Raw data is messy — it has duplicates, nulls, inconsistent formats, and no business logic applied. This post covers how to transform that raw data into clean, analytics-ready tables using Snowflake’s native transformation tools: Streams for change tracking, Tasks for scheduling, Dynamic Tables for declarative pipelines, MERGE for upserts, and stored procedures for complex logic.

Analogy — A food processing plant. Raw data is like fresh produce arriving at a food processing plant — it needs washing (cleaning), sorting (deduplication), cutting (transformation), and packaging (loading into final tables). Streams are the conveyor belt sensors that detect when new produce arrives. Tasks are the timers that start the machines every hour. Dynamic Tables are the smart machines that automatically produce the finished product whenever fresh ingredients appear — you just describe what the output should look like, and the machine figures out the process. MERGE is the quality inspector who decides whether each item is new (insert), updated (update), or should be removed (delete). Stored procedures are the custom recipes for specialty products that the standard machines cannot handle.

Transformation Options in Snowflake

Snowflake provides five transformation mechanisms:

  1. Streams        -- Track changes (inserts, updates, deletes) on a source table
  2. Tasks          -- Schedule SQL statements or stored procedures on a cron
  3. Dynamic Tables -- Declare "what the result should look like" and Snowflake
                       handles scheduling, dependencies, and incremental refresh
  4. MERGE          -- Upsert (insert + update + delete) in a single statement
  5. Stored Procs   -- Write multi-step procedural logic in SQL, Python, or Java

  How they combine:
    Streams + Tasks + MERGE  = Classic CDC pipeline (you control everything)
    Dynamic Tables           = Declarative pipeline (Snowflake controls everything)
    Stored Procedures        = Complex logic that SQL alone cannot express

Streams — Change Data Capture Built In

A stream is a Snowflake object that tracks row-level changes (inserts, updates, deletes) on a table. When you query a stream, you see only the rows that changed since the last time the stream was consumed. Once consumed (read in a DML transaction), the stream advances its offset and the consumed changes disappear from the stream.

Analogy — A bookmark in a logbook. Imagine a logbook that records every change to a table. A stream is a bookmark in that logbook. When you read from the bookmark to the current page, you see all changes since you last looked. After processing those changes, the bookmark moves to the current page. Next time you look, you only see new changes. You never reprocess old changes, and you never miss new ones.

-- Create a stream on the raw orders table
CREATE STREAM orders_stream ON TABLE RAW_DB.VENDOR_A.ORDERS
  COMMENT = 'Tracks inserts, updates, deletes on raw orders';

-- The stream adds three metadata columns to the source table:
--   METADATA$ACTION   = 'INSERT' or 'DELETE'
--   METADATA$ISUPDATE = TRUE if the row is part of an UPDATE (appears as DELETE + INSERT)
--   METADATA$ROW_ID   = Unique row identifier

-- Check if the stream has changes
SELECT SYSTEM$STREAM_HAS_DATA('orders_stream');
-- Returns TRUE if there are unconsumed changes, FALSE if empty

Querying a Stream

-- See all changes since last consumption
SELECT * FROM orders_stream;

-- See only new inserts
SELECT * FROM orders_stream
WHERE METADATA$ACTION = 'INSERT' AND METADATA$ISUPDATE = FALSE;

-- See only updates (appear as a DELETE of old row + INSERT of new row)
SELECT * FROM orders_stream
WHERE METADATA$ISUPDATE = TRUE;

-- See only deletes
SELECT * FROM orders_stream
WHERE METADATA$ACTION = 'DELETE' AND METADATA$ISUPDATE = FALSE;

Stream Types

-- Standard stream (default) -- tracks inserts, updates, deletes
CREATE STREAM orders_stream ON TABLE orders;

-- Append-only stream -- tracks only inserts (no updates or deletes)
-- Best for event/log tables where rows are never modified
CREATE STREAM events_stream ON TABLE events
  APPEND_ONLY = TRUE;

-- Stream on a view
CREATE STREAM view_stream ON VIEW my_view;

-- Stream on an external table
CREATE STREAM ext_stream ON EXTERNAL TABLE my_ext_table
  INSERT_ONLY = TRUE;

How Streams Track Changes

Stream behavior for different operations:

  INSERT a new row:
    Stream shows: ACTION=INSERT, ISUPDATE=FALSE
    One row in the stream

  UPDATE an existing row:
    Stream shows TWO rows:
      Row 1: ACTION=DELETE, ISUPDATE=TRUE  (the old values)
      Row 2: ACTION=INSERT, ISUPDATE=TRUE  (the new values)

  DELETE a row:
    Stream shows: ACTION=DELETE, ISUPDATE=FALSE
    One row in the stream

  Key behavior:
    - Stream data is transactional -- consumed when read inside a DML statement
    - If you SELECT from a stream without a DML, it does NOT advance the offset
    - Only a DML (INSERT INTO, MERGE, CREATE TABLE AS) that reads the stream
      will advance the offset and "consume" the changes
    - Multiple changes to the same row between consumptions are collapsed
      (net effect only)

Tasks — Scheduled SQL Execution

A task runs a SQL statement on a schedule — like a cron job inside Snowflake. Tasks can run independently or in chains (task trees) where child tasks run after parent tasks complete.

Analogy — An alarm clock for your data. A task is an alarm that goes off on a schedule (every hour, every day at 6 AM) and runs a SQL statement. You can chain alarms: the first alarm (parent task) wakes up the cleaning crew, and when they finish, the second alarm (child task) wakes up the packaging crew. If the cleaning crew does not finish, the packaging crew does not start.

-- Create a standalone task that runs every hour
CREATE TASK clean_orders_task
  WAREHOUSE = WH_ETL
  SCHEDULE = 'USING CRON 0 * * * * America/Toronto'  -- Every hour
  COMMENT = 'Hourly data cleaning'
AS
  INSERT INTO ANALYTICS_DB.STAGING.ORDERS_CLEAN
  SELECT
    order_id,
    TRIM(UPPER(customer_name)) AS customer_name,
    amount,
    order_date,
    CURRENT_TIMESTAMP() AS processed_at
  FROM RAW_DB.VENDOR_A.ORDERS
  WHERE order_date = CURRENT_DATE();

-- Tasks are created in a SUSPENDED state -- you must resume them
ALTER TASK clean_orders_task RESUME;

-- Suspend a task
ALTER TASK clean_orders_task SUSPEND;

-- Run a task manually (for testing)
EXECUTE TASK clean_orders_task;

Task Scheduling Options

-- Cron-based schedule
SCHEDULE = 'USING CRON 0 6 * * * America/Toronto'    -- Daily at 6 AM ET
SCHEDULE = 'USING CRON 0 */2 * * * UTC'               -- Every 2 hours
SCHEDULE = 'USING CRON 0 6 * * 1-5 America/Toronto'   -- Weekdays at 6 AM

-- Interval-based schedule
SCHEDULE = '5 MINUTE'    -- Every 5 minutes
SCHEDULE = '1 HOUR'      -- Every hour
SCHEDULE = '60 MINUTE'   -- Every 60 minutes

Task Trees — Chaining Tasks

-- Parent task: extract and clean
CREATE TASK task_extract
  WAREHOUSE = WH_ETL
  SCHEDULE = 'USING CRON 0 6 * * * America/Toronto'
AS
  INSERT INTO staging.orders_clean SELECT ... FROM raw.orders;

-- Child task 1: transform (runs AFTER parent completes)
CREATE TASK task_transform
  WAREHOUSE = WH_ETL
  AFTER task_extract                     -- Dependency on parent
AS
  INSERT INTO core.orders_enriched SELECT ... FROM staging.orders_clean;

-- Child task 2: aggregate (runs AFTER transform completes)
CREATE TASK task_aggregate
  WAREHOUSE = WH_ETL
  AFTER task_transform                   -- Dependency on transform
AS
  INSERT INTO marts.revenue_daily SELECT ... FROM core.orders_enriched;

-- Resume all tasks (start from the ROOT task)
ALTER TASK task_aggregate RESUME;    -- Resume children first
ALTER TASK task_transform RESUME;
ALTER TASK task_extract RESUME;      -- Resume root task last

-- Check task history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
  TASK_NAME => 'TASK_EXTRACT',
  SCHEDULED_TIME_RANGE_START => DATEADD('day', -1, CURRENT_TIMESTAMP())
))
ORDER BY SCHEDULED_TIME DESC;

Serverless Tasks (No Warehouse Needed)

-- Serverless task -- Snowflake manages compute (no warehouse specified)
CREATE TASK lightweight_cleanup
  SCHEDULE = '60 MINUTE'
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
  -- No WAREHOUSE parameter -- Snowflake provisions serverless compute
AS
  DELETE FROM staging.temp_data WHERE created_at < DATEADD('day', -7, CURRENT_TIMESTAMP());

Streams + Tasks Together — The CDC Pipeline

The classic Snowflake data pipeline combines a stream (to detect changes) with a task (to process them on a schedule). This is the traditional approach before Dynamic Tables.

-- Step 1: Create stream on source table
CREATE STREAM raw_orders_stream ON TABLE RAW_DB.VENDOR_A.ORDERS;

-- Step 2: Create target table
CREATE TABLE ANALYTICS_DB.CORE.ORDERS_CLEAN (
  order_id INTEGER,
  customer_name STRING,
  amount DECIMAL(10,2),
  order_date DATE,
  processed_at TIMESTAMP
);

-- Step 3: Create task that processes stream changes
CREATE TASK process_orders_task
  WAREHOUSE = WH_ETL
  SCHEDULE = '5 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('raw_orders_stream')   -- Only run when there are changes
AS
  INSERT INTO ANALYTICS_DB.CORE.ORDERS_CLEAN
  SELECT
    order_id,
    TRIM(UPPER(customer_name)),
    amount,
    order_date,
    CURRENT_TIMESTAMP()
  FROM raw_orders_stream
  WHERE METADATA$ACTION = 'INSERT';

-- Step 4: Resume the task
ALTER TASK process_orders_task RESUME;

-- The WHEN clause is critical:
--   Without WHEN: task runs every 5 minutes, wasting compute when no changes exist
--   With WHEN: task only runs when stream has data, saving credits

MERGE — Upserts and SCD Patterns

MERGE combines INSERT, UPDATE, and DELETE in a single atomic statement. It is Snowflake’s equivalent of the upsert pattern and is essential for SCD Type 1 and Type 2 implementations.

Analogy — A warehouse inventory check. MERGE is like an inventory audit. The auditor (MERGE) compares the delivery manifest (source/stream) against the shelf inventory (target table). New items (not on shelves) get placed on shelves (INSERT). Existing items with updated prices get relabeled (UPDATE). Discontinued items get removed (DELETE). One pass through the manifest handles all three actions.

SCD Type 1 (Overwrite)

-- SCD Type 1: overwrite existing records with latest values
MERGE INTO ANALYTICS_DB.CORE.CUSTOMERS AS target
USING (
  SELECT * FROM raw_customers_stream
  WHERE METADATA$ACTION = 'INSERT'
) AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET
  target.customer_name = source.customer_name,
  target.email = source.email,
  target.phone = source.phone,
  target.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (customer_id, customer_name, email, phone, created_at, updated_at)
  VALUES (source.customer_id, source.customer_name, source.email, source.phone,
          CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP());

SCD Type 2 (Full History)

-- SCD Type 2: keep full history with effective dates
-- Step 1: Expire existing current records that have changes
MERGE INTO ANALYTICS_DB.CORE.CUSTOMERS_HISTORY AS target
USING (
  SELECT customer_id, customer_name, email, phone
  FROM raw_customers_stream
  WHERE METADATA$ACTION = 'INSERT' AND METADATA$ISUPDATE = TRUE
) AS source
ON target.customer_id = source.customer_id AND target.is_current = TRUE
WHEN MATCHED AND (
  target.customer_name != source.customer_name OR
  target.email != source.email OR
  target.phone != source.phone
) THEN UPDATE SET
  target.is_current = FALSE,
  target.effective_end = CURRENT_TIMESTAMP();

-- Step 2: Insert new current records
INSERT INTO ANALYTICS_DB.CORE.CUSTOMERS_HISTORY
  (customer_id, customer_name, email, phone, effective_start, effective_end, is_current)
SELECT customer_id, customer_name, email, phone,
  CURRENT_TIMESTAMP(), NULL, TRUE
FROM raw_customers_stream
WHERE METADATA$ACTION = 'INSERT';

Dynamic Tables — Declarative Pipelines

Dynamic Tables are Snowflake’s declarative approach to data transformation. Instead of writing streams, tasks, and MERGE statements, you write a single SQL query that describes what the result should look like, and Snowflake handles scheduling, dependency tracking, and incremental refresh automatically.

Analogy — A smart spreadsheet formula. Traditional pipelines (streams + tasks) are like manually recalculating a spreadsheet every hour — you set up the timer, write the formula, and handle errors. A Dynamic Table is like a cell with a formula that automatically recalculates whenever the input cells change. You define =SUM(A1:A100) (the SQL query) and say “keep this fresh within 5 minutes” (TARGET_LAG). The spreadsheet (Snowflake) figures out when and how to recalculate.

-- Dynamic Table: cleaned orders (refreshes within 5 minutes of source changes)
CREATE DYNAMIC TABLE ANALYTICS_DB.STAGING.ORDERS_CLEAN
  TARGET_LAG = '5 minutes'
  WAREHOUSE = WH_ETL
AS
  SELECT
    order_id,
    TRIM(UPPER(customer_name)) AS customer_name,
    amount,
    order_date,
    CURRENT_TIMESTAMP() AS processed_at
  FROM RAW_DB.VENDOR_A.ORDERS
  WHERE amount > 0 AND order_date IS NOT NULL;

-- Dynamic Table: revenue by region (depends on orders_clean)
CREATE DYNAMIC TABLE ANALYTICS_DB.MARTS.REVENUE_BY_REGION
  TARGET_LAG = '10 minutes'
  WAREHOUSE = WH_ETL
AS
  SELECT
    region,
    order_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
  FROM ANALYTICS_DB.STAGING.ORDERS_CLEAN
  GROUP BY region, order_date;

TARGET_LAG — Freshness Guarantee

TARGET_LAG tells Snowflake how fresh the Dynamic Table should be:

  TARGET_LAG = '1 minute'     -- Very fresh (higher compute cost)
  TARGET_LAG = '5 minutes'    -- Near-real-time (good balance)
  TARGET_LAG = '1 hour'       -- Standard batch (lower cost)
  TARGET_LAG = '1 day'        -- Daily refresh (cheapest)
  TARGET_LAG = DOWNSTREAM     -- Refresh only when a downstream DT needs it

  How it works:
    Snowflake monitors the source tables
    When source data changes, Snowflake waits up to TARGET_LAG
    Then automatically refreshes the Dynamic Table
    If no source data changes, no refresh happens (no wasted compute)

  Dependency chains:
    If DT_SILVER depends on DT_BRONZE, and DT_GOLD depends on DT_SILVER:
    Snowflake automatically refreshes them in the correct order
    You do NOT need to set up task dependencies manually

Monitoring Dynamic Tables

-- Check refresh history
SELECT *
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_REFRESH_HISTORY(
  NAME => 'ANALYTICS_DB.STAGING.ORDERS_CLEAN'
))
ORDER BY REFRESH_START_TIME DESC
LIMIT 10;

-- Check current lag (how stale is the data?)
SELECT NAME, TARGET_LAG, SCHEDULING_STATE
FROM TABLE(INFORMATION_SCHEMA.DYNAMIC_TABLE_GRAPH_HISTORY())
WHERE NAME = 'ORDERS_CLEAN';

-- Suspend and resume a Dynamic Table
ALTER DYNAMIC TABLE ANALYTICS_DB.STAGING.ORDERS_CLEAN SUSPEND;
ALTER DYNAMIC TABLE ANALYTICS_DB.STAGING.ORDERS_CLEAN RESUME;

-- Force a manual refresh
ALTER DYNAMIC TABLE ANALYTICS_DB.STAGING.ORDERS_CLEAN REFRESH;

Stored Procedures — Complex Logic

Stored procedures handle logic that pure SQL cannot express: loops, conditional branching, error handling, and multi-step operations. Snowflake supports stored procedures in SQL (Snowflake Scripting), Python, Java, and Scala.

-- SQL stored procedure: process daily orders with error handling
CREATE OR REPLACE PROCEDURE process_daily_orders(processing_date DATE)
  RETURNS STRING
  LANGUAGE SQL
  EXECUTE AS CALLER
AS
DECLARE
  row_count INTEGER;
  error_msg STRING;
BEGIN
  -- Step 1: Clean and insert
  INSERT INTO ANALYTICS_DB.STAGING.ORDERS_CLEAN
  SELECT order_id, TRIM(UPPER(customer_name)), amount, order_date, CURRENT_TIMESTAMP()
  FROM RAW_DB.VENDOR_A.ORDERS
  WHERE order_date = :processing_date;

  row_count := SQLROWCOUNT;

  -- Step 2: Log the result
  INSERT INTO ANALYTICS_DB.METADATA.PIPELINE_LOG
    (step_name, processing_date, row_count, status, logged_at)
  VALUES ('process_daily_orders', :processing_date, :row_count, 'SUCCESS', CURRENT_TIMESTAMP());

  RETURN 'Processed ' || :row_count || ' rows for ' || :processing_date;

EXCEPTION
  WHEN OTHER THEN
    error_msg := SQLERRM;
    INSERT INTO ANALYTICS_DB.METADATA.PIPELINE_LOG
      (step_name, processing_date, row_count, status, error_message, logged_at)
    VALUES ('process_daily_orders', :processing_date, 0, 'FAILED', :error_msg, CURRENT_TIMESTAMP());
    RETURN 'FAILED: ' || :error_msg;
END;

-- Call the stored procedure
CALL process_daily_orders('2026-07-19');

Python Stored Procedure

-- Python stored procedure for complex transformations
CREATE OR REPLACE PROCEDURE detect_anomalies(table_name STRING, column_name STRING)
  RETURNS TABLE (row_id INTEGER, value FLOAT, is_anomaly BOOLEAN)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python', 'numpy')
  HANDLER = 'run'
AS
$$
import numpy as np

def run(session, table_name, column_name):
    df = session.table(table_name)
    pdf = df.to_pandas()
    values = pdf[column_name].values
    mean = np.mean(values)
    std = np.std(values)
    pdf['is_anomaly'] = np.abs(values - mean) > (3 * std)
    return session.create_dataframe(pdf[['ROW_ID', column_name, 'is_anomaly']])
$$;

Medallion Architecture in Snowflake

Bronze -> Silver -> Gold in Snowflake:

  Option 1: Streams + Tasks (traditional, full control)
    Raw table (bronze) -> Stream -> Task + MERGE -> Clean table (silver)
    Clean table (silver) -> Stream -> Task + INSERT -> Mart table (gold)

  Option 2: Dynamic Tables (declarative, Snowflake manages)
    Raw table (bronze)
      |
      v
    DT: silver_orders (TARGET_LAG = '5 minutes')
      |
      v
    DT: gold_revenue_by_region (TARGET_LAG = '10 minutes')
      |
      v
    DT: gold_customer_lifetime_value (TARGET_LAG = '1 hour')

  Option 3: dbt (external orchestration)
    Raw table (bronze) -> dbt models (SQL + Jinja) -> Silver/Gold tables
    dbt handles dependency ordering, testing, documentation
    Runs on a Snowflake warehouse via scheduled dbt Cloud or Airflow

  Which to choose:
    Streams + Tasks: full control, complex CDC, sub-minute latency
    Dynamic Tables: simple declarative, auto-managed, 1+ minute latency
    dbt: version-controlled SQL, testing, docs, team collaboration

Streams + Tasks vs Dynamic Tables — When to Use Which

CriteriaStreams + TasksDynamic Tables
ControlFull (you manage everything)Minimal (Snowflake manages)
ComplexityHigh (stream + task + MERGE)Low (single SQL statement)
LatencySub-minute possible1+ minute minimum (TARGET_LAG)
DependenciesManual (task trees)Automatic (Snowflake resolves)
UDFs / ProceduresSupportedSQL-only (no UDFs, no procs)
CDC patternFull control (INSERT/UPDATE/DELETE)Automatic incremental refresh
Error handlingCustom (stored procedure logic)Snowflake-managed (retry)
Best forComplex CDC, conditional logic, sub-minuteStandard transformations, medallion layers

Decision guide:

  Use Dynamic Tables when:
    - Your transformation is a straightforward SQL query
    - 1-minute lag is acceptable
    - You want Snowflake to manage scheduling and dependencies
    - You are building medallion layers (bronze -> silver -> gold)

  Use Streams + Tasks when:
    - You need sub-minute latency
    - Your logic requires conditional branching or loops
    - You need to call stored procedures or UDFs
    - You need fine-grained control over CDC (handle updates and deletes separately)
    - You need custom error handling and alerting

Common Mistakes

  1. Using Streams + Tasks when Dynamic Tables would suffice. If your transformation is a straightforward SQL query and 1-minute lag is acceptable, Dynamic Tables are simpler, cheaper, and easier to maintain. Streams + Tasks require creating three objects (stream, task, target table) and managing dependencies manually.

  2. Forgetting the WHEN clause on tasks with streams. Without WHEN SYSTEM$STREAM_HAS_DATA('stream_name'), the task runs on every scheduled interval regardless of whether there are changes. This wastes warehouse credits on empty runs. Always add the WHEN clause for stream-based tasks.

  3. Not understanding stream consumption behavior. A stream’s offset advances only when its data is read inside a DML transaction (INSERT INTO, MERGE, CREATE TABLE AS). A plain SELECT does NOT advance the offset. If you SELECT from a stream to debug it, the data will still be there for the next DML. But if a task reads the stream and fails after the DML, the stream data is consumed and lost.

  4. Setting TARGET_LAG too aggressively on Dynamic Tables. TARGET_LAG = ‘1 minute’ means Snowflake checks for changes and potentially refreshes every minute. If your source table changes frequently, this causes near-continuous warehouse usage and high compute costs. Start with TARGET_LAG = ’30 minutes’ or ‘1 hour’ and reduce only if business requirements demand fresher data.

  5. Not resuming tasks after creation. Tasks are created in a SUSPENDED state. Forgetting to run ALTER TASK … RESUME means your pipeline silently does nothing. Always resume tasks after creation, starting with child tasks first and the root task last.

  6. Writing MERGE without proper matching logic. A MERGE with ON target.id = source.id where source has duplicate IDs causes the error “multiple source rows matched a single target row.” Always deduplicate the source before MERGE, or use a subquery with ROW_NUMBER to pick the latest row per key.

  7. Using stored procedures for simple transformations. If your transformation is just SELECT with joins, filters, and aggregations, a Dynamic Table or a simple INSERT INTO … SELECT is cleaner and faster than wrapping it in a stored procedure. Use stored procedures only when you need loops, conditional logic, or error handling that SQL alone cannot express.

  8. Ignoring task history and monitoring. Tasks fail silently unless you check INFORMATION_SCHEMA.TASK_HISTORY. Set up alerts for failed tasks. Query task history regularly to catch issues before they accumulate stale data.

Interview Questions

Q: What are Snowflake Streams and how do they enable CDC? A: Streams are Snowflake objects that track row-level changes (inserts, updates, deletes) on a table. They add three metadata columns: METADATA$ACTION (INSERT or DELETE), METADATA$ISUPDATE (TRUE if part of an update), and METADATA$ROW_ID (unique identifier). Updates appear as a DELETE of the old row plus an INSERT of the new row. A stream maintains an offset that advances when its data is consumed by a DML statement, ensuring each change is processed exactly once.

Q: What is a Snowflake Task and how do task trees work? A: A task schedules a single SQL statement to run on a cron schedule or at a fixed interval. Tasks can form trees: a root task runs on a schedule, and child tasks run after their parent completes. You specify dependencies using the AFTER clause. Tasks are created suspended and must be explicitly resumed. For tasks that read streams, the WHEN SYSTEM$STREAM_HAS_DATA clause prevents unnecessary runs when no changes exist.

Q: What are Dynamic Tables and how do they differ from Streams and Tasks? A: Dynamic Tables are a declarative approach to data transformation. You write a CREATE DYNAMIC TABLE with a SQL query and a TARGET_LAG (freshness guarantee). Snowflake handles scheduling, dependency resolution, and incremental refresh automatically. Unlike Streams + Tasks, where you manage three objects and their dependencies manually, Dynamic Tables require only a single SQL statement. Dynamic Tables are best for standard SQL transformations where 1-minute or greater lag is acceptable. Streams + Tasks are better for sub-minute latency, complex CDC logic, or transformations requiring UDFs and stored procedures.

Q: How does MERGE work in Snowflake and when would you use it? A: MERGE combines INSERT, UPDATE, and DELETE in a single atomic statement by comparing a source (stream or staging table) against a target table using a join condition. WHEN MATCHED handles updates and deletes for existing rows. WHEN NOT MATCHED handles inserts for new rows. Use MERGE for SCD Type 1 (overwrite current values) and SCD Type 2 (expire old records and insert new versioned records). Always deduplicate the source before MERGE to avoid the “multiple source rows matched a single target row” error.

Q: How would you implement a medallion architecture in Snowflake? A: Three options. First, Dynamic Tables: create a chain of DTs where silver DTs read from bronze tables and gold DTs read from silver DTs. Snowflake manages dependencies and refresh order automatically. Second, Streams + Tasks: create streams on bronze tables, tasks that process stream data into silver tables, and additional streams and tasks for silver to gold. Third, dbt: write SQL models organized in bronze, silver, and gold folders, with dbt managing dependency ordering, testing, and documentation. Dynamic Tables are simplest for standard SQL. dbt is best for version-controlled, team-based development. Streams + Tasks provide the most control.

Q: What is TARGET_LAG in Dynamic Tables and how should you set it? A: TARGET_LAG defines the maximum staleness allowed for a Dynamic Table. If set to 5 minutes, Snowflake guarantees the table is never more than 5 minutes behind its source. Snowflake uses this to schedule refreshes efficiently. A shorter lag means more frequent refreshes and higher compute cost. A longer lag means less frequent refreshes and lower cost. Start with 30 minutes or 1 hour for most use cases. Use DOWNSTREAM to refresh only when a downstream Dynamic Table needs the data.

Q: When would you use a stored procedure instead of a Dynamic Table? A: Use stored procedures when the transformation requires procedural logic that SQL alone cannot express: loops, conditional branching, multi-step operations with error handling, calling external APIs, or dynamic SQL that builds queries at runtime. Dynamic Tables only support a single SQL query with no UDFs, external functions, or procedural logic. If your transformation is a straightforward SELECT with joins and aggregations, use a Dynamic Table. If you need if-then-else logic, retries, audit logging, or custom error handling, use a stored procedure called by a task.

Wrapping Up

Snowflake’s transformation toolkit gives you three approaches at different levels of control. Dynamic Tables are the simplest — write a SQL query, set a freshness target, and let Snowflake handle the rest. Streams + Tasks give you full control over CDC processing and scheduling, at the cost of more complexity. Stored procedures handle the edge cases where SQL alone is not enough. Most production pipelines use a combination: Dynamic Tables for standard bronze-to-silver-to-gold transformations, Streams + Tasks for complex CDC patterns, and stored procedures for custom audit logging and error handling.

In the next post, we will explore Snowpark — writing Python inside Snowflake for DataFrame transformations, UDFs, stored procedures, and machine learning.

Related posts: – Snowflake Overview & Architecture – Snowflake Account Setup & RBAC – Snowflake Loading Data & Snowpipe – SCD Type 1 & 2 with PySpark – Medallion Architecture

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top