COF-C03 Question 72
Single answerData loadingA retail company receives hourly CSV files from a partner in an internal stage. The files are named with a timestamp and are never modified after delivery. A Snowflake table must be kept current by loading only new files, and the operations team wants to avoid reloading files that were already processed successfully. Which approach best meets these requirements with the least operational overhead?
- A
Run a COPY INTO target_table command from the stage on each schedule, allowing Snowflake to track which staged files have already been loaded successfully
- B
Use INSERT INTO target_table SELECT * FROM @stage on each schedule, because Snowflake automatically skips files that were previously queried from a stage
- C
Before each load, execute REMOVE on the stage to delete previously processed files, then run COPY INTO target_table for the remaining files
- D
Run TRUNCATE TABLE target_table before each hourly load, then use COPY INTO target_table with FORCE=TRUE to ensure consistency
Show answer and explanation
Correct answer: A
Explanation
For recurring bulk loads from staged files, Snowflake best practice is to use COPY INTO
. Snowflake stores metadata about successfully loaded files and, by default, skips them in future COPY operations from the same stage/path combination, which supports efficient incremental loading of immutable files. This behavior is central to avoiding duplicate processing with minimal administration. In contrast, querying files directly from a stage with SELECT does not create table load history, and FORCE=TRUE overrides duplicate prevention by reloading files. Although deleting files from a stage can be part of some operational designs, it is not necessary for deduplication when COPY INTO is used properly. See Snowflake documentation on COPY INTO- A. Correct.
Correct. COPY INTO
from a stage is the standard Snowflake bulk-loading method for staged files. Snowflake maintains load metadata for files that were loaded successfully, so subsequent COPY operations skip those files by default unless FORCE=TRUE is used. This is the lowest-overhead approach for incrementally loading immutable files from a stage. - B. Incorrect.
Incorrect. Querying staged files with SELECT from @stage does not provide the same load-tracking behavior as COPY INTO
. While SELECT can read staged file contents, it does not maintain the file load history used to prevent duplicate loads into a table. This option reflects a common misunderstanding between querying staged files and loading them. - C. Incorrect.
Incorrect. Removing files from the stage can prevent accidental reloads, but it adds unnecessary operational overhead and is not required to avoid duplicate loads when using COPY INTO
. Snowflake already tracks successfully loaded files for COPY operations. Deleting files may also reduce recoverability and auditability if the team later needs to inspect source files. - D. Incorrect.
Incorrect. Truncating the table before each load would discard previously loaded data, which conflicts with the requirement to keep the table current with only new files. Using FORCE=TRUE would also instruct Snowflake to reload files even if they were loaded before, increasing the risk of duplication rather than preventing it.