Exam SnowPro Advanced Architect topic 1 question 4 discussion

Actual exam question from Snowflake's SnowPro Advanced Architect

Question #: 4
Topic #: 1

[All SnowPro Advanced Architect Questions]

An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously.
Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)

A. COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;
B. COPY INTO tablea FROM @%tablea;
C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv');
D. COPY INTO tablea FROM @%tablea FORCE = TRUE;
E. COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;
F. COPY INTO tablea FROM @%tablea MERGE = TRUE;

Show Suggested Answer

Suggested Answer: BC 🗳️

by henrik0 at March 31, 2023, 5:26 p.m.

Comments

Submit Cancel

serg_khar

Highly Voted 2 years, 3 months ago

Key point here - "ON_ERROR=SKIP_FILE". It means that file hasn't loaded. I we want to load the file we have to point it explicitly - this is C option or load all unloaded files (with file5.csv); its B option. If we use FORCE we will load again ALL data from the stage - it doesn't look as a good option.

upvoted 5 times

...

Gandharv75

Most Recent 7 months, 2 weeks ago

Option B and C are correct. Question says bulk load files, so there may be files other than fil5.csv. When File5.csv was modified and re-uploaded a new checksum was generated by snowflake, so B. COPY INTO tablea FROM @%tablea- will work as it won't load the files already loaded first time except file5.csv which has a new checksum now. Also, C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv'); works because it specifically requests file5.csv to be loaded only(which already has anew checksum,so will be loaded successfully) Hence B and C only..

upvoted 1 times

...

ggorzki

1 year ago

Selected Answer: BC

B and C Parameter NEW_FILES_ONLY does not exist. File is already changed in the stage, so FORCE = TRUE is not needed. Besides, it would load duplicates from the other files that reside in the stage.

upvoted 1 times

...

laksnarn

1 year ago

C & D Here's the reasoning: C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv'); This command specifically targets the file file5.csv, ensuring that only this file is loaded. D. COPY INTO tablea FROM @%tablea FORCE = TRUE; This command forces the reloading of the file, ignoring any previous load history and thus ensuring the fixed file5.csv is loaded even if the system might otherwise think it has already been processed.

upvoted 2 times

...

ivanjs44

1 year, 2 months ago

The key of this question is that say: The Architect fixes the file and re-loads Here in Snowflake documentation: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table#usage-notes To reload the data, you must either specify FORCE = TRUE or ******modify the file and stage it again, which generates a new checksum.****

upvoted 1 times

...

cornz55

1 year, 7 months ago

The answer is C and D. You cannot choose C and take any other answer other than D because the file was previously loaded (with failure). So C will never work again unless there is D.

upvoted 1 times

...