Separate change capture, execution and maintained results
Expose row changes after a consumer-specific offset.
Run SQL or procedural work on a schedule, trigger or graph dependency.
Materialize a query result and refresh it toward a freshness objective.
Describe the result with SELECT and let Snowflake manage refreshes: dynamic table. Control the event, procedure, DML and workflow: streams and tasks. A stream is change-tracking state, not an orchestrator or copied target table.
Streams and tasks form an imperative pipeline: detect change, decide when code runs, then execute DML or a procedure. A dynamic table is declarative: define desired rows and freshness, and Snowflake manages refresh timing and dependency order within supported behavior.
Choose from the pipeline requirement
Snowflake streams: a change-tracking offset
When created, a stream establishes an offset at a transactional version of its source. Querying it returns changes between that offset and the current source version using change-tracking metadata. The stream itself does not store copied rows.
- CDC columns: source columns are accompanied by METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID where supported.
- Independent consumers: create separate streams when consumers need separate offsets.
- Inspect versus consume: selecting lets you inspect rows; a committed DML transaction using the stream advances its offset.
- Updates: standard-stream updates appear as delete and insert records with update metadata.
- Retention: consume changes before STALE_AFTER to avoid stream staleness.
Operational trap: a stream is not a queue with copied durable messages. Its change results depend on retained source history; an unconsumed stream can become stale.
Snowflake tasks: execution and orchestration
A task runs SQL, Snowflake Scripting or a supported stored procedure. Depending on configuration, it uses Snowflake-managed serverless compute or a specified virtual warehouse.
Time or CRON driven
Use when the business requirement is tied to an explicit cadence or batch window.
Stream change driven
Use a WHEN condition to respond to stream changes without frequent polling compute.
Task graphs arrange a root and child tasks as a directed acyclic graph. Children can run serially or in parallel after predecessors; an optional finalizer can perform cleanup or notification work.
Compact triggered-task pattern: CREATE TASK process_orders WAREHOUSE = etl_wh WHEN SYSTEM$STREAM_HAS_DATA('orders_stream') AS MERGE INTO curated_orders USING orders_stream ...;
SYSTEM$STREAM_HAS_DATA is designed to avoid false negatives but can return a false positive. Keep the task safe when a run finds no rows that actually require processing.
Dynamic tables: declarative maintained query results
A dynamic table is defined by a query. Snowflake stores its result and refreshes it automatically according to the selected operating model. A standard SELECT-based pipeline does not need a separate stream and task for every stage.
- TARGET_LAG: desired maximum staleness relative to upstream base tables—not a fixed interval or guarantee.
- DOWNSTREAM: intermediate tables refresh when a downstream dynamic-table consumer needs fresh data; a terminal table needs its own duration target.
- Refresh mode: incremental processes supported changes; full recomputes; auto chooses at creation.
- Warehouse: refresh compute consumes credits, and capacity, query shape and depth affect actual lag.
- Read-only result: consumers cannot directly INSERT, UPDATE, DELETE or TRUNCATE a dynamic table.
Compact definition: CREATE DYNAMIC TABLE daily_orders TARGET_LAG = '10 minutes' WAREHOUSE = transform_wh REFRESH_MODE = INCREMENTAL AS SELECT order_date, SUM(amount) FROM raw_orders GROUP BY order_date;
A ten-minute target asks Snowflake to keep the result within that freshness when feasible. It does not mean “start exactly every ten minutes.” Monitor refresh history and actual lag.
Worked pipeline scenarios
Declarative bronze-to-gold analytics
Raw orders feed cleansed and aggregated SQL layers with no procedural side effects. Use dynamic tables, set intermediates to TARGET_LAG = DOWNSTREAM, and give the terminal table the freshness target.
CDC upsert with custom handling
Changes must MERGE into a standard target, call a stored procedure for rejects and write audit rows. Use a stream plus triggered task or task graph for explicit DML and procedural work.
Existing orchestrator controls timing
Evaluate orchestrator-managed dynamic tables where supported, or keep streams and tasks when procedural sequencing remains essential. TARGET_LAG is not an exact CRON substitute.
Use dynamic tables for declarative transformations and tasks for procedural actions at clear boundaries. Document freshness, ownership and recovery instead of mixing mechanisms without an operating model.
Compare cost and operational responsibility
Neither approach is free because it is automated. Streams track offsets without transformation compute, tasks consume compute when they run, and dynamic-table refreshes consume warehouse credits. Compare latency, credits, maintenance and correctness under real change volume.
Troubleshoot the correct pipeline layer
- Stream returns no rows: confirm source changes occurred after its offset and distinguish SELECT from committed DML consumption.
- Stream is stale: compare STALE_AFTER with source retention and consumer history before recreating it.
- Triggered task does not run: verify it is resumed, inspect the WHEN condition, ownership, privileges, trigger interval and task history.
- Task repeats or misses work: inspect overlap, failures, graph dependencies and DML idempotency.
- Dynamic table misses target lag: inspect refresh duration/history, warehouse capacity, query operators and upstream depth.
- Incremental refresh is absent: review the selected refresh mode and unsupported or inefficient query operators.
- DOWNSTREAM table is stale: confirm it has a downstream dynamic-table consumer with a duration target.
SnowPro Core practice checks with explanations
A SQL-only pipeline needs results no more than 15 minutes behind without exact run times. Which approach?
Best answer: dynamic tables with a suitable target lag. The requirement describes a declarative result and freshness objective.
A stored procedure must execute only when CDC changes arrive. Which approach?
Best answer: a stream plus triggered task. The stream exposes changes and the task provides event-driven procedural execution.
Does SELECT * FROM a stream advance its offset?
Best answer: inspection alone does not consume the offset. A committed DML transaction using the stream advances it.
Does TARGET_LAG = 10 minutes start refreshes exactly every 10 minutes?
Best answer: no. Target lag is a best-effort freshness objective; timing depends on pipeline work and compute.
Common SnowPro Core mistakes
- Describing a stream as a copied change table.
- Assuming SELECT consumes a stream.
- Sharing one stream between independent consumers needing separate offsets.
- Ignoring retention and STALE_AFTER.
- Polling frequently when a triggered task fits.
- Using dynamic tables for procedural side effects or direct target DML.
- Treating TARGET_LAG as an exact interval or guarantee.
- Giving every intermediate dynamic table an independent short lag without considering DOWNSTREAM.
- Ignoring refresh credits because scheduling is managed.
Frequently asked questions
What is the difference between Snowflake streams and dynamic tables?
A stream exposes change data since its offset on a source object and is normally consumed by explicit DML or procedural processing. A dynamic table stores the result of a declarative query and Snowflake refreshes it to pursue a configured freshness target.
Do Snowflake streams store copied table data?
No. A stream stores an offset for its source object and uses the source's change-tracking metadata and version history to return CDC rows, including stream metadata columns.
Is TARGET_LAG a dynamic-table refresh interval?
No. Target lag is a freshness objective relative to upstream base tables, not a fixed schedule. Snowflake chooses refresh timing, and actual lag can exceed the target when refresh work, warehouse capacity or pipeline depth prevents it.
When should I use streams and tasks instead of dynamic tables?
Choose streams and tasks for procedural logic, stored procedures, explicit DML against a standard target, external calls, event triggers, CRON control, custom branching or task-graph behavior that a declarative dynamic-table query does not provide.
Official Snowflake references
- Introduction to streams
- Introduction to tasks
- Triggered tasks
- Dynamic tables decision guide
- Dynamic-table target lag
Confirm current account features, edition, SQL support and limits before changing production pipelines. ITCertPath uses original learning scenarios and does not reproduce confidential exam questions.