Skip to main content

Keebo Warehouse Optimization Security Setup for Snowflake

Warehouse Optimization (KWO) requires a dedicated Snowflake user, role, and a set of metadata views before onboarding. These can be configured independently or with assistance during trial setup.

What Metadata Does Warehouse Optimization Access?​

KWO accesses usage metadata from three views in SNOWFLAKE.ACCOUNT_USAGE:

  • WAREHOUSE_METERING_HISTORY
  • QUERY_HISTORY
  • WAREHOUSE_EVENTS_HISTORY

KWO does not access user data. For additional protection, a KEEBO_SCHEMA with restricted views limits access to only the required metadata fields.

How Is This Metadata Used?​

KWO's patented algorithms use 76 metadata fields to continuously adapt to changing conditions in the Snowflake environment. These fields provide insight into:

  • Workload distribution
  • Resource utilization
  • Query behaviors

Each field plays a role in optimization decisions. When KWO detects an opportunity — such as a period of low utilization — it autonomously triggers actions to reduce costs without impacting performance. If query latencies increase, KWO detects the change and increases warehouse size to maintain performance.

Each of these fields are critical for the data learning models, whether they are workload, resource, or query related. Next in this document are the specific steps to run in Snowflake for Keebo setup.

What Permissions Are Required?​

Metadata views — read-only access via restricted views in KEEBO_SCHEMA:

ViewSourceAction
QUERY_HISTORYSNOWFLAKE.ACCOUNT_USAGESELECT
WAREHOUSE_METERING_HISTORYSNOWFLAKE.ACCOUNT_USAGESELECT
WAREHOUSE_EVENTS_HISTORYSNOWFLAKE.ACCOUNT_USAGESELECT

Warehouse actions — applied to each warehouse selected for optimization:

PermissionPurpose
USAGERead warehouse state and metadata
MODIFYChange warehouse size and configuration
OPERATESuspend and resume the warehouse

Database and schema access:

ResourcePermission
Database (KEEBO_DB)USAGE
Schema (KEEBO_SCHEMA)USAGE, CREATE PROCEDURE
Each metadata viewSELECT

How Is the Keebo User and Role Created?​

Key Pair Authentication​

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;
-- Creates a new role that will be used by Keebo.
CREATE ROLE IF NOT EXISTS KEEBO_ROLE;
-- Creates a new user that Keebo will use to access your Snowflake environment.
-- If the user already exists, Snowflake keeps the existing password/key configuration.
CREATE USER IF NOT EXISTS KEEBO_USER RSA_PUBLIC_KEY = '<rsa_public_key>';
-- Grants the newly created role to the user.
GRANT ROLE KEEBO_ROLE TO USER KEEBO_USER;
-- Sets the default role for the user to the newly created one
ALTER USER KEEBO_USER SET DEFAULT_ROLE = KEEBO_ROLE;
-- Specifies the type of user. You can set this property to differentiate between human, service, and legacy service users.
ALTER USER KEEBO_USER SET TYPE = SERVICE;

Programmatic Access Token (PAT)​

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;
-- Creates a new role that will be used by Keebo.
CREATE ROLE IF NOT EXISTS KEEBO_ROLE;
-- Creates a new user that Keebo will use to access your Snowflake environment.
CREATE USER IF NOT EXISTS KEEBO_USER;
-- Grants the newly created role to the user.
GRANT ROLE KEEBO_ROLE TO USER KEEBO_USER;
-- Sets the default role for the user to the newly created one
ALTER USER KEEBO_USER SET DEFAULT_ROLE = KEEBO_ROLE;
-- Specifies the type of user. You can set this property to differentiate between human, service, and legacy service users.
ALTER USER KEEBO_USER SET TYPE = SERVICE;

No password or key clause is set on the user. The token itself is added after the network policy is configured — see How Is a Programmatic Access Token Created? below.

How Is a Programmatic Access Token Created?​

When KEEBO_USER uses PAT authentication, Snowflake requires the user to be subject to a network policy before a token can be created, so this step runs after How Is Network Access Configured? above.

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;
ALTER USER KEEBO_USER ADD PROGRAMMATIC ACCESS TOKEN KEEBO_ACCESS_TOKEN
    ROLE_RESTRICTION = KEEBO_ROLE
    DAYS_TO_EXPIRY = 365;

Snowflake displays the token secret in the console output only once. Copy it into the Keebo form immediately — it cannot be retrieved again. See Snowflake's documentation on programmatic access tokens for details on rotating or revoking a token.

Unlike a password or private key, a programmatic access token expires automatically once DAYS_TO_EXPIRY elapses, and Snowflake cannot extend an existing token's expiration. Track its expiration date — run SHOW USER PROGRAMMATIC ACCESS TOKENS FOR USER KEEBO_USER; in Snowflake — and generate a new token before the current one lapses. Warehouse Optimization loses access and stops optimizing once the token expires.

An account already connected with basic or key pair authentication can be migrated to PAT from Advanced Settings — see How Is PAT Authentication Configured? in the advanced settings guide.

How Are Keebo Database, Schema, and Views Created?​

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;
-- Creates a new database that will be used by Keebo.
CREATE DATABASE IF NOT EXISTS KEEBO_DB;
-- Allows the newly created role to access the database.
GRANT USAGE ON DATABASE KEEBO_DB TO ROLE KEEBO_ROLE;
-- Creates a new schema within the database.
CREATE SCHEMA IF NOT EXISTS KEEBO_DB.KEEBO_SCHEMA;
-- Allows the newly created role to access the schema.
-- Revokes any previously-granted schema privileges beyond USAGE and CREATE PROCEDURE,
-- so the role ends up with exactly the minimal set this script grants.
REVOKE ALL ON SCHEMA KEEBO_DB.KEEBO_SCHEMA FROM ROLE KEEBO_ROLE;
GRANT USAGE ON SCHEMA KEEBO_DB.KEEBO_SCHEMA TO ROLE KEEBO_ROLE;
-- Lets Keebo install and call stored procedures for Proactive Suspend optimizations
GRANT CREATE PROCEDURE ON SCHEMA KEEBO_DB.KEEBO_SCHEMA TO ROLE KEEBO_ROLE;
-- Creates a new view that will be used to store the query history.
-- Ensures this view is owned by ACCOUNTADMIN and grants the Keebo role only the
-- SELECT access it needs on it, nothing more.
EXECUTE IMMEDIATE $$
BEGIN
  GRANT OWNERSHIP ON VIEW KEEBO_DB.KEEBO_SCHEMA.QUERY_HISTORY TO ROLE ACCOUNTADMIN COPY CURRENT GRANTS;
  REVOKE ALL ON VIEW KEEBO_DB.KEEBO_SCHEMA.QUERY_HISTORY FROM ROLE KEEBO_ROLE;
  RETURN 'reclaimed';
EXCEPTION
  WHEN OTHER THEN
    RETURN 'no-op: view does not exist yet, or nothing to reclaim';
END;
$$;
CREATE OR REPLACE VIEW KEEBO_DB.KEEBO_SCHEMA.QUERY_HISTORY
COPY GRANTS
AS SELECT
  QUERY_ID,
  SHA2(QUERY_TEXT, 256) AS HASH_QUERY_TEXT,
  SHA2(REGEXP_REPLACE(REGEXP_REPLACE(QUERY_TEXT, $$('.*?')$$, $$'{str}'$$), $$ -?\d+$$, ' {digit}'), 256) AS HASH_SANITIZED_QUERY,
  DATABASE_ID,
  DATABASE_NAME,
  SCHEMA_ID,
  SHA2(SCHEMA_NAME, 256) AS HASH_SCHEMA_NAME,
  QUERY_TYPE,
  SESSION_ID,
  SHA2(USER_NAME, 256) AS HASH_USER_NAME,
  SHA2(ROLE_NAME, 256) AS HASH_ROLE_NAME,
  WAREHOUSE_ID,
  WAREHOUSE_NAME,
  WAREHOUSE_SIZE,
  WAREHOUSE_TYPE,
  CLUSTER_NUMBER,
  QUERY_TAG,
  EXECUTION_STATUS,
  ERROR_CODE,
  ERROR_MESSAGE,
  START_TIME,
  END_TIME,
  TOTAL_ELAPSED_TIME,
  BYTES_SCANNED,
  PERCENTAGE_SCANNED_FROM_CACHE,
  BYTES_WRITTEN,
  BYTES_WRITTEN_TO_RESULT,
  BYTES_READ_FROM_RESULT,
  ROWS_PRODUCED,
  ROWS_INSERTED,
  ROWS_UPDATED,
  ROWS_DELETED,
  ROWS_UNLOADED,
  BYTES_DELETED,
  PARTITIONS_SCANNED,
  PARTITIONS_TOTAL,
  BYTES_SPILLED_TO_LOCAL_STORAGE,
  BYTES_SPILLED_TO_REMOTE_STORAGE,
  BYTES_SENT_OVER_THE_NETWORK,
  COMPILATION_TIME,
  EXECUTION_TIME,
  QUEUED_PROVISIONING_TIME,
  QUEUED_REPAIR_TIME,
  QUEUED_OVERLOAD_TIME,
  TRANSACTION_BLOCKED_TIME,
  CREDITS_USED_CLOUD_SERVICES,
  QUERY_LOAD_PERCENT,
  OUTBOUND_DATA_TRANSFER_CLOUD,
  OUTBOUND_DATA_TRANSFER_REGION,
  OUTBOUND_DATA_TRANSFER_BYTES,
  INBOUND_DATA_TRANSFER_CLOUD,
  INBOUND_DATA_TRANSFER_REGION,
  INBOUND_DATA_TRANSFER_BYTES,
  LIST_EXTERNAL_FILES_TIME,
  RELEASE_VERSION,
  EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,
  EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
  EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
  EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,
  EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,
  IS_CLIENT_GENERATED_STATEMENT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;
-- Allows the newly created role to access the view.
GRANT SELECT ON VIEW KEEBO_DB.KEEBO_SCHEMA.QUERY_HISTORY TO ROLE KEEBO_ROLE;
-- Creates a new view that will be used to store the warehouse metering history.
-- Ensures this view is owned by ACCOUNTADMIN and grants the Keebo role only the 
-- SELECT access it needs on it, nothing more.
EXECUTE IMMEDIATE $$
BEGIN
  GRANT OWNERSHIP ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_METERING_HISTORY TO ROLE ACCOUNTADMIN COPY CURRENT GRANTS;
  REVOKE ALL ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_METERING_HISTORY FROM ROLE KEEBO_ROLE;
  RETURN 'reclaimed';
EXCEPTION
  WHEN OTHER THEN
    RETURN 'no-op: view does not exist yet, or nothing to reclaim';
END;
$$;
CREATE OR REPLACE VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_METERING_HISTORY
COPY GRANTS
AS SELECT
  CREDITS_USED,
  CREDITS_USED_CLOUD_SERVICES,
  CREDITS_USED_COMPUTE,
  END_TIME,
  START_TIME,
  WAREHOUSE_ID,
  WAREHOUSE_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY;
-- Allows the newly created role to access the view.
GRANT SELECT ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_METERING_HISTORY TO ROLE KEEBO_ROLE;
-- Creates a new view that will be used to store the warehouse events history.
-- Ensures this view is owned by ACCOUNTADMIN and grants the Keebo role only the 
-- SELECT access it needs on it, nothing more.
EXECUTE IMMEDIATE $$
BEGIN
  GRANT OWNERSHIP ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_EVENTS_HISTORY TO ROLE ACCOUNTADMIN COPY CURRENT GRANTS;
  REVOKE ALL ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_EVENTS_HISTORY FROM ROLE KEEBO_ROLE;
  RETURN 'reclaimed';
EXCEPTION
  WHEN OTHER THEN
    RETURN 'no-op: view does not exist yet, or nothing to reclaim';
END;
$$;
CREATE OR REPLACE VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_EVENTS_HISTORY
COPY GRANTS
AS SELECT
  CLUSTER_NUMBER,
  EVENT_NAME,
  EVENT_REASON,
  EVENT_STATE,
  QUERY_ID,
  ROLE_NAME,
  TIMESTAMP,
  USER_NAME,
  WAREHOUSE_ID,
  WAREHOUSE_NAME
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY;
-- Allows the newly created role to access the view.
GRANT SELECT ON VIEW KEEBO_DB.KEEBO_SCHEMA.WAREHOUSE_EVENTS_HISTORY TO ROLE KEEBO_ROLE;

How Are Warehouse Permissions Granted?​

GRANT USAGE, MODIFY, OPERATE
ON WAREHOUSE <WAREHOUSE>
TO ROLE KEEBO_ROLE;

How Is Network Access Configured?​

KWO connects to Snowflake from a fixed set of IP addresses. These must be allowlisted via a network policy scoped to the Keebo user.

IP AddressIP AddressIP AddressIP Address
34.123.209.15935.232.243.181104.198.153.40104.198.16.39
34.134.199.9834.41.176.165104.198.59.87136.114.65.89
34.136.192.18935.224.13.139136.114.222.178136.119.162.69
34.123.121.25134.29.108.17104.197.93.248136.113.177.57
35.226.95.6434.30.123.13534.61.122.14334.46.49.16

Authentication: Basic password or RSA key pair, scoped to the KEEBO_USER.

Network policy: Applied at the user level (ALTER USER ... SET NETWORK_POLICY), not at the account level.

Option 1: Using Network Rules​

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;

CREATE NETWORK RULE KEEBO_NETWORK_RULE
  TYPE = IPV4
  VALUE_LIST = (
    '34.123.209.159', '34.134.199.98', '34.136.192.189',
    '34.123.121.251', '35.226.95.64', '35.232.243.181',
    '34.41.176.165', '35.224.13.139', '34.29.108.17',
    '34.30.123.135', '104.198.153.40', '104.198.59.87',
    '136.114.222.178', '104.197.93.248', '34.61.122.143',
    '104.198.16.39', '136.114.65.89', '136.119.162.69',
    '136.113.177.57', '34.46.49.16')
  MODE = INGRESS;
-- Creates a new network policy that will allow our systems to access your Snowflake environment.
CREATE NETWORK POLICY IF NOT EXISTS keebo_user_np
  ALLOWED_NETWORK_RULE_LIST = ('KEEBO_NETWORK_RULE');
-- Alters our user to use the newly created network policy.
ALTER USER KEEBO_USER SET NETWORK_POLICY = keebo_user_np;

Option 2: Without Network Rules​

-- Run this script with ACCOUNTADMIN, or replace with your preferred role derived from ACCOUNTADMIN.
USE ROLE ACCOUNTADMIN;

CREATE NETWORK POLICY IF NOT EXISTS keebo_user_np ALLOWED_IP_LIST = (
  '34.123.209.159', '34.134.199.98', '34.136.192.189', '34.123.121.251',
  '35.226.95.64', '35.232.243.181', '34.41.176.165', '35.224.13.139',
  '34.29.108.17', '34.30.123.135', '104.198.153.40', '104.198.59.87',
  '136.114.222.178', '104.197.93.248', '34.61.122.143', '104.198.16.39', 
  '136.114.65.89', '136.119.162.69', '136.113.177.57', '34.46.49.16'
);
-- Alters our user to use the newly created network policy.
ALTER USER KEEBO_USER SET NETWORK_POLICY = keebo_user_np;

Keebo supports connecting to Snowflake accounts that are configured with AWS PrivateLink or Azure Private Link. When an account has public access disabled and is only reachable via Private Link, Keebo connects through its own VPC endpoints instead of the public internet.

Setup​

To use Private Link with Keebo, two things must be in place:

  1. Your Snowflake account must accept connections from Keebo's VPC endpoint. Follow the Snowflake documentation for your cloud provider to authorize Keebo's endpoint as an allowed Private Link connection:

  2. Contact Keebo support to have our staff complete the backend configuration necessary for us to communicate with your Snowflake account. The process is different depending on where your Snowflake account is hosted. Our staff will provide you with the information specific to your cloud provider.

Connectivity Diagnostics​

When you connect an account, Keebo runs a connectivity check. If the account uses Private Link and the hostname has not yet been registered on Keebo's side, Keebo surfaces Private Link Required until support finishes that step. If you see this, reach out to Keebo support with your account URL to complete the setup.