How to Handle Complex Stored Procedures with DML and Cross-References in Microsoft Fabric?

[aditridant]

I'm working on a solution in Microsoft Fabric where I need to accomplish two main goals:

1. Create tables with full SQL Server features such as primary keys, indexes, constraints, etc.

2. Execute a complex stored procedure that:

   • Performs cross-referencing between tables.

   • Performs UPSERT (update or insert) operations based on the logic.

Here’s the architectural setup I’m working with:

What’s Working:

• I can create fully featured tables in the Fabric Database, which supports standard SQL features like PKs, indexes, constraints — great for my data model.

• For SPs that don’t need cross-reference logic or DML, these work fine inside the Fabric Database.

The Problem:

• The stored procedure I need to run performs cross-references between tables (think joins across silver and gold) and includes DML (UPDATE, INSERT, etc.).

• To perform cross-reference logic, I have to use the SQL Endpoint of the Fabric Warehouse.

• However, SQL Endpoints in Fabric are read-only, so any DML operations fail.

What I'm Considering:

Since I can't run DML inside SQL Endpoints:

• I could create SPs or views in the SQL Endpoint purely for reading / cross-reference logic.

• Then use Data Pipelines to do the actual data copy (UPSERT) into the target Fabric table. 

My Questions:

1. Is there a better way to handle this scenario in Microsoft Fabric?
   This is part of a migration effort, and I already have a large number of stored procedures that tightly couple transformation logic with DML operations.
   Because the SQL Endpoint is read-only, I'm now considering splitting logic — moving cross-reference queries to the SQL Endpoint and doing the actual data writes via Data Pipelines. However, this involves significant effort to refactor and decouple existing procedures.
   Is there a more efficient or recommended pattern in Fabric to avoid this level of rework?
2. Can the SQL Endpoint be enabled to perform DML operations (writes)?
   Since I'm ultimately using Data Pipelines to perform writes, it feels like I'm already working around a limitation. Is there any plan or supported way to enable write access via the SQL Endpoint, even in a limited capacity, to simplify this architecture?

[v-veshwara-msft]

Hi @aditridant ,

Thanks for posting in Microsoft Fabric Community.

Regarding your scenario - where the stored procedure needs to perform cross-referencing between tables (such as across Silver and Gold layers) along with DML operations - it’s worth noting that Microsoft Fabric does support cross-database and cross-warehouse queries within the same workspace.

This means you can:

1. Join tables from different SQL Databases or Warehouses using three-part naming (DatabaseName.SchemaName.TableName)

2. Perform DML operations like INSERT INTO ... SELECT FROM across these items

3. Include this logic inside stored procedures, provided all referenced objects are part of the same workspace.

For example:

INSERT INTO ContosoWarehouse.dbo.Affiliation
SELECT *
FROM My_Lakehouse.dbo.Affiliation;

 

To enable this, ensure the required databases or warehouses are added to Object Explorer using the + Warehouses action. Once added, they can be referenced directly in your SQL queries and stored procedures.

Please follow this link for step by step procedure: Query the SQL Analytics Endpoint or Warehouse - Microsoft Fabric | Microsoft Learn

If your current setup spans multiple workspaces, or if you're using the SQL Endpoint (read-only) for part of the logic, those limitations would still apply. In such cases, using Data Pipelines or Notebooks for the write operations remains a valid alternative.

Hope this helps. Please reach out for further assistance.
If this post helps, then please consider to give a kudos and Accept as the solution to help the other members find it more quickly.

Thank you.