In today’s blog, I want to do something a little different. I want to walk through a request I had from a user and how it went from “Eh…” to “Bam!”
The initial request from a user—yes, this user is in management—was to see the costs for Snowflake each day of the current month. Yes, they realize that on day 1 of each month, it will reset to 0. As always, they would like this information emailed to them. (No fancy dashboards here.)
Like many of the other emails we send to users from Snowflake, we’re going to start with a very simple query:
SELECT TO_DATE(start_time) as DayNumber,
SUM(credits_used * 3) as Cost
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time >= DATE_TRUNC('month', CURRENT_DATE)
AND start_time <= DATE_TRUNC('month', DATEADD(month, 1, CURRENT_DATE))
GROUP BY DayNumber
ORDER BY DayNumber DESC;The first function you may not be aware of is the TO_DATE() function in Snowflake. This does exactly as it says on the tin – it converts the data to a date. Since I know that data is going to be a DateTime, then I find no need to use the TRY_TO_DATE() function – which gives me a bit more usability with data I may not be so sure about.
In the Cost field, I’ve used the “Magic Number” 3 here because I’m trying to give an accurate idea of how much the processing will cost daily. The number 3, in this case, stands for the cost of a single credit. I use $3 because, at least according to Snowflake Pricing Model, the most popular edition of Snowflake costs that much – at least as of this posting. As always, if yours costs more or less, please change this “Magic Number.”
The Warehouse_Metering_History table is a great table to use in Snowflake for tracking, on an hourly basis, the amount of credits used by your queries. It can also help to performance-tune any of your warehouse configurations when working with your queries.
I also used the DATE_TRUNC function instead of the MONTH function for 2 simple reasons.
- If I use the MONTH() function, I also have to use the YEAR() function to make sure that I keep it to this year. Or I could get into some major headaches once I’m in Snowflake for over a year.
- If I use the MONTH() function, I have made the database server work much harder because I now have to run the MONTH() function on every row. And yes, for this table, that would be really really bad.
Something to keep in mind as you send this data out via email is that the data will change greatly if you send it at 1 AM versus sending it out at 1 PM or even 11 PM. Since this data is truly real-time, then sending it out at 1 AM is probably going to be close to 0 while 11 PM will be far after most people are done working for the day, and little to no one will review it until the next day. For now, let’s send it out at 4 PM so that it gives our user some time to work with the data and review it before going home for the day.
Now, normally, I would send this out straight away to our users, but, I’d like to pause right here and see what extra bit of goodness we can help the manager get more useful information before we continue.
But, that’s what we’ll talk about next time. I hope you can make some time to work with this data in your own instance of Snowflake. Until then, we will definitely be paying the cost to be the boss…