A Data Engineer is creating a pipe by executing the following statement: create or replace pipe mypipe auto_ingest=true as copy into mytable from @mystage; The Data Engineer adds a file data.csv.gz…

The correct answer is C. drop table mytable; D. create or replace pipe mypipe auto_ingest=true as copy into mytable. Dropping the table (C) destroys all associated load history metadata, so when the table is recreated and the pipe runs again, Snowflake has no record that data.csv.gz was previously ingested and treats it as a new file. Recreating the pipe with CREATE OR REPLACE PIPE (D) resets…

Data Movement

Question

A Data Engineer is creating a pipe by executing the following statement:

create or replace pipe mypipe auto_ingest=true as copy into mytable from @mystage; The Data Engineer adds a file data.csv.gz to the stage and it successfully loads into the table mytable. What will cause the file data.csv.gz to be loaded a second time? (Choose two.)

Options

Acopy into mytable from @mystage;
Btruncate table mytable;
Cdrop table mytable;
Dcreate or replace pipe mypipe auto_ingest=true as copy into mytable
EThe file will automatically be reloaded a second time after eight days.

How the community answered

(34 responses)

A
6% (2)
B
3% (1)
C
74% (25)
E
18% (6)

Explanation

Dropping the table (C) destroys all associated load history metadata, so when the table is recreated and the pipe runs again, Snowflake has no record that data.csv.gz was previously ingested and treats it as a new file. Recreating the pipe with CREATE OR REPLACE PIPE (D) resets the pipe's own load history, causing it to re-queue files from the stage notification event log as if they were never loaded.

Why the distractors are wrong:

A - A manual COPY INTO checks the table's load history and skips files already marked as loaded; it won't re-ingest them.
B - TRUNCATE TABLE removes rows but preserves the load history metadata, so Snowflake still "remembers" the file and will not reload it.
E - The automatic re-ingestion window is 64 days (not 8), and files are blocked during that window, not scheduled for a second load.

Memory tip: Think "wipe the slate, reload the file." Only actions that destroy metadata - dropping the table or replacing the pipe - make Snowflake forget a file was ever loaded. Truncating data ≠ truncating history.

Topics

#Snowflake Pipes#Data Loading#Auto-ingest#Metadata Tracking

Community Discussion

No community discussion yet for this question.

Full DEA-C02 Practice