SnowPro Specialty: Gen AI Question 162
Single answerPerformance considerationsA data engineering team built a Snowflake pipeline that generates product descriptions for 20 million catalog rows using Cortex LLM functions. The initial implementation calls the model once per row in an ad hoc way from analyst worksheets, and jobs often run for many hours with inconsistent completion times. The team wants to improve throughput and reduce unnecessary latency without changing the business output. Which action is the BEST recommendation?
- A
Execute the generation workload as a set-based SQL operation over batches of rows instead of issuing many small, row-by-row interactive requests
- B
Increase micro-partition count on the source table so Cortex model inference can parallelize more aggressively across partitions
- C
Materialize the prompts into a dynamic table, because dynamic tables automatically cache LLM responses for repeated prompts
- D
Use a larger language model for each request, because larger models typically finish long batch jobs faster than smaller models
Show answer and explanation
Correct answer: A
Explanation
The key performance issue is the anti-pattern of invoking LLM generation one row at a time from ad hoc analyst workflows. For bulk GenAI processing in Snowflake, best practice is to structure inference as set-based SQL operating on batches of rows, which reduces request overhead and improves throughput consistency. This follows general Snowflake performance guidance to favor set-based processing over row-by-row execution, and it aligns with practical use of Cortex AISQL/LLM functions for large datasets. The distractors reflect common misconceptions: storage-level partitioning is not a primary lever for LLM inference performance, dynamic tables do not automatically cache model outputs, and larger models generally increase latency rather than reduce it. Candidates should recognize that the most effective optimization is changing the execution pattern, not assuming table storage changes or model size increases will solve inference latency.
- A. Correct.
Correct. For large-scale inference workloads in Snowflake, performance is generally better when work is expressed as set-based SQL over many rows rather than as many small, interactive, per-row requests from worksheets or client loops. Batching reduces per-request overhead and aligns with Snowflake's execution model, improving throughput and consistency for bulk generation jobs.
- B. Incorrect.
Incorrect. Micro-partitions are a core Snowflake storage optimization concept for table pruning and scan efficiency, but they do not directly control how Cortex LLM inference parallelizes in the way described here. Increasing micro-partition count is not an established tuning method for improving model inference throughput.
- C. Incorrect.
Incorrect. Dynamic tables help maintain derived data incrementally, but they do not provide automatic caching of LLM outputs for repeated prompts. Materializing prompts may help pipeline design in some cases, but the stated benefit is inaccurate and does not address the main performance issue of many small requests.
- D. Incorrect.
Incorrect. Larger models usually have higher latency and higher cost, not lower latency for the same task. Choosing a bigger model without a quality requirement would typically worsen performance for a large batch generation workload.