Parameterizing Schema in Fabric UDF Across Environments

[Ayush05-gateway]

Hi Community Members,

I’m currently using a Fabric UDF to write to the Fabric Data Warehouse as part of a translytical flow triggered from Power BI.

While the documentation suggests otherwise, I’ve found that the schema must be explicitly specified—either within the SQL SELECT statements or, as in my case, by executing cursor.execute("USE <schema_name>") before issuing the INSERT statement.

This approach works; however, since the schema name differs across Dev, UAT, and Prod environments, I end up manually editing the function post-deployment. I'm using Fabric Deployment Pipelines for promoting the changes.

Has anyone encountered a similar scenario and found a way to parameterize the schema to avoid manual edits? I suspect this could be streamlined using Fabric CLI with GitHub-based CI/CD, but I’m hoping for a simpler alternative if one exists.

Appreciate your insights and collaboration, as always.

Thanks!

[burakkaragoz]

Hi @Ayush05-gateway ,

You're hitting one of Fabric's annoying limitations - deployment pipelines just copy code as-is without any environment-aware substitutions.

Cleanest workaround I've seen:

Use the workspace name to determine schema:

import fabric

def get_current_schema():
    workspace = fabric.get_workspace_name()
    if 'dev' in workspace.lower():
        return 'dev_schema'
    elif 'uat' in workspace.lower():
        return 'uat_schema'
    return 'prod_schema'

# Then in your UDF
schema = get_current_schema()
cursor.execute(f"USE {schema}")

Alternative - config table per environment: Create a tiny config table in each warehouse:

CREATE TABLE admin.config (environment VARCHAR(10), target_schema VARCHAR(50));
INSERT INTO admin.config VALUES ('current', 'your_env_specific_schema');

Read it at runtime in your UDF. One-time setup per environment, but then your code stays the same everywhere.

The GitHub CI/CD route would definitely solve this properly with template substitution, but if you want to stick with deployment pipelines for now, the workspace name detection is probably your easiest bet.

Environment parameterization in Fabric is still pretty rough around the edges. Microsoft keeps saying they'll improve it, but here we are...

What does your workspace naming convention look like? That'll determine if the workspace detection approach works for you.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[Ayush05-gateway · Accepted solution]

Hi @burakkaragoz 

Thanks for your reply. I tried this solution but it does not work and error thrown was fabric.get_workspace_name() is not a recognized attribute. Researched about this error and as per the research and available docs, this functionality is not available to UDFs .  However your solution made me think a bit more and I was able to get around the issue but creating a helper function to get the db name and then put a logic around it.

Code snippet of get schema fn ( this is function before the UDF function block).

# Get Schema function

def get_env_from_db(conn):

    cursor = conn.cursor()

    cursor.execute("SELECT DB_NAME()")

    db_name = cursor.fetchone()[0]

    if 'prod' in db_name.lower():

        return "prod"

    elif 'uat' in db_name.lower():

        return "uat"

    else:

        return "dev"

In step 2, call the above fn inside the UDF fn.

try:

        conn= wh.connect()

        env = get_env_from_db(conn)

        if env == "prod":

            schema = "dwh_prod"

        elif env == "uat":

            schema = "dwh_uat"

        else:

            schema = "dwh_dev"

        # Use connection to execute a query

        cursor = conn.cursor()

        cursor.execute(f"USE {schema}")

This works perfectly fine.

Thanks again for your help. It made me try and finally get to the solution.