Building a continuous pipeline with tasks, streams, dynamic tables, and Snowpipe

Continuous pipelines in Snowflake are typically built by combining Snowpipe (or Snowpipe Streaming) for automated file/row ingestion, Streams for change data capture, and Tasks (or Dynamic Tables) to transform and move data on a schedule or trigger. Troubleshooting relies on system views like COPY_HISTORY, PIPE_USAGE_HISTORY, TASK_HISTORY, and STREAM staleness checks to identify ingestion lag, failed loads, or broken task graphs.

1 · Learn the must-know

• 1Streams track DML changes (inserts, updates, deletes) on a table via metadata columns and must be consumed (via DML in a transaction) to advance their offset, otherwise they can go stale if the underlying table's retention period is exceeded.
• 2Tasks can be chained into a DAG using AFTER dependencies (up to 100 tasks per graph) or triggered by a stream having data via SYSTEM$STREAM_HAS_DATA, and only the root task can have a schedule.
• 3Snowpipe uses auto-ingest (via cloud storage event notifications) or REST API calls for near-real-time, serverless, file-based loading and bills based on compute-seconds used, not warehouse size.
• 4Snowpipe Streaming ingests rows directly (no staged files) via the Streaming Ingest SDK, offering lower latency than Snowpipe for row-level or high-frequency inserts, and does not use the standard COPY-based pipe object.
• 5Dynamic Tables offer a declarative alternative to manually orchestrating streams/tasks, automatically refreshing based on a target lag and internally managing incremental refresh where possible.
• 6Use TASK_HISTORY, PIPE_USAGE_HISTORY, COPY_HISTORY, and the ACCOUNT_USAGE/INFORMATION_SCHEMA views (plus SYSTEM$PIPE_STATUS and SYSTEM$TASK_DEPENDENTS) to diagnose ingestion failures, task suspension due to errors, or DAG execution delays.

2 · Check your understanding