Snowflake materialized views spending refresh credits on fewer than 100 query hits

Maintenance runs on every base-table change, used or not

A materialized view stores the precomputed result of its query. Snowflake keeps it current with a background service that updates the view after the base table changes, using Snowflake-provided compute billed in 1-second increments, and the stored results add to monthly storage. Materialized views require Enterprise Edition.

Snowflake’s own advice is to create one only when the costs are offset by reusing the results often enough. A view over a busy, frequently written table that a few people query once a week is the opposite case: every write triggers maintenance, and almost nothing reads the result.

Refresh credits per view, and who reads it

MATERIALIZED_VIEW_REFRESH_HISTORY has the maintenance credits:

SELECT database_name, schema_name, table_name AS mv_name,
       SUM(credits_used::number) AS refresh_credits_30d
FROM SNOWFLAKE.ACCOUNT_USAGE.MATERIALIZED_VIEW_REFRESH_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
HAVING SUM(credits_used::number) > 1
ORDER BY refresh_credits_30d DESC;

To count reads of a candidate, search ACCESS_HISTORY (Enterprise Edition) for queries whose direct_objects_accessed include the view’s fully qualified name over the same 30 days. The refresh view needs USAGE_VIEWER; access history needs GOVERNANCE_VIEWER.

More than 1 credit spent, fewer than 100 hits

ZopNight sums the view’s refresh credits over the last 30 days and requires more than 1. It then sums the view’s query hits over the same window and requires fewer than 100. A view that is both cheap to maintain and rarely used is left alone, as is an expensive view that is read often.

When the view is not flagged

If either the refresh-credit series or the query-hit series is missing, or has no datapoints in the window, the rule does not fire. It also stays silent when no credit rate is available for the account, since the finding is priced and a zero-dollar recommendation would be misleading.

Pricing the refresh credits

The saving is the refresh credits the view would stop consuming, turned into dollars at the account’s per-credit rate for its edition. When the edition is not known, ZopNight uses the Enterprise rate. Because the window is 30 days, the credit figure is already monthly:

saving per month = refresh credits over 30 days x credit rate for the edition

Storage for the view’s results is not included, so the real saving is slightly higher.

Dropping or pausing the view

1. Check the few queries that do hit the view. Point them at the base table, or at a table rebuilt by a scheduled task if they need precomputed results.
2. To stop maintenance while you decide: ALTER MATERIALIZED VIEW sales_mv SUSPEND; A suspended view cannot be queried until it is resumed.
3. When nothing depends on it: DROP MATERIALIZED VIEW sales_mv;