Using AI_Functions in Your Data Warehouse: Top Use Cases

In most organizations, data warehouses hold structured data, while unstructured data is kept in the data lake. This works well for analytics workloads, which consume structured data at scale, serving a known body of reports day in and day out.

AI workloads, however, require different inputs. AI models often need to parse unstructured data - like reviews, support tickets, and PDFs - and combine them with the structured data to train, build, and serve models. So an analyst who wants sentiment on support tickets has to ship the rows out to a service, wait for predictions, and stitch them back into a table by hand. It's slow, it breaks when a schema changes, and it introduces unnecessary security and governance risks.

AI Functions solve this by bringing the AI directly to your data, rather than moving your data to a separate AI environment. You invoke models within standard SQL queries, keeping the entire inference process within your existing pipelines and Unity Catalog governance. This architecture fundamentally changes how you work with AI in your data warehouse:

Governance by default: Because AI Functions respect Unity Catalog permissions, your data remains secure and private. The model only accesses the data you explicitly permit.

SQL-native simplicity: If you can write a SELECT statement, you can build with AI. Databricks manages the complexity - planning, parallelization, and retries so you don't have to worry about cluster management or external orchestration. It's just as easy to run an inference on millions of rows as one row, the same query scales without rewriting.

Unified billing: Eliminate the complexity of reconciling disparate dashboards. AI usage surfaces in system.billing.usage right alongside your standard Databricks SQL warehouse costs.

Specialized functions: Get better results for less cost. By using task-specific functions - such as ai_classify, ai_extract, ai_translate, and ai_parse_document - you leverage models tailored for specific jobs rather than overpaying for general-purpose inference.

You can use these AI functions from anywhere on Databricks, including notebooks, Lakeflow Spark Declarative Pipelines, and Workflow. But in this post we're going to be focusing on calling these functions from Databricks Lakehouse specifically. The use cases below will show you how you can integrate these AI functions into workloads where you need to combine structured data in your data warehouse with unstructured data, either from outside the data warehouse or by producing it yourself via GenAI-enabled functions.

ai_parse_document acts as the ingestion bridge that converts raw binary file content - like PDFs or images - into readable text. Once parsed, ai_extract handles the granular extraction of specific keys and values. This combined approach eliminates the need for fragile, custom OCR pipelines or third-party parsing services that often break during schema changes.

In this use case, we point the ai_parse_document at a Databricks volume that contains invoices. Once those invoices are parsed an AI parse document produces the results in JSON, which are then passed to the ai_extract function, in which we define what entities we want to extract from those invoices. The result is a structured table with the fields that we want extracted from the invoices.

Lineage now runs from the raw PDF to the extracted rows inside a single query plan. The bridge people build for this by hand - a Python OCR service, an LLM call and a JSON-flattening step - all collapse into the query.

The ai_classify function performs zero-shot classification, mapping free-text feedback into a specific set of user-defined labels without requiring model training. This process transforms chaotic, unstructured text into governed, queryable columns, making sentiment and topic data immediately available for BI dashboards and executive reporting.

In this example, we want to classify customer reviews from the bronze.nps_responses table into positive, negative, neutral, and mixed.

With ai_translate, you can normalize multilingual data into a single target language directly within the query layer. This prevents data silos and fragmentation, allowing all downstream analysis (including classification and extraction) to operate on the entire global dataset simultaneously rather than processing English-only slices.

In this example, we extract sentiment from different customer reviews and then we translate them into English.

Focusing on operational efficiency, ai_classify converts free-form inputs like support tickets or call transcripts into actionable categories. By identifying the intent and urgency of incoming feedback at the point of ingestion, it enables automated, intelligent routing to the appropriate teams or automated response systems.

In the use case below we're ingesting different support tickets from a table and then using ai_classify to determine the user intent and urgency of the ticket.

The ai_extract function is designed to mine semi-structured information from long-form content, such as sales call transcripts, and convert narrative text into discrete, structured fields. This provides significant value by putting qualitative information directly into BI tools, effectively turning spoken conversations into queryable metrics like deal stage and risk flags.

In this use case we're mining a long transcript to identify what the next step, the deal stage, the risk flag, and the risk reason is so that salespeople can action the outcome of the meeting that produced the transcript.

ai_query is the most general function and the foundation for the rest: it allows you to send a prompt to any Databricks-hosted Foundation Model serving point you have access to, and it will return the model's answer for each row.

In this use case, we can use ai_query to draft a renewal outreach email for every customer account in the fictional gold.renewal_signals table which shows us which accounts are primed for renewal.

Because you write the prompt, it can do anything the model can do, which is why it handles the cases the more specific functions do not.

Ask for structured output: For ai_query, use responseFormat for structured output. If you pass a DDL STRUCT schema, you get typed fields instead of raw strings; JSON-schema/json_object formats still return JSON strings.

Be intentional about model choice: Every foundation model has trade-offs including cost, performance, and supported input formats. Make sure that you are intentional about which model you want to choose for which use case

Sample before you scale: Run at least 10,000 rows, read the output, then run the rest. The cost-accuracy trade-off is specific to each use case.

Treat prompts as code: Version them, review them in pull requests, comment them. A prompt is a transformation with business logic in it in this workflow

The thread through all six is the same. The AI runs in the same place as the rest of the warehouse: one platform, one governance model, one bill, one set of pipelines. Any line of your existing SQL ETL can pick up an AI step without you standing up a system to host it, and each Python script that used to translate, score or classify data on the side becomes a candidate for a one-line replacement.

So start with one column. Take the workload where the current service is most fragile, rewrite it as a SELECT, run it on 10,000 rows, and read what comes back. You will know after a quick sprint whether it fits - and you will have stopped paying the extra overhead of shipping data out just to use it.

Each notebook ships with inline sample data, the SQL step by step, and the output you should expect.