ARA-C01 Question 298
Single answerData unloadingA retail company must unload a 12 TB fact table from Snowflake every night to an Amazon S3 data lake for downstream Spark processing. The Spark jobs expect many compressed text files of roughly similar size so that work can be parallelized efficiently. The current process uses a simple COPY INTO
- A
Use COPY INTO
with PARTITION BY on a high-cardinality column so Snowflake creates uniformly sized files automatically. - B
Use COPY INTO
with SINGLE=TRUE and MAX_FILE_SIZE to create one large file that Spark will split evenly during processing. - C
Use COPY INTO
with a FILE FORMAT that uses compression and specify MAX_FILE_SIZE so Snowflake targets output files up to the requested size. - D
Create an external table on S3 and use ALTER EXTERNAL TABLE REFRESH after unloading so Snowflake can rebalance file sizes across partitions.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to use COPY INTO
By contrast, PARTITION BY is useful when consumers benefit from directory-style partitioning, but it is not a file-size control mechanism and can worsen file skew if the partition key is too granular. SINGLE=TRUE reduces output to one file and undermines parallelism. External table refresh operations only update file metadata for querying external data and do not change the unloaded file layout.
This aligns with Snowflake documentation and best practices for COPY INTO
- A. Incorrect.
Incorrect. PARTITION BY in COPY INTO
organizes unloaded data into separate path prefixes based on the partition expression, but it does not guarantee uniformly sized files. In fact, using a high-cardinality column can create many small partitions and lead to more uneven output. PARTITION BY is primarily for organizing files by logical partition values, not for controlling consistent file size. - B. Incorrect.
Incorrect. SINGLE=TRUE forces Snowflake to write a single output file, which directly conflicts with the requirement for many files to support downstream Spark parallelism. Also, MAX_FILE_SIZE does not help when SINGLE=TRUE is used to produce one file. This option reflects a common misconception that downstream engines will always compensate for poor file layout efficiently.
- C. Correct.
Correct. For unloading to cloud storage such as Amazon S3, COPY INTO
supports file-format options such as compression and the MAX_FILE_SIZE copy option to influence the target size of each unloaded file. While Snowflake does not guarantee identical file sizes, MAX_FILE_SIZE is the correct mechanism to make file sizing more predictable and to generate multiple compressed text files suitable for parallel Spark consumption. - D. Incorrect.
Incorrect. External tables are for querying data already stored in external locations such as S3. ALTER EXTERNAL TABLE REFRESH updates metadata about files in the external stage; it does not modify, rebalance, or rewrite unloaded files. This option confuses metadata management with physical file generation during unload.