ARA-C01 Question 263
Single answerExternal sources and formatsA retail company stores daily product catalog exports as CSV files in an Amazon S3 bucket. The files are generated by multiple upstream systems, so some rows contain quoted fields with embedded commas, some fields are optionally enclosed in double quotes, and the string NULL should be loaded as SQL NULL. The architect wants to let analysts query the files directly first, without loading them into Snowflake tables, and ensure the parsing behavior matches a later COPY INTO process. Which approach should the architect use?
- A
Create an external stage pointing to the S3 bucket and define a named CSV file format with FIELD_OPTIONALLY_ENCLOSED_BY='"', NULL_IF=('NULL'), and the appropriate FIELD_DELIMITER, then query the files using SELECT from the stage with that file format.
- B
Create an external table on the S3 bucket without specifying a file format because Snowflake automatically detects CSV quoting and NULL handling for external sources.
- C
Use a JSON file format with STRIP_OUTER_ARRAY=TRUE when querying the S3 files, because quoted CSV fields with commas are parsed more reliably through the semi-structured engine.
- D
Load one sample file into a temporary table first, then create an external stage from that temporary table so analysts can query the remaining S3 files with consistent parsing.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to define an external stage over the S3 location and use a named CSV file format that explicitly captures the source characteristics. In Snowflake, file formats are the standard mechanism for describing external data characteristics such as delimiters, optional text enclosure, escape handling, header rows, compression, and NULL representations. For CSV files containing embedded commas inside quoted fields, FIELD_OPTIONALLY_ENCLOSED_BY must be set correctly so Snowflake treats commas inside quoted strings as part of the field value rather than as delimiters. NULL_IF allows the architect to map specific source literals such as 'NULL' to SQL NULL values. Querying staged files directly with SELECT ... FROM @stage and using the same named file format later in COPY INTO is a best practice because it validates parsing before loading and ensures consistency across ingestion methods. This aligns with Snowflake documentation for CREATE FILE FORMAT, CREATE STAGE, querying data in staged files, and COPY INTO usage with named file formats.
- A. Correct.
Correct. For querying staged files directly, Snowflake supports querying data in stages with SELECT statements and applying a named file format or inline file format options. A named CSV file format is the correct way to control parsing of optional double quotes, embedded commas inside quoted fields, field delimiters, and conversion of the literal string NULL to SQL NULL using NULL_IF. Using the same named file format later in COPY INTO ensures consistent behavior between ad hoc querying and production loading.
- B. Incorrect.
Incorrect. Snowflake does not automatically infer all CSV parsing behaviors such as optional enclosure characters and custom NULL string handling in a way that should be relied on for production design. External tables require a file format definition for structured parsing behavior. Assuming automatic detection is a common misconception, especially with CSV, where quoting and null semantics must be explicitly controlled.
- C. Incorrect.
Incorrect. JSON file format options apply to JSON data, not CSV. STRIP_OUTER_ARRAY is specifically for JSON arrays and has no role in parsing quoted CSV fields. This distractor reflects a misunderstanding that semi-structured parsing can be used as a workaround for malformed or complex CSV, which is not the recommended or correct design.
- D. Incorrect.
Incorrect. A stage is an object that references external cloud storage or internal Snowflake staging storage; it is not created from a table. Loading a sample file into a temporary table does not establish parsing rules for direct querying of remaining external files. The right mechanism is to define a reusable file format and associate it with the stage or query.