SnowPro Associate: Platform Question 256
Single answer3.3 Create tables in Snowflake and load data into the tables.A data engineer needs to load a daily CSV file of sales transactions from an internal stage into a Snowflake table. The file includes a header row, fields are comma-delimited, and some optional columns may be empty. The target table SALES_RAW already exists with columns in the same order as the file. The engineer wants to load the data while skipping the header row and preserving empty fields as NULL values. Which approach should the engineer use?
- A
Run COPY INTO SALES_RAW FROM @mystage/sales/ FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_DELIMITER = ',' EMPTY_FIELD_AS_NULL = TRUE);
- B
Run INSERT INTO SALES_RAW SELECT * FROM @mystage/sales/ FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1 FIELD_DELIMITER = ',' NULL_IF = ('NULL'));
- C
Create a temporary table from the stage using CREATE TEMP TABLE SALES_RAW AS SELECT * FROM @mystage/sales/;
- D
Run COPY INTO SALES_RAW FROM @mystage/sales/ FILE_FORMAT = (TYPE = JSON STRIP_OUTER_ARRAY = TRUE);
Show answer and explanation
Correct answer: A
Explanation
For loading staged CSV data into an existing Snowflake table, COPY INTO
is the recommended and most efficient command. In this scenario, the engineer must account for the file structure by defining the CSV file format characteristics directly in the command or through a named file format object. SKIP_HEADER = 1 prevents the header row from being loaded as data, and EMPTY_FIELD_AS_NULL = TRUE is important when optional columns may be blank and should be stored as NULL. This aligns with Snowflake best practices for bulk data loading using staged files. Snowflake documentation for COPY INTO table and CREATE FILE FORMAT explains these options and their behavior for delimited files.- A. Correct.
Correct. COPY INTO is the standard Snowflake command for bulk loading staged data into an existing table. The CSV file format options shown are appropriate for this scenario: SKIP_HEADER = 1 skips the header row, FIELD_DELIMITER = ',' matches the file structure, and EMPTY_FIELD_AS_NULL = TRUE ensures empty fields in delimited files are loaded as NULL rather than empty strings where applicable. Because the table already exists and the file columns align with the table column order, this is the best fit.
- B. Incorrect.
Incorrect. While Snowflake can query staged files with SELECT from stage in some contexts, INSERT INTO ... SELECT * FROM @stage is not the typical or best-practice approach for straightforward bulk loading of CSV files into an existing table. COPY INTO is specifically designed for this purpose, provides better load management and performance, and supports robust load options. Also, NULL_IF = ('NULL') only converts the literal string 'NULL' to SQL NULL; it does not address empty fields unless configured appropriately.
- C. Incorrect.
Incorrect. This does not meet the requirement. The target table already exists, and CREATE TEMP TABLE ... AS SELECT would create a new temporary table instead of loading into SALES_RAW. It also does not explicitly address skipping the header row or applying the required CSV parsing behavior unless additional file format settings are included.
- D. Incorrect.
Incorrect. JSON is the wrong file format for a CSV file. STRIP_OUTER_ARRAY is a JSON-specific option and has no relevance to comma-delimited text files. This option reflects a common mistake of choosing an incorrect file format when loading staged data.