SnowPro Advanced Data Engineer: Data Pipelines and Orchestration

What candidates must know

Continuous pipelines combine streams, tasks and Snowpipe. A stream records change data capture on a table or view with metadata columns METADATA$ACTION, METADATA$ISUPDATE and METADATA$ROW_ID; reading it with DML advances its offset. Standard streams capture inserts, updates and deletes; append-only streams capture inserts only. Tasks run SQL on a schedule, form a DAG through AFTER dependencies, and can be warehouse-backed or serverless. Snowpipe loads files automatically via event notifications and runs on Snowflake-managed compute, separate from your warehouses. Dynamic tables declare a target lag and let Snowflake manage refresh order. Alerts and notification integrations handle failure paths.

Traps

• A stream becomes stale if it is not consumed within its staleness window, and stale streams cannot be queried until recreated.
• Two tasks consuming the same stream fight over one offset; use one consumer per stream.
• SYSTEM$STREAM_HAS_DATA in a task's WHEN clause avoids empty runs but only works against streams, not arbitrary tables.
• Running a task DAG requires the account-level EXECUTE TASK privilege; EXECUTE MANAGED TASK is needed for serverless tasks.
• Tasks execute with the privileges of the task owner, so ownership changes can silently break a pipeline.

Rule of thumb

Pattern: source table → stream → task with a WHEN clause → MERGE into target. Always resume tasks after creation and monitor TASK_HISTORY.

Worked example

Land files with Snowpipe into RAW, capture changes with a stream, and have a task run every minute only when SYSTEM$STREAM_HAS_DATA('RAW_STREAM') is true, merging rows into SILVER with a single MERGE keyed on the business key.