What you will be able to do
- Tell a batch inference workload apart from a real-time serving workload
- Pick the right ai_query call form and model type for a batch job
- Use modelParameters, responseFormat and failOnError to get controlled output that survives failures
- Run ai_query in a scheduled or incremental pipeline, then monitor and scale it properly
Key concept
Batch inference with ai_query — ai_query lets you apply a model to every row of a table from one SQL or Python query. Databricks spreads that query across the rows and handles parallelism, retries and scaling, so you never write a per-request serving loop.
1.Recognizing a batch inference workload
The first question in any inference design is whether someone is waiting for the answer. Real-time serving puts a model behind a low-latency REST endpoint. A user or application sends one request and blocks until the response comes back. Batch inference works the other way round: a large set of inputs already exists, and the goal is to score all of them efficiently. Nobody is waiting on any single row.
A batch workload has three telltale signs:
- Many rows already stored. The inputs sit in a Delta table or a volume, often thousands to millions of records.
- No user waiting. The job runs on a schedule, such as nightly or weekly, or as a backfill. Latency per row does not matter, but total throughput does.
- Results land in a table. The output becomes a new column or a new Delta table that dashboards and downstream jobs read later.
Take 3 million historical support tickets that need sentiment labels every night. That is a batch job. The right shape is a SELECT over the Delta table that calls ai_query() once per row, all inside one distributed query. Putting a provisioned-throughput endpoint in front of it and sending each ticket as its own REST call adds request plumbing and per-call latency that no one benefits from. Copying the text into a feature table without ever calling a model doesn't produce a single label.
Streaming deserves care, because it is a legitimate way to run ai_query. What decides between batch and streaming is how the data arrives. A finished historical table that gets scored once per night is plain batch. Data that keeps arriving, such as files landing in a volume or rows appended all day, is a good fit for incremental processing with Lakeflow pipelines or Structured Streaming. Even then, the model call is the same ai_query expression applied to each new row.
| Signal | Batch inference | Real-time serving |
|---|---|---|
| Input | A table or set of files that already exists | One request at a time from an app or user |
| Who waits | Nobody waits on an individual row | A caller blocks until the response returns |
| Output | Written to a Delta table | Returned in the API response |
| Typical tool | ai_query inside SELECT, a pipeline or a job | A low-latency Model Serving endpoint |
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 10Checkpoint 1 of 8· Fill the gap
This query adds a model-generated answer to every taxi trip in one distributed query, with a model name and a per-row prompt. Which function completes it?
SELECT *,
? (
'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 10ai_query is the general-purpose AI Function. It takes a model endpoint name and a request, and runs it once per row inside a SELECT. That is the batch pattern for scoring a whole table without a per-request serving loop.
Source: docs.databricks.comCheckpoint 2 of 8· Check yourself
Product reviews arrive all day as CSV files in a Unity Catalog volume. Each new review needs a category label written to a Delta table. No user waits on any single review. What is the best design?
No caller is waiting, so this is not real-time serving. Because the data keeps arriving, incremental processing with Lakeflow pipelines or Structured Streaming fits best, and the model call is still ai_query applied to each new row.
“The following example performs incremental batch inference using Lakeflow pipelines for when data is continuously updated.”Source: docs.databricks.com
Checkpoint 3 of 8· Exam question
A team needs to classify the sentiment of 3 million historical support tickets stored in a Delta table. The job runs once nightly, has no user waiting on a response, and results are written back to a new Delta table. Which approach best fits this workload and how should it be implemented?
Correct answer: A — This is a batch inference workload; call ai_query() inside a SELECT statement over the Delta table so each row is scored against a model serving endpoint in one distributed job.
- A. Correct: a large, scheduled, non-interactive scoring pass over an existing Delta table is the canonical batch inference pattern, and
ai_query()lets you invoke a model serving endpoint directly from SQL for every row in a single distributed query. - B. Provisioned throughput and low-latency REST calls target interactive, per-request serving where a caller is waiting on an immediate response, not a nightly bulk scoring pass over millions of stored rows.
- C. A streaming table reacting to new files is meant for continuously arriving data, but this scenario describes a fixed, already-landed historical table scored on a schedule, not an incremental stream.
- D. Feature Store publishes reusable feature values for training or serving; it does not perform model inference on its own, so it cannot produce the sentiment classifications the ticket asks for.
2.Call forms and the models ai_query can reach
Every call takes an endpoint and a request, and the endpoint type decides what else you pass. For a foundation model or an external model, the request is a STRING, which is usually a fixed instruction concatenated with a column. For a custom model serving endpoint, the request can be a single column or a STRUCT whose field names match the model's input features. Whoever defines the query needs CAN QUERY permission on the endpoint. A custom endpoint may also carry a model schema, the description of its output that ai_query reads to work out the return type on its own; where an endpoint has none, you state the type yourself.
| Endpoint type | Call form | Request shape |
|---|---|---|
| Foundation model | ai_query(endpoint, request) | STRING |
| Custom model with a model schema | ai_query(endpoint, request) | Column or STRUCT matching input features |
| Custom model without a model schema | ai_query(endpoint, request, returnType, failOnError) | Column or STRUCT; returnType states the output type |
returnType exists only to type the output of a custom endpoint. On Databricks Runtime 15.2 and above it is optional, because ai_query infers the type from the endpoint's model schema. On 15.1 and below it is required for custom endpoints. It has no effect on how long or how well-formed a chat model's answer is. That job belongs to responseFormat.
SELECT text, ai_query(
endpoint => 'spam-classification-endpoint',
request => named_struct(
'timestamp', timestamp,
'sender', from_number,
'text', text),
returnType => 'BOOLEAN') AS is_spam
FROM messages
LIMIT 10ai_query can reach three kinds of model:
- Databricks-hosted models under
system.ai.need no endpoint provisioning. A named subset, includingsystem.ai.meta-llama-3-3-70b-instruct,system.ai.gpt-oss-120bandsystem.ai.claude-sonnet-4-6, is optimized for batch. Other hosted models work but are meant for real-time pay-per-token use, not batch production at scale. - Provisioned throughput models, such as fine-tuned foundation models, need a provisioned throughput endpoint. AI Functions does not run on that endpoint's provisioned compute. It manages the batch scaling itself.
- Custom and external models need a custom model serving endpoint or an external model serving endpoint, respectively.
Choosing a model affects job duration, not only quality. A model outside the batch-optimized list gives lower throughput and longer completion times. For batch work, Databricks recommends the hosted system.ai model services over provisioned throughput, because they are fully managed and scale automatically. Older pages refer to these hosted endpoints by a databricks- prefix.
Checkpoint 4 of 8· Check yourself
A team fine-tuned a foundation model and served it on a provisioned throughput endpoint sized for light real-time traffic. They now plan a nightly ai_query job over 20 million rows. What happens to the compute for this batch job?
ai_query can call provisioned throughput models, such as fine-tuned foundation models, but AI Functions does not run on the endpoint's provisioned compute. It handles the scaling for batch inference itself, so the size of the endpoint does not cap the nightly job.
“AI Functions does not use the compute provisioned to the endpoint. AI Functions fully manages the scaling used for batch inference.”Source: docs.databricks.com
3.Shaping output and surviving failures: modelParameters, responseFormat, failOnError
Three optional arguments decide whether a batch job writes a usable table. Each one controls a different thing, and exam distractors often swap them.
modelParameterssets how the model generates.responseFormatsets the shape of what it returns.failOnErrorsets what happens when a row fails.
modelParameters is a struct of generation settings such as max_tokens and temperature. Use it for chat, completion and embedding models. If a summarization job produces summaries that run too long and change style from run to run, you can fix that here without touching the prompt. max_tokens caps how much the model generates, and a lower temperature makes the output less variable. The values must be constants. A parameter cannot change from row to row based on the data. Any parameter you leave unset uses its default. Temperature's default is 0.0, and the others follow the Foundation Model REST API defaults.
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 produce output that matches a schema, so every row parses into the same fields. You can supply it as a DDL-style string or as a JSON string of type text, json_object or json_schema. How to design the schema itself is covered in Structured outputs with response_format and JSON schema. In a batch job, what matters is that a downstream step needing title and authors on every row can get them. A sentence in the prompt saying "reply only with JSON" is a request the model may ignore. A low temperature makes output more consistent, but it does not enforce a structure. A schema passed through responseFormat constrains what the model can emit, for example responseFormat => 'STRUCT<research_paper_extraction:STRUCT<title:STRING, authors:ARRAY<STRING>, abstract:STRING, keywords:ARRAY<STRING>>>'. It works only with chat foundation models.
failOnError defaults to true. With that default, ai_query returns the parsed response directly, and a failing row can stop the query. Setting it to false changes the return type to a STRUCT holding the response and an errorMessage. On success the error is null, and on an endpoint failure the response is null. For a large workload this is the setting to use. The job finishes, the successful rows are kept, and you reprocess only the rows whose errorMessage is not null. There is a limit, though. Only errors from the model endpoint are captured per row. If a row fails for any other reason, the whole query still fails. The flag also doesn't make output valid. It reports failures and never enforces a schema.
files is the fourth argument worth knowing, and it applies only when the input is images. Written files => content, it hands image data to a vision-capable chat model. files is the parameter name the model expects for multimodal input, and content is a column holding the binary content of each image, such as the content column that read_files returns for a folder of images. The argument is required for multimodal requests, and only JPEG and PNG images are supported. The prompt stays an ordinary STRING, for example ai_query('system.ai.llama-4-maverick', 'what is this image about?', files => content) over READ_FILES(...).
| Problem in the output table | Argument | What it does not do |
|---|---|---|
| Summaries too long or inconsistent | modelParameters (max_tokens, temperature) | Guarantee a parseable structure |
| Downstream parser needs fixed fields | responseFormat (DDL or json_schema) | Apply to non-chat models |
| A few bad rows kill a long job | failOnError => false | Catch errors that are not from the model endpoint |
| Custom endpoint output has no schema | returnType | Truncate or reshape a chat model's answer |
| Inputs are images | files => content (JPEG or PNG) | Shorten or limit the response |
Checkpoint 5 of 8· Match them up
A nightly ai_query job writes a table with problems. Match each ai_query argument to the problem it is meant to fix.
Tap a term, then the definition that fits it.
modelParameters controls how the model generates, for example max_tokens and temperature. responseFormat makes a chat model's output follow a schema. failOnError => false records endpoint errors per row so the job can finish. returnType only sets the type of a custom endpoint's output, and responseFormat is the argument for chat model output.
“Use the responseFormat parameter to specify response formats for chat foundation models.”Source: docs.databricks.com
Checkpoint 6 of 8· Exam question
A batch pipeline must extract title and an array of authors from research paper abstracts stored in a Delta table, and the downstream job requires the output to always be valid, parseable JSON with exactly those two fields rather than free-form prose. Which ai_query() configuration enforces this?
Correct answer: A — Pass a responseFormat argument defining a JSON schema (or DDL-style struct) with title and authors fields, so the chat model is constrained to emit output matching that schema.
- A. Correct:
responseFormatenforces structured output by constraining the chat model's generation to conform to a supplied JSON schema or DDL-style struct, guaranteeing thetitleandauthorsfields come back as parseable JSON rather than as prose. - B. Prompt-based instructions are a request, not a constraint; the model can still drift into extra commentary, markdown fences, or missing fields, so this does not guarantee the strict, always-parseable JSON shape the downstream job requires.
- C. A low
temperaturemakes generations more repeatable but does not constrain the output schema, so the model could still consistently produce well-written prose that lacks the required JSON structure. - D.
failOnErrorcontrols whether an endpoint exception aborts the job or is captured in an error struct; it has no effect on the shape or validity of a successfully generated response.
Checkpoint 7 of 8· Exam question
A batch summarization job calls ai_query() against a foundation model endpoint for every article in a table, but the generated summaries are consistently too long and vary unpredictably in style between runs. The team wants shorter, more consistent output without changing the prompt text. Which addition to the ai_query() call addresses this?
Correct answer: A — Pass modelParameters => named_struct('max_tokens', 100, 'temperature', 0.2) to cap the generation length and reduce output variability.
- A. Correct:
modelParameterscarries chat/completion controls likemax_tokensandtemperaturestraight through to the foundation model endpoint, so cappingmax_tokensbounds summary length and loweringtemperaturereduces run-to-run variability. - B.
returnTypedescribes the expected schema of a custom endpoint's response for parsing purposes; it is not a truncation mechanism and has no effect on how much text a foundation chat model generates. - C. The endpoint argument is a plain string identifying which serving endpoint to call; wrapping it in a struct does not change generation length and would break the call rather than configure output size.
- D.
filesis used to attach image content for multimodal requests; it is unrelated to text summarization and does not influence response length or determinism for a text-only prompt.
4.Running ai_query in production pipelines
A query that works in a notebook becomes a production batch job once it sits inside a pipeline. AI Functions run from Databricks SQL, notebooks, Lakeflow pipelines and Workflows. Batch inference pipelines can handle ingestion, preprocessing, inference and post-processing from start to finish. They are written in SQL or Python and deployed in one of three ways.
| Deployment | Fits when | Example shape |
|---|---|---|
| Lakeflow pipelines | Data is continuously updated and should be processed incrementally | Streaming table ingests files; a materialized view calls ai_query; another view validates |
| Scheduled Databricks workflows | A fixed table is scored on a schedule | A job runs SELECT *, ai_query(...) FROM catalog.schema.product_reviews |
| Structured Streaming | New rows arrive in a Delta table over time | readStream from the table, ai_query in a select, writeStream to a summary table |
The Lakeflow pattern shows why responseFormat matters in production. An upstream materialized view, news_categorized, calls ai_query with a JSON schema whose category field is limited to six values, and stores the model's JSON answer in a column named meta_data. The next view, shown below, reads that field with get_json_object and declares expectations on it. An expectation is a named data-quality condition attached to a view with CONSTRAINT ... EXPECT (...): here, valid_title requires a title of at least three words and valid_category requires one of the six listed categories. In Python the same idea is written with decorators, where expect and expect_or_fail are separate variants. In the Structured Streaming variant, Spark processes every existing row once in the first micro-batch and then only rows that arrive later.
CREATE OR REFRESH MATERIALIZED VIEW news_validated (
CONSTRAINT valid_title EXPECT (size(split(get_json_object(meta_data, '$.title'), ' ')) >= 3),
CONSTRAINT valid_category EXPECT (get_json_object(meta_data, '$.category') IN ('Politics', 'Sports', 'Technology', 'Health', 'Entertainment', 'Business'))
)
COMMENT "Validated news articles ensuring the title has at least 3 words and the category is valid."
AS
SELECT *
FROM news_categorized;Scaling well mostly means not getting in the platform's way. Submit the whole dataset in one query, choose a batch-optimized system.ai model, and set failOnError => false so a single bad row can't wipe out hours of work. Don't benchmark on 50 rows and multiply. Performance does not scale linearly from very small workloads to large ones.
For monitoring a running job, use the query profile. In the SQL editor, open the running query's performance pane, choose See query profile, and click AI Query. That view shows the number of completed and failed inferences and the total time taken. Gateway-side tracking is limited for these calls. Requests to system.ai model services pass through Unity Gateway, which records usage, budgets and permission checks. Inference tables, guardrails and rate limits do not apply to those requests. For the batch job itself, the query profile is where you track progress.
Checkpoint 8 of 8· Match them up
Three teams are moving a notebook ai_query into production. Match each deployment option to the team whose situation it fits.
Tap a term, then the definition that fits it.
Lakeflow pipelines suit data that is updated continuously and should be processed incrementally. A scheduled workflow suits a fixed table scored on a schedule. Structured Streaming handles a Delta table that gains rows over time: the first micro-batch processes all existing rows, and later batches process only the new ones.
“The following example performs incremental batch inference using Lakeflow pipelines for when data is continuously updated.”Source: docs.databricks.com
Practise it for real
Turn a one-off ai_query into a batch-ready query that returns parseable output, tolerates endpoint failures, and can be monitored.
1.In the SQL editor, run the taxi example: SELECT *, ai_query('system.ai.meta-llama-3-3-70b-instruct', ... || pickup_zip) FROM samples.nyctaxi.trips LIMIT 10.
Why: This confirms the workspace, runtime and model are available before you scale up.
You should see: Ten rows, each with a free-text answer naming a US state.
2.Add modelParameters => named_struct('max_tokens', 100, 'temperature', 0.7) to a summarization query over your own table.
Why: Generation settings are constants that apply to every row.
You should see: The summaries stay within the token cap.
3.Add responseFormat with a DDL-style STRUCT or a json_schema that defines the fields you need.
Why: Every row then returns output that downstream steps can parse.
You should see: Each response matches the schema rather than being free prose.
4.Add failOnError => false, remove the LIMIT, and while the query runs open See query profile, then AI Query.
Why: The full dataset goes in one query, and failed rows are reported instead of aborting the run.
You should see: The output column is a STRUCT with response and errorMessage, and the profile shows counts of completed and failed inferences.
Stuck? Get a nudge
If a row's errorMessage is not null, reprocess only those rows. Don't rerun the whole table.
Sources
(5)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Batch inference applies models efficiently to large datasets, whereas real-time serving provides models as low-latency API endpoints.”
↩︎ Recognizing a batch inference workload - 2.
“The following example performs incremental batch inference using Lakeflow pipelines for when data is continuously updated.”
↩︎ Recognizing a batch inference workload“These pipelines can perform end-to-end workflows that include ingestion, preprocessing, inference, and post-processing.”
↩︎ Running ai_query in production pipelines“Spark processes all existing rows exactly once in the first micro-batch.”
↩︎ Running ai_query in production pipelines“The following example performs incremental batch inference using Lakeflow pipelines for when data is continuously updated.”
↩︎ Checkpoint - 3.
“ai_query is a general purpose AI Function that enables you to query existing endpoints for real-time inference or batch inference workloads.”
↩︎ Recognizing a batch inference workload“If it is not provided, ai_query() automatically infers the return type from the model schema of the custom model serving endpoint.”
↩︎ Call forms and the models ai_query can reach“The definer must have CAN QUERY permission on the endpoint.”
↩︎ Call forms and the models ai_query can reach“With the exception of temperature which has a default value of 0.0”
↩︎ Shaping output and surviving failures: modelParameters, responseFormat, failOnError“Only available for querying chat foundation models.”
↩︎ Shaping output and surviving failures: modelParameters, responseFormat, failOnError“responseFormat => 'STRUCT<research_paper_extraction:STRUCT<title:STRING, authors:ARRAY<STRING>, abstract:STRING, keywords:ARRAY<STRING>>>'”
↩︎ Shaping output and surviving failures: modelParameters, responseFormat, failOnError“Required for multimodal requests. Only image inputs: JPEG or PNG are supported.”
↩︎ Shaping output and surviving failures: modelParameters, responseFormat, failOnError“ai_query is a general purpose AI Function that enables you to query existing endpoints for real-time inference or batch inference workloads.”
↩︎ Key concept“If the inference of the row fails due to other errors, the whole query fails.”
↩︎ Exam trap 1“These model parameters must be constant parameters and not data dependent.”
↩︎ Exam trap 4“If the inference of the row fails due to model endpoint errors, the response field is null.”
↩︎ Prediction“Use the responseFormat parameter to specify response formats for chat foundation models.”
↩︎ Checkpoint - 4.
“Other Databricks-hosted models are available for use with AI Functions, but are not recommended for batch inference production workflows at scale.”
↩︎ Call forms and the models ai_query can reach“Using a non-optimized model can result in reduced throughput and longer job completion times.”
↩︎ Call forms and the models ai_query can reach“Use ai_query when a task-specific function doesn't meet your needs.”
↩︎ Call forms and the models ai_query can reach“This allows the job to complete and return error messages for failed rows, so you retain successful results without reprocessing the entire dataset.”
↩︎ Shaping output and surviving failures: modelParameters, responseFormat, failOnError“Not supported: guardrails (service policies), inference tables, tracing tables, rate limits, fallbacks, and external model providers.”
↩︎ Running ai_query in production pipelines“Submit your full dataset in a single query. AI Functions automatically handle parallelization, retries, and scaling.”
↩︎ Exam trap 2“AI Functions does not use the compute provisioned to the endpoint. AI Functions fully manages the scaling used for batch inference.”
↩︎ Exam trap 3“AI Functions does not use the compute provisioned to the endpoint. AI Functions fully manages the scaling used for batch inference.”
↩︎ Checkpoint“Manually splitting data into small batches can reduce throughput.”
↩︎ Prediction - 5.
“use Databricks-hosted foundation models (prefixed with databricks-), not provisioned throughput.”
↩︎ Call forms and the models ai_query can reach“Performance may not scale linearly from very small workloads to large-scale workloads.”
↩︎ Running ai_query in production pipelines“Click AI Query to see metrics for that particular query including the number of completed and failed inferences”
↩︎ Running ai_query in production pipelines