SnowPro Associate: Platform Question 286
Single answer○ File format optionsA data engineering team loads daily CSV files from an external stage into a Snowflake table. The source files are generated by a legacy system with these characteristics: fields are enclosed in double quotes, some field values contain embedded commas, the first row is a header, and empty fields should be loaded as SQL NULL values rather than empty strings. The team wants to define a file format that handles these files correctly during COPY INTO. Which file format definition is the best choice?
- A
TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1 EMPTY_FIELD_AS_NULL = TRUE
- B
TYPE = CSV FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 1 NULL_IF = ('NULL')
- C
TYPE = CSV FIELD_ENCLOSED_BY = '"' PARSE_HEADER = TRUE REPLACE_INVALID_CHARACTERS = TRUE
- D
TYPE = CSV COMPRESSION = AUTO SKIP_HEADER = 1 ESCAPE_UNENCLOSED_FIELD = NONE
Show answer and explanation
Correct answer: A
Explanation
The best answer is the file format that explicitly addresses all stated CSV characteristics: quoted fields, embedded commas, a header row, and empty fields that should become NULL. In Snowflake, FIELD_OPTIONALLY_ENCLOSED_BY is the key option for CSV files that use quotes around fields, especially when field values can contain delimiters such as commas. SKIP_HEADER is used to ignore header rows during loading. EMPTY_FIELD_AS_NULL = TRUE is the appropriate setting when blank fields in delimited files should be interpreted as SQL NULLs. This reflects common Snowflake best practices for CSV ingestion with COPY INTO. Snowflake documentation for CREATE FILE FORMAT and COPY INTO table describes these CSV file format options and their behavior.
- A. Correct.
Correct. For CSV files where fields may be enclosed in double quotes and some values contain embedded commas, FIELD_OPTIONALLY_ENCLOSED_BY = '"' is the appropriate setting because it allows Snowflake to treat commas inside quoted values as part of the field rather than as delimiters. SKIP_HEADER = 1 correctly ignores the first header row. EMPTY_FIELD_AS_NULL = TRUE ensures empty fields are loaded as SQL NULL values instead of empty strings, which matches the requirement.
- B. Incorrect.
Incorrect. FIELD_DELIMITER and RECORD_DELIMITER are valid CSV settings, and SKIP_HEADER = 1 correctly addresses the header row, but this definition does not handle quoted fields with embedded commas. Without FIELD_OPTIONALLY_ENCLOSED_BY, Snowflake can split quoted values incorrectly. Also, NULL_IF = ('NULL') only maps the literal string NULL to SQL NULL; it does not convert empty fields to NULL.
- C. Incorrect.
Incorrect. Snowflake supports FIELD_OPTIONALLY_ENCLOSED_BY for CSV, not FIELD_ENCLOSED_BY. PARSE_HEADER is not the appropriate file format option for standard CSV loading in this scenario; skipping the header row with SKIP_HEADER is the expected approach. REPLACE_INVALID_CHARACTERS is unrelated to handling embedded commas or empty fields.
- D. Incorrect.
Incorrect. COMPRESSION = AUTO is a valid setting and SKIP_HEADER = 1 correctly skips the header, but this option set does not solve the key parsing requirement for double-quoted fields containing commas. ESCAPE_UNENCLOSED_FIELD = NONE affects escaping behavior for unenclosed fields, not recognition of quoted fields or conversion of empty fields to NULL.