ARA-C01 Question 262
Single answerExternal sources and formatsA retail company stores daily sales files in Amazon S3 and wants analysts to query the files immediately without loading them into Snowflake tables. The files are a mix of CSV and Parquet. CSV files use a pipe delimiter, may contain embedded line breaks inside quoted fields, and occasionally include malformed rows that should not cause the entire query to fail. The architects also want to minimize maintenance by avoiding per-query file format definitions. Which design best meets these requirements?
- A
Create an external stage on the S3 bucket with a named CSV file format configured for FIELD_DELIMITER='|', FIELD_OPTIONALLY_ENCLOSED_BY='"', MULTI_LINE=TRUE, and an error-handling setting such as ON_ERROR='CONTINUE'; query CSV files with SELECT from the stage and query Parquet files directly from the same stage using the Parquet file metadata and automatic schema interpretation.
- B
Create an external stage on the S3 bucket and define two named file formats: one for CSV with FIELD_DELIMITER='|', FIELD_OPTIONALLY_ENCLOSED_BY='"', MULTI_LINE=TRUE, and one for Parquet. Reference the appropriate named file format in each SELECT from the stage; use ON_ERROR='CONTINUE' in the SELECT for CSV if malformed records should be skipped.
- C
Load both CSV and Parquet files into a single VARIANT column table using COPY INTO, because Snowflake cannot query external staged files directly when the bucket contains more than one file type.
- D
Create a single named file format with TYPE=CSV and attach it to the external stage. Snowflake will automatically detect Parquet files in the same stage and apply the correct parser when querying them.
- E
Create external tables over the S3 location for both CSV and Parquet, because external tables are required whenever data must be queried in place without loading into Snowflake-managed storage.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use an external stage over S3 with separate named file formats for CSV and Parquet, then reference the appropriate file format in each query. This design meets the requirement to query data immediately without loading it into Snowflake tables and avoids repeating full file format definitions in every statement. For CSV, settings such as FIELD_DELIMITER='|', FIELD_OPTIONALLY_ENCLOSED_BY='"', and MULTI_LINE=TRUE are important when records can contain quoted fields with embedded line breaks. For malformed rows, architects should use supported error-handling behavior in the relevant command or query pattern rather than assuming such behavior is part of a file format object. Snowflake best practices generally recommend named file formats for reuse and consistency, especially when multiple ingestion or query patterns share the same parsing rules. Snowflake documentation covers querying data in stages, creating and reusing named file formats, and handling semi-structured and columnar formats such as Parquet differently from delimited text formats like CSV.
- A. Incorrect.
Incorrect. Using a named CSV file format on the stage can simplify CSV queries, but it does not satisfy the mixed-format requirement well because a stage-level file format is not appropriate for both CSV and Parquet access at the same time. Also, ON_ERROR is not a file format property for ad hoc stage queries; it is specified on operations such as COPY INTO or SELECT from staged files where supported, not embedded in the file format definition itself. For mixed file types in one stage, the safer and clearer design is to define separate named file formats and reference the correct one per query.
- B. Correct.
Correct. Snowflake supports querying staged files directly with SELECT statements, so loading into tables is not required for immediate access. For mixed-format files in one external stage, defining separate named file formats is the most maintainable approach and avoids repeating parsing settings in every query. The CSV format should include pipe delimiter handling, optional field enclosure, and MULTI_LINE=TRUE to support embedded line breaks inside quoted values. Parquet should use its own file format. Then each query can reference the appropriate FILE_FORMAT. If malformed CSV records should be skipped rather than fail the entire query or load, ON_ERROR can be specified in the relevant operation rather than assumed to be part of the file format itself.
- C. Incorrect.
Incorrect. Snowflake can query staged files directly using SELECT from internal or external stages, so loading into a VARIANT table is not required merely because multiple file types exist in the bucket. This option also changes the requirement from query-in-place to load-then-query, adding storage and ETL overhead that the scenario explicitly wants to avoid.
- D. Incorrect.
Incorrect. Snowflake does not automatically switch parsers based on file extension when a stage is configured with a single CSV file format. If the stage or query uses TYPE=CSV, Parquet files will not be interpreted correctly. Mixed file types require explicitly using the right file format for each query or separating file types into different stages or paths.
- E. Incorrect.
Incorrect. External tables are one way to query data in external storage, but they are not required. Snowflake also supports querying staged files directly with SELECT statements. External tables are useful for persistent metadata, partitioning strategies, and repeated query access patterns, but they add management overhead and are not mandated by the scenario.