SnowPro Advanced: Data Engineer · 35% of the exam

Data Movement: free practice questions

5 sample questions from our 88-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. A data engineering team is implementing a Change Data Capture pipeline. They create a standard Stream on a source table and use a Task to consume it every 10 minutes. The task runs the following pattern: ```sql INSERT INTO target_table SELECT col1, col2, METADATA$ACTION FROM my_stream WHERE METADATA$ACTION = 'INSERT'; ``` The team later realizes that UPDATE operations on the source table are not being correctly reflected in the target. Which TWO statements explain this behavior? (Select TWO)

A. Standard streams represent UPDATEs as a pair of rows: one DELETE row (METADATA$ACTION='DELETE', METADATA$ISUPDATE=TRUE) and one INSERT row (METADATA$ACTION='INSERT', METADATA$ISUPDATE=TRUE); the current WHERE clause discards the DELETE half but keeps the INSERT half✓ Correct
B. Standard streams do not capture UPDATE operations at all; only INSERT and DELETE actions are tracked
C. The task must use a MERGE statement instead of INSERT to correctly apply CDC changes including updates to the target table✓ Correct
D. The WHERE clause filtering on METADATA$ACTION = 'INSERT' will miss the update's new-value row because updates are recorded only as METADATA$ACTION = 'UPDATE'
E. The METADATA$ISUPDATE flag must be set to FALSE in the WHERE clause to correctly filter for true inserts versus update-inserts

Explanation

Option A is correct: in a standard stream, UPDATEs are represented as two rows — a DELETE row for the old value and an INSERT row for the new value, both with METADATA$ISUPDATE=TRUE. The current query keeps the INSERT half (new value) but discards the DELETE half, so the target will accumulate duplicate rows instead of updating them. Option C is correct: to properly apply CDC (handle inserts, update-new-values, and deletes), a MERGE statement matching on a primary key is required rather than a plain INSERT. Option B is false: standard streams DO capture updates, just as DELETE+INSERT pairs. Option D is false: there is no METADATA$ACTION = 'UPDATE' value; updates appear as DELETE and INSERT pairs. Option E is incorrect: filtering METADATA$ISUPDATE=FALSE would isolate true inserts from update-inserts, but the problem description is about updates not being reflected — this filter would make things worse, not better.

2. A data engineer configures a Snowpipe with AUTO_INGEST = TRUE on an S3 bucket. New files arrive roughly every 30 seconds. Three days after deployment, the ops team reports that no new data has been loaded for the past 6 hours, yet files continue to appear in S3. The SQS queue has a large number of messages. The pipe status shows PAUSED. What is the MOST likely root cause?

A. The S3 bucket's event notification was accidentally deleted, so SQS is no longer receiving new messages.
B. Snowpipe automatically pauses when the SQS queue depth exceeds 10,000 messages to prevent runaway costs.
C. Snowpipe encountered repeated file-level errors (e.g., malformed data) and the system automatically paused the pipe after a threshold of failures.✓ Correct
D. The Snowflake Storage Integration used by the stage expired after 72 hours and must be re-granted.

Explanation

Correct (C): Snowpipe will automatically pause a pipe if it encounters a sustained pattern of errors during ingestion (such as consistent file parsing failures). The presence of many messages in SQS but a PAUSED pipe status, combined with the timeline, is characteristic of automatic pausing due to repeated load errors. Why the others are wrong: A – If the event notification were deleted, SQS would receive no new messages; the problem states the SQS queue has a large backlog, implying notifications are still flowing. B – Snowpipe does not have a documented automatic pause mechanism triggered solely by queue depth; this is a common misconception. D – Storage Integrations do not expire after 72 hours. The IAM trust relationship established during setup is persistent until explicitly revoked.

3. Which of the following are valid stream types that can be created on a Snowflake TABLE object? (Select TWO)

A. Standard stream✓ Correct
B. Append-only stream✓ Correct
C. Insert-only stream
D. Delete-only stream
E. Update-only stream

Explanation

Correct (A - Standard stream): A standard stream on a table captures all DML changes: inserts, updates, and deletes. It is the default stream type for tables. | Correct (B - Append-only stream): An append-only stream on a table captures only INSERT operations, making it more efficient for use cases where only new rows matter (e.g., event log tables). | Wrong (C - Insert-only stream): Insert-only streams exist in Snowflake but are specifically for EXTERNAL TABLES, not standard internal tables. Confusing insert-only (external tables) with append-only (internal tables) is a common misconception. | Wrong (D - Delete-only stream): No such stream type exists in Snowflake. | Wrong (E - Update-only stream): No such stream type exists in Snowflake. All change capture types are bundled into the standard stream.

4. A data engineer needs to configure auto-ingest for a Snowpipe that loads files from a Google Cloud Storage (GCS) bucket. Which combination of Snowflake objects and GCS configuration steps is required?

A. Create a Storage Integration, create an external stage referencing the integration and GCS bucket, create a Snowpipe with AUTO_INGEST = TRUE, and configure a GCS Pub/Sub notification on the bucket pointing to the Snowpipe's SQS ARN.
B. Create a Storage Integration, create an external stage referencing the integration and GCS bucket, create a Snowpipe with AUTO_INGEST = TRUE, and configure a GCS Pub/Sub notification on the bucket; then register the Pub/Sub subscription with the Snowpipe.✓ Correct
C. Create an external stage with GCS credentials inline (no Storage Integration needed), create a Snowpipe with AUTO_INGEST = TRUE, and configure a GCS bucket trigger directly in Snowflake's UI.
D. Create a Storage Integration, create a Snowpipe with AUTO_INGEST = TRUE, and configure a GCS Pub/Sub notification; no external stage is required because the Snowpipe references the bucket URL directly.

Explanation

Option B is correct: For GCS auto-ingest, the required steps are: (1) create a Storage Integration object to securely delegate GCS access, (2) create an external stage that references the integration and the GCS bucket path, (3) create a Snowpipe with AUTO_INGEST = TRUE referencing the stage, and (4) configure a GCS Pub/Sub notification on the bucket and register the Pub/Sub subscription ARN with the Snowpipe using the notification_channel property. Option A is incorrect because it references an SQS ARN, which is the AWS S3 pattern, not GCS. GCS uses Pub/Sub topics/subscriptions, not SQS. Option C is incorrect for two reasons: embedding credentials inline is a security anti-pattern that bypasses Storage Integrations, and there is no 'GCS bucket trigger' configurable from Snowflake's UI. Option D is incorrect: a Snowpipe always requires an external stage; it does not accept a raw GCS bucket URL in place of a stage reference.

5. Which of the following statements about Snowflake Append-Only Streams are correct? (Select TWO)

A. An append-only stream on a table captures INSERT, UPDATE, and DELETE operations, but the METADATA$ACTION column will always show 'INSERT' for updates.
B. An append-only stream only captures row insertions; UPDATE and DELETE operations on the source table do not appear in the stream.✓ Correct
C. Append-only streams are more efficient than standard streams for use cases like event logging tables where rows are never updated or deleted.✓ Correct
D. An append-only stream can be created on an external table to capture newly discovered files in a stage.
E. Consuming an append-only stream advances its offset, so the same inserted rows cannot be re-read unless the stream is recreated.

Explanation

(B) is correct: append-only streams track only INSERT operations. UPDATEs and DELETEs are invisible to the stream, making them unsuitable for CDC on mutable tables. (C) is correct: because append-only streams skip change tracking overhead for updates/deletes, they are more performant and storage-efficient for immutable/append-only workloads like event logs. — (A) is wrong: this description fits a standard stream's behavior for pre/post-image rows, not an append-only stream; an append-only stream simply omits non-insert changes entirely. (D) is wrong: insert-only streams (not append-only streams) are created on external tables; the terminology is distinct in Snowflake documentation. (E) is wrong as a distinguishing fact: while offset advancement is true of all stream types, it is not specific to append-only streams and is therefore not a defining characteristic.

83 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free