ARA-C01 Question 221
Single answerFile formatsA retail company receives daily product catalog files from multiple suppliers in Amazon S3. Each supplier delivers CSV files, but the files are inconsistent: some use commas, others use pipes, some fields are optionally enclosed in double quotes, embedded line breaks appear inside quoted descriptions, and several suppliers include an extra header row. The architecture team wants to create a Snowflake ingestion design that minimizes ongoing maintenance while allowing each supplier's files to be loaded reliably into the same target table. Which approach is the MOST appropriate?
- A
Create a separate named FILE FORMAT for each supplier and reference the appropriate file format in each COPY INTO command or external stage definition.
- B
Use a single generic CSV FILE FORMAT with SKIP_HEADER=1 and FIELD_OPTIONALLY_ENCLOSED_BY='"'; Snowflake will automatically detect the delimiter and handle embedded line breaks.
- C
Convert all supplier files to JSON in the target table by loading them into a VARIANT column first, because JSON file formats are more tolerant of inconsistent CSV structures.
- D
Set ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE in one shared CSV FILE FORMAT so Snowflake can ingest files with different delimiters, quoting rules, and header behavior without supplier-specific configuration.
Show answer and explanation
Correct answer: A
Explanation
The best architectural choice is to define separate named file formats for suppliers whose CSV files differ in delimiter, enclosure, header usage, or multiline behavior. Snowflake file formats are designed precisely for this purpose and can be referenced by stages and COPY INTO commands, improving reusability and reducing operational errors. This approach aligns with Snowflake best practices for external data loading: keep parsing rules explicit and reusable instead of relying on embedded ad hoc options or permissive error settings. Key Snowflake documentation areas include CREATE FILE FORMAT, data loading with COPY INTO, and CSV file format options such as FIELD_DELIMITER, FIELD_OPTIONALLY_ENCLOSED_BY, SKIP_HEADER, and handling for multiline fields. The incorrect options rely on capabilities Snowflake does not provide, such as automatic delimiter detection, or misuse settings like ERROR_ON_COLUMN_COUNT_MISMATCH, which do not address fundamental parsing differences.
- A. Correct.
Correct. In Snowflake, file format objects encapsulate parsing rules such as FIELD_DELIMITER, SKIP_HEADER, FIELD_OPTIONALLY_ENCLOSED_BY, ESCAPE, and handling of multiline records. When upstream suppliers produce structurally different CSV files, defining a separate named file format per supplier is the cleanest and most maintainable design. It allows COPY INTO operations or stages to apply the correct parsing behavior without repeatedly embedding format details in SQL. This is a practical architecture pattern for multi-source ingestion pipelines.
- B. Incorrect.
Incorrect. Snowflake does not automatically detect CSV delimiters during COPY INTO. A CSV file format must specify the expected delimiter or use the default comma, which would fail or misparse pipe-delimited files. Although FIELD_OPTIONALLY_ENCLOSED_BY can help with quoted fields, and multiline handling is supported through CSV file format settings, a single generic format cannot reliably ingest multiple incompatible CSV structures without supplier-specific configuration. SKIP_HEADER=1 also assumes every file has exactly one header row, which may not be universally true.
- C. Incorrect.
Incorrect. Snowflake supports loading JSON into VARIANT, but that does not solve inconsistent CSV parsing. Snowflake does not automatically reinterpret arbitrary CSV files as JSON. The CSV must still be parsed correctly first. Converting upstream CSV to JSON could be a separate preprocessing strategy outside Snowflake, but the option states loading them directly as JSON into VARIANT, which is not an appropriate or native solution for this scenario.
- D. Incorrect.
Incorrect. ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE only affects how Snowflake handles rows whose number of parsed columns differs from the target expectation. It does not make Snowflake infer delimiters, fix inconsistent quoting conventions, or correctly process differing header behaviors. This option reflects a common misconception that permissive error handling can replace proper file format definition. In reality, the parser still needs the correct structural rules for each supplier's files.