What is the recommended approach for speeding up complex aggregations on a large, slowly changing dataset subset?

• A. Create a secure view and cluster the view on those columns.
• B. Create a view and cluster the view on those columns.
• C. Create a materialized view and cluster the view on those columns.
• D. Create a table and cluster the view on those columns.

Answer: (C)

For speeding up complex aggregations on a large, slowly changing subset, using a materialized view is the best approach. A materialized view precomputes and stores the aggregated results, so repeated queries don’t have to redo the heavy calculations each time. Because the subset changes slowly, the materialized view only needs infrequent refreshes, delivering fast query results with modest maintenance cost.

Clustering the materialized view on the relevant columns further speeds things up by helping Snowflake prune unnecessary data during scans. When the data is large and queries focus on those columns (for filtering or grouping), clustering directs the query to the most relevant micro-partitions, reducing the amount of data read.

Regular views or secure views don’t store data, so aggregations are computed on the fly every time, which is slow for large datasets. A plain table would require you to manage the aggregation and refresh logic yourself, whereas a materialized view provides automated, efficient refreshes tailored to slowly changing data.