Shared SQL logic across workspace in Fabric

Hello 1maximax1 

there isn't a perfect solution to accommodate all your needs , but please tey these :

Option 1: Cross-Workspace Lakehouse Queries
You can use cross-workspace queries to access data from a Lakehouse in one workspace from another workspace. This approach allows you to:
1. Build data models by joining tables using SQL in the source Lakehouse.
2. Access these joined tables from notebooks in another workspace.
3. Perform joins on-the-fly for near real-time results.
To implement this:
1. Create your data model (joined tables) in the source Lakehouse using SQL views.
2. In the destination workspace, create a shortcut to the source Lakehouse.
3. Use the shortcut in your notebooks to query the views from the source Lakehouse.

Pros:
• Allows cross-workspace data access
• Supports on-the-fly joins for near real-time data
• No need to duplicate data
Cons:
• Performance may be impacted for complex joins
• Requires proper access permissions across workspaces

Option 2: Fabric SQL Database with Cross-Workspace Access
Another approach is to use a Fabric SQL Database:
1. Create a SQL Database in your source workspace.
2. Build your data models using SQL views or stored procedures in this database.
3. Use the SQL analytics endpoint to query this database from other workspaces.
Pros:
• Supports complex SQL logic
• Can be queried in near real-time
• Accessible across workspaces
Cons:
• Requires setting up and managing a SQL Database
• May have some performance overhead for very large datasets

Given your requirements, Option 1 (Cross-Workspace Lakehouse Queries) seems to be the best fit. It allows you to build data models using SQL, access them from notebooks in other workspaces, and perform joins on-the-fly for near real-time results

please give kudos and accept this solution, if this resolves your query.

thanks