SnowPro Specialty: Gen AI Question 70
Single answerStructured/text-to-SQL use casesA retail analytics team wants business users to ask questions such as "Which product categories had the highest return rate in the last quarter in Germany?" and have an application generate SQL against Snowflake. The team stores sales, returns, products, and geography data in multiple schemas, and they are concerned that a general-purpose LLM may generate invalid SQL or join the wrong tables. They want to improve accuracy while keeping the solution maintainable and governed in Snowflake. Which approach is MOST appropriate?
- A
Provide the LLM with a curated semantic representation of the approved tables, columns, relationships, and business definitions relevant to the use case, and constrain SQL generation to that context before executing queries in Snowflake.
- B
Fine-tune a large language model on all raw table data from the warehouse so it can infer joins directly from the data values and generate SQL without schema guidance.
- C
Expose the entire Snowflake account metadata and all schemas to the model at prompt time so it has maximum context and can decide which objects to query.
- D
Have users write natural language prompts, then execute the generated SQL directly in production because Snowflake RBAC alone is sufficient to ensure semantic correctness.
Show answer and explanation
Correct answer: A
Explanation
In Snowflake text-to-SQL scenarios, the key challenge is not just generating syntactically valid SQL but generating SQL that reflects the correct business meaning. The strongest pattern is to ground the model with structured metadata: approved tables and views, column descriptions, relationships, filters, and metric definitions. This reduces hallucinations and improves accuracy in domains with many schemas or overlapping datasets. It also supports governance because teams can expose a curated semantic layer rather than the entire warehouse. This aligns with common best practices for natural language to SQL systems and Snowflake-focused GenAI architectures: limit context to the relevant domain, use well-described structured data assets, and validate or constrain generated SQL before execution. Broad account-wide metadata exposure and reliance on RBAC alone are common misconceptions; they address access, not semantic precision. Fine-tuning on raw data is typically more costly and risky than using retrieval and schema grounding for structured use cases.
- A. Correct.
Correct. For structured/text-to-SQL use cases, the most effective pattern is to narrow the model's scope to a curated set of approved database objects and business semantics. Supplying relevant schema context such as table descriptions, column meanings, join paths, and metric definitions reduces hallucinated joins and invalid SQL. This is aligned with best practices for text-to-SQL systems: ground the model with metadata and semantic context, limit the search space, and execute only within governed Snowflake objects.
- B. Incorrect.
Incorrect. Fine-tuning on raw warehouse data is not the recommended first approach for text-to-SQL accuracy, and it introduces major governance, privacy, and maintenance concerns. SQL generation problems are usually improved more effectively by grounding the model with schema metadata and business semantics rather than teaching it from raw records. Inferring joins from values is unreliable and does not replace explicit relational context.
- C. Incorrect.
Incorrect. Giving the model all account metadata increases noise, ambiguity, token usage, and the likelihood of selecting the wrong tables or columns. In real environments, many schemas contain similarly named objects, staging tables, or deprecated assets. Best practice is to scope the context to only the approved domain needed for the question, not to maximize context indiscriminately.
- D. Incorrect.
Incorrect. RBAC helps enforce access control, but it does not guarantee that generated SQL is semantically correct, uses the right join logic, or answers the intended business question. A query can be fully authorized and still be wrong. Production text-to-SQL systems typically combine governance controls with semantic grounding, validation, and constrained execution.