Question

A data engineer manages a Fabric Warehouse with a large fact table. A frequently run reporting query filters on a single OrderDate value but consistently scans the entire table, causing high CU consumption. The engineer opens the estimated query plan and confirms a full table scan. Statistics were recently updated and the query already uses a WHERE clause on OrderDate. What is the MOST effective next step to reduce the amount of data read by this query?

Accepted Answer

Rely on Fabric Warehouse automatic min/max rowgroup elimination by ensuring the fact data is physically ordered (sorted) by OrderDate during load.

Answer

Create a nonclustered columnstore index on the OrderDate column to force an index seek.

Answer

Add the OPTION (RECOMPILE) query hint so the optimizer regenerates the plan on every execution.

Answer

Enable result set caching so subsequent runs of the query return instantly.