Question

An analytics engineer maintains a Fabric warehouse where several reports repeatedly run the same expensive aggregation joining a 2-billion-row fact table to three dimensions and grouping by month and region. The engineer wants the aggregated result to be automatically kept up to date as new data lands, without rewriting each report query or scheduling a manual refresh job. Which approach best meets these requirements?

Accepted Answer

Create a materialized view in the Fabric warehouse that defines the aggregation

Answer

Wrap the aggregation logic in a common table expression (CTE) inside each report query

Answer

Create a standard (non-materialized) view over the aggregation and point reports to the view

Answer

Build a stored procedure that writes the aggregation to a summary table and schedule it hourly