COF-C03 Question 210
Single answerFile formatsA data engineering team needs to load daily product catalog files from an external stage into Snowflake. The files are JSON documents with deeply nested arrays and objects, and the schema may evolve over time as new attributes are added. Analysts want to query both existing and newly added attributes without requiring the load process to be redesigned each time the source changes. Which file format should the team use to best support this requirement?
- A
Create a CSV file format and load the data into fixed relational columns
- B
Create a JSON file format and load the data into a VARIANT column
- C
Create an Avro file format and load the data into VARCHAR columns for flexibility
- D
Create a PARQUET file format and flatten the nested objects during the file load automatically
Show answer and explanation
Correct answer: B
Explanation
The best choice is to use a JSON file format and load the data into a VARIANT column. This approach aligns with Snowflake best practices for semi-structured data when the source contains nested objects and arrays and may change over time. Snowflake natively supports semi-structured file formats such as JSON, Avro, ORC, Parquet, and XML. For JSON specifically, storing the payload in VARIANT allows downstream users to query nested attributes without requiring rigid schema definitions up front. This is especially useful when new keys appear in the source over time. In Snowflake documentation, semi-structured data is commonly loaded into VARIANT and queried using path notation, with FLATTEN used when expanding arrays. CSV is not appropriate for preserving nested structures, and neither Avro nor Parquet should be selected here because the source files are JSON and the question asks for the best fit for this exact scenario.
- A. Incorrect.
Incorrect. CSV is best suited for delimited, tabular data. Deeply nested arrays and objects are not naturally represented in CSV, and loading into fixed relational columns would make schema evolution difficult because new attributes would typically require changes to the table structure and load logic.
- B. Correct.
Correct. JSON is a semi-structured file format supported by Snowflake, and loading JSON into a VARIANT column is the standard approach for handling nested data and evolving schemas. Snowflake allows querying semi-structured content directly using dot and bracket notation, so newly added attributes can often be accessed without redesigning the ingestion pipeline.
- C. Incorrect.
Incorrect. Snowflake does support Avro as a file format, but loading it into VARCHAR columns is not the appropriate design for preserving nested structure and flexible querying. Semi-structured formats such as Avro are typically loaded into VARIANT, OBJECT, or ARRAY-compatible structures rather than plain text columns when schema flexibility is required.
- D. Incorrect.
Incorrect. Snowflake supports Parquet, including nested data, but Snowflake does not automatically flatten nested objects during file load simply because a Parquet file format is used. Flattening is typically done at query time with functions such as FLATTEN, not implicitly by the file format. Also, the scenario specifically states the source files are JSON.