Question

You are a Fabric Analytics Engineer working in a single workspace that contains a Warehouse named 'SalesDW' and a Lakehouse named 'CustomerLH'. The 'SalesDW' warehouse holds a fact table 'FactOrders', while the 'CustomerLH' lakehouse contains a Delta table 'DimCustomer'. You need to write a single T-SQL SELECT statement that joins 'FactOrders' with 'DimCustomer' to enrich orders with customer attributes, without copying any data between the items. What is the most efficient approach?

Accepted Answer

Run a three-part name cross-item query from the SalesDW SQL analytics endpoint that joins FactOrders to CustomerLH.dbo.DimCustomer directly.

Answer

Use a Copy activity to load DimCustomer into SalesDW as a new table, then join the two tables within the warehouse.

Answer

Create a Dataflow Gen2 that merges the two tables and writes the result back to the warehouse before querying.

Answer

Export DimCustomer to a CSV file in OneLake, then use OPENROWSET to read it during the join.