# Snowflake Materialized View Refresh Exceeds Benefit

> Flags Snowflake materialized views using over 1 refresh credit in 30 days with under 100 query hits, priced at the edition credit rate.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-materialized-view-refresh-exceeds-benefit

---

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

A [materialized view](https://docs.snowflake.com/en/user-guide/views-materialized) 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](https://docs.snowflake.com/en/sql-reference/account-usage/materialized_view_refresh_history)
has the maintenance credits:

```sql
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](https://docs.snowflake.com/en/sql-reference/account-usage/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:

```text
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;`

**Warning**
Dropping a materialized view breaks any query, dashboard or view that names it directly.
Suspend first and watch for errors before you drop.