Snowflake Gen AI Functions · Topic 18 of 26
Structured Data Analysis with AI_COMPLETE, Cortex Analyst and Verified Queries
Analyze structured data in Snowflake with AI_COMPLETE structured output, Cortex Analyst semantic views and the Verified Query Repository (VQR).
- 4 min
AI_COMPLETEwithresponse_formatgives schema shaped JSON for per row extraction.- Cortex Analyst: text-to-SQL over a semantic view, endpoint
/api/v2/cortex/analyst/message. - Semantic views are recommended; YAML on a stage is legacy but supported.
What it is
Structured data lives in tables and columns. Snowflake offers two ways to apply an LLM to it, and they solve different problems.
AI_COMPLETEon rows: the LLM reads values from each row and writes a result, such as a label, a summary or a JSON object. It is per row enrichment.- Cortex Analyst: the LLM reads a semantic view that describes your tables and turns a business question into SQL. It is question answering over the whole dataset.
The Verified Query Repository (VQR) is a set of approved question and SQL pairs that makes Cortex Analyst more accurate.
How it works
AI_COMPLETE on structured data
The single string form is AI_COMPLETE(<model>, <prompt> [, <model_parameters>, <response_format>, <show_details>]).
| Parameter | Detail |
|---|---|
temperature | 0 to 1, default 0 |
top_p | 0 to 1, default 0 |
max_tokens | default 4096; a small value can truncate |
guardrails | TRUE or FALSE, default FALSE; uses Cortex Guard |
response_format | JSON schema (a SQL object, not a string) or a TYPE literal, whose top level must be an OBJECT |
With response_format, the function returns an object that follows the schema, so you can read fields without parsing prose. With show_details TRUE you also get usage token counts (prompt_tokens, completion_tokens, total_tokens).
SELECT AI_COMPLETE(
'claude-sonnet-4-5',
'Extract the product and the issue from: ' || ticket_text,
{'temperature': 0},
{'type': 'json',
'schema': {'type': 'object',
'properties': {'product': {'type': 'string'},
'issue': {'type': 'string'}}}}
) AS parsed
FROM tickets;
The structured output is useful for turning free text columns into typed fields. It is not the tool for answering "what was total revenue." That is arithmetic over data, and an LLM reading rows can get it wrong.
Cortex Analyst
Cortex Analyst is a managed text-to-SQL service with a REST API: POST /api/v2/cortex/analyst/message. The request carries a messages array and one of these:
semantic_view: a fully qualified semantic view name (the recommended approach).semantic_model_file: a stage path to a legacy YAML file.semantic_model: the YAML as a string.semantic_models: an array of files or views; Analyst chooses the best one.
The response has a message with content items of type text, sql (with statement and confidence) or suggestions. Generated SQL runs in your own warehouse, so warehouse cost is separate from Analyst charges, and your row access and masking policies still apply.
Why a semantic view? Column names such as cust_rev_q3 mean nothing to an LLM. The semantic view adds logical tables, dimensions, facts, metrics, relationships, synonyms and descriptions, so "revenue by region" maps to the right columns and joins.
Access: the role needs the SNOWFLAKE.CORTEX_USER or SNOWFLAKE.CORTEX_ANALYST_USER database role. CORTEX_USER is granted to PUBLIC by default and covers all covered AI features. CORTEX_ANALYST_USER covers Cortex Analyst only. It also needs SELECT on the referenced tables, and for a YAML model, READ or WRITE on the stage.
To restrict a team to Analyst and nothing else, revoke CORTEX_USER from PUBLIC and grant CORTEX_ANALYST_USER.
Verified Query Repository
The VQR holds questions that are already answered correctly. When a user's question resembles a verified query, Cortex Analyst uses that query to build its SQL, which raises accuracy and consistency.
In the YAML each entry sits under verified_queries with these keys.
| Key | Required |
|---|---|
name, question, sql | Yes |
verified_at (integer), verified_by (string) | Optional |
use_as_onboarding_question | Optional boolean |
Rules for the SQL: use the logical table and column names of the semantic model, not the physical ones, and prefix logical table names with two underscores, such as __sales_data. A good generated query can be saved as a verified query, and Snowsight also proposes suggested verified queries from real usage. A person must review and accept them. Nothing is applied automatically.
To see whether a verified query was used, read confidence.verified_query_used in the response; it is null when none was used.
Common mistakes
Using AI_COMPLETE to sum rows. Use SQL or Cortex Analyst, which runs real SQL.
Passing response_format as a JSON string. It must be a SQL object.
Writing verified SQL with physical table names. Use the logical names.
Believing Cortex Analyst executes queries on its own compute. It generates SQL that runs in your warehouse.
Poor column names and missing descriptions in the semantic view are the usual cause of wrong SQL. Fix the semantic view before blaming the model.
What to remember for the exam
AI_COMPLETEwithresponse_formatgives schema shaped JSON for per row extraction.- Cortex Analyst: text-to-SQL over a semantic view, endpoint
/api/v2/cortex/analyst/message. - Semantic views are recommended; YAML on a stage is legacy but supported.
- VQR entries need
name,questionandsql; they raise accuracy for similar questions. CORTEX_ANALYST_USERlimits a role to Cortex Analyst.
Check yourself
Which feature makes
AI_COMPLETEreturn JSON that follows a schema?The
response_formatargument.Where does Cortex Analyst's generated SQL run?
In the customer's virtual warehouse, under the caller's role.
What three keys must every verified query have?
name,questionandsql.
Topic 18 of 26 in Snowflake Gen AI Functions
Test yourself on Snowflake Gen AI Functions
The full Snowflake GES-C02 question bank has 585 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.