Databricks interview questions: AI functions and Vector Search in SQL

Say this first: ai_query is a SQL expression the engine batches and distributes, so the rows stay in the table and the calls go out in batches.

Why: collect() pulls the million rows to the driver, and a Python loop then sends them one at a time, with you owning retries, rate limits, and the write-back. ai_query('endpoint', text_col) is an ordinary expression in a SELECT, so it runs where the data already is. The engine groups rows into batches and sends those to the serving endpoint, which is both faster and simpler than the loop. The result comes back as a column you can filter, group, or write with CREATE TABLE AS. You still pay per call, but you stop paying the engineering cost of the loop.

CREATE TABLE support.ticket_labels AS
SELECT ticket_id,
       ai_query('ticket-classifier', comment) AS label
FROM support.tickets
WHERE created_date >= current_date() - 7

Common mistake: Calling ai_query a Python UDF and expecting to manage the HTTP calls yourself.

Follow-up they will ask: If it is just SQL, why does the cost still scale with the number of rows?

Say this first: Use a task function such as ai_analyze_sentiment or ai_classify, because the result is a constrained value you can group by rather than prose you have to parse.

Why: ai_query with a prompt returns whatever the model writes, which changes shape between rows and between reruns. Task functions target one job: ai_classify picks from a label set you supply, ai_extract pulls fields into a structure, ai_summarize, ai_translate, ai_analyze_sentiment, ai_similarity, ai_forecast, and ai_parse_document each return a predictable type. A sentiment column that is always one of a few values can be grouped and joined. A paragraph cannot. When you do need ai_query, pass a response schema so the endpoint returns structured fields instead of text you split later.

SELECT ticket_id,
       ai_classify(comment, ARRAY('billing', 'outage', 'other')) AS topic,
       ai_analyze_sentiment(comment) AS sentiment
FROM support.tickets

Common mistake: Using ai_query with a long prompt and then parsing the prose with string functions.

Follow-up they will ask: What does a response schema change about the value you get back?

Say this first: The filter was applied to the model's output, so the endpoint was called for every row and only then were rows discarded.

Why: ai_query costs one inference per row that reaches it, and the engine does not know the model will reject most of them. WHERE ai_query(...) = 'urgent' evaluates the model for the whole table and filters afterwards. WHERE created_date >= ... AND region = 'EMEA' followed by ai_query in the SELECT list evaluates the model only for the rows that survive. The same applies inside a view: a downstream filter on the label does not push through the model call. Look at the query profile for the number of rows entering the AI operator. That number times the per-token price is the bill.

-- calls the model for every row
SELECT * FROM tickets WHERE ai_classify(comment, ARRAY('urgent','other')) = 'urgent';
-- calls it only for this week's rows
SELECT ai_classify(comment, ARRAY('urgent','other'))
FROM tickets WHERE created_date >= current_date() - 7

Common mistake: Assuming the optimizer pushes a predicate through a model call the way it pushes one through a Parquet scan.

Follow-up they will ask: How would you check the profile to confirm how many calls you paid for?

Say this first: The engine batches and distributes ai_query, and the endpoint it calls is a governed Unity Catalog object. A UDF is opaque per-row code running in a Python worker.

Why: A Python UDF sends rows to a Python process, serializes each one, and runs your loop, and the optimizer cannot see inside it. ai_query is planned by the engine: it batches many rows into one request, runs those batches across the cluster, and retries against the serving endpoint. The endpoint is a Unity Catalog object, so grants decide who may call it, lineage records that the table was produced from it, and audit records the calls. AI Gateway can add rate limits, payload logging, and usage tracking in front of that endpoint. None of that exists for a URL hardcoded inside a UDF. The result is still one inference per row — batching changes throughput, not the bill's slope.

Senior depth: No. Batching amortises request overhead, so a million rows finish sooner and waste fewer tokens on repeated prompts, but you still pay for every row's input and output tokens. The way to lower the bill is to call the model on fewer rows.

Common mistake: Wrapping the endpoint in a UDF for flexibility and losing batching, grants, and lineage.

Follow-up they will ask: Does batching mean a million rows costs the same as a thousand?

Say this first: The SQL does not change — you name an endpoint. The bill changes from a per-token price to capacity you pay for whether or not queries arrive.

Why: ai_query takes an endpoint name, and any of those three can sit behind one. A foundation-model endpoint bills per token, so idle costs nothing and a big backfill costs a lot, all at once. Provisioned throughput reserves capacity, so latency is predictable and you pay for the reservation even on a quiet day. A custom model on Model Serving is your artifact, your latency, and your scale-to-zero or always-on choice. Picking provisioned throughput for a nightly batch that runs for twenty minutes means paying for capacity the other twenty-three hours and forty minutes. Picking pay-per-token for an interactive assistant means latency and rate limits you do not control. The SQL is the wrong place to encode that choice; the endpoint configuration is the right one.

Senior depth: Provisioned throughput or a warm custom endpoint, because a person is waiting and a cold pay-per-token call adds latency and can be rate-limited mid-session. The nightly batch stays on pay-per-token unless its volume justifies a reservation.

Common mistake: Provisioning throughput because it sounds faster, for a workload that runs once a night.

Follow-up they will ask: Which of the three would you put behind an interactive assistant, and why?

Say this first: The sync job. Delta Sync tracks the source Delta table through change data feed and updates the index as rows are inserted, changed, and deleted.

Why: A self-managed index is correct until the table changes, and then freshness is a pipeline you operate: detect changes, embed them, upsert them, and delete the ones that disappeared. In Delta Sync mode the index is defined on a Delta table column, Databricks can compute the embeddings, and change data feed drives incremental updates. Querying the index is a mix of vector similarity and keyword filters, so you can restrict by tenant or date as well as by meaning. The index is a Unity Catalog object, so grants decide who can query it. You can still supply your own vectors if you have an embedding model you do not want replaced. What you give up is control of the index internals.

CREATE VECTOR SEARCH INDEX support.ticket_index
ON support.tickets (ticket_id)
WITH (embedding_column => comment, sync_mode => 'CONTINUOUS')

Senior depth: The change is captured by change data feed, re-embedded, and upserted into the index. Until that sync completes, queries see the previous vector. Sync lag is the failure mode to monitor, not a missing cron.

Common mistake: Keeping a separate embedding job 'so we control it' and then debugging staleness every week.

Follow-up they will ask: A row is updated in the source table. What has to happen before a query sees the new text?

Say this first: The output is non-deterministic, so incremental refresh cannot reuse prior results, and every refresh calls the model again for every row.

Why: An incremental refresh reuses rows whose inputs did not change. A non-deterministic expression has no stable output for a given input, so the planner cannot prove that yesterday's label is still valid and falls back to recomputing the view. Recomputing calls the endpoint for the whole table, every hour, which is the bill. The labels also drift: the same ticket can classify differently on Tuesday, so downstream aggregates move without any source data moving. Keep model inference in a table you append to, with the label stored as data, and let the materialized view aggregate that table. Inference becomes a deliberate write instead of a side effect of refresh.

Senior depth: Incrementally re-infer only the changed keys, using the source table's change data feed, and merge those labels. The view then refreshes over a deterministic table and can be incremental again.

Common mistake: Putting ai_query inside a materialized view because the syntax allows it.

Follow-up they will ask: How do you keep the stored labels from going stale when the source row changes?

Say this first: The endpoint and the index are Unity Catalog objects, so grants decide who calls them, and AI Gateway records and rate-limits what was sent.

Why: A URL in a notebook plus a personal token is invisible: anyone who can run the notebook can send any column anywhere, and you will not know. An endpoint registered in Unity Catalog is grantable, so only a specific group can invoke it, and lineage shows which tables were produced using it. AI Gateway in front of the endpoint adds rate limits, usage accounting, and payload logging, which is the record you need when someone asks what customer text left the account. Combine that with column masks so the PII columns are masked before they ever reach the prompt. The remaining decision is contractual: which provider, which region, and whether prompts are retained, and that one is not a Unity Catalog setting.

Senior depth: The model sees whatever the caller sees. If the caller is subject to the mask, the prompt contains the masked value. If the view or job runs as an owner who is exempt from the mask, the model receives the raw value, which is usually not what anyone intended.

Common mistake: Relying on a guideline that says not to paste customer data into a model.

Follow-up they will ask: A masked column is used inside ai_query. Who sees the unmasked value — the model, or only the function?