How I cut a Snowflake bill by reading the query history
The query history table already knows where the money goes. You only have to ask it.
A warehouse bill grows in small steps. Nobody notices a single expensive query. Everybody notices the invoice.
Start with the query history
Snowflake keeps every query in SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY. Group by the text of the query and sort by total time:
SELECT
LEFT(query_text, 80) AS query,
COUNT(*) AS runs,
SUM(total_elapsed_time) / 1000 / 60 AS minutes
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD(day, -30, CURRENT_TIMESTAMP)
GROUP BY 1
ORDER BY minutes DESC
LIMIT 20;What the list showed
- One dashboard refreshed a full table scan every five minutes.
- A dbt model rebuilt a year of history on every run.
- A forgotten scheduled task copied a table to itself.
The fixes
Each fix took less than a day:
- Cache the dashboard query for one hour.
- Make the dbt model incremental.
- Delete the task.
The bill fell by about a third. The query history had the answer the whole time.