Question

An analytics team runs the same expensive aggregation query dozens of times per hour against a very large, slowly-changing fact table. The query scans billions of rows and computes SUM and COUNT grouped by a low-cardinality region column. They want to dramatically reduce compute cost and latency for these repeated aggregations, and they accept a small amount of maintenance cost when the base table changes. Which Snowflake feature best fits this requirement?

Accepted Answer

Create a materialized view that pre-computes the grouped aggregation, so queries read the pre-aggregated results instead of scanning the full fact table

Answer

Create a standard (non-materialized) view over the aggregation so the definition is reused and Snowflake caches the results permanently

Answer

Rely solely on the result cache by re-running the identical query, since results are always served from cache for 24 hours regardless of base table changes

Answer

Add a clustering key on the region column so the aggregation no longer needs to scan any micro-partitions