Automate AI comment generation for columns

[25Tabs]

Hello, 

I have a question regarding the new AI Comment generation feature. Is it possbile to use this feature without using the UI. Currently i have to accept every suggested comment one-by-one. Is there a feature / SQL or Phython Statement that i can use to auto update multiple tables and their collumns with the AI generated comments feature?

[DoTA]

Short answer: no, there's no REST API or SQL statement that bulk-accepts the AI-generated comment suggestions you see in Catalog Explorer - that Accept/checkmark flow is UI-only, there's no endpoint behind it.

The workaround people actually use is to skip the "suggest then accept" UI feature entirely and generate + apply the comments yourself with ai_query(), which is a regular SQL function so it's fully scriptable:

1. Loop over information_schema.columns for the tables/schemas you care about.

2. For each column, call ai_query() against a serving endpoint (a pay-per-token FM like databricks-meta-llama-3-3-70b-instruct works fine for this) with a prompt that includes the table name, column name, data type, and maybe a few sample values.

3. Apply the result with ALTER TABLE catalog.schema.table ALTER COLUMN col COMMENT '<generated text>' (or COMMENT ON COLUMN ... IS '...').

That whole loop can be a single notebook/Python script driven off the catalog metadata, so you comment hundreds of columns without touching the UI. A couple of things worth building in:

- Batch it per-table and commit as you go, so a bad generation on one column doesn't block the rest.

- Sanity-check the AI output before applying (strip quotes/newlines, length-cap it) since ALTER COLUMN COMMENT will happily accept garbage.

- If you want a human-in-the-loop step without the UI, write the generated comments to a staging table first and review/approve there before the ALTER pass.

This is a known gap, not something you're missing - the Catalog Explorer feature and the ai_query()-based approach are two separate things under the hood.

[ThiamLee]

Great question! It would be really useful to have an API or SQL/Python way to generate and apply AI comments in bulk instead of accepting them one by one. Hopefully Databricks can support this workflow. 🚀

[ThiamLee]

You could check if there’s an API or Python/SQL-based way to automate this in bulk. Would be great to avoid manually accepting each comment one by one!

ChatGPT

Option 2

You could check if the platform provides an API or Python/SQL integration for bulk comment generation. Automating it would save a lot of time compared to approving each comment manually.

[data_pulse]

@25Tabs 

Adding further to above replies, for this use case there aren't any native API calls yet available to generate the comments, but another work around could be:

• Use IDE's like VS Code with GitHub Copilot for Python Extension enabled. Copilot uses column names, types, nearby code, and similar repository models to suggest comments.
• Enable VS Code inline suggestions (Check if this is been enabled in vscode): "editor.inlineSuggest.enabled": true​
• Maintain metadata of the table (Name, Columns, data types, comments etc as kind of data model), basically it's like source of truth of all table related metadata version controlled. It could be in python or yaml files.
• Then the copilot extension auto suggests table comments when new table is created/editing existing table model file like this, can accept/modify the text slightly from the suggested comments (like below).
• Commit these changes and when CD is done to any workspace, either can have a mechanism within the code itself to update Table Meta data which reads this data model file and update's table comments/ Ad-hoc one time script to run ALTER table table_name ALTER COLUMN column_name COMMENT with comments from above data model file.

This gives version controlled Column comments and ability to tweak them when required and Govern those aswell.

Another approach on the fly update directly as one of activity:

• Identify the tables missing columns from information_schema.columns where comment is NULL.
• Use ai_query to generate the comments as 

SELECT
    table_catalog,
    table_schema,
    table_name,
    column_name,
    data_type,
    ai_query(
      'system.ai.meta-llama-3-3-70b-instruct',
      CONCAT(
        'Generate a concise Unity Catalog column description. ',
        'Return only the description, no quotes or additional explanation. ',
        'Do not invent business meaning that cannot reasonably be inferred. ',
        'If the meaning is ambiguous, say what the column contains without ',
        'assuming additional business semantics. ',
        'Table: ', table_name,
        'Column: ', column_name,
        'Data type: ', data_type
      ),
      modelParameters => named_struct(
        'temperature', 0.0,
        'max_tokens', 80
      )
    ) AS proposed_comment
FROM columns_to_document​

• Then an update script with results from above query to dynamically apply missing comments for all the tables with ALTER COLUMN Comment mechanism.
• If needed, can go a step further to validate the ai_query generated column comments with ai_decide (currently in beta version) as quality gate to identify only high confidence score related comments to dynamically update.

[juanlozadab]

The ai_query route is the way to go, nothing to add there. Just a few things
that will bite you once you point it at a whole catalog.

The big one is in the docs for the AI comments feature itself: saving a comment
fires an ALTER, and that can disrupt running pipelines and jobs. Everything
suggested in this thread ends in an ALTER too, so if you are doing a few
hundred columns you are firing a few hundred ALTERs. Do the apply pass in a
maintenance window, or at least leave your busy tables for last. Generating the
comments is harmless, it is the applying that needs care.

Permissions are not the same everywhere either. Tables and columns need MODIFY,
but views and materialized views need ownership. So a loop over
information_schema will run fine until it hits the first view you do not own
and then die. Catch failures per object and log them instead of letting one
kill the whole run.

And escape the text before you build the statement. The model will eventually
return something like "the customer's id" and your ALTER breaks on the quote.
Double the single quotes, cap the length, done.

Last thing, more about quality than mechanics: doing one column at a time gives
you pretty generic comments. If you send the whole column list of a table in
one prompt and ask for all the descriptions together, the model actually sees
what the table is, and the output is a lot less boilerplate. Fewer calls as
well.