Warehouse connection in user data functions points to lakehouse in same workspace

[Lukas2357]

Defining a user data function in a workspace with lakehouse and warehouse we see the connection always pointing to the lakehouse, even if explicitely selected the warehouse. Creating a connection to warehouse "my_warehouse" and executing the following function returns the name of my lakehouse, not the warehouse.

@udf.connection(alias="my_warehouse", argName="sqlDB")

@udf.function()

def whoami(sqlDB: FabricSqlConnection) -> str:

    """Check actual DB + a few objects."""

    conn = sqlDB.connect()

    with conn.cursor() as cur:

        cur.execute("SELECT DB_NAME(), @@VERSION;")

        db_name, version = cur.fetchone()

    return db_name

Is this a known issue?

[tayloramy]

Hi @Lukas2357,

What you’re seeing usually happens when the UDF’s connection alias is bound to the SQL endpoint of the Lakehouse instead of the Warehouse, even if the picker showed the Warehouse name. In practice, FabricSqlConnection can target both Warehouse and Lakehouse SQL endpoints, and a mismatched alias will make DB_NAME() read back the Lakehouse name. (docs: UDF programming model, docs: Connect to data sources). There have also been a few community reports of alias mix-ups that look exactly like this (example thread).

Some troubleshooting steps

1. Recreate two distinct connections from the Functions “Manage connections” pane:
   • One for the Warehouse (filter the OneLake Catalog to “Microsoft Fabric → Warehouse” before selecting).
   • One for the Lakehouse SQL endpoint (if you need it).
2. Give them visibly different aliases, e.g. wh_sales_dw and lh_sales_sql.
3. Update your UDF to use the Warehouse alias and type:

import fabric.functions as fn
udf = fn.UserDataFunctions()

@udf.connection(alias="wh_sales_dw", argName="sqlDB")
@udf.function()
def whoami(sqlDB: fn.FabricSqlConnection) -> str:
    conn = sqlDB.connect()
    with conn.cursor() as cur:
        cur.execute("SELECT DB_NAME(), @@VERSION;")
        db_name, version = cur.fetchone()
    return db_name

1. Re-publish the UDF, then invoke it again. You should now see the Warehouse name.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Lukas2357]

Thanks for the reply. In fact the reddit thread is describing the exact same issue and it was not solved for several months. I tried your suggested solution, but found the same error. Below you see I tested the function against a lakehouse and warehouse connection and both returned the lakehouse. It seems the selected endpoint and related artifact from the workspace is arbitrary. To me it seems like a bug that needs to be fixed to use data functions properly.

import fabric.functions as fn

udf = fn.UserDataFunctions()

@udf.connection(alias="whtest", argName="sqlDB")

@udf.function()

def whoami_wh(sqlDB: fn.FabricSqlConnection) -> str:

    conn = sqlDB.connect()

    with conn.cursor() as cur:

        cur.execute("SELECT DB_NAME(), @@VERSION;")

        db_name, version = cur.fetchone()

    return db_name

@udf.connection(alias="lhtest", argName="sqlDB")

@udf.function()

def whoami_lh(sqlDB: fn.FabricSqlConnection) -> str:

    conn = sqlDB.connect()

    with conn.cursor() as cur:

        cur.execute("SELECT DB_NAME(), @@VERSION;")

        db_name, version = cur.fetchone()

    return db_name

[tayloramy]

Hi @Lukas2357, 

Facinating, my fix worked in my tenant, but I agree that there is a bug here. Maybe the bug is tenant dependant and only affecting some users? 

I'd recommend opening a support ticket with Microsoft to raise this as a bug with the product team. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[v-kpoloju-msft]

Hi @Lukas2357,

Thank you for reaching out to the Microsoft Fabric Community Forum and sharing the details and your code snippet. Also, thanks to @tayloramy, for his inputs on this thread.

What you are seeing is a known behaviour in Microsoft Fabric. Even when a warehouse is explicitly selected in the UDF connection, sometimes the function defaults to the Lakehouse SQL endpoint. This happens because the connection alias might still be pointing to the Lakehouse behind the scenes, even if the UI shows your warehouse. That is why DB_NAME() returns the Lakehouse name.

Create separate connections for the Lakehouse and warehouse: Go to the Functions → Manage connections pane. Create one connection for your warehouse and another for the Lakehouse SQL endpoint. Give them clear aliases, e.g., wh_sales_dw for the warehouse.

Update Your UDF: Modify your UDF code to use the warehouse alias.

import fabric.functions as fn

udf = fn.UserDataFunctions()

@udf.connection(alias="wh_sales_dw", argName="sqlDB")
@udf.function()
def whoami(sqlDB: fn.FabricSqlConnection) -> str:
    conn = sqlDB.connect()
    with conn.cursor() as cur:
        cur.execute("SELECT DB_NAME(), @@VERSION;")
        db_name, version = cur.fetchone()
    return db_name

Republish and test: now it should return your warehouse name instead of the Lakehouse.

Refer these links:
1. https://learn.microsoft.com/en-us/fabric/data-engineering/user-data-functions/connect-to-data-source... 
2. https://learn.microsoft.com/en-us/fabric/data-engineering/user-data-functions/python-programming-mod... 

Hope this helps get you unblocked! Please keep us posted on the results so we can assist further if needed.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @Lukas2357,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.