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)ALL
Each metadata viewALL

How Is the Keebo User and Role Created?​

Basic Authentication​

CREATE ROLE KEEBO_ROLE;
CREATE USER KEEBO_USER PASSWORD = 'password';
GRANT ROLE KEEBO_ROLE TO USER KEEBO_USER;
ALTER USER KEEBO_USER SET DEFAULT_ROLE = KEEBO_ROLE;
ALTER USER KEEBO_USER SET TYPE = LEGACY_SERVICE;

Key Pair Authentication​

CREATE ROLE KEEBO_ROLE;
CREATE USER KEEBO_USER RSA_PUBLIC_KEY = '<rsa_public_key>';
GRANT ROLE KEEBO_ROLE TO USER KEEBO_USER;
ALTER USER KEEBO_USER SET DEFAULT_ROLE = KEEBO_ROLE;
ALTER USER KEEBO_USER SET TYPE = SERVICE;

How Is Database Access Granted?​

GRANT USAGE ON DATABASE ${databaseName} TO ROLE ${userRole};
CREATE SCHEMA ${databaseName}.${schemaName};
GRANT ALL ON SCHEMA ${databaseName}.${schemaName} TO ROLE ${userRole};

How Is the Query History View Created?​

CREATE VIEW ${databaseName}.${schemaName}.QUERY_HISTORY
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;

GRANT ALL ON VIEW ${databaseName}.${schemaName}.QUERY_HISTORY TO ROLE KEEBO_ROLE;

How Is the Metering History View Created?​

CREATE VIEW ${databaseName}.${schemaName}.WAREHOUSE_METERING_HISTORY
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;

GRANT ALL ON VIEW ${databaseName}.${schemaName}.WAREHOUSE_METERING_HISTORY TO ROLE ${userRole};

How Is the Warehouse Events View Created?​

CREATE VIEW ${databaseName}.${schemaName}.WAREHOUSE_EVENTS_HISTORY
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;

GRANT ALL ON VIEW ${databaseName}.${schemaName}.WAREHOUSE_EVENTS_HISTORY TO ROLE ${userRole};

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​

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;

CREATE NETWORK POLICY KEEBO_NETWORK_POLICY
  ALLOWED_NETWORK_RULE_LIST = ('KEEBO_NETWORK_RULE');

ALTER USER KEEBO_USER SET NETWORK_POLICY = KEEBO_NETWORK_POLICY;

Option 2: Without Network Rules​

CREATE NETWORK POLICY KEEBO_NETWORK_POLICY 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'
);

ALTER USER KEEBO_USER SET NETWORK_POLICY = KEEBO_NETWORK_POLICY;

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.