DAA-C01 Question 74
Single answerLoad different types of dataA retail analytics team receives a daily drop of files in an external stage. Each drop contains a mix of CSV transaction files and newline-delimited JSON clickstream files. The team wants to load both file types into separate raw tables with minimal preprocessing, preserve semi-structured attributes from the JSON files, and prevent accidental loading of files into the wrong table. Which approach should the data analyst implement?
- A
Create two named file formats, one for CSV and one for JSON, and use separate COPY INTO commands for each target table with PATTERN filters that match the appropriate filenames.
- B
Use a single CSV file format for both file types and load the JSON files into a VARIANT column because Snowflake automatically detects JSON content during COPY INTO.
- C
Create one external table over the stage and use it to automatically populate both relational raw tables without defining file formats.
- D
Load all files into a single staging table as VARCHAR columns first, then convert both CSV and JSON into the final raw tables in a later step to avoid using PATTERN matching.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to define separate named file formats and use separate COPY INTO commands with filename filtering such as PATTERN. This matches Snowflake best practices for loading mixed file types from a shared stage. CSV data should be loaded with a CSV file format into structured columns, while newline-delimited JSON should be loaded with a JSON file format, typically into a VARIANT column to preserve semi-structured content for downstream analysis. Using PATTERN or other file-selection controls reduces the risk of misrouting files when multiple data types are stored in the same stage. This approach is consistent with Snowflake documentation for CREATE FILE FORMAT and COPY INTO
, including support for CSV and JSON file types, named file formats, staged data loading, and semi-structured data ingestion into VARIANT.- A. Correct.
Correct. This is the most appropriate and practical design. Snowflake supports separate named file formats for different source file types, such as TYPE = CSV and TYPE = JSON. Using distinct COPY INTO commands with file-format-specific settings and PATTERN filters helps ensure that only matching files are loaded into the intended target table. For the JSON files, loading into a VARIANT column preserves semi-structured attributes with minimal preprocessing, which aligns with the stated requirement.
- B. Incorrect.
Incorrect. Snowflake does not use a CSV file format to automatically interpret JSON during COPY INTO. JSON files should use a JSON file format. Although VARIANT is the correct target type for preserving semi-structured JSON data, the file format must still match the source structure. This option reflects a common misconception that VARIANT alone is sufficient for parsing any incoming file type.
- C. Incorrect.
Incorrect. External tables can query data in staged files, especially semi-structured formats, but they do not automatically populate multiple relational tables in the way described. File formats are still required for interpreting staged data. This option confuses querying staged data through external tables with loading data into Snowflake tables using COPY INTO.
- D. Incorrect.
Incorrect. While loading raw text into intermediate VARCHAR columns is sometimes possible, it adds unnecessary complexity and does not address the requirement to prevent files from being loaded into the wrong table. It also sacrifices the efficiency of Snowflake's native parsing for CSV and JSON. PATTERN-based filtering and proper file formats are the more direct and maintainable solution.