DAA-C01 Question 72
Single answerLoad data from external/internal stages into a tableA data analyst needs to load daily CSV sales files into the SALES_RAW table. The files are already stored in a named internal stage called @sales_int_stage. Some rows occasionally contain malformed numeric values in the AMOUNT column, but the analyst wants the load to continue and skip only the problematic rows. The analyst also wants to avoid loading the same file twice if the COPY command is accidentally re-run. Which approach should the analyst use?
- A
Run COPY INTO SALES_RAW FROM @sales_int_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1) ON_ERROR = 'CONTINUE';
- B
Run INSERT INTO SALES_RAW SELECT * FROM @sales_int_stage (FILE_FORMAT => (TYPE => CSV, SKIP_HEADER => 1)) WHERE TRY_TO_DECIMAL($3) IS NOT NULL;
- C
Run COPY INTO SALES_RAW FROM @sales_int_stage FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1) ON_ERROR = 'ABORT_STATEMENT' FORCE = TRUE;
- D
Run MERGE INTO SALES_RAW USING @sales_int_stage s ON SALES_RAW.FILE_NAME = s.METADATA$FILENAME WHEN NOT MATCHED THEN INSERT *;
Show answer and explanation
Correct answer: A
Explanation
For loading files from internal or external stages into Snowflake tables, COPY INTO
is the primary and recommended command. In this scenario, the analyst needs three things: load from a named internal stage, skip malformed rows rather than fail the entire batch, and avoid reloading the same file on reruns. COPY INTO satisfies all three. The FILE_FORMAT clause defines how to parse the CSV files, ON_ERROR = 'CONTINUE' allows valid rows to load while problematic rows are skipped, and Snowflake's load metadata prevents successfully loaded files from being loaded again unless the user explicitly overrides that behavior with FORCE = TRUE. This is consistent with Snowflake documentation on data loading, COPY INTO- A. Correct.
Correct. COPY INTO from a named internal stage is the standard Snowflake method for bulk loading staged files into a table. Setting FILE_FORMAT for CSV with SKIP_HEADER = 1 handles header rows, and ON_ERROR = 'CONTINUE' allows the load to proceed while skipping rows that cause conversion or parsing errors. By default, Snowflake tracks load history for staged files and does not reload files that were already loaded successfully unless FORCE = TRUE is specified, which satisfies the requirement to avoid duplicate file loads on accidental re-runs.
- B. Incorrect.
Incorrect. While Snowflake supports querying staged files with SELECT, using INSERT ... SELECT from staged files is not the recommended bulk-loading mechanism for this scenario. It also does not automatically provide the same staged file load-history behavior as COPY INTO for preventing duplicate file ingestion. The TRY_TO_DECIMAL filter only addresses one specific column pattern and assumes the analyst knows the problematic column position and wants to discard rows manually, which is less robust than using COPY INTO with ON_ERROR handling.
- C. Incorrect.
Incorrect. ON_ERROR = 'ABORT_STATEMENT' does the opposite of the requirement: it stops the load when an error is encountered instead of continuing. In addition, FORCE = TRUE tells Snowflake to load files even if they were loaded before, which increases the risk of duplicate loads if the command is rerun. This option combines two settings that conflict directly with the stated business requirements.
- D. Incorrect.
Incorrect. MERGE is used to reconcile source and target rows based on matching keys, but it is not the normal method for directly bulk loading CSV files from a stage. Also, referencing staged file data in a MERGE in this way is incomplete and misleading, because staged file columns and metadata must be explicitly projected through a SELECT. More importantly, this does not address row-level parsing errors in CSV ingestion as effectively as COPY INTO with ON_ERROR, and it adds unnecessary complexity for a straightforward staged-file load.