llooker / snowflake-usage-block Goto Github PK

Hi,

I found that few fields in tables view are not up-to-date to those of the snowflake field names for tables view.

The dashbaord https://github.com/llooker/snowflake-usage-block/blob/ea03fdde90331ac572914e31fc5b1005e2136e04/account_usage_dashboard.dashboard.lookml mentions Average Execution Time (secs). The unit is wrong. Snowflake data gives durations in milliseconds, not seconds.

In file warehouse_metering_history.view.lkml
The measure "total_credits_used" is expressed in usd_0. This should be decimal_0.

One credit does not necessarily equal one dollar.

Hi guys,
in a recent update, Snowflake introduced the Snowflake database that does what the code below (from the install instructions) does.

use role accountadmin;
show shares; -- this should return snowflake.account_usage
create database snowflake_account_usage from share snowflake.account_usage;
grant imported privileges on database snowflake_account_usage to role <preferred_role>;
use role <preferred_role>;
use schema snowflake_account_usage.account_usage;
show views;

Now that's one step less to install this block!

This is the error:
SQL compilation error: error line 2 at position 1 Invalid argument types for function 'IFF': (VARCHAR(3), VARCHAR(3), VARCHAR(2))

This is what causes it:
dimension: is_transient {
type: yesno
sql: ${TABLE}.IS_TRANSIENT ;;
description: "Whether the database is transient"
}

"yesno" should be changed to "string", the Snowflake field is VARCHAR(3) not BOOLEAN, doesn't need conversion

What does this Block do for me?

(1) Reduce Costs and Optimize Performance - Gain a holistic view across your data warehouse and optimize across the board. Identify your longest running queries, most costly queries, troublesome users, and more, to uncover the most impactful cost-saving and performance-enhancing opportunities.

(2) Monitor Cost and Usage Data - Provide comprehensive insights around your Snowflake account and usage across all your tables, databases and users. See how these metrics trend over time and compare to prior periods to ensure you're scaling effectively.

(3) Compare Performance Across Databases - If you're using another database, in addition to Snowflake, and want to understand the cost and performance implications for your workloads across your cloud environments, compare the Snowflake results from this Block with the results from other databases, such as Redshift (Block here) or BigQuery (Block here).

(4) Enterprise Data Platform - Take advantage of Looker's data platform functionality, including data actions, scheduling, permissions, alerting, parameterization (each user can only see their own data), and more. Get immediate alerts when usage is exceeded, queries exceed a runtime threshold, or any other business criteria.

(5) Understand Data Loading Issues - Analyze data loading successes and failures into any of your snowflake databases and track or alert on unexpected changes.

Snowflake Account Usage Data Structure

• Snowflake's Account Usage dataset comes complete with several views that describe objects in your Snowflake account, such as columns, tables, databases, functions, query_history, and much more. Colectively these views provide a comprehensive overview of your account activity.

Block Structure

• This Block is built on the Account Usage share provided by Snowflake, which uses Snowflake's Data Sharing offering.
• Each Account Usage view has a corresponding view in Looker. The model file of this block joins together the underlying views based on available keys. It provides a starting place for additional custom modeling.
• This Block covers all of the core metrics that come with the dataset, as well as additional value-add analysis, and should serve as a great jump start for further exploration.
• The schema documentation for Account Usage can be found in Snowflake's documentation.

Additional Info

Data Latency

• The Information Schema views and table functions display data in real-time but present a limited history, whereas the Account Usage views present a 12-month history but have some built-in latency due to the process of extracting the usage data from Snowflake’s internal metadata store. However, the Account Usage views have been designed to minimize the latency impact, based on the type of data displayed in the views. Latency SLAs can be found in the official documentation for this feature.

Implementation Instructions

Accessing the Account Usage Share

To access the Account Usage share, an account administrator must grant the user provided in your looker connection with access to the appropriate schema.

As an example, the commands below would be used if the user in your Looker connection was granted the looker_role and that is how you plan on permissioning the snowflake shared DB:

use role accountadmin;
grant imported privileges on database snowflake to role looker_role;

Dashboards

The dashboard can be used as is, or customized to your specific requirements. If you've customized the model name, rename the model in each LookML Dashboard element from snowflake_usage_block to the model name you've selected. We recommend using a global Find & Replace for this.

What if I find an error? Suggestions for improvements?

Great! Blocks were designed for continuous improvement through the help of the entire Looker community, and we'd love your input. To log an error or improvement recommendation, simply create a "New Issue" in the corresponding Github repo for this Block. Please be as detailed as possible in your explanation, and we'll address it as quick as we can.

In this Looker block the usage for Auto Clustering and Snow PIpe do not show up as a Warehouse the way they do in the front end Account Usage in Snowflake. Is there a way to add them so we can have a complete view of Cost & Usage?

This is the error:
SQL Syntax Error: SQL compilation error: error line 14 at position 1 invalid identifier 'STORAGE_USAGE.STAGE_BYTES'

If database_id or database_name is selected when it switches to DATABASE_STORAGE_USAGE_HISTORY table, something goes wrong with stage_bytes. All other similar fields in storage_usage_core have something like this:

sql: {% if database_name._in_query or database_id._in_query %}
${TABLE}.AVERAGE_FAILSAFE_BYTES
{% else %}
${TABLE}.FAILSAFE_BYTES
 {% endif %};;
description: "Number of bytes of data in Fail-safe"

but stage_bytes is simply:

dimension: stage_bytes {
type: number
sql: ${TABLE}.stage_bytes ;;
description: "Number of bytes of stage storage used by files in all internal stages (named, table, and user)"
}