Data Engineering on Microsoft Azure — Question 24

You build a data warehouse in an Azure Synapse Analytics dedicated SQL pool.
Analysts write a complex SELECT query that contains multiple JOIN and CASE statements to transform data for use in inventory reports. The inventory reports will use the data and additional WHERE parameters depending on the report. The reports will be produced once daily.
You need to implement a solution to make the dataset available for the reports. The solution must minimize query times.
What should you implement?

Answer options

• A. an ordered clustered columnstore index
• B. a materialized view
• C. result set caching
• D. a replicated table

Correct answer: B

Explanation

The correct answer is B, a materialized view, because it precomputes and stores the results of the complex query, significantly speeding up access for the daily reports. Options A, C, and D do not provide the same level of performance improvement for complex queries as a materialized view does, with A being more suited for optimizing data storage and retrieval rather than precomputed results.