SnowPro Advanced: Data Engineer free question 1 / 10 · Domain 1: Data Movement
A team loads the same external-stage files with COPY INTO in bulk at night and also with an auto-ingest Snowpipe during the day. Later they find that rows from the same files are in the table twice. Which explanation is correct?
- If a file is overwritten later, both the nightly COPY INTO and the daytime Snowpipe treat it as a different file and load it again
- Both the nightly COPY INTO and the daytime Snowpipe keep their history in the table, but the retention periods of 64 days and 14 days do not match
- The nightly COPY INTO relies on the table's metadata and the daytime Snowpipe on the pipe's metadata to prevent duplicates, and neither sees the other's loads
- The daytime Snowpipe reads from the beginning again, on every notification, even the files that the nightly COPY INTO already loaded
Show the answer and explanation
Answer: C
Why this is correct
Bulk load history is stored in the metadata of the target table for 64 days, and Snowpipe load history is stored in the metadata of the pipe for 14 days. COPY INTO skips files already loaded by consulting the load metadata on the table side, and Snowpipe prevents reloading the same file by using the file metadata associated with the pipe. Each duplicate-prevention mechanism looks only at its own history, so when the same files are read through both, neither treats a file loaded by the other as already loaded, and the rows are inserted twice.
Why the other options are wrong
"If a file is overwritten later, both the nightly COPY INTO and the daytime Snowpipe treat it as a different file and load it again" … Snowpipe keeps the file path and name in the pipe metadata and does not reload a file with the same name even if it was modified later and its eTag changed. Overwriting a file is not what causes the duplicates.
"Both the nightly COPY INTO and the daytime Snowpipe keep their history in the table, but the retention periods of 64 days and 14 days do not match" … Bulk load history lives in the table metadata for 64 days, and Snowpipe history lives in the pipe metadata for 14 days. They are not both kept in the table; the two histories are stored in different places.
"The daytime Snowpipe reads from the beginning again, on every notification, even the files that the nightly COPY INTO already loaded" … Snowpipe remembers the files it has loaded in the pipe metadata and does not read the same file again. It does not start over from the beginning on every notification.
Common pitfall
The only way to reconcile the two histories is ALTER PIPE … REFRESH, which checks the load history of both the table and the pipe to avoid loading a file twice. The basic design is to avoid pointing a nightly bulk load and notification-based auto-ingest at the same files.