Your team is loading data into Snowflake using Snowpipe. You've noticed that data loading is significantly slower than expected, even though your virtual warehouse is sized appropriately. Which of the

Question

Options

• AThe virtual warehouse is configured with auto-suspend, causing it to frequently start and stop.
• BThe files being loaded are very small (e.g., less than 1 MB) and numerous.
• CThe Snowflake table has a clustering key that is poorly chosen, resulting in uneven data
• DSnowpipe is configured to use the COPY INTO statement directly, bypassing the Snowpipe
• EThe IAM role used for accessing data in S3 has insufficient permissions.

• A
  60% (12)
• C
  25% (5)
• D
  5% (1)
• E
  10% (2)

Explanation

A is correct because if Snowpipe is configured to use a customer-managed virtual warehouse, frequent auto-suspend/resume cycles add resume latency before each micro-batch can be processed - every file batch triggers a cold start delay. B is correct because Snowpipe is optimized for larger files (100–250 MB range); thousands of sub-1 MB files create disproportionate metadata overhead per file, overwhelming the ingestion queue with tiny, inefficient operations.

C is wrong - clustering keys affect query performance on reads, not write/load throughput; even poor clustering won't meaningfully slow Snowpipe ingestion. D is wrong - Snowpipe internally uses COPY INTO as its mechanism; the option describes a contradiction that doesn't represent a real configuration. E is wrong - insufficient IAM permissions would cause outright failures (access denied errors), not a silent slowdown.

Memory tip: The two bottlenecks are about overhead per operation - auto-suspend adds time overhead (warehouse restart), and tiny files add metadata overhead (too many operations). Both make Snowpipe spin its wheels without actually moving data efficiently. If you see "slow but not broken," think compute warmup and file sizing, not permissions.

No community discussion yet for this question.