ARA-C01 Question 276
Single answerBulk file uploadA retail company receives hourly batches of compressed CSV files from stores and uploads them to an internal Snowflake stage before loading them into a fact table. During peak periods, each batch contains thousands of files, and architects have observed that upload time from the data center to Snowflake is the main bottleneck. The company wants to reduce end-to-end ingestion time without changing the file format or the downstream COPY INTO process. Which action should the architect recommend to optimize the bulk file upload step?
- A
Use the PUT command with the AUTO_COMPRESS option disabled so Snowflake can compress files after they arrive in the stage
- B
Increase parallelism for the PUT command so multiple files are uploaded concurrently to the internal stage
- C
Replace the internal stage with a temporary table and insert the files directly using multi-row INSERT statements
- D
Run COPY INTO directly against the local file system path so Snowflake can pull files without a staging step
- E
Split each compressed file into many smaller compressed files under 1 MB before running the upload
Show answer and explanation
Correct answer: B
Explanation
For bulk file upload to an internal stage, the architect should focus on optimizing the PUT operation. Snowflake supports concurrent uploads using the PUT command's PARALLEL setting, which is especially useful when many files must be uploaded and client-to-stage transfer time is the bottleneck. Since the company does not want to change file format or downstream COPY INTO behavior, increasing upload parallelism is the most direct and appropriate improvement. Snowflake best practices also emphasize staging files before loading and avoiding excessive numbers of very small files, as file-count overhead can hurt performance. Relevant documentation areas include Snowflake guidance for the PUT command, internal stage usage, and data loading best practices for file sizing and parallel operations.
- A. Incorrect.
Incorrect. AUTO_COMPRESS in PUT applies compression on upload for uncompressed files, but the scenario already states the files are compressed CSV files. Disabling AUTO_COMPRESS does not improve upload throughput here, and Snowflake does not perform a separate post-arrival compression step on staged files in the way described. This option reflects a misunderstanding of how PUT handles compression.
- B. Correct.
Correct. The PUT command supports parallel upload threads for transferring files to internal stages. When network transfer is the bottleneck and there are many files, increasing PUT parallelism is a standard way to improve throughput by uploading multiple files concurrently. This is a practical optimization for bulk file upload without requiring changes to file format or downstream COPY INTO logic.
- C. Incorrect.
Incorrect. Snowflake does not support uploading raw data files into a table using multi-row INSERT as a substitute for staging in a bulk file ingestion pattern. INSERT statements are not an efficient or appropriate mechanism for transferring thousands of external files into Snowflake. This distractor targets the misconception that tables can replace stages for file ingestion.
- D. Incorrect.
Incorrect. COPY INTO loads files from Snowflake stages or supported external cloud storage locations, not directly from a client machine or on-premises local file system. Local files must first be uploaded with PUT to an internal stage. This option is plausible for candidates who confuse Snowflake-managed loading with database systems that can access local server paths.
- E. Incorrect.
Incorrect. Snowflake generally recommends avoiding very small files because they increase file management overhead and can reduce overall efficiency. Creating many sub-1 MB files would typically worsen the bulk upload and downstream loading process rather than improve it. This distractor addresses the common misconception that smaller files always upload faster overall.