Data Engineering and Continuous Data Pipelines
Streams and tasks
Streams capture change data on tables, views, and directories. A standard stream tracks inserts, updates, and deletes; append-only streams track inserts only. Streams have an offset; consuming a stream advances it, and stale streams (beyond the table's Time Travel retention) become invalid. Tasks run SQL on a schedule or when triggered inside a task DAG; a task cannot run concurrently with itself. Serverless tasks auto-size compute and are billed per compute used.
Dynamic tables
Dynamic tables declare a target lag and a query; Snowflake refreshes them automatically. They suit declarative pipelines, but refreshes consume warehouse credits, and long dependency chains can become expensive.
Traps
- Expecting a stream to capture changes made by the same transaction that reads it.
- Forgetting that cloned tables do not carry usable stream offsets.
- Assuming a task runs exactly at the scheduled time; it runs within the schedule, subject to warehouse availability.
- Using a stream on a table with no Time Travel retention left.
- Confusing task DAG finalizer tasks with general error handling.
Worked example: a stream shows 0 rows after an ETL job - either the job's own transaction consumed the stream or the stream was recreated. Check the stream's stale_after value and the task history before rebuilding the pipeline.