Tutorial: Create cross-warehouse queries with the SQL query editor
Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric
In this tutorial, learn about how you can easily create and execute T-SQL queries with the SQL query editor across multiple warehouse, including joining together data from a SQL analytics endpoint and a Warehouse in Microsoft Fabric.
Add multiple warehouses to the Explorer
Select the
Data Warehouse Tutorial
workspace in the navigation menu.Select the
WideWorldImporters
warehouse item.In the Explorer, select the + Warehouses button.
Select the SQL analytics endpoint of the lakehouse you created using shortcuts previously, named
ShortcutExercise
. Both items are added to the query.Your selected warehouses now show the same Explorer pane.
Execute a cross-warehouse query
In this example, you can see how easily you can run T-SQL queries across the WideWorldImporters
warehouse and ShortcutExercise
SQL analytics endpoint. You can write cross-database queries using three-part naming to reference the database.schema.table
, as in SQL Server.
From the ribbon, select New SQL query.
In the query editor, copy and paste the following T-SQL code.
SELECT Sales.StockItemKey, Sales.Description, SUM(CAST(Sales.Quantity AS int)) AS SoldQuantity, c.Customer FROM [dbo].[fact_sale] AS Sales, [ShortcutExercise].[dbo].[dimension_customer] AS c WHERE Sales.CustomerKey = c.CustomerKey GROUP BY Sales.StockItemKey, Sales.Description, c.Customer;
Select the Run button to execute the query. After the query is completed, you will see the results.
Rename the query for reference later. Right-click on
SQL query 1
in the Explorer and select Rename.Type
Cross-warehouse query
to change the name of the query.Press Enter on the keyboard or select anywhere outside the tab to save the change.
Execute a cross-warehouse cross-workspace query
To query data from Warehouse A residing in another workspace than your Warehouse B, follow these steps:
- Create a lakehouse in the same workspace as your Warehouse B.
- In that lakehouse, create a shortcut pointing to the required databases or tables from Warehouse A.
- Through the previous cross-warehouse sample query, you can now query tables in that lakehouse which are just a shortcut to Warehouse A. For example:
SELECT * FROM [lakehouse].[dbo].[table_shortcuted_from_warehouse_A]
Note
Cross-warehouse cross-workspace querying is currently limited for queries within the same region.