Snowflake

Roiva writes these metric observations on each sync. Reference the key in a value formula to use this data in your ROI calculations.

Compute Credits

Query Statistics

• Track Snowflake credit spend before and after an AI initiative that adds or moves data workloads
• Measure query volume and average execution time as a gauge of how much the warehouse is used
• Put warehouse compute on the cost side of an AI data or ML initiative

1. In Snowflake: Open a SQL worksheet, paste the setup script below, select all of it, and run it. Run executes only the statement under the cursor unless the whole script is selected. The script creates a small warehouse, a role that can read ACCOUNT_USAGE, a service user, an authentication policy that lets the user's token work without an IP allowlist, and the token itself
2. In Snowflake: Copy token_secret from the last result. Snowflake shows it only once; to issue another later, run just the script's last statement under a new token name
3. Back in Roiva: Enter your Account Identifier (SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() returns it), leave Warehouse blank since the script makes ROIVA_WH the user's default, paste the token into API Key / Secret, and click Create Connection
4. Back in Roiva: The connection page checks each step with the token and shows the SQL for anything still missing. Once it's all ticked, click Test Connection, then link this connection to an initiative on the Initiatives tab

-- Run in a Snowflake SQL worksheet: select all of it, then click Run.
USE ROLE ACCOUNTADMIN;

-- A small warehouse for Roiva's queries; it suspends after a minute idle.
CREATE WAREHOUSE IF NOT EXISTS ROIVA_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

-- The role Roiva's token is restricted to: it reads ACCOUNT_USAGE and uses the warehouse.
CREATE ROLE IF NOT EXISTS ROIVA_READER;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE ROIVA_READER;
GRANT USAGE ON WAREHOUSE ROIVA_WH TO ROLE ROIVA_READER;

-- The service user Roiva signs in as.
CREATE USER IF NOT EXISTS ROIVA_SERVICE
  TYPE = SERVICE DEFAULT_ROLE = ROIVA_READER DEFAULT_WAREHOUSE = ROIVA_WH;
GRANT ROLE ROIVA_READER TO USER ROIVA_SERVICE;

-- Lets the user's token work from any address; to allow only certain
-- addresses, apply a network policy to the user instead. Authentication
-- policies live in a schema, so this one gets a database of its own.
CREATE DATABASE IF NOT EXISTS ROIVA_ADMIN;
CREATE AUTHENTICATION POLICY IF NOT EXISTS ROIVA_ADMIN.PUBLIC.ROIVA_PAT_POLICY
  AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN')
  PAT_POLICY = (NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED);
ALTER USER ROIVA_SERVICE SET AUTHENTICATION POLICY ROIVA_ADMIN.PUBLIC.ROIVA_PAT_POLICY;

-- The token Roiva uses. Copy token_secret from this result: Snowflake shows it only once.
ALTER USER ROIVA_SERVICE ADD PROGRAMMATIC ACCESS TOKEN ROIVA_TOKEN
  ROLE_RESTRICTION = 'ROIVA_READER' DAYS_TO_EXPIRY = 365;

• Snowflake is ending single-factor password sign-ins, and a service user can't sign in with a password at all, so Roiva uses a programmatic access token. The script issues one for a year, the most Snowflake allows, so note when to issue the next one
• Run the whole script as ACCOUNTADMIN. A worksheet using another role reports the Roiva user as not existing, because that role can't see it
• The script's authentication policy lets the token work from any address. To allow only certain addresses, apply a network policy to the user instead
• Credits and query counts cover the whole account, every warehouse together, for the last 12 months
• ACCOUNT_USAGE views lag behind by as much as a few hours, so the latest day fills in on the next nightly sync