Skip to main content

Security Setup

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.

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?​

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

ALTER USER KEEBO_USER SET NETWORK_POLICY = KEEBO_NETWORK_POLICY;