CertSafari
    Snowflake SnowPro Specialty: Gen AI (GES-C02)· Lessons

    Domain 2 · Lesson 4/15

    Cortex Analyst text-to-SQL and choosing models for performance

    Perform data analysis given a use case.

    9 min read
    7.6% of exam
    6 sources
    Published 21 Oct 2026
    Docs as of 18 Oct 2026

    What you will be able to do

    • Explain how Cortex Analyst uses a semantic view to turn questions into SQL
    • Use verified queries, onboarding questions and custom instructions to steer Analyst
    • Weigh capability, latency and cost when choosing a model for AI_COMPLETE
    • Decide when Provisioned Throughput is worth reserving

    1.How Cortex Analyst turns questions into SQL

    Cortex Analyst is a fully managed text-to-SQL service for structured data, called through a REST API. A business user asks "what was average order value in EMEA last quarter?" and gets an answer without writing SQL. The generated query runs on your warehouse under your role-based access controls. Analyst picks the underlying models itself at runtime, so you have no model to choose here.

    A schema on its own isn't enough. A column called amt says nothing about whether revenue excludes refunds, or which date column defines "last quarter". Generic text-to-SQL tools fail at exactly this point. A semantic view supplies the missing business meaning as a schema-level object.

    What a semantic view defines for Cortex Analyst
    ElementCapturesExample
    Logical tablesBusiness entitiescustomers, orders, products
    DimensionsCategorical contextcustomer name, product category, order date
    FactsRow-level quantitiessale amounts, quantities
    MetricsAggregations as KPIstotal revenue, average order value
    RelationshipsPredefined join pathsorders to customers

    Metrics fix the aggregation formula, and relationships fix the join path. Because of that, two users who phrase the same question differently get the same SQL. Semantic views are the recommended form for new work. Older YAML semantic model files on a stage still work, for backward compatibility.

    Checkpoint 1 of 5· Match them up

    A retail team is building a semantic view so that Cortex Analyst can answer sales questions. Match each semantic view element to the business detail it should capture.

    Tap a term, then the definition that fits it.

    Sources1

    2.Verified queries, onboarding questions and custom instructions

    The Verified Query Repository (VQR) holds questions paired with SQL that someone has checked is correct. Its main job is accuracy. When a user asks something close to a verified question, those examples guide query generation toward SQL that is already known to be right. Use the VQR for the questions that matter most, such as the board's revenue definition, not for everything.

    Suggested (onboarding) questions work in three modes. With no VQR, an LLM generates up to three suggestions, and they may not be answerable: a user can click one and get no results. With a VQR, Analyst offers up to five verified questions most similar to the user's input. If you want full control, set the use_as_onboarding_question flag on chosen VQR entries, and Analyst shows exactly those.

    Custom instructions are free-text guidance in the semantic view. They shape how Analyst writes SQL and how it categorises questions, for example "fiscal year starts in February". A semantic model can also reference Cortex Search services. A role then needs USAGE on those services, plus SELECT on the tables, to query it. This is the point where the structured and unstructured paths connect inside Analyst.

    Checkpoint 2 of 5· Check yourself

    The finance lead has written and checked the exact SQL for the board's revenue question. When users ask that question in Cortex Analyst, though, the generated SQL sometimes uses a different calculation. What is the most direct fix?

    Sources21

    3.AI_COMPLETE over rows of structured data

    Structured data doesn't always need text-to-SQL. Sometimes the task is to run an LLM over each row, such as writing a one-line explanation for every flagged transaction. AI_COMPLETE called in a SELECT does this. Cortex AI Functions are optimised for throughput and suit batch work over large SQL tables. When a person is waiting on a single answer, the REST API (Complete, Embed and Agents) is the lower-latency route.

    Checkpoint 3 of 5· Check yourself

    Each night, a fraud team needs a one-line plain-English explanation written for every one of two million flagged rows in a transactions table. Nobody is waiting on any single answer. Which approach fits?

    Sources3

    4.Trading capability, latency and cost when choosing a model

    AI_COMPLETE gives you a range of models that differ in capability, latency, cost and context-window size. Choose the one that matches the size and complexity of the content. A large model spent on classifying short tickets wastes credits and adds latency. A small model on long multi-document reasoning gives wrong answers cheaply. If you're unsure, start with the most capable model to set a quality baseline, then try smaller models against it and keep the cheapest one that still passes.

    Wrong answers have different causes, and each cause has its own fix. If the model doesn't know your data (current policy, last week's prices), a bigger model will just make things up more fluently. Grounding is the fix. With Retrieval-augmented generation (RAG), Cortex Search gives the model the context it needs to answer from your own, current data. If the knowledge is in the prompt but the model still handles a narrow, repeated task badly, Cortex Fine-tuning is the lever to try. On the structured side, hallucinated SQL is mostly fixed by a richer semantic view and verified queries, not by a different model.

    Matching the symptom to the lever
    SymptomLever
    Answers ignore or invent company factsGround with Cortex Search (RAG)
    Quality is fine but too slow or expensiveSmaller model, benchmarked against a capable baseline
    Narrow repeated task stays inaccurate despite good prompts and contextFine-tuning
    Analyst writes wrong joins or metric formulasSemantic view relationships, metrics, VQR

    Checkpoint 4 of 5· Match them up

    Four Cortex projects each have a different quality or cost problem. Match each remedy to the symptom it fixes.

    Tap a term, then the definition that fits it.

    Sources4

    5.Provisioned Throughput for predictable production latency

    On-demand inference shares capacity with everyone else. Provisioned Throughput reserves capacity for you instead. You buy provisioned throughput units (PTUs) for a specific model and cloud for a one-month term, then pass the PT ID in REST API calls to give end users consistent performance. Adding the ID doesn't change how the API behaves. It only routes the request to your reserved capacity.

    Reserving 64 PTUs of Llama 3.1-8B on AWS for one termsql
    CREATE PROVISIONED THROUGHPUT my_pt
        CLOUD_PROVIDER = 'aws'
        MODEL = 'llama3.1-8B'
        PTUS = 64
        TERM_START = '2025-04-15'
        TERM_END = '2025-05-15';

    The cost model works the opposite way from on-demand. You pay for every allocated PTU for the whole term, whether you use it or not. The term does not renew automatically, so a forgotten renewal drops production back to shared capacity. Each model has a minimum PTU count and fixed increments above it. Requests that break those rules are rejected. Reserve capacity for steady, latency-sensitive traffic such as a customer-facing assistant. Bursty batch jobs are better left on-demand.

    Minimum and increment PTUs for the Complete REST API
    ModelMinimum PTUsIncrement PTUs
    Mistral Large 2256128
    Llama 3.1-405B512256
    Llama 3.1-70B12864
    Llama 3.1-8B6432

    Checkpoint 5 of 5· Check yourself

    Which of these workloads is the best candidate for buying Provisioned Throughput for a model?

    Sources5

    Sources

    (6)

    Every claim above is drawn from one of these pages, quoted as it was written on the date shown.

    1. 1.
      “Generic AI solutions often struggle with text-to-SQL conversions when given only a database schema”
      ↩︎ How Cortex Analyst turns questions into SQL
      “Relationships that define how tables join together”
      ↩︎ How Cortex Analyst turns questions into SQL
      “Legacy semantic model YAML files (stored on stages) are still supported for backward compatibility”
      ↩︎ How Cortex Analyst turns questions into SQL
      “Custom instructions: Provide guidance to Cortex Analyst for SQL generation and question categorization”
      ↩︎ Verified queries, onboarding questions and custom instructions
      “The Cortex Search services mentioned in the semantic model.”
      ↩︎ Verified queries, onboarding questions and custom instructions
      “Offering verified examples: Sample questions and their SQL answers guide query generation”
      ↩︎ Exam trap 1
      “Metrics that aggregate data into business KPIs (such as total revenue or average order value)”
      ↩︎ Checkpoint
      “Offering verified examples: Sample questions and their SQL answers guide query generation”
      ↩︎ Checkpoint
    2. 2.
      “Note that these questions may not always be answerable”
      ↩︎ Verified queries, onboarding questions and custom instructions
      “you can use the new use_as_onboarding_question flag in your VQR configuration”
      ↩︎ Verified queries, onboarding questions and custom instructions
      “If your semantic model has a Verified Query Repository (VQR) defined, Cortex Analyst returns up to five suggested questions from the VQR.”
      ↩︎ Prediction
    3. 3.
      “We recommend using these functions to process numerous inputs such as text from large SQL tables.”
      ↩︎ AI_COMPLETE over rows of structured data
      “For more interactive use cases where latency is important, use the REST API.”
      ↩︎ AI_COMPLETE over rows of structured data
      “Cortex AI Functions are optimized for throughput. We recommend using these functions to process numerous inputs such as text from large SQL tables.”
      ↩︎ Checkpoint
      “For more interactive use cases where latency is important, use the REST API.”
      ↩︎ Checkpoint
    4. 4.
      “The Snowflake Cortex AI_COMPLETE function supports multiple models of varying capability, latency, and cost.”
      ↩︎ Trading capability, latency and cost when choosing a model
      “To achieve the best performance per credit, choose a model that’s a good match for the content size and complexity of your task.”
      ↩︎ Trading capability, latency and cost when choosing a model
      “If you’re not sure where to start, try the most capable models first to establish a baseline to evaluate other models.”
      ↩︎ Trading capability, latency and cost when choosing a model
    5. 5.
      “Use Provisioned Throughput to reserve throughput for managed inference on Snowflake Cortex.”
      ↩︎ Provisioned Throughput for predictable production latency
      “Using provisioned throughput in the request doesn’t change the behavior of the API.”
      ↩︎ Provisioned Throughput for predictable production latency
      “Provisioned Throughput does not renew automatically.”
      ↩︎ Provisioned Throughput for predictable production latency
      “You incur charges for the allocated PTUs regardless of your actual usage during the term.”
      ↩︎ Exam trap 2

    Also cited

    Ready to test yourself?

    Practise the 25 questions on this subdomain.

    Spotted a mistake, or was something unclear? Tell us.