Snowflake Usage Analytics

The most popular analytics offering in DLH.io allows the viewing of comprehensive Snowflake costs

Snowflake cost calculators surface usage and costs, available to the account administrator role under the Account > Billing & Usage tab. These insights are largely presented as data, not as historical trends. As a result, this excludes in-depth analysis of the various parameters that affect usage and costs, and levers you can pull to control them.

Rest assured, Snowflake does a good job of making the usage statistics and costs easily visible via their standard interface. However, some organizations may need more detail: IT Infrastructure leads and business sponsors of initiatives that leverage Snowflake to achieve important business outcomes would need to review usage statistics and costs on a more frequent basis.

Data analytics tools provide deeper insight on Snowflake costs and usage. This is where DLH.io comes into play. Prior to deploying Snowflake Usage Analytics, complete the Pre-Install Items:

Pre-Install Items

New DLH.io Customer

If DLH.io is not already landing data into your Snowflake Target, follow these steps which will create the necessary users, warehouse, databases, etc.

In order to get this dashboard configured in DLH.io, the following script will need to be run in Snowflake. Modify the role, username & password to fit your naming convention and existing names.


-- // DLH.io Create User Script
begin;

  -- // Create variables at top to propogate through script for user
  set role_name = 'DLH.io_ROLE';
  set user_name = 'DLH.io_USER';
  set user_password = 'tmp!password'; -- change to a unique password
  set warehouse_name = 'DLH.io_WH';
  set database_name = 'DLH.io_RAW';

  -- // Change role to securityadmin as required for user and role controls
  use role securityadmin;

  -- // Create role for DLH.io with required grant
  create role if not exists identifier($role_name);
  grant role identifier($role_name) to role SYSADMIN;

  -- // Create a new user for DLH.io interaction
  create user if not exists identifier($user_name)
  password = $user_password
  default_role = $role_name
  default_warehouse = $warehouse_name
  comment = 'DLH.io user to synchronize data from our source systems into our Snowflake data cloud.';

  -- // Grant the created DLH.io_role to the user
  grant role identifier($role_name) to user identifier($user_name);

  -- // Change the active role to sysadmin as required for db and wh controls
  use role sysadmin;

  -- // Create a warehouse for DLH.io separate compute
  create warehouse if not exists identifier($warehouse_name)
  warehouse_size = xsmall
  warehouse_type = standard
  auto_suspend = 60
  auto_resume = true
  initially_suspended = true
  comment = 'DLH.io Warehouse to separate compute from other warehouse usage in our Snowflake account.';

  -- // Create the database for DLH.io raw landing zone
  create database if not exists identifier($database_name);

  -- // Grant the DLH.io role access to the created warehouse
  grant USAGE, MONITOR on warehouse identifier($warehouse_name)
  to role identifier($role_name);

  -- // Grant the DLH.io access to database
  grant CREATE SCHEMA, MONITOR, USAGE on database identifier($database_name)
  to role identifier($role_name);

  -- //
  -- // -------------- Snowflake Usage Analytics --------------
  -- //
  -- // This section is required to be completed by an ACCOUNTADMIN in order
  -- // to give this user access to certain SNOWFLAKE database tables for Usage Analytics
  -- // to apply, simply uncomment the rows in this section and run...
  -- // NB: If needing to run separately, update with the role name created above.
  -- //

  -- use role accountadmin;
  -- GRANT MONITOR USAGE ON ACCOUNT TO ROLE identifier($role_name);
  -- GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE identifier($role_name);

  -- // -------------- Snowflake Usage Analytics Only Pre-Requisite (finish)

--// Finally commit the transaction of creating the above objects
commit;

-- // DLH.io Create User Scriptbegin;  -- // Create variables at top to propogate through script for user  set role_name = 'DLH.io_ROLE';  set user_name = 'DLH.io_USER';  set user_password = 'tmp!password'; -- change to a unique password  set warehouse_name = 'DLH.io_WH';  set database_name = 'DLH.io_RAW';  -- // Change role to securityadmin as required for user and role controls  use role securityadmin;  -- // Create role for DLH.io with required grant  create role if not exists identifier($role_name);  grant role identifier($role_name) to role SYSADMIN;  -- // Create a new user for DLH.io interaction  create user if not exists identifier($user_name)  password = $user_password  default_role = $role_name  default_warehouse = $warehouse_name  comment = 'DLH.io user to synchronize data from our source systems into our Snowflake data cloud.';  -- // Grant the created DLH.io_role to the user  grant role identifier($role_name) to user identifier($user_name);  -- // Change the active role to sysadmin as required for db and wh controls  use role sysadmin;  -- // Create a warehouse for DLH.io separate compute  create warehouse if not exists identifier($warehouse_name)  warehouse_size = xsmall  warehouse_type = standard  auto_suspend = 60  auto_resume = true  initially_suspended = true  comment = 'DLH.io Warehouse to separate compute from other warehouse usage in our Snowflake account.';  -- // Create the database for DLH.io raw landing zone  create database if not exists identifier($database_name);  -- // Grant the DLH.io role access to the created warehouse  grant USAGE, MONITOR on warehouse identifier($warehouse_name)  to role identifier($role_name);  -- // Grant the DLH.io access to database  grant CREATE SCHEMA, MONITOR, USAGE on database identifier($database_name)  to role identifier($role_name);  -- //  -- // -------------- Snowflake Usage Analytics --------------  -- //  -- // This section is required to be completed by an ACCOUNTADMIN in order  -- // to give this user access to certain SNOWFLAKE database tables for Usage Analytics  -- // to apply, simply uncomment the rows in this section and run...  -- // NB: If needing to run separately, update with the role name created above.  -- //  -- use role accountadmin;  -- GRANT MONITOR USAGE ON ACCOUNT TO ROLE identifier($role_name);  -- GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE identifier($role_name);  -- // -------------- Snowflake Usage Analytics Only Pre-Requisite (finish)--// Finally commit the transaction of creating the above objectscommit;

Existing DLH.io Customer

If DLH.io is already landing data into your Snowflake Target, follow these steps.

In order to get this dashboard configured in DLH.io, the following script will need to be run in Snowflake. Modify the role, username & password to fit your naming convention and existing names.


use role accountadmin;

CREATE USER DLH_USAGE_ANALYTICS_SVC PASSWORD="@bl3B0died#SvcAccount" --change to meet your standards
COMMENT="User specifically for DLH.io Snowflake Usage Analytics"; --change password to meet your standards
CREATE ROLE DLH_USAGE_ANALYTICS_SVC_ROLE; --if necessary, change the role name to meet your naming standards

GRANT MONITOR USAGE ON ACCOUNT TO ROLE DLH_USAGE_ANALYTICS_SVC_ROLE;
GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE DLH_USAGE_ANALYTICS_SVC_ROLE;
GRANT MONITOR, USAGE ON WAREHOUSE COMPUTE_WH TO DLH_USAGE_ANALYTICS_SVC_ROLE;

GRANT ROLE DLH_USAGE_ANALYTICS_SVC_ROLE TO USER DLH_USAGE_ANALYTICS_SVC;

use role accountadmin;CREATE USER DLH_USAGE_ANALYTICS_SVC PASSWORD="@bl3B0died#SvcAccount" --change to meet your standardsCOMMENT="User specifically for DLH.io Snowflake Usage Analytics"; --change password to meet your standardsCREATE ROLE DLH_USAGE_ANALYTICS_SVC_ROLE; --if necessary, change the role name to meet your naming standardsGRANT MONITOR USAGE ON ACCOUNT TO ROLE DLH_USAGE_ANALYTICS_SVC_ROLE;GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE DLH_USAGE_ANALYTICS_SVC_ROLE;GRANT MONITOR, USAGE ON WAREHOUSE COMPUTE_WH TO DLH_USAGE_ANALYTICS_SVC_ROLE;GRANT ROLE DLH_USAGE_ANALYTICS_SVC_ROLE TO USER DLH_USAGE_ANALYTICS_SVC;

Create a Snowflake Source Connector

Create a Source Connector to Snowflake that will be used for creating and running the Sync Bridge.

Install the Package

Install the Snowflake Usage Analytics package by following the How to Access Analytics steps with a user who has the ORG_ADMIN role in DLH.io.

Pre-Install Items

New DLH.io Customer

Existing DLH.io Customer

Create a Snowflake Source Connector

Install the Package

On this page