Materialized views costing more to refresh than they save

The trade a materialized view makes

An MV pre-computes a result so queries skip the work. You pay refresh credits every time the base table changes, and you get that back only when queries hit the materialized result.

That trade holds when the source is stable and the view is queried often. It inverts when the source churns: an MV over a table receiving continuous inserts refreshes constantly, whether or not anybody reads it.

The two thresholds

Refresh credits above 1 and query hits below 100, both summed over 30 days. Deliberately loose bounds: this is a heuristic flagging a suspicious ratio, not an exact accounting of credits spent versus credits saved.

Snowflake does not expose a per-query “credits I would have burned without this MV” figure, so an exact comparison is not available. The rule finds the shape and leaves the judgment to you.

What to do instead of the MV

Two alternatives, and which one fits depends on why the MV existed.

If queries need current data, query the base table directly and let Snowflake’s result cache do the work. The result cache is free and covers repeated identical queries for 24 hours.

If the pre-computation genuinely helps but freshness does not matter, a scheduled task refreshing a regular table is usually cheaper, because you control when it runs, so it runs on a cadence rather than on every upstream write.

Summing credits in MATERIALIZED_VIEW_REFRESH_HISTORY

SELECT mv.table_name,
       SUM(mvh.credits_used) AS refresh_credits
FROM snowflake.account_usage.materialized_view_refresh_history mvh
JOIN snowflake.account_usage.tables mv ON mv.table_id = mvh.table_id
WHERE mvh.start_time > DATEADD(day,-30,CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY refresh_credits DESC;

Cross-reference against QUERY_HISTORY for reads that actually resolved against the view.

Dropping is reversible

An MV can be recreated from its definition at any time. The only cost of dropping one and being wrong is the backfill on recreation, which makes this a low-risk change compared with most storage findings.