DAA-C01 Question 120
Single answerParquetA retail analytics team receives daily Parquet files in an external stage on cloud storage. The files contain a nested column named CUSTOMER that includes fields such as ID, TIER, and REGION. Analysts only need ID, REGION, ORDER_TOTAL, and ORDER_TS in Snowflake, and they want the load process to be resilient to extra fields being added to future Parquet files without failing. Which approach best meets these requirements?
- A
Create a file format of TYPE = PARQUET and use a COPY INTO target table statement that selects only the required fields from the staged files, including nested fields from CUSTOMER.
- B
Load the full Parquet rows into a single VARCHAR column first, then use SPLIT_PART to parse the nested CUSTOMER fields into relational columns.
- C
Convert the Parquet files to CSV before loading because Snowflake can only project individual columns during load from delimited text formats.
- D
Use MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE in COPY INTO to automatically flatten the nested CUSTOMER object into target table columns and ignore all new fields.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use Snowflake's native Parquet support with a PARQUET file format and a COPY INTO ... FROM (SELECT ...) pattern to extract only required fields, including nested values. This is a practical approach when analysts need a relational subset of a wider Parquet schema and want to tolerate schema evolution in unused fields. Snowflake documentation describes using staged file queries and COPY INTO with transformations for loading selected data from semi-structured file formats, including Parquet. It also distinguishes between simple column-name matching and true extraction of nested structures. In practice, explicitly selecting needed fields reduces downstream complexity and prevents failures caused by relying on assumptions about automatic flattening of nested objects.
- A. Correct.
Correct. Snowflake supports loading from Parquet using a PARQUET file format, and COPY INTO can load from a SELECT over staged files. In that SELECT, you can project only the needed columns and reference nested elements from semi-structured data paths, which is appropriate for Parquet data. This approach avoids loading unnecessary fields and is resilient to new, unused fields appearing in the Parquet files because only the selected fields are loaded.
- B. Incorrect.
Incorrect. Parquet is a columnar binary format, not a delimited text payload that should be loaded into VARCHAR and parsed with string functions. Nested fields in Parquet should be accessed through semi-structured querying patterns rather than string parsing. This option reflects a common misconception carried over from CSV-style ingestion.
- C. Incorrect.
Incorrect. Snowflake supports Parquet natively and can query and load from Parquet directly. Converting Parquet to CSV would add unnecessary preprocessing, lose some schema richness, and is not required to project specific fields. The claim that column projection during load is only available for delimited text formats is false.
- D. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME can help align source columns to target columns by name in supported loading scenarios, but it does not automatically flatten nested Parquet objects into separate relational columns. Nested fields such as CUSTOMER.ID and CUSTOMER.REGION still need to be explicitly addressed. This option is plausible because the feature name suggests automatic mapping, but it does not replace explicit extraction of nested elements.