ARA-C01 Question 297
Single answerData unloadingA company must unload a 12 TB fact table from Snowflake to an Amazon S3 bucket every night for an external analytics platform. The downstream platform requires gzip-compressed CSV files with a header row, one file per date partition, and no records enclosed in quotation marks. The architecture team also wants to minimize manual post-processing and avoid generating many small files. Which approach best meets these requirements?
- A
Run a COPY INTO
command selecting the required partition columns, specify FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = NONE), HEADER = TRUE, SINGLE = TRUE, and use PARTITION BY date_column. - B
Run a COPY INTO
command with FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = NONE), HEADER = TRUE, and use PARTITION BY date_column without SINGLE = TRUE. - C
Create an external table on the S3 bucket and use INSERT INTO the external table with a CSV file format so Snowflake writes one gzip CSV file per date partition.
- D
Use GET on the table's internal stage after querying the fact table, because GET supports downloading query results as gzip CSV files with headers and partitioned output.
- E
Run a COPY INTO
command with FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"'), OVERWRITE = TRUE, MAX_FILE_SIZE = 5368709120, and PARTITION BY date_column.
Show answer and explanation
Correct answer: B
Explanation
For unloading Snowflake data to cloud storage, the supported and recommended pattern is COPY INTO
- A. Incorrect.
Incorrect. In Snowflake unload operations, SINGLE = TRUE cannot be used together with PARTITION BY. SINGLE forces creation of a single output file, while PARTITION BY writes separate files by partition value. Because the requirement is one file per date partition, this combination is not valid for the intended result and would not meet the design requirement.
- B. Correct.
Correct. COPY INTO
is the standard Snowflake method for unloading data to cloud storage. Using TYPE = CSV and COMPRESSION = GZIP satisfies the gzip CSV requirement. HEADER = TRUE includes the header row. FIELD_OPTIONALLY_ENCLOSED_BY = NONE avoids enclosing values in quotation marks. PARTITION BY date_column causes output to be organized by partition value, which aligns with the requirement to produce output by date partition while minimizing post-processing. Also, avoiding SINGLE = TRUE is necessary because it is incompatible with PARTITION BY and because Snowflake may generate multiple files per partition depending on volume; however, this is still the closest supported approach and best architectural choice among the options. - C. Incorrect.
Incorrect. External tables in Snowflake are for querying data already stored in external cloud storage; they are not a mechanism for unloading query results or writing files to S3. Snowflake unloads data using COPY INTO
, not INSERT INTO an external table. - D. Incorrect.
Incorrect. GET downloads files from an internal stage to a local file system where the client is running. It does not unload query results directly from a table to S3, and it does not provide partitioned unload behavior for external cloud storage targets. This reflects a common confusion between stage file transfer commands and unload commands.
- E. Incorrect.
Incorrect. Although COPY INTO
with PARTITION BY is the right family of command, this option sets FIELD_OPTIONALLY_ENCLOSED_BY to a double quote, which causes fields to be optionally enclosed in quotation marks. That directly conflicts with the stated requirement for no quotation marks. MAX_FILE_SIZE may help influence file sizing, but it does not guarantee exactly one file per partition.