COF-C03 - Data Loading, Unloading, and Connectivity (18% of the exam) - Section 3.4

Build continuous and automated data pipelines with Snowpipe, streams, tasks, and dynamic tables.

Build automated ingestion and transformation pipelines: Snowpipe for continuous micro-batch loading with auto-ingest notifications, streams that record change data capture on a table, tasks that run SQL or call procedures on a schedule or in a tree, and dynamic tables that declaratively refresh from a query. Match a latency and orchestration need to the right feature.

Snowpipeauto-ingeststreams and change data capturetasksdynamic tables

Practice question for this objective

Free sampleData Loading, Unloading, and Connectivityhard

A data engineer wants a target table to stay automatically up to date from a join of two base tables, declaring the desired result with a query and a freshness target while letting Snowflake schedule and run the incremental refreshes. The engineer prefers not to wire up streams and tasks by hand. Which object best fits this requirement?

AA dynamic table defined by the join query with a target lag, so Snowflake automatically performs incremental refreshes to keep results within the declared freshness window Correct
BA materialized view defined over the two base tables, so Snowflake transparently maintains the joined result and refreshes it automatically as the underlying data changes
CA standard view over the join, because Snowflake caches and incrementally refreshes the view results in the background to meet a freshness target the engineer sets
DA scheduled task that rebuilds the target table from the join every few minutes, because the recurring schedule keeps the joined result current without manual streams

Recognise that a dynamic table declaratively maintains a query result, including multi-table joins, to a target lag via automatic incremental refresh. A dynamic table lets you declare the result of a query, including joins across several base tables, together with a target lag that expresses how fresh the data must be. Snowflake then schedules and performs incremental refreshes automatically, so the engineer avoids hand-building streams and tasks, and unlike a materialized view it supports joins across multiple tables.

Why A is correct: A dynamic table is declarative: the engineer specifies the query and a target lag, and Snowflake orchestrates incremental refreshes to keep it within that freshness window, removing the need to build streams and tasks manually.

Why B is wrong: A materialized view is tempting because it auto-maintains, but Snowflake materialized views cannot be defined on joins of multiple tables, so they cannot express this multi-table requirement.

Why C is wrong: A standard view stores no data and computes results at query time, so it has no stored result to keep fresh and offers no target-lag or incremental-refresh mechanism.

Why D is wrong: A scheduled task can refresh the table but the engineer must author the DML and schedule, it fully rebuilds rather than incrementally refreshing, and it is imperative rather than the declarative approach asked for.

See more COF-C03 practice questions, answers explained.

Exam traps in Data Loading, Unloading, and Connectivity

Answers that look right on this material and are not. Each one is a distractor from a different question in the COF-C03 bank for this domain.

Auto-ingest requires a scheduled serverless task to call SYSTEM$PIPE_FORCE_RESUME every minute, because a pipe stays suspended between notifications and must be resumed before each file loads.
Why it is wrong: This is tempting because pipes can be paused, but an auto-ingest pipe stays running and reacts to notifications on its own; it does not need a task to resume it for every file.
A dynamic table must be refreshed manually with ALTER DYNAMIC TABLE REFRESH because, unlike a materialized view, Snowflake never schedules its refreshes automatically once the target lag is set.
Why it is wrong: This is tempting because a manual refresh command exists, but the whole point of the target lag is that Snowflake schedules refreshes automatically, so the claim that it never does so is wrong.
Give each child task the same CRON schedule as the parent and add a short offset, so the children start a few seconds after the parent is expected to finish its load
Why it is wrong: Matching schedules with an offset only guesses when the parent ends; if the parent runs long the children start before it finishes, so this does not enforce a real dependency.

Examworthy is not affiliated with or endorsed by Snowflake. Original, blueprint-aligned practice material only.