SnowPro Associate: Platform Question 219
Single answer● CommandsA data engineering team loads daily CSV files from an internal stage into the table SALES_RAW. During testing, they discover that some files have rows with an extra column because of an upstream formatting issue. The team wants to validate the load first, identify whether any files contain parsing errors, and only then run the actual load for valid files. Which command is the best first step to meet this requirement?
- A
Run COPY INTO SALES_RAW FROM @internal_stage PATTERN='.*.csv' VALIDATION_MODE='RETURN_ERRORS';
- B
Run LIST @internal_stage PATTERN='.*.csv';
- C
Run GET @internal_stage file:///tmp/;
- D
Run REMOVE @internal_stage PATTERN='.*.csv';
Show answer and explanation
Correct answer: A
Explanation
When a team needs to validate staged data before loading it into a table, the most appropriate command is COPY INTO with the VALIDATION_MODE parameter. This enables Snowflake to parse the files using the defined file format and report errors without committing rows to the target table. For malformed CSV records, such as rows with an unexpected number of columns, VALIDATION_MODE='RETURN_ERRORS' is a practical way to identify issues early in the workflow. By contrast, LIST is only for viewing staged files, GET retrieves files from internal stages to local storage, and REMOVE deletes staged files. Snowflake documentation for COPY INTO
describes validation options such as RETURN_ERRORS and related modes for checking load readiness before executing production loads.- A. Correct.
Correct. The COPY INTO command supports VALIDATION_MODE, which allows the team to test staged files for load issues without performing the actual data load. Using VALIDATION_MODE='RETURN_ERRORS' returns parsing and data-load errors, making it an appropriate first step when checking for malformed CSV rows such as records with extra columns.
- B. Incorrect.
Incorrect. LIST shows which files exist in the stage and can help confirm file names or patterns, but it does not validate file contents or detect row parsing errors. Someone might choose this because it is often used before loading, but it only inventories staged files.
- C. Incorrect.
Incorrect. GET downloads files from an internal stage to a local file system. It is useful for manual inspection outside Snowflake, but it is not the best first step for validating staged data through Snowflake load logic. It also adds unnecessary operational overhead compared with built-in COPY validation.
- D. Incorrect.
Incorrect. REMOVE deletes files from a stage. This would be inappropriate as a first step because the team needs to inspect and validate files, not delete them. This option reflects a misunderstanding of stage management commands versus load-validation commands.