How to visualize Snowflake Warehouse costs in euros using Power BI

In this blog post, I'll demonstrate how you can create a Power BI report to visualize monthly Snowflake virtual warehouse costs in euros and make Snowflake costs visible even for those not having access to Snowflake console.

At the core of Snowflake are the virtual warehouses which enable unlimited scalability for computing. Data load, integration and visualization tools can have their own virtual warehouses. You can even provide a dedicated virtual warehouse for a business analyst who wants to query the raw data by himself.

You although want to ensure that Snowflake costs won’t skyrocket due to misuse of computational power what Snowflake provides so easily and the easiest way to do this is to visualize the virtual warehouse credit costs in Power BI (or similar tool) using Snowflake’s internal data dictionary and share the dashboard for relevant parties not having access to Snowflake console.

If you open any Snowflake database, you see that Snowflake databases come with INFORMATION_SCHEMA -schema included. INFORMATION_SCHEMA is Snowflake’s own data dictionary of all the stuff which are inside the database.

LOAD_HISTORY -view, for example, contains information of all files which are loaded into Snowflake. Do you want to know who has logged into Snowflake recently and what queries he or she has done? Just visit LOGIN_HISTORY and QUERY_HISTORY -views. Insides of INFORMATION_SCHEMA have been illustrated greatly in Kent Graziano’s “Using Snowflake Information Schema” so I won’t go more into details.

Our use case is to visualize the credit cost of virtual warehouses in Euros and for this, we will need:

1) Snowflake user who has access either to ACCOUNT_USAGE -schema or desired INFORMATION_SCHEMA’s.
2) PowerBI or similar BI -tool to visualize the warehouse credit usage

Finding correct schema and values which we need.

As we want to visualize credit usage of warehouses, we want to use WAREHOUSE_METERING_HISTORY -view. The view is located at two places, inside ACCOUNT_USAGE -schema and INFORMATION_SCHEMA inside every database (as information of historical and current usage of data are shared inside the account inside every INFORMATION_SCHEMA). This time we’re going to use ACCOUNT_USAGE -schema. The values which interest us are START_TIME, WAREHOUSE_NAME and CREDITS_USED.

Creating Power BI data sources

Power BI supports Snowflake, but before you can access Snowflake data, you need to download Snowflake ODBC -driver. Once you have the setup running, create a new data source using the “Get Data” -button. In my case, I have chosen “DV_WAREHOUSE_METERING_HISTORY_S” -table which is same as the WAREHOUSE_METERING_HISTORY -table, but is loaded weekly to provide historical data beyond one year.

The cost of warehouses are stored inside WAREHOUSE_METERING_HISTORY -table in CREDITS_USED column, but the Snowflake credits need to be converted into dollars and euro’s and grouped accordingly.

Add two new columns to the data set. Use the Modeling -tab and the “New Column” -button. Name first column as Dollar and one more column “Dollar” to the data and give it current value for dollar in euros. Currently one dollar is valued at 0,89 euros, so will give that value. In the future we can modify this value to be updated constantly, but for getting a best estimate for costs, a fixed value works fine.

Remember to give enough decimals for column so that Power BI won’t round up the value into one.

Create second column and give it name “Euro”. For this column, use following calculation. This calculation will count the sum the used credits and multiply the with the value of dollar. Outcome is the used credits in euros.

1 EURO = CALCULATE(SUM([CREDITS_USED])*CALCULATE(SUM(DV_WAREHOUSE_METERING_HISTORY_S[Dollar])))

Creating Power BI report

Once you have data sources ready, doing the final report is really easy. Add START_TIME (on month level) to AXIS, WAREHOUSE_NAME to LEGEND and finally EURO as value.

For this visualization, I have chosen a clustered column chart. You can add SUM of EURO to the dashboard to give it a final touch. I have also added a note that this does not include storage costs. The final result should look something like below.

Hopefully this short guidance will help you to visualize your Snowflake costs and ease the task of proofing that Snowflake is really cheap if you use it correctly.

For more information
https://docs.snowflake.net/manuals/sql-reference/functions/warehouse_metering_history.html
https://docs.snowflake.net/manuals/sql-reference/account-usage/warehouse_metering_history.html
https://docs.snowflake.net/manuals/sql-reference/info-schema.html
https://docs.snowflake.net/manuals/sql-reference/account-usage.html

New call-to-action
Visualizing location data with Excel 3D Map including a time dimension. 3D Map utility is very effective for simple and occasional use cases.

Visualizing location data by time – Excel 3D Map

Visualizing location data with Excel 3D Map including the time dimension. 3D Map utility is very effective for quick demonstrations.

The good old Microsoft Excel has an effective utility called 3D Maps. It’s great for simple geo-animations. My original use case was to visualize geographical master data for one of Solita’s customers.

See the demo video (00:35 min) created from OECD asylum seeker inflow data from years 2000-2017. The asylum data is not related to the customer project.

Next I will show how to create a 3D animation yourself.

Preparing the data for Excel 3D Map

I downloaded OECD asylum data from International Migration Database in CSV format.

After saving the CSV to my laptop I imported the data to Excel from Data > New Query > From file > From CSV.

Data after Excel Power Query importing. Asylum seeker data from OECD database. Years 2000-2017.
Data after Excel Power Query import.

 

The data had only a Year column, so I needed to create a Date column to fulfill 3D map requirements. For example year 2018 became date 31.12.2018.

The data set had lots of information about different types of asylums in each country. For the demo I only took Inflows of asylum seekers by nationality category and Total for each country.

Excel Power Query editor. Added a custom column to convert the year to a date.
Excel Power Query editor.

Creating a new tour in Excel 3D Map

You can create a new 3D map tour in Excel from Insert > 3D Map.

Launching Excel 3D Map. You can either create a new tour or open an existing one.
Launching Excel 3D Map.

 

A tour contains multiple scenes. In my demo the tour had five scenes.

Transitions and animations between the scenes are automatic. You just zoom the map to the right position for each scene. When all the scenes in the tour are in place, the tour can be played or you can create a video file from it.

Excel 3D Map has relatively few editing options. This makes the use cases somewhat limited but keeps things simple.

Creating a 3D Map with a time dimension

This is what you see when entering to 3D Map editing view.

Tour editing view in Excel 3D Map. Scenes on the left. Country as the location. Number of asylums as the value. Date as the time. Color can be changed from Layer Options.
Tour editing view in Excel 3D Map.

 

Here are the setting I adjusted from the panel on the right side of the screen.

Layer name. In the demo there’s only one layer. It would be possible to add multiple data layers per scene. You could have different data series for the number of asylums and number of immigrants.

Visualization type. There are five icons in the right panel where you can choose. My selection was region. Other options are stacked column, clustered column, bubble and heat map.

Location. Column in the data that represents the location. I chose the Country column. Could be also an address or coordinates.

Value. Column in the data that represents the magnitude, height or intensity of the location. In this example it’s the Number of asylum seekers in a specific year and country.

Time. It would be simple to show asylums in different countries on a single year. To make the demo more interesting I added a timeline to animate the asylum numbers annually. From the clock icon select Data stays until it is replaced to avoid showing cumulative numbers.

Layer options. Changed the color from blue to red.

Finally you can set the scene transition duration by clicking the gear icon from a specific scene in the left panel.

For some scenes I added a Text Box from the top menu (the Ribbon) to explain what is happening.

Creating a video from the 3D Map tour

Click Create Video from the ribbon.

Select the quality. Background music often makes the video more vivid, so you can add music from Soundtrack Options. I downloaded the sound track from here.