Introduction

Snowflake transformation pipelines combine declarative SQL with objects that track change and schedule work. Streams expose change data relative to an offset; tasks execute SQL or stored procedures on a schedule or in response to supported events. Together they can implement repeatable Extract, Load, Transform (ELT) workflows without treating either object as a durable message queue.

Pipeline Architecture

A basic incremental pipeline contains four responsibilities:

  • A source object receives committed changes.
  • A stream exposes change records since its current offset.
  • A task runs a transformation when scheduled or triggered.
  • A target object stores the derived state.

Monitoring closes the loop. Task history, stream state, target freshness, rejected records, and warehouse or serverless cost provide different evidence. A task marked successful only proves its statement completed; it does not prove that business reconciliation passed.

How Streams Track Change

A stream records an offset for its source object and relies on change-tracking metadata. It does not copy all source rows into a separate table. Querying a stream presents change records and metadata columns that describe inserts, deletes, and update-related behavior according to the stream type and source.

The offset advances when a qualifying Data Manipulation Language transaction consumes the stream. A plain diagnostic SELECT does not provide the same consumption semantics as inserting or merging the stream into a target within a committed transaction.

Streams can become stale if changes are not consumed within the supported retention boundary. Snowflake may extend source retention under documented conditions to help prevent staleness, which can increase storage cost. Monitoring must therefore include both pipeline execution and stream lifecycle.

How Tasks Execute Work

Tasks can use a schedule or supported trigger. Compute can be serverless, where Snowflake selects resources, or user managed through a virtual warehouse. Tasks are created suspended and must be resumed before scheduled or triggered execution begins.

Task graphs connect a root task to dependent child tasks. Dependencies establish execution order; they do not replace transaction design, idempotency, or data-quality validation. A failed upstream task prevents the expected downstream path, so history should be reviewed across the graph rather than only at the final table.

An Incremental Transformation Pattern

The following lab sequence illustrates the core control flow:

CREATE OR REPLACE STREAM analytics.raw.orders_stream  
     ON TABLE analytics.raw.orders;

CREATE OR REPLACE TASK analytics.pipeline.merge_orders  
     WAREHOUSE = transform_wh  
     WHEN SYSTEM$STREAM_HAS_DATA('analytics.raw.orders_stream') 
AS 
MERGE INTO analytics.curated.orders AS target 
USING analytics.raw.orders_stream AS source  
     ON target.order_id = source.order_id 
WHEN MATCHED AND source.metadata$action = 'DELETE' THEN DELETE 
WHEN MATCHED THEN UPDATE SET  
     target.order_total = source.order_total,  
     target.order_ts = source.order_ts 
WHEN NOT MATCHED THEN INSERT  
     (order_id, order_total, order_ts) 
VALUES  
     (source.order_id, source.order_total, source.order_ts);

Validate current syntax and stream-update semantics before adapting this example. Production pipelines also need explicit handling for duplicate business keys, schema changes, invalid values, late arrivals, and replay.

Ordering and Reliability

  1. Confirm source commits are visible and the stream is not stale.
  2. Validate that the stream exposes the expected metadata and business keys.
  3. Test the transformation in a transaction against representative changes.
  4. Confirm the task owner and execution role have required privileges.
  5. Resume the task and verify schedule or trigger behavior.
  6. Review task history and the executed query profile when performance matters.
  7. Reconcile target counts and business totals with the consumed change set.

Idempotency is essential. A task retry or operator rerun should not duplicate business results. MERGE can help when the match key and change semantics are correct, but it is not automatically idempotent if source keys are ambiguous.

Common Mistakes

  • Describing a stream as a table containing a copied change log.
  • Assuming a diagnostic read consumes the stream.
  • Creating a task and forgetting that it begins suspended.
  • Checking only task success while ignoring target reconciliation.
  • Allowing a stream to become stale because no process owns retention monitoring.
  • Building a task graph without defining retry and partial-failure behavior.
  • Using a warehouse that lacks capacity or an execution role that lacks object privileges.

Certification Relevance

COF-C03 includes data transformation and performance monitoring. The central skill is to trace change from source commit through stream offset, task execution, target mutation, and observable validation.

Authoritative Sources

Conclusion

Streams and tasks provide a Snowflake-native control plane for incremental transformation. Reliable pipelines depend on offset lifecycle, transaction semantics, task ownership, idempotent transformations, and reconciliation, not merely on creating the two objects.