`ai_enrich` function

Applies to: Databricks SQL Databricks Runtime

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

The ai_enrich() function generates new columns for a row from a schema you define. Given input content and a target schema, the function calls an AI model to fill in each field. It can optionally ground the generated values in one or more knowledge sources like an AI Search index or a live web search, so the values reflect your own data or up-to-date information rather than the model's training data alone.

Use ai_enrich to add derived attributes to a table at scale. You can tag and categorize records, fill in missing metadata, or attach researched context to each row from a single SQL function call. By default, each generated field is returned with a short rationale that explains how the value was derived.

Requirements

Databricks Runtime 18.2 or above.
If you are using Serverless compute, the serverless environment version must be set to 3 or above, as this enables features like VARIANT.
To ground enrichment in an AI Search index, you need one or more AI Search indexes to use as knowledge sources.
The ai_enrich function is available using Databricks notebooks, SQL editor, Databricks workflows, jobs, or Spark Declarative Pipelines on Lakeflow.

Data security

Your document data is processed within the Databricks security perimeter. Databricks does not store the parameters that are passed into the AI function calls, but does retain metadata run details, such as the Databricks Runtime version used.

Syntax

ai_enrich(content, schema [, knowledge_sources] [, options])

Arguments

content: A STRING or VARIANT expression. The row to enrich. VARIANT input, such as the output of another AI function like ai_parse_document, is serialized to a JSON string internally.
schema: A STRING literal that defines the columns to generate. It uses the same grammar as ai_extract. The schema can be:
- Simple schema: A JSON array of field names, which are generated as strings.
  JSON
```
["industry", "headquarters_country", "year_founded"]
```
- Advanced schema: A JSON object with type information, descriptions, and nested structures.
  - Supports string, integer, number, boolean, and enum types. Performs type validation. Maximum of 500 enum values.
  - Supports nested objects using "type": "object" with "properties".
  - Supports arrays of primitives or objects using "type": "array" with "items".
  - Optional "description" field for each property to guide the generated value.
  JSON
```
{
  "hq_address": {
    "type": "object",
    "description": "Registered headquarters address",
    "properties": {
      "city": { "type": "string" },
      "country": { "type": "string" }
    }
  },
  "founding_team": { "type": "array", "description": "Full names of the founders", "items": { "type": "string" } },
  "founding_year": { "type": "integer", "description": "Year the company was founded" }
}
```
knowledge_sources: An optional VARIANT or STRING expression containing a JSON array of knowledge source configurations used to ground the generated values. See Knowledge source configuration.
options: An optional MAP<STRING, STRING>. Supported keys:
- 'version': The function version to use.
- 'instructions': A STRING of up to 20,000 characters. Natural-language guidance that describes the enrichment task. Optional; the schema field names alone can drive the enrichment. For example, 'Infer attributes for each company from its public profile.'
- 'enableRationale': 'true' (default) or 'false'. When 'true', each generated field is returned as a {rationale, value} object, where rationale explains how the value was derived. Set to 'false' to return {value} only.

Knowledge source configuration

The knowledge_sources argument is a JSON array. Each element is a {type, description, config} envelope. The type field identifies how ai_enrich retrieves grounding context, and the config field contains the source-specific configuration.

Key	Required	Description
`type`	Yes	The knowledge source type. One of `vector_search` (an AI Search index) or `web_search`.
`description`	No	A natural-language description of the source. Used to help the function decide when and how to retrieve from it.
`config`	Yes	An object containing the source-specific configuration. See AI Search index configuration for `vector_search` and Web search configuration for `web_search`.

Key	Required	Description
`type`	Yes	The knowledge source type. One of `vector_search` (an AI Search index) or `web_search`.
`description`	No	A natural-language description of the source. Used to help the function decide when and how to retrieve from it.
`config`	Yes	An object containing the source-specific configuration. See AI Search index configuration for `vector_search` and Web search configuration for `web_search`.

AI Search index configuration

For an AI Search index with type set to vector_search, config accepts the following keys:

Key	Required	Description
`index_name`	Yes	The Unity Catalog three-level name of the AI Search index, for example `catalog.schema.my_index`.
`text_col`	Yes	The column in the index that contains the document text.
`doc_uri_col`	Yes	The column in the index that contains the document URI.
`filter_columns`	No	A comma-separated string or JSON array of columns available for metadata filtering. When omitted, the list is derived from the index schema, excluding reserved, text, and document URI columns.

Key	Required	Description
`index_name`	Yes	The Unity Catalog three-level name of the AI Search index, for example `catalog.schema.my_index`.
`text_col`	Yes	The column in the index that contains the document text.
`doc_uri_col`	Yes	The column in the index that contains the document URI.
`filter_columns`	No	A comma-separated string or JSON array of columns available for metadata filtering. When omitted, the list is derived from the index schema, excluding reserved, text, and document URI columns.

You can configure more than one vector_search source in a single call.

Web search configuration

For a web search with type set to web_search, config accepts the following optional keys. Web search runs through web search on Databricks; see Limitations for availability.

Key	Required	Description
`allowed_domains`	No	A JSON array of domains to restrict the search to. When set, only results from these domains are used.
`blocked_domains`	No	A JSON array of domains to exclude from the search.

Key	Required	Description
`allowed_domains`	No	A JSON array of domains to restrict the search to. When set, only results from these domains are used.
`blocked_domains`	No	A JSON array of domains to exclude from the search.

You can configure at most one web_search source per call.

The following example configures an AI Search index and a web search as knowledge sources:

JSON
[
  {
    "type": "vector_search",
    "description": "Internal product catalog",
    "config": {
      "index_name": "prod_catalog.docs.product_catalog",
      "text_col": "description",
      "doc_uri_col": "product_url"
    }
  },
  {
    "type": "web_search",
    "config": {
      "allowed_domains": ["wikipedia.org"]
    }
  }
]

Returns

A VARIANT with the following schema:

JSON
{
  "response": { ... },     // Generated columns matching the provided schema. Each leaf is returned as an object (see below).
  "error_message": null,   // null on success, or an error message on failure
  "metadata": { ... }      // Metadata about the response, including grounding sources.
}

The response field contains the generated columns:

Field names and types match the schema definition. Nested objects and arrays keep their original shape.
By default (enableRationale is 'true'), each leaf is a {rationale, value} object, where rationale is a short explanation of how the value was derived and value is the generated value, typed according to the schema. When enableRationale is 'false', each leaf is a {value} object.
A field's value is null when it cannot be generated.

The metadata field contains metadata about the response. When the row is grounded by a knowledge source, metadata.sources is an array of the source document identifiers that grounded the row. Grounding is row-level, so sources applies to the whole row rather than to individual fields.

If content is NULL, the result is NULL.

Examples

Basic enrichment

The following example generates two columns for each company name using the model's own knowledge. Because rationale is on by default, each field is returned as a {rationale, value} object:

SQL
SELECT ai_enrich(
  company_name,
  '["industry", "headquarters_country"]'
) AS result
FROM sales.accounts.companies;

Structured schema with instructions

The following example defines a typed schema, adds instructions to steer the task, and disables rationale so each field returns a plain value:

SQL
SELECT ai_enrich(
  review_text,
  '{
    "sentiment": {"type": "string", "description": "positive, negative, or neutral"},
    "topics": {"type": "array", "items": {"type": "string"}},
    "requires_follow_up": {"type": "boolean"}
  }',
  options => map(
    'instructions', 'Analyze the customer review and categorize it for the support team.',
    'enableRationale', 'false'
  )
) AS result
FROM support.reviews.customer_reviews;

Generate a nested schema

The following example generates a nested schema for each company — a structured address object, an array of founder names, a typed year, and a nested funding round:

SQL
SELECT ai_enrich(
  company_name,
  '{
    "hq_address": {
      "type": "object",
      "description": "Registered headquarters address",
      "properties": {
        "city": {"type": "string"},
        "country": {"type": "string"}
      }
    },
    "founding_team": {"type": "array", "description": "Full names of the founders", "items": {"type": "string"}},
    "founding_year": {"type": "integer", "description": "Year the company was founded"},
    "latest_funding_round": {
      "type": "object",
      "properties": {
        "stage": {"type": "string", "description": "Funding stage, for example Seed or Series A"},
        "amount_usd": {"type": "number", "description": "Amount raised in USD"}
      }
    }
  }'
) AS result
FROM sales.accounts.companies;

Ground enrichment in an AI Search index

The following example enriches each support ticket with fields grounded in an AI Search index of product documentation, so the generated values are drawn from your own content:

SQL
SELECT
  ticket_id,
  ai_enrich(
    customer_description,
    '{
      "affected_product": {"type": "string"},
      "suggested_resolution": {"type": "string"},
      "documentation_url": {"type": "string"}
    }',
    PARSE_JSON('[{
      "type": "vector_search",
      "description": "Product documentation and troubleshooting guides",
      "config": {
        "index_name": "support.docs.product_documentation",
        "text_col": "content",
        "doc_uri_col": "doc_url"
      }
    }]')
  ) AS result
FROM support.tickets.open_tickets;

Ground enrichment with web search

The following example enriches each company row with up-to-date information retrieved from the web:

SQL
SELECT ai_enrich(
  company_name,
  '["recent_funding_round", "latest_headline"]',
  PARSE_JSON('[{
    "type": "web_search",
    "config": {"allowed_domains": ["reuters.com", "bloomberg.com"]}
  }]'),
  options => map('instructions', 'Find the most recent, verifiable information for each company.')
) AS result
FROM sales.accounts.companies;

Limitations

Grounding with a web_search knowledge source is only available in some regions and workspaces. See web search on Databricks.
The instructions option is limited to 20,000 characters.

Requirements​

Data security​

Syntax​

Arguments​

Knowledge source configuration​

AI Search index configuration​

Web search configuration​

Returns​

Examples​

Basic enrichment​

Structured schema with instructions​

Generate a nested schema​

Ground enrichment in an AI Search index​

Ground enrichment with web search​

Limitations​

Related functions​