Don’t Just Watch Snowflake Costs – Analyze Them with Trend Insights

Last time, in Dashboard Dreams and Snowflake Schemes, we talked a little about showing how much Snowflake really costs in a dashboard internal to Snowflake itself instead of having to push it to PowerBi, Tableau, Looker, or a myriad of other tools.

This time, let’s take it a step further: instead of sticking with the basic bar charts or exploding pie charts, we’ll explore how to better highlight usage trends by adding a Rolling 7-Day Average to our visualizations. This helps us more easily spot patterns and anomalies within our warehouses.

First, instead of showing all of our metric data since time began (at least as far as Snowflake is concerned), let’s keep it to the last 60 days, which should be more than enough for analysis and trend-spotting.

Let’s change our query to the following:

SELECT 
     CAST(start_time as DATE) as usage_date,
     SUM(credits_used) as daily_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(DAY, -60, CURRENT_TIMESTAMP())
GROUP BY CAST(start_time as DATE)
ORDER BY usage_date;

In our trusty Snowflake dashboard tool, we get something like the following:

It looks very much like our last attempt at a dashboard, though. I hear you now, “Sherpa, why did we change our query if we just get the same visuals!”

Hold your horses, we’re just setting the stage…

To add some trending data to our dashboard, we’ll need to work with magic – Windowing Functions! If you haven’t explored them yet, make it a priority—they’re incredibly useful in analytics work.

For our trend to work, I want to add a Rolling 7-day Average. To do that, I slightly change our query by changing it to the following:

SELECT usage_date,
       daily_credits,
       AVG(daily_credits) OVER (
            ORDER BY usage_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_day_avg
FROM (
    SELECT 
          CAST(start_time as DATE) as usage_date,
          SUM(credits_used) as daily_credits
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
    WHERE start_time >= DATEADD(DAY, -60, CURRENT_TIMESTAMP())
    GROUP BY CAST(start_time as DATE)
) as base
ORDER BY usage_date;          

And that, when the type of chart is changed to a line chart, provides the following:

In your Snowflake dashboard, switch the visualization to a line chart. You’ll now see:

• A blue line representing daily credit usage.
• A yellow line shows the 7-day rolling average.

This gives your viewers a much clearer picture of usage trends, especially useful for spotting cost spikes or sustained increases.

Now, I would love to be able to make the Daily Usage a bar or series and leave the Rolling 7-day Average as is. Sadly – and this may save you all a lot of grief – you can not have 2 different chart types in the same graph…. yet.

Your options to make this happen are as follows:

• Have 2 different charts. One with each chart type visually represented upon it.
• Have this done in one of the other Third-Party tools, such as PowerBI, Tableau, or Looker.

With that, I hope I have shown you some new things with Snowflake Dashboards, or at least made you think about it slightly differently. Until next time, my friends!