DAA-C01 Question 119
Single answerParquetA retail analytics team receives daily Parquet files in an external stage on Amazon S3. Each file contains nested customer and order attributes, but analysts only need a small subset of columns for reporting. The current load process uses a broad SELECT from the staged files and has become slower and more expensive as the files have grown in width. The team wants to improve load efficiency while preserving support for nested Parquet structures. Which approach should they use?
- A
Create a file format of TYPE = PARQUET and use COPY INTO with a SELECT statement that projects only the required columns from the staged Parquet files
- B
Convert the Parquet files to CSV before loading because Snowflake can only prune columns efficiently for delimited text formats
- C
Load the full Parquet records into a VARIANT column first, then create a view that selects only needed fields; this is the only way to preserve nested Parquet data
- D
Use MATCH_BY_COLUMN_NAME with a CSV file format so Snowflake can automatically map nested Parquet fields into relational columns
Show answer and explanation
Correct answer: A
Explanation
The best solution is to use a PARQUET file format and load only the required fields with a SELECT from the staged files. Snowflake supports querying staged Parquet data and transforming it during COPY INTO. Since Parquet is a columnar file format, projecting only needed columns is aligned with best practices for reducing unnecessary data scanning and improving ingestion efficiency. Snowflake documentation for loading semi-structured data and querying data in staged files describes using file formats such as TYPE = PARQUET and selecting from staged files, including nested elements. Converting Parquet to CSV is unnecessary and usually counterproductive, while loading all content into VARIANT first adds extra work and cost when the reporting use case only needs a subset of fields.
- A. Correct.
Correct. Snowflake supports loading from Parquet using a PARQUET file format, and it is a best practice to project only the columns needed when selecting from staged semi-structured files. Because Parquet is a columnar format, selecting only required columns can reduce unnecessary data processing compared with broadly selecting all fields. Snowflake also supports reading nested data from Parquet, so this approach preserves access to nested structures while improving efficiency.
- B. Incorrect.
Incorrect. Snowflake natively supports Parquet and does not require conversion to CSV. In fact, converting Parquet to CSV would typically lose some of Parquet's columnar benefits and can make ingestion less efficient. The misconception here is that text formats are better for pruning, but Parquet is specifically designed as a columnar storage format.
- C. Incorrect.
Incorrect. Loading everything into a VARIANT column can preserve nested data, but it is not the only way to work with nested Parquet data in Snowflake, nor is it the most efficient approach for this scenario. If the requirement is to improve performance and reduce cost when analysts only need a subset of columns, directly projecting the required columns during load is more appropriate than first loading complete records into VARIANT.
- D. Incorrect.
Incorrect. MATCH_BY_COLUMN_NAME can be used in certain load scenarios, including with columnar file types, but specifying a CSV file format for Parquet data is invalid. Also, nested Parquet fields are not handled simply by declaring a CSV format. This option mixes unrelated features and reflects a common misunderstanding about how file formats and column mapping work.