SnowPro Associate: Platform Question 220
Single answer● CommandsA data engineer needs to load a CSV file from an external stage into a Snowflake table and quickly validate whether the file format, column order, and data types will load correctly before any rows are committed. Which command should the engineer use to inspect how Snowflake will parse the staged file without actually loading the data?
- A
COPY INTO target_table FROM @ext_stage FILE_FORMAT = (TYPE = CSV) VALIDATION_MODE = RETURN_ERRORS
- B
LIST @ext_stage
- C
SELECT * FROM target_table
- D
SELECT * FROM TABLE(INFER_SCHEMA(LOCATION => '@ext_stage', FILE_FORMAT => 'my_csv_format'))
Show answer and explanation
Correct answer: D
Explanation
The best answer is to use INFER_SCHEMA through TABLE(INFER_SCHEMA(...)) to inspect staged file structure before loading. This is a practical command when validating incoming files and checking whether the file's columns and types align with expectations. By contrast, LIST only confirms stage contents, and SELECT against the target table does nothing for staged file validation. COPY INTO ... VALIDATION_MODE helps validate load errors, but it is focused on load validation behavior rather than providing a clean schema inspection workflow. Snowflake documentation describes INFER_SCHEMA as a way to retrieve schema information from staged files, which is especially useful in data loading and onboarding scenarios.
- A. Incorrect.
Incorrect. COPY INTO with VALIDATION_MODE = RETURN_ERRORS is used to validate a load operation and report errors, but it does not inspect the parsed file structure in the way needed to quickly review inferred columns, order, and data types before loading. It is useful for load validation, but not the best command for previewing schema interpretation of staged files.
- B. Incorrect.
Incorrect. LIST shows files available in a stage, including names, sizes, and timestamps. It helps confirm that files are present, but it provides no insight into how Snowflake will interpret columns or data types in the file.
- C. Incorrect.
Incorrect. Querying the target table only shows data already loaded into the table. It does not help validate a newly staged file before loading, so it would not address the engineer's requirement.
- D. Correct.
Correct. INFER_SCHEMA can inspect supported staged files and return metadata such as column names, order, and inferred types without loading data into the table. This makes it the best command for validating how Snowflake will interpret the file structure before performing a load.