Working with unstructured text in Fabric Data Warehouse with built-in AI functions (Preview)
Data warehouses traditionally focus on structured and semi-structured data. Free-form text such as notes, logs, or comments was difficult to process in data warehousing scenarios without external components.
Microsoft Fabric Data Warehouse is opening new scenarios in modern data warehousing by enabling you to work with unstructured text using new AI capabilities.
We are introducing built-in AI functions that enable extraction, classification, sentiment analysis, and transformation of unstructured text. In addition to this, the AI functionalities enable you to use custom prompt-based processing directly in T-SQL language.
In this article, you will learn about these new capabilities that are in preview.
Extract information from text
The ai_extract(text, topics…) function identifies and extracts information from the unstructured text using contextual understanding. The following example extracts the symptoms, medical diagnosis, and treatment from the medical notes that reside in a table:
Figures 1-2. Extracting details from medical notes.
This function will extract the required topics from the text and return them as a set of JSON properties. The extraction relies on contextual interpretation rather than complex rules or regular expressions.
Analyze sentiment of text
The ai_analyze_sentiment(text) function enables you to determine the sentiment of text. This functionality enables you to analyze comments, reviews, emails and other text stored in tables as shown in the following example:
SELECT CommentId, CommentText,
ai_analyze_sentiment(CommentText) AS Sentiment
FROM Feedback;
Based on text content, the function will categorize the sentiment of text as positive, negative, neutral, or mixed.
Classify text with ai_classify
The ai_classify(text, classes…) function enables you to assign a category or label on a text based on contextual similarity with the provided classes. A scenario might be processing log files stored in a lakehouse accessed using the OPENROWSET() function, classifying the text before inserting it into the table, as shown in the following example:
INSERT INTO silver.Logs (LogTime, LogMessage, Category)
SELECT LogTime, LogMessage,
ai_classify(LogMessage, ‘UI’,’wrong result’,’performance’,’timeout’) AS Category
FROM OPENROWSET(‘/Files/logs/*.jsonl’, DATA_SOURCE=’AppLogLakehouse’);
The ai_classify() function will pick one category that is contextually most like the input text and return it as a result. This value is ingested as an additional category column in the target table. This might be extremely helpful for labeling and categorizing new inputs.
Transform text using AI functions
Fabric DW enables you to fix grammar, summarize or translate text using AI functions. An example of query that transforms descriptions for a ticket table using built-in ai functions is:
UPDATE Tickets
SET Summary = ai_summarize(Description),
Description = ai_fix_grammar(Description),
SpanishDescription = ai_translate(Description,’es’)
FROM Tickets;
These functions enable standard text processing tasks grammar correction, summarization, and translation, which you might typically perform in the application layer. However, using these AI functions within the data warehouse allows you to apply them at scale, processing large volumes of records directly within your tables, something that is not easily achievable at the application layer without complex integration and orchestration.
Apply prompt engineering rules to transform text
When built in functions are not enough, you can supply a custom instruction prompt to generic ai_generate_response(instructions, text) function to perform any transformation. The custom prompts enable you to mask sensitive data, customize rules for analyzing sentiments, and extract information based on more precise rules.
As an example, you can customize extraction of information from incident text:
CREATE OR ALTER FUNCTION dbo.analyze_incident( @incident_text VARCHAR(8000) )
RETURNS VARCHAR(8000) AS BEGIN
RETURN
ai_generate_response(‘Analyze the incident and return a concise JSON with root_cause, immediate_action, and owner_team. Use short technical explanations to avoid quoting the text. Incident: ‘, @incident_text )
)
END;
Once you create this kind of function, you can call it from any query and apply the logic defined in prompt on input text. As a best practice, you should encapsulate your prompt in T-SQL function or procedure and invoke it as a separate module.
Conclusion
The addition of AI features in Fabric Data Warehouse makes it possible to process unstructured text directly with the T-SQL language. Now, SQL developers can use custom prompts and build advanced AI logic with functions and procedures to automate tasks like text analysis, sentiment detection, and classification. Everything is handled within your usual queries, making powerful text transformation easy and scalable. As these features grow during the preview, they open new ways to handle data intelligently right inside T-SQL environment.
To learn more, refer to the documentation: Use AI functions in Fabric Data Warehouse.
