Microsoft Fabric Updates Blog

Another dimension of Functions in Data Warehouse

• Data Warehouse

• April 1, 2025 von Veljko Vasic 16.906 Ansichten

Today, we are announcing new types of Functions in Fabric Data Warehouse and Lakehouse SQL endpoint. Continue reading to find out more and if interested refer to sign up form for Functions preview in Fabric Data Warehouse.

About functions

Functions in SQL encapsulates specific logic that can be executed by invoking the function within queries, enabling simplicity and reusability. They can be built in or user defined. Fabric Data Warehouse provides a rich set of built-in functions, such as COUNT, SUM, TRIM, and many more. On the other hand, user-defined functions are, as the name implies, created by the user and are ideal for centralizing specific business logic.

Based on the type of value returned, functions can be scalar-value functions and table-value functions. Scalar-value functions return a single value based on the input provided. They are often used in SELECT statements, and WHERE clauses. Table-value functions return an entire table or a result set. They are commonly used in FROM clauses, subqueries, or JOINs.

Now, we are introducing another dimension that categorizes functions based on their execution engine: SQL-based, Fabric-based, or AI-based. SQL-based functions are executed by the exceptional SQL engine powering the entire querying experience in Fabric Data Warehouse. Fabric-based functions are executed by the newly released Fabric User Data Functions engine, which supports Python and will expand to additional languages in the future. AI-based functions leverage large language models (LLMs), bringing powerful AI capabilities directly into your queries.

New function types

We are announcing a private preview of the following function types:

• SQL-based scalar-value user-defined functions
• Fabric-based scalar-value user-defined functions
• AI-based scalar-value built-in functions
• AI-based scalar-value user-defined functions

Want to learn more? Sign up for new function types preview in Fabric Data Warehouse.

This announcement significantly expands existing capabilities and paints a path forward to an even more capable and extensible SQL in Fabric Data Warehouse.

To learn more, refer to our docs about supported SQL table-value user-defined functions.

Preview: SQL-based scalar-value user-defined functions

SQL-based scalar-value user-defined functions in Fabric Data Warehouse is now in preview! This highly requested feature enables you to create custom functions written in SQL that return a single scalar value. You can then easily invoke these functions within your queries, making your code simpler, more readable, and easier to maintain and share. Our approach ensures that you keep getting great performance while using SQL-based scalar-value user-defined functions in your queries.

The following is an example of simple SQL-based scalar-value user-defined function.

CREATE FUNCTION dbo.CalcDiscount (@Price DECIMAL(10,2), @Rate DECIMAL(4,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price * (1 - @DiscountRate)
END;

SELECT ProductName, Price, dbo.CalcDiscount(Price, 0.10) AS DiscountedPrice
FROM Products;

Preview: Fabric-based scalar-value user-defined functions

We are excited to announce the preview of Fabric-based scalar-value user-defined functions in Fabric Data Warehouse. This innovative feature allows you to define custom scalar functions using languages beyond SQL, starting with Python, using Fabric User Data Functions. These functions can be invoked directly within your SQL queries, providing powerful flexibility to integrate advanced logic, computations, and business rules into your data workflows.

The following is an example of Python function and its usage in Fabric Data Warehouse.

import re
from html import unescape

def clean_text(text: str) -> str:
        # Remove HTML tags, Decode HTML entities, and Strip leading and trailing spaces
        text_no_html = re.sub(r'<.*?>', '', text)
        text_decoded = unescape(text_no_html) 
        return text_cleaned.strip()

Once the function is defined it can be used in Data Warehouse like this:

CREATE FUNCTION dbo.CleanComment(@Comment VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS EXTERNAL FUNCTION [MyFunctionSet].[clean_text];

INSERT INTO CustomerFeedback
SELECT *, dbo.CleanComment(Comment) AS CleanedComment
FROM StageCustomerFeedback;

Preview: AI-based scalar-value built-in functions

We are thrilled to announce the preview of AI-based scalar-value built-in functions in Fabric Data Warehouse. These powerful functions leverage advanced AI capabilities directly within your SQL queries, enabling tasks such as sentiment analysis, text classification, content extraction, grammar correction, summarization, and translation. With these functions, you can seamlessly integrate AI-driven insights into your data analytics workflow, enriching your queries with ease and efficiency.

The following is an example of usage of AI-based scalar-value built-in functions.

INSERT INTO
    CustomerReviews
SELECT
    ReviewText,
    ai_analyze_sentiment(ReviewText) AS Sentiment,
    ai_classify(ReviewText, 'Complaint', 'Praise', 'Other') AS ReviewType,
    ai_extract(ReviewText, 'Feature Name') AS ExtractedFeature,
    ai_fix_grammar(ReviewText) AS CorrectedReview,
    ai_summarize(ReviewText) AS ReviewSummary,
    ai_translate(ReviewText, 'es') AS SpanishTranslation
FROM
    StageCustomerReviews;

Preview: AI-based scalar-value user-defined functions

We are ecstatic to introduce AI-based scalar-value user-defined functions to Fabric Data Warehouse through the versatile ai_generate_response function. With ai_generate_response, you can specify custom prompts directly within your SQL queries to leverage powerful AI capabilities, such as answering questions or generating tailored content. You can invoke this function directly or encapsulate it within new user-defined functions for easy reuse. This brings the intelligence of large language models directly into your analytics workflows, providing richer insights and uniquely customized results aligned precisely with your business needs.

CREATE FUNCTION dbo.ai_answer(@question VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
   RETURN ai_generate_response(
      CONCAT(
         'You are a customer support assistant...', 
         'Politely answer the following customer question:', 
         @question
      )
   )
END;
 
INSERT INTO
    CustomerQuestions
SELECT
    *,
    dbo.ai_answer(Question) AS Answer
FROM
    StageCustomerQuestions;

Choosing the right function type

When choosing a function type for your use case in Fabric Data Warehouse, first look into leveraging SQL built-in functions whenever possible, as these offer excellent performance and simplicity. SQL-based user-defined functions are ideal for general data operations that are not available out-of-the-box while providing seamless integration and efficiency. For scenarios involving AI-driven tasks, AI-based functions offer a straightforward and powerful solution. On the other hand, Fabric-based functions are perfect when you need maximum flexibility and support for logic beyond SQL, unlocking even greater analytics capabilities.

Conclusion

With these releases, we are delivering highly requested features and opening entirely new frontiers of capabilities for Fabric Data Warehouse and Lakehouse SQL endpoint. For the first time, you can create SQL-based scalar-value user-defined functions directly within SQL, define flexible Fabric-based scalar-value functions, and seamlessly integrate powerful AI-based scalar-value built-in and user-defined functions into your SQL queries. These new capabilities represent significant leap forward, enabling richer, smarter, and more flexible analytics workflows. We’re excited to see how you’ll leverage these innovations to unlock even greater potential in your Data Warehouse.

If you find all this interesting and useful, please refer to sign up form for Functions preview in Fabric Data Warehouse, submit your feedback on Fabric Ideas, and join the conversation on the Fabric Community