4 Lessons for Using AI Functions in Microsoft Fabric Data Warehouse

A reconciliation break gets cleared, and the analyst types "agent fee diff, rebooked, see ticket 4471" into a comment field. Multiply that by a few hundred breaks a month over a few years, and your warehouse holds a detailed record of why settlement costs drift, written in a dialect no SQL query can read. Microsoft's new preview for Fabric Data Warehouse is aimed at exactly this: built-in AI functions that extract, classify and transform unstructured text directly in T-SQL, plus a generic function that accepts custom prompts.

The functions are new. The failure modes of putting model output into a warehouse are not, and most of what follows comes from that second category: things learned wiring LLMs into ETL around a Fabric lakehouse, applied to what this preview actually shipped.

What shipped, in one pass

Per the announcement, the preview adds:

• ai_extract(text, topics…) identifies the topics you name in free text and returns them as JSON properties, using contextual understanding rather than regular expressions.
• ai_analyze_sentiment(text) labels text as positive, negative, neutral or mixed.
• ai_classify(text, classes…) assigns the one label from your list that is contextually closest to the text.
• ai_summarize, ai_fix_grammar, ai_translate handle the standard transformations you would otherwise do in the application layer.
• ai_generate_response(instructions, text) takes a custom prompt for anything the built-ins don't cover.

Everything runs inside ordinary T-SQL queries, which is the actual point: text processing at table scale without an external service, an orchestration layer, or an export step.

For finance text, the mapping is fairly direct:

Lesson 1: the built-ins cover more finance text than you expect

The reflex of anyone who has wired an LLM into a pipeline is to reach for the custom prompt immediately. Here the better order is reversed: try ai_extract and ai_classify first, because they force you to state precisely what you want (a topic list, a label set) and they return predictable shapes. The announcement's own examples are extraction from medical notes and classification of application logs; swap the nouns and you have AP query routing or break-comment coding.

Free-text description lines were the hard part when I automated the processing of 600+ monthly invoices (PDF and Excel) at Morgan Stanley; amounts and dates parse, but the line that tells you what the charge actually was is prose, and allocation logic lives in that prose. A function that turns prose into a constrained label set, inside the warehouse, removes a whole category of brittle parsing code.

Sentiment analysis is the one I'd quietly skip. Nobody in finance operations needs a model to detect the tone of an overdue-payment chaser.

Lesson 2: classify at ingestion, not at reporting time

The most useful pattern in the announcement is also the smallest one: ai_classify applied to rows read from lakehouse files via OPENROWSET(), with the resulting category inserted into the target table as one more column. The label is computed once, when the row lands, and every downstream consumer inherits the same value.

Compare that with how free-text categorization usually happens in finance teams. Each analyst re-buckets the same comments in their own Power Query step or Excel lookup, the buckets drift apart, and by month-end two reports disagree on how many issues were "timing" versus "data error". A category assigned at ingestion is a derived column like any other: computed once, persisted, queryable, and traceable back to a load timestamp. It also gives you something the email-and-Excel workflow never had, namely a place to measure whether the labels are any good. The argument has nothing to do with AI; AI just makes the derivation possible for text that previously refused to be derived.

Lesson 3: a prompt is a database object now, so treat it like one

When the built-ins are not enough, ai_generate_response accepts free-form instructions, and the announcement's stated best practice is to encapsulate the prompt in a T-SQL function or procedure and invoke it as a separate module. That advice deserves more attention than a closing footnote, because it changes what a prompt is. Wrapped in CREATE OR ALTER FUNCTION, the prompt becomes a versionable, reviewable, deployable object: the same CI/CD pipeline that ships your views and stored procedures can ship your prompts. CI/CD for BI assets is part of my day job at Syngenta, and prompts living in chat windows or notebook cells are exactly the kind of asset that discipline exists for.

Adapted from the announcement's incident-analysis example, a finance flavor of the pattern:

-- The prompt lives in one place, under source control,
-- and every caller gets the same rules.
CREATE OR ALTER FUNCTION dbo.classify_break_comment (@comment VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
    RETURN ai_generate_response(
        'Read this reconciliation break comment and return concise JSON with:
         break_cause (one of: fee_mismatch, timing, missing_instruction, data_error, other),
         recurring_candidate (true/false),
         follow_up_team.
         Do not quote the original comment. Comment: ',
        @comment
    );
END;

With the prompt wrapped this way, a change to the label taxonomy goes through a pull request instead of a Slack message.

Lesson 4: a model's answer is an opinion, so store it like one

The output of these functions is a contextual judgement, not a deterministic computation, and the warehouse should record it that way. Keep the original text column untouched next to anything AI-derived. Name derived columns so nobody mistakes them for source data (break_cause_ai, not break_cause). Sample the outputs against a hand-labeled set before any report depends on them, the same way you would validate a fuzzy-matching rule in a reconciliation. And keep them out of control-critical paths for now: a category that routes a ticket to the wrong queue costs minutes, while a category that feeds a journal posting or a sign-off costs an audit finding. The preview label matters too: syntax and behavior may still change before general availability, which is one more reason to route every call through the wrapper functions from Lesson 3, so a breaking change is one ALTER away from being fixed.

Where this fits

Worth trying first on text that is genuinely stuck: break comments, close issue logs, ticket descriptions, the AP mailbox. Pick one, hand-label a sample, run ai_classify against it, and measure before you trust. The full preview write-up, with the medical-notes, log-classification and ticket-transformation examples, is Working with unstructured text in Fabric Data Warehouse with built-in AI functions on the Microsoft Fabric blog, and it links the documentation with the full function reference. Free-form text has long been the data type finance warehouses agreed to ignore; this preview makes ignoring it a choice rather than a constraint.