SnowPro Associate: Platform Question 257
Single answer3.3 Create tables in Snowflake and load data into the tables.A data engineer needs to load a daily CSV file from an internal stage into a Snowflake table named SALES_RAW. The file contains a header row, fields are comma-delimited, and some records may have invalid numeric values in the AMOUNT column. The business requirement is to load all valid rows while skipping invalid rows, without stopping the entire load. Which approach should the engineer use?
- A
Create SALES_RAW with appropriate column data types, define a CSV file format with SKIP_HEADER = 1 and FIELD_DELIMITER = ',', then run COPY INTO SALES_RAW ... ON_ERROR = CONTINUE
- B
Run INSERT INTO SALES_RAW SELECT * FROM @internal_stage/daily_sales.csv and rely on Snowflake to automatically skip the header row and invalid numeric values
- C
Create SALES_RAW as a temporary table, then use PUT and GET commands to transform invalid numeric values before loading
- D
Use CREATE TABLE SALES_RAW AS SELECT * FROM @internal_stage/daily_sales.csv with a CSV file format so that invalid rows are automatically ignored
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use COPY INTO with an appropriate file format and ON_ERROR = CONTINUE. In Snowflake, bulk loading staged data into tables is typically done with COPY INTO
. For CSV files, file format options such as TYPE = CSV, FIELD_DELIMITER = ',', and SKIP_HEADER = 1 control how the file is parsed. When some rows may contain invalid values but the requirement is to continue loading valid rows, ON_ERROR = CONTINUE is the correct load option. This aligns with Snowflake best practices for staged file ingestion and structured data loading. Relevant Snowflake documentation includes the sections on CREATE FILE FORMAT, COPY INTO- A. Correct.
Correct. This is the standard Snowflake pattern for loading staged CSV data into an existing table. A file format can specify CSV parsing behavior such as FIELD_DELIMITER and SKIP_HEADER = 1. The COPY INTO command is designed for bulk loading from stages into tables, and ON_ERROR = CONTINUE allows valid rows to load while skipping rows that fail conversion, such as invalid numeric values in the AMOUNT column.
- B. Incorrect.
Incorrect. INSERT ... SELECT from a staged file is not the appropriate bulk-loading approach for this requirement, and Snowflake does not automatically skip header rows or bad records unless instructed through a file format and load options. The candidate might choose this option if they confuse querying staged files with production data loading behavior.
- C. Incorrect.
Incorrect. Temporary tables do not solve the stated loading problem. Also, PUT uploads local files to an internal stage and GET downloads files from a stage; neither command transforms file contents. This option reflects a common misconception that stage commands perform data cleansing.
- D. Incorrect.
Incorrect. CREATE TABLE AS SELECT (CTAS) is used to create a table from a query result, but it is not the correct mechanism for resilient bulk loading of staged CSV files with row-level error handling. Error handling such as ON_ERROR = CONTINUE is associated with COPY INTO, not CTAS. This option is plausible because staged files can be queried, but it does not best meet the requirement.