Question

A data engineer manages a Fabric warehouse where a large fact table (2 billion rows) is frequently joined to a date dimension in analytical queries. Query monitoring shows most execution time is spent redistributing data between compute nodes during these joins. The engineer wants to reduce this data movement without changing the query logic. Which action is most likely to improve join performance?

Accepted Answer

Ensure statistics are up to date and rely on the warehouse's automatic distribution optimization for the join keys

Answer

Create a nonclustered columnstore index on the join columns of both tables

Answer

Convert the warehouse to a lakehouse and enable V-Order on the fact table

Answer

Increase the capacity SKU so more compute is available during the join