SOL-C01 · Question #172
A Snowflake data engineer is tasked with optimizing the performance of daily data loads into a fact table named `SALES FACT. The data is loaded from multiple CSV files stored in an AWS S3 bucket…
The correct answer is B. Ensure that the CSV files are split into smaller files (e.g., 10-100MB each). Snowflake. Splitting CSV files into smaller chunks (10-100MB each) directly enables Snowflake's parallel loading mechanism - each file becomes an independent unit of work that can be distributed across the warehouse's compute nodes simultaneously, which is the root cause fix for…
Question
A Snowflake data engineer is tasked with optimizing the performance of daily data loads into a fact table named `SALES FACT. The data is loaded from multiple CSV files stored in an AWS S3 bucket. The data engineer observes that the data loading process is slow, even though the virtual warehouse is adequately sized. Analyzing the COPY INTO command, they notice that data loading happens in single thread even though multiple files are staged. Which of the following techniques, or combination of techniques, would be MOST effective in parallelizing the data load and improving the overall loading performance?
Options
- AIncrease the size of the virtual warehouse to the largest available size (e.g., X-Large or higher).
- BEnsure that the CSV files are split into smaller files (e.g., 10-100MB each). Snowflake
- CUse Snowpipe with auto-ingest enabled to continuously load data as new files arrive in the S3
- DPartition the 'SALES FACT table by a relevant date column and then use the 'ON ERROR =
- EB and C are both correct
How the community answered
(19 responses)- B58% (11)
- C5% (1)
- D26% (5)
- E11% (2)
Explanation
Splitting CSV files into smaller chunks (10-100MB each) directly enables Snowflake's parallel loading mechanism - each file becomes an independent unit of work that can be distributed across the warehouse's compute nodes simultaneously, which is the root cause fix for single-threaded loading. Option A is wrong because warehouse size alone doesn't parallelize the load; a larger warehouse has more compute but still processes one large file sequentially. Option C (Snowpipe) is wrong because it solves continuous/event-driven ingestion, not the parallelization problem - it also uses single-file processing under the hood and doesn't address the file-size bottleneck. Option D is wrong because partitioning is a query optimization technique and has no effect on COPY INTO load performance.
Memory tip: Think of Snowflake's parallel loader as a team of workers - if you hand them one giant box (large file), only one worker can carry it; split it into many small boxes and the whole team works at once. "Many small files = many parallel threads."
Topics
Community Discussion
No community discussion yet for this question.