CertSafari
    Databricks Certified Generative AI Engineer Associate· Lessons

    Domain 4 · Lesson 36/56

    Batch inference with ai_query: workloads, arguments and pipelines

    Identify batch inference workloads and apply ai_query() appropriately

    19 min read
    1.47% of exam
    5 sources
    Published 22 Oct 2026
    Docs as of 17 Oct 2026

    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.

    Signals that separate the serving patterns
    SignalBatch inferenceReal-time serving
    InputA table or set of files that already existsOne request at a time from an app or user
    Who waitsNobody waits on an individual rowA caller blocks until the response returns
    OutputWritten to a Delta tableReturned in the API response
    Typical toolai_query inside SELECT, a pipeline or a jobA low-latency Model Serving endpoint
    ai_query applied to every row of a table: one prompt per taxi trip, built by concatenating a columnsql
    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 10

    Checkpoint 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 10

    Checkpoint 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?

    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?

    Sources123

    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.

    ai_query call forms by endpoint type
    Endpoint typeCall formRequest shape
    Foundation modelai_query(endpoint, request)STRING
    Custom model with a model schemaai_query(endpoint, request)Column or STRUCT matching input features
    Custom model without a model schemaai_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.

    Batch scoring against a traditional ML endpoint: a STRUCT request and an explicit returnTypesql
    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 10

    ai_query can reach three kinds of model:

    • Databricks-hosted models under system.ai. need no endpoint provisioning. A named subset, including system.ai.meta-llama-3-3-70b-instruct, system.ai.gpt-oss-120b and system.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?

    Sources345

    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.

    • modelParameters sets how the model generates.
    • responseFormat sets the shape of what it returns.
    • failOnError sets 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.

    Capping length and setting temperature for every row of a batch summarizationsql
    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(...).

    Which argument fixes which batch problem
    Problem in the output tableArgumentWhat it does not do
    Summaries too long or inconsistentmodelParameters (max_tokens, temperature)Guarantee a parseable structure
    Downstream parser needs fixed fieldsresponseFormat (DDL or json_schema)Apply to non-chat models
    A few bad rows kill a long jobfailOnError => falseCatch errors that are not from the model endpoint
    Custom endpoint output has no schemareturnTypeTruncate or reshape a chat model's answer
    Inputs are imagesfiles => 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.

    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?

    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?

    Sources34

    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.

    Three ways to deploy an ai_query pipeline
    DeploymentFits whenExample shape
    Lakeflow pipelinesData is continuously updated and should be processed incrementallyStreaming table ingests files; a materialized view calls ai_query; another view validates
    Scheduled Databricks workflowsA fixed table is scored on a scheduleA job runs SELECT *, ai_query(...) FROM catalog.schema.product_reviews
    Structured StreamingNew rows arrive in a Delta table over timereadStream 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.

    Validating ai_query output with Lakeflow expectations before the next inference stepsql
    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.

    Sources254

    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. 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. 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. 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. 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. 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. 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. 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
      “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. 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. 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

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