SnowPro Associate: Platform Question 258
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 named stage in Snowflake. The file contains a header row, fields may be enclosed in double quotes, and some rows contain empty fields that must be loaded as NULL values. The team has already created a target table SALES_RAW with columns in the same order as the file. They want to load the file with minimal transformation and avoid inserting the header row as data. Which approach should the engineer use?
- A
Create a CSV file format with SKIP_HEADER = 1, FIELD_OPTIONALLY_ENCLOSED_BY = '"', and EMPTY_FIELD_AS_NULL = TRUE, then use COPY INTO SALES_RAW from the named stage with that file format.
- B
Use PUT to upload the file directly into SALES_RAW, because Snowflake can infer the CSV structure and automatically skip the header row during table loads.
- C
Create an external table on the CSV file and run INSERT INTO SALES_RAW SELECT * FROM the external table, because external tables automatically convert empty strings to NULL for CSV files.
- D
Use COPY INTO SALES_RAW with MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE and no file format options, because Snowflake automatically detects quoted CSV fields and ignores header rows when loading into an existing table.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to define and use a CSV file format that matches the incoming file characteristics, then load with COPY INTO. For delimited files, Snowflake file format options such as SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, and EMPTY_FIELD_AS_NULL are commonly used to control parsing behavior during loads. This approach is practical, repeatable, and aligns with Snowflake bulk-loading best practices: stage the file, define the file format, and execute COPY INTO the target table. PUT is only for uploading to internal stages, not tables. External tables are designed for querying data in external storage rather than as the default mechanism for routine loads into native Snowflake tables. Relevant Snowflake documentation includes guidance on CREATE FILE FORMAT, staged data, and COPY INTO
for loading structured data.- A. Correct.
Correct. This is the standard and appropriate way to load structured CSV data into an existing Snowflake table when the file layout is known. A file format can be defined with SKIP_HEADER = 1 to avoid loading the header row, FIELD_OPTIONALLY_ENCLOSED_BY = '"' to correctly parse values enclosed in double quotes, and EMPTY_FIELD_AS_NULL = TRUE so empty fields are loaded as SQL NULLs rather than empty strings. COPY INTO
from a stage is the recommended bulk-loading pattern for this scenario. - B. Incorrect.
Incorrect. PUT uploads local files to an internal stage; it does not load files directly into a table. Snowflake does not support uploading a file straight into SALES_RAW as a table target using PUT. After staging, a separate COPY INTO command is required to load the data. Also, Snowflake does not automatically infer all CSV handling behavior unless explicitly configured.
- C. Incorrect.
Incorrect. External tables are primarily used to query data in external cloud storage without fully loading it into Snowflake-managed tables. They are not the simplest or best fit for a daily CSV load into an internal table with minimal transformation, especially when the file is already in a named stage and the target table exists. In addition, empty-field handling for CSV still depends on file format configuration rather than being automatically converted to NULL in all cases.
- D. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME is used in certain loading scenarios, commonly with semi-structured formats or when matching source fields to target columns by name, but it does not replace proper CSV file format settings. Without SKIP_HEADER, the header row could be loaded as data. Without FIELD_OPTIONALLY_ENCLOSED_BY, quoted fields may not be parsed as intended. Snowflake does not automatically ignore header rows or fully detect all CSV parsing requirements when those options are omitted.