Data

Snowflake

Cloud data warehouse. Roiva reads your Snowflake account's compute credits and query statistics from its ACCOUNT_USAGE views, month by month, to track what the warehouse costs and how hard it works. And your own metric queries read the figures you already model there, such as claims cycle days or quotes per underwriter, as monthly readings of the metrics your value formulas use.

← All integrations

What gets synced

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

snowflake.compute.credits_used Credits Used count

Query Statistics

snowflake.query.count Query Count count

snowflake.query.avg_execution_seconds Avg Execution Time (seconds) duration

Common use cases

Feed a metric from your own tables with a versioned SQL query, so claims, underwriting or operations figures aren't typed in by hand
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

How to connect

You connect Snowflake from inside Roiva, so you need a Roiva workspace first. Sign in to connect it now. New to Roiva? Take the free assessment and create your workspace at the end, or request an invite.

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
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
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
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
In Snowflake: To feed metrics from your own tables, create a view for each in ROIVA_METRICS.PUBLIC, which the script lets Roiva's role read and nothing else. Then in Roiva, set Metrics Schema to ROIVA_METRICS.PUBLIC in this connection's settings, and add a query for each metric on its Metric Queries tab

Setup script

-- 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;

-- Where your metric views live: Roiva's role may read the views in this
-- schema and nothing else. A view runs with its owner's rights, so the
-- role needs no grant on the tables behind it.
CREATE DATABASE IF NOT EXISTS ROIVA_METRICS;
GRANT USAGE ON DATABASE ROIVA_METRICS TO ROLE ROIVA_READER;
GRANT USAGE ON SCHEMA ROIVA_METRICS.PUBLIC TO ROLE ROIVA_READER;
GRANT SELECT ON ALL VIEWS IN SCHEMA ROIVA_METRICS.PUBLIC TO ROLE ROIVA_READER;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ROIVA_METRICS.PUBLIC TO ROLE ROIVA_READER;

-- 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;

Tips for capturing value

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
A metric query returns one row per month, with period and value columns (and entity for a breakdown). Roiva reads only those columns, versions the query's text, and keeps the rows each version returns, so a reading always names what produced it
A Snowflake view runs with its owner's rights, so Roiva's role reads what a view in ROIVA_METRICS returns, not the tables behind it. Keep names and policy numbers out of the views, and put the monthly rollup in the query, where Roiva versions it
Every Snowflake role holds what your account grants to PUBLIC, Roiva's included. Snowflake's sample data is granted to PUBLIC by default, so check what else your account grants to PUBLIC
Roiva books Snowflake's cost for contracts billed in US dollars and doesn't convert currencies. For a contract in euros or pounds, the connection page says so and Roiva books no Snowflake cost, rather than counting that currency as dollars. Credits and query counts still sync
If you also map Snowflake's invoice to the same initiative in QuickBooks, Xero, NetSuite, Brex or Ramp, the invoice is left out in any month this connection books, because it carries the whole Snowflake bill and this connection books only the usage types this link maps. An allocation rule for Snowflake, or a locked period, keeps the invoice instead, and this connection books nothing that month

Need help?

Email hello@roiva.ai with what you're seeing, and you'll hear back within one business day.

Snowflake

Start with the free AI ROI exposure assessment, then connect Snowflake once your workspace is set up.

Get your exposure report → Sign in to connect

Related integrations

Databricks

BigQuery

Looker