DAA-C01 Question 67
Single answer1.6 Given a scenario, prepare data and load into Snowflake.A retail analytics team receives a daily CSV file from a third-party vendor in an Amazon S3 bucket. The file contains a header row, uses pipe (|) as the delimiter, encloses some text fields in double quotes, and represents missing numeric values as the string NULL. Analysts need the data loaded into a Snowflake table with missing numeric values stored as SQL NULLs. They also want to avoid loading the header row as data and be able to rerun the load safely if the same file appears again. Which approach best meets these requirements?
- A
Create a named file format with TYPE = CSV, FIELD_DELIMITER = '|', SKIP_HEADER = 1, FIELD_OPTIONALLY_ENCLOSED_BY = '"', and NULL_IF = ('NULL'); stage the file and use COPY INTO the target table from the stage.
- B
Use COPY INTO without a file format and rely on Snowflake to infer the delimiter, header row, quoted fields, and NULL handling automatically for CSV files stored in S3.
- C
Load the file into a VARIANT column first using JSON parsing, then cast the data into relational columns so that the header row and NULL values are handled during SELECT.
- D
Use Snowpipe Streaming to ingest the S3 file directly into the target table, because it automatically skips CSV headers and prevents duplicate file loads without additional configuration.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to define a file format that matches the source file characteristics and then load with COPY INTO from a stage. For CSV and other delimited files, Snowflake best practice is to separate parsing behavior into a reusable named file format. In this scenario, the important settings are TYPE = CSV, FIELD_DELIMITER = '|', SKIP_HEADER = 1, FIELD_OPTIONALLY_ENCLOSED_BY = '"', and NULL_IF = ('NULL'). These ensure the data is prepared correctly during ingestion so that analysts do not need post-load cleanup for common formatting issues. Using staged files plus COPY INTO is also appropriate for S3-based batch ingestion and supports idempotent behavior through Snowflake's load metadata tracking, which helps prevent accidental duplicate loads of the same file. This aligns with Snowflake documentation on data loading, file formats, staged data, and COPY INTO behavior.
- A. Correct.
Correct. This is the standard and most reliable approach for preparing and loading structured CSV data into Snowflake. A named file format lets you explicitly define how Snowflake should parse the incoming file: SKIP_HEADER = 1 prevents the column names from being loaded as data, FIELD_DELIMITER = '|' matches the vendor's format, FIELD_OPTIONALLY_ENCLOSED_BY = '"' handles quoted text values correctly, and NULL_IF = ('NULL') converts the literal string NULL into SQL NULL during load. Using COPY INTO from a stage is the recommended bulk-loading pattern for files in cloud storage. In addition, Snowflake tracks loaded files for COPY operations to help avoid reloading the same file unintentionally unless the user explicitly forces a reload.
- B. Incorrect.
Incorrect. Snowflake does not automatically infer all CSV parsing details during COPY INTO in the way described here. If the file uses a non-default delimiter, contains a header row, or requires special NULL handling, those parsing rules must be defined explicitly through file format options. Relying on defaults would likely cause the header row to be loaded as data, parse the file incorrectly because the default delimiter is a comma, and leave the string NULL as text rather than converting it to SQL NULL.
- C. Incorrect.
Incorrect. CSV files should generally be loaded using CSV file format options, not JSON parsing into VARIANT. Loading delimited text into VARIANT via JSON parsing is not appropriate for this scenario and does not solve the parsing requirements cleanly. It also adds unnecessary complexity and processing overhead. The requirement is straightforward structured-file ingestion, which COPY INTO with a proper CSV file format is designed to handle.
- D. Incorrect.
Incorrect. Snowpipe Streaming is intended for row-by-row or event-based streaming ingestion from client applications, not for directly reading staged files from S3 in the same bulk-file pattern as COPY INTO. Standard Snowpipe can automate file ingestion from cloud storage events, but even then you still need the correct file format settings to handle headers, delimiters, quotes, and NULL values. The statement that Snowpipe Streaming directly ingests the S3 file and automatically handles these CSV details is inaccurate.