What you will be able to do
- Decide when to use a task-specific AI Function and when to use the general-purpose ai_query
- Match each ai_query model type to the endpoint setup it needs
- Build ai_query calls with the right request, returnType, modelParameters and responseFormat for the endpoint you are calling
Key concept
ai_query as general-purpose inference — ai_query is the AI Function that sends each row's request to a model serving endpoint you choose and returns the parsed response as a column. Task-specific functions each do one fixed job. With ai_query you pick the model, write the prompt and set the parameters yourself.
1.Two kinds of AI Functions
AI Functions are built-in SQL functions that run model inference on data stored in Databricks. You call them like any other function, so they fit into a data pipeline. They can be run from Databricks SQL, notebooks, Lakeflow pipelines and Workflows. Your query runs on the compute you submit it from. Depending on the function, the inference itself may run on separate Databricks-managed infrastructure, which is billed on top of that compute.
The family has two halves. Task-specific functions are built for one job, such as classification, entity extraction, translation or sentiment analysis. They need no prompt and no choice of model. The general-purpose function is ai_query: you write a prompt and choose any supported model.
| Function | What it does |
|---|---|
| ai_classify | Classify input text according to labels you provide |
| ai_extract | Extract structured fields from documents or text using a schema you define |
| ai_analyze_sentiment | Perform sentiment analysis on input text |
| ai_summarize | Generate a summary of text |
| ai_translate | Translate text to a specified target language |
| ai_mask | Mask specified entities in text |
Databricks recommends starting with a task-specific function when one fits the job. Switch to ai_query when it doesn't: when you need tighter control of the prompt, the model parameters or the output format; when you want to call a custom, fine-tuned or external model; or when you need room to tune for throughput or quality. Neither kind runs on Classic SQL warehouses, and both need Databricks Runtime 15.4 LTS or above.
Checkpoint 1 of 6· Check yourself
A team wants to score support tickets with a fine-tuned model they deployed on Model Serving. Which AI Function should they use?
Task-specific functions run models that Databricks chooses and maintains. Calling your own custom, fine-tuned or external model is one of the documented reasons to use ai_query.
“Query a custom, fine-tuned, or external model”Source: docs.databricks.com
2.What ai_query calls: endpoints and model types
ai_query invokes an existing Databricks Model Serving endpoint, then parses and returns its response. It works for both real-time and batch inference. The first argument is always the endpoint name as a STRING literal. How much setup that endpoint needs depends on the kind of model behind it.
| Model type | Examples | Requirement |
|---|---|---|
| Databricks-hosted models | system.ai.meta-llama-3-3-70b-instruct, system.ai.gpt-oss-20b | No endpoint provisioning or configuration; Databricks Runtime 15.4 LTS or above |
| Provisioned throughput models | Fine-tuned foundation models deployed on Model Serving | Create a provisioned throughput endpoint; AI Functions manages batch scaling itself |
| External models | Foundation models hosted outside Databricks | Create an external model serving endpoint |
| Custom models | Custom traditional ML and DL models | Create a custom model serving endpoint |
Here is the simplest form of enrichment. Every row of a table gets a new column produced by a hosted foundation model, and the prompt is built by joining a fixed instruction to a column value with ||.
SELECT *,
ai_query(
'system.ai.meta-llama-3-3-70b-instruct',
"Can you tell me the name of the US state that serves the provided ZIP code? zip code: " || pickup_zip
)
FROM samples.nyctaxi.trips
LIMIT 10Provisioned throughput endpoints come with one surprise. You must create the endpoint, but batch inference through AI Functions does not run on the compute you provisioned for it. AI Functions manages its own scaling.
Checkpoint 2 of 6· Check yourself
You run ai_query in batch against a fine-tuned model on a provisioned throughput endpoint. Which statement about compute is correct?
For provisioned throughput models, the documentation says AI Functions fully manages the scaling used for batch inference and does not use the endpoint's provisioned compute.
“AI Functions does not use the compute provisioned to the endpoint.”Source: docs.databricks.com
3.request and returnType: what goes in and what comes back
What you can pass as the request depends on the endpoint. Foundation Model API endpoints and external model endpoints take a STRING, which is your prompt. A custom model serving endpoint takes either a single column or a STRUCT, and the STRUCT field names should match the input feature names the endpoint expects. For a traditional ML model, ai_query is therefore a way to score rows in SQL by sending feature columns to the model.
returnType tells ai_query how to parse the response, much like the schema argument of from_json. On Databricks Runtime 15.2 and above it is optional: if you leave it out, ai_query infers the return type from the custom endpoint's model schema. On 15.1 and below, querying a custom endpoint requires it. Whatever you call, the definer of the query needs CAN QUERY permission on the endpoint.
Checkpoint 3 of 6· Fill the gap
This query classifies messages as spam with a custom model. Which return type completes it?
SELECT text, ai_query(
endpoint => 'spam-classification-endpoint',
request => named_struct(
'timestamp', timestamp,
'sender', from_number,
'text', text),
returnType => ' ? ') AS is_spam
FROM messages
LIMIT 10The column is named is_spam and holds a yes/no classification, so the documented example parses the response as BOOLEAN. The request is a named_struct whose field names match the model's input features.
Source: docs.databricks.comThe endpoint serves a custom model, not a chat model. Custom endpoints take a single column or a STRUCT whose field names match the input features the model expects, here timestamp, sender and text.
Checkpoint 4 of 6· Exam question
A data engineer needs to enrich a Delta table of customer support tickets with a sentiment label generated by a Databricks-hosted foundation model, calling the model directly from a Spark SQL query inside a nightly batch job. The `ticket_text` column holds free-form text. Which SQL expression correctly returns a sentiment label using `ai_query` against the pay-per-token `system.ai/llama-4-70b` endpoint?
Correct answer: A — `ai_query('system.ai/llama-4-70b', 'Classify sentiment as positive, negative, or neutral: ' || ticket_text) AS sentiment`
- A. Foundation model endpoints take a single STRING request; concatenating an explicit instruction with the column value produces a prompt the model can act on to return a sentiment label.
- B. A named_struct request is the pattern for custom model-serving endpoints that expose named input features; pay-per-token foundation model endpoints expect a plain STRING prompt, so this call would not classify correctly.
- C. Passing the raw column value with no instruction text gives the model nothing to classify against, so even though the returnType and failOnError syntax is valid, the model has no directive to produce a sentiment label.
- D. `model_input` is not a recognized ai_query parameter name; ai_query's parameters are `endpoint` and `request`, so this call raises an error rather than running.
Sources3
4.Controlling the model: modelParameters and responseFormat
Two optional arguments control how a foundation model behaves and what shape its answer takes.
modelParameters is a struct of settings such as max_tokens and temperature. The values must be constants and cannot depend on the data, so you can't set a different temperature for each row. Any parameter you leave unset uses its default. The exception worth remembering is temperature, which defaults to 0.0 under ai_query.
SELECT text, ai_query(
"system.ai.meta-llama-3-3-70b-instruct",
"Please summarize the following article: " || text,
modelParameters => named_struct('max_tokens', 100, 'temperature', 0.7)
) AS summary
FROM uc_catalog.schema.table;responseFormat makes a chat foundation model follow a schema you give it, which makes its output easy to process downstream. It works only with chat foundation models and needs Databricks Runtime 15.4 LTS or above. You can write the schema as a DDL-style string or as a JSON string of type text, json_object or json_schema.
SELECT ai_query(
"system.ai.gpt-oss-20b",
"Extract research paper details from the following abstract: " || abstract,
responseFormat => 'STRUCT<research_paper_extraction:STRUCT<title:STRING, authors:ARRAY<STRING>, abstract:STRING, keywords:ARRAY<STRING>>>'
)
FROM research_papers;Checkpoint 5 of 6· Match them up
Match each ai_query argument to what it controls
Tap a term, then the definition that fits it.
endpoint picks the model and returnType parses a custom model's output. modelParameters tunes generation and must be constant, and responseFormat enforces structured output for chat models.
“These model parameters must be constant parameters and not data dependent.”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
A nightly job calls `ai_query` against a custom sentiment-classification model endpoint for one million support tickets. On some rows the payload violates the endpoint's input schema, and the current default configuration makes the whole SQL statement — and the job — fail as soon as one bad row is hit. Which change lets the job finish, capturing failures per row without aborting the whole batch?
Correct answer: A — Set `failOnError => false` in the ai_query call so it returns a STRUCT with response and errorMessage fields, letting failed rows surface a null result instead of aborting.
- A. `failOnError => false` is exactly for this situation: it makes ai_query return a STRUCT with a response and an errorMessage instead of raising, so the query completes and individual failures are captured for later inspection.
- B. ai_query runs as a SQL built-in evaluated inside the query engine, not inside a Python UDF, so wrapping unrelated Python code in try/except does not intercept the SQL-level model invocation errors.
- C. Provisioned throughput controls request capacity and latency, not schema validation, so faster serving does not stop rows with malformed payloads from erroring out.
- D. Limiting the batch size changes how many rows are processed per run but does not remove malformed rows from the data, so the same schema violation still aborts the statement whenever it is reached.
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.ai_query can run on any SQL warehouse, including a Classic one.Why is that wrong?
ai_query and the other AI Functions are not available on Databricks SQL Classic. Use a supported warehouse type or Databricks Runtime 15.4 LTS or above.
Covered in Two kinds of AI Functions
2.modelParameters can be built from column values, for example a different max_tokens per row.Why is that wrong?
modelParameters must be constant values that do not depend on the data. Parameters you leave out use their defaults, and temperature defaults to 0.0.
Covered in Controlling the model: modelParameters and responseFormat
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Purpose-built functions optimized for a specific task, such as document parsing, entity extraction, classification, and sentiment analysis.”
↩︎ Two kinds of AI Functions“They can be run from Databricks SQL, notebooks, Lakeflow pipelines, and Workflows.”
↩︎ Two kinds of AI Functions - 2.
“Databricks recommends starting with a task-specific AI Function when one matches your objective.”
↩︎ Two kinds of AI Functions“For external models, you must create an external model serving endpoint.”
↩︎ What ai_query calls: endpoints and model types“ai_query gives you full control over the model, prompt, and parameters.”
↩︎ Key concept“Query a custom, fine-tuned, or external model”
↩︎ Checkpoint“Requires no endpoint provisioning or configuration.”
↩︎ Prediction“AI Functions does not use the compute provisioned to the endpoint.”
↩︎ Checkpoint - 3.
“Invokes an existing Databricks Model Serving endpoint and parses and returns its response.”
↩︎ What ai_query calls: endpoints and model types“If the endpoint is a custom model serving endpoint, the request can be a single column or a STRUCT expression.”
↩︎ request and returnType: what goes in and what comes back“If it is not provided, ai_query() automatically infers the return type from the model schema of the custom model serving endpoint.”
↩︎ request and returnType: what goes in and what comes back“The definer must have CAN QUERY permission on the endpoint.”
↩︎ request and returnType: what goes in and what comes back“With the exception of temperature which has a default value of 0.0”
↩︎ Controlling the model: modelParameters and responseFormat“Only available for querying chat foundation models.”
↩︎ Controlling the model: modelParameters and responseFormat“This function is not available on Databricks SQL Classic.”
↩︎ Exam trap 1“These model parameters must be constant parameters and not data dependent.”
↩︎ Exam trap 2“These model parameters must be constant parameters and not data dependent.”
↩︎ Checkpoint