FDE Times: Snowflake Dynamic Tables vs Streams and Tasks — one SELECT, or four objects and a MERGE
A 6 November FDE Times hands-on guide builds the same incremental revenue pipeline two ways on Snowflake and follows Snowflake’s own advice: prefer Dynamic Tables for new SQL pipelines; use Streams and Tasks when you need MERGE control, stored procedures, or external functions.
FDE Times walks through building an incremental daily-revenue pipeline on Snowflake twice: once as a Dynamic Table (TARGET_LAG, warehouse, REFRESH_MODE = INCREMENTAL over a single SELECT) and once by hand with a stream, a scheduled task gated by SYSTEM$STREAM_HAS_DATA, and a MERGE. Incremental refresh costs scale with change rate, not table size—so ask the client how fast data grows and how stale numbers may be before picking a tool.
Three traps trip FDEs who choose Streams and Tasks: a SELECT on a stream does not advance its offset (only DML such as MERGE does); new tasks start suspended until you ALTER TASK … RESUME; and a stream goes stale if its offset falls outside the source retention window—so alert on task state and stream staleness from day one.
Snowflake’s decision guide prefers Dynamic Tables for new SQL pipelines; switch to Streams and Tasks when you need stored procedures, a custom MERGE, external functions, or full control over incremental processing. Agreeing a TARGET_LAG with the client is often a more honest answer than promising a “real-time” dashboard.
Source: fdetimes.net