Tutorial: Create a cross-warehouse query in Warehouse

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

In this tutorial, learn how to work with the SQL query editor to write cross-warehouse query.

Note

This tutorial forms part of an end-to-end scenario. In order to complete this tutorial, you must first complete these tutorials:

1. Create a workspace
2. Create a Warehouse
3. Ingest data into a Warehouse
4. Analyze data with a notebook

Add a warehouse to the Explorer pane

In this task, learn how to work with the SQL query editor to write cross-warehouse query.

1. Ensure that the workspace you created in the first tutorial is open.

2. Select the Wide World Importers warehouse.

3. In the Explorer pane, select + Warehouses.

   

4. In the OneLake catalog window, select the Shortcut_Exercise SQL analytics endpoint. You created the SQL analytics endpoint in the Analyze data with a notebook tutorial.

   

5. Select Confirm.

6. In the Explorer pane, notice that the Shortcut_Exercise SQL analytics endpoint is available.

Run the cross-warehouse query

In this task, learn how to run the cross-warehouse query. Specifically, you will run a query that joins the Wide World Importers warehouse to the Shortcut_Exercise SQL analytics endpoint.

Note

A cross-database query uses three-part naming of database.schema.table to reference objects.

1. On the Home ribbon, select New SQL query.

   

2. In the query editor, paste the following code. The code retrieves an aggregate of quantity sold by stock item, description, and customer.

   --Retrieve an aggregate of quantity sold by stock item, description, and customer.
   SELECT
       Sales.StockItemKey,
       Sales.Description,
       c.Customer,
       SUM(CAST(Sales.Quantity AS int)) AS SoldQuantity
   FROM
       [dbo].[fact_sale] AS Sales
       INNER JOIN [Shortcut_Exercise].[dbo].[dimension_customer] AS c
           ON Sales.CustomerKey = c.CustomerKey
   GROUP BY
       Sales.StockItemKey,
       Sales.Description,
       c.Customer;
   

3. Run the query, and review the query result.

   

4. When execution completes, rename the query as Cross-warehouse Query.

Note

You can also run cross-warehouse queries that span data from a warehouse in a different workspace. However, cross-warehouse cross-workspace querying is only supported for queries within the same region.

Next step

Tutorial: Create a Direct Lake semantic model and Power BI report