SnowPro Associate: Platform Question 259
Single answer3.3 Create tables in Snowflake and load data into the tables.A data engineering team receives a daily CSV file from a vendor and uploads it to an internal Snowflake stage named @sales_stage. The file contains a header row, fields may be enclosed in double quotes, and some rows have empty values for optional columns. The team needs to load the data into a new permanent table named SALES_RAW while minimizing load failures caused by occasional bad records. They also want the load process to skip the header row correctly. Which approach should the engineer use?
- A
Create SALES_RAW with appropriate column definitions, define a CSV file format with SKIP_HEADER=1 and FIELD_OPTIONALLY_ENCLOSED_BY='"', then run COPY INTO SALES_RAW FROM @sales_stage FILE_FORMAT=(FORMAT_NAME='csv_fmt') ON_ERROR='CONTINUE'.
- B
Create SALES_RAW as a temporary table, then run SELECT * FROM @sales_stage to insert the CSV data directly into the table because Snowflake automatically ignores headers in staged CSV files.
- C
Use CREATE TABLE SALES_RAW AS SELECT * FROM @sales_stage and specify ON_ERROR='CONTINUE' in the CREATE TABLE statement so invalid rows are skipped during table creation.
- D
Run COPY INTO @sales_stage FROM SALES_RAW with SKIP_HEADER=1 and EMPTY_FIELD_AS_NULL=TRUE so Snowflake can transform the file in the stage before loading it into the permanent table.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create the target table, define an appropriate CSV file format, and use COPY INTO
from the stage. In Snowflake, loading structured data from staged files is commonly done with COPY INTO, and CSV-specific parsing behavior is controlled through file format options such as SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, and, when needed, EMPTY_FIELD_AS_NULL. ON_ERROR='CONTINUE' is a practical choice when the goal is to avoid failing the entire load because of a few malformed records. This aligns with Snowflake best practices for staged file ingestion and table loading. Relevant Snowflake documentation areas include CREATE TABLE, CREATE FILE FORMAT, and COPY INTO- A. Correct.
Correct. This is the standard Snowflake pattern for loading staged CSV data into a table. The table should be created first, then a CSV file format should be defined to handle parsing rules such as SKIP_HEADER=1 and FIELD_OPTIONALLY_ENCLOSED_BY='"'. Using COPY INTO
from the internal stage is the correct loading command. ON_ERROR='CONTINUE' is appropriate when the business requirement is to minimize load failures caused by a small number of bad rows, because valid rows will still load while problematic rows are skipped. - B. Incorrect.
Incorrect. Although temporary tables are valid Snowflake objects, the requirement is to load into a new permanent table named SALES_RAW. More importantly, Snowflake does not automatically ignore header rows in staged CSV files unless the file format or COPY options specify SKIP_HEADER. Also, querying staged files directly with SELECT can be used for inspection or transformation, but it is not the normal direct replacement for a controlled CSV load when parsing options and error handling are required.
- C. Incorrect.
Incorrect. CREATE TABLE AS SELECT (CTAS) can create a table from query results, including some staged file queries, but ON_ERROR is a COPY option, not a CTAS option. This choice incorrectly assumes CTAS provides the same loading controls as COPY INTO. It also does not directly address the CSV parsing requirements as clearly as a defined file format does.
- D. Incorrect.
Incorrect. This reverses the load direction. COPY INTO @stage unloads data from a table into a stage, while COPY INTO
loads data from a stage into a table. SKIP_HEADER applies when reading a staged file during load, not when transforming the staged file in place. EMPTY_FIELD_AS_NULL can be useful in file formats for handling empty fields, but this option does not describe a valid approach for the stated requirement.