Skip to main content

Security Setup

Workload Intelligence (KWI) requires a dedicated Snowflake user, role, metadata views, and a warehouse before onboarding. This page covers each setup step and the security configurations involved.

How Does KWI Collect Data?​

KWI uses Snowflake tasks to populate pre-aggregated data tables. These tasks run daily at 6:30 AM UTC. The tables are created asynchronously during onboarding, allowing KWI to begin data collection shortly after setup.

How Is the KWI User and Role Created?​

To connect KWI to Snowflake, a dedicated role and user, along with the necessary permissions will need to be created. The following SQL scripts, which are part of the onboarding process, show how to setup these objects:

CREATE ROLE KEEBO_KWI_ROLE;
CREATE USER KEEBO_KWI_USER PASSWORD = '<password>';
GRANT ROLE KEEBO_KWI_ROLE TO USER KEEBO_KWI_USER;
ALTER USER KEEBO_KWI_USER SET DEFAULT_ROLE = KEEBO_KWI_ROLE;
ALTER USER KEEBO_KWI_USER SET TYPE = LEGACY_SERVICE;

How Are the Database and Schema Configured?​

The following views provide KWI with read-only access to the metadata required for workload intelligence. All views are created under a dedicated database and schema.

Query History View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.QUERY_HISTORY
AS
SELECT QUERY_ID,
  QUERY_TEXT,
  QUERY_HASH,
  QUERY_HASH_VERSION,
  QUERY_PARAMETERIZED_HASH,
  QUERY_PARAMETERIZED_HASH_VERSION,
  DATABASE_ID,
  DATABASE_NAME,
  SCHEMA_ID,
  SCHEMA_NAME,
  QUERY_TYPE,
  SESSION_ID,
  USER_NAME,
  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 KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.QUERY_HISTORY TO ROLE KEEBO_KWI_ROLE;

Warehouse Metering History View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.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 KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.WAREHOUSE_METERING_HISTORY TO ROLE KEEBO_KWI_ROLE;

Warehouse Events History View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.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 KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.WAREHOUSE_EVENTS_HISTORY TO ROLE KEEBO_KWI_ROLE;

Table Storage Metrics View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.TABLE_STORAGE_METRICS
AS
SELECT TABLE_CATALOG,
  TABLE_SCHEMA,
  TABLE_NAME,
  ID,
  CLONE_GROUP_ID,
  IS_TRANSIENT,
  ACTIVE_BYTES,
  TIME_TRAVEL_BYTES,
  FAILSAFE_BYTES,
  RETAINED_FOR_CLONE_BYTES,
  TABLE_CREATED,
  TABLE_DROPPED,
  TABLE_ENTERED_FAILSAFE,
  CATALOG_CREATED,
  CATALOG_DROPPED,
  SCHEMA_CREATED,
  SCHEMA_DROPPED,
  COMMENT
FROM SNOWFLAKE.INFORMATION_SCHEMA.TABLE_STORAGE_METRICS;

GRANT ALL ON VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.TABLE_STORAGE_METRICS TO ROLE KEEBO_KWI_ROLE;

Tables View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.TABLES AS
SELECT
  TABLE_ID,
  TABLE_NAME,
  TABLE_SCHEMA_ID,
  TABLE_SCHEMA,
  TABLE_CATALOG_ID,
  TABLE_CATALOG,
  TABLE_OWNER,
  TABLE_TYPE,
  IS_TRANSIENT,
  CLUSTERING_KEY,
  ROW_COUNT,
  BYTES,
  RETENTION_TIME,
  SELF_REFERENCING_COLUMN_NAME,
  REFERENCE_GENERATION,
  USER_DEFINED_TYPE_CATALOG,
  USER_DEFINED_TYPE_SCHEMA,
  USER_DEFINED_TYPE_NAME,
  IS_INSERTABLE_INTO,
  IS_TYPED,
  COMMIT_ACTION,
  CREATED,
  LAST_ALTERED,
  LAST_DDL,
  LAST_DDL_BY,
  DELETED,
  AUTO_CLUSTERING_ON,
  COMMENT,
  OWNER_ROLE_TYPE,
  INSTANCE_ID,
  IS_ICEBERG,
  IS_DYNAMIC
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES;

GRANT ALL ON VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.TABLES TO ROLE KEEBO_KWI_ROLE;

Access History View​

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.ACCESS_HISTORY AS
SELECT
  QUERY_ID,
  QUERY_START_TIME,
  USER_NAME,
  DIRECT_OBJECTS_ACCESSED,
  BASE_OBJECTS_ACCESSED,
  OBJECTS_MODIFIED,
  OBJECT_MODIFIED_BY_DDL,
  POLICIES_REFERENCED,
  PARENT_QUERY_ID,
  ROOT_QUERY_ID
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY;

GRANT ALL ON VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.ACCESS_HISTORY TO ROLE KEEBO_KWI_ROLE;

Account-Level Permissions​

GRANT EXECUTE TASK ON ACCOUNT TO ROLE KEEBO_KWI_ROLE;
GRANT MONITOR ON ACCOUNT TO ROLE KEEBO_KWI_ROLE;

How Is Query Text Privacy Handled?​

If privacy concerns exist regarding QUERY_TEXT, it can be hashed using SHA-256 to anonymize the text. Replace the QUERY_TEXT column in the Query History View with:

CREATE VIEW KEEBO_KWI_DB.KEEBO_KWI_SCHEMA.QUERY_HISTORY
AS
SELECT QUERY_ID,
  SHA2(QUERY_TEXT, 256) AS QUERY_TEXT,
  QUERY_HASH,
  QUERY_HASH_VERSION,
  QUERY_PARAMETERIZED_HASH,
  QUERY_PARAMETERIZED_HASH_VERSION,
  DATABASE_ID,
  DATABASE_NAME,
...

How Is the KWI Warehouse Configured?​

Resource Monitor​

A resource monitor limits KWI warehouse spend. When KWI reaches its daily credit limit, the warehouse suspends automatically. A limit of 10 credits daily is recommended (3–5x typical usage), but this value can be adjusted.

CREATE RESOURCE MONITOR KEEBO_KWI_MONITOR
  WITH CREDIT_QUOTA = 10
  FREQUENCY = DAILY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 99 PERCENT DO SUSPEND_IMMEDIATE;

GRANT MODIFY ON RESOURCE MONITOR KEEBO_KWI_MONITOR TO ROLE KEEBO_KWI_ROLE;

Warehouse Creation​

CREATE WAREHOUSE KEEBO_KWI_WAREHOUSE
  WAREHOUSE_SIZE = MEDIUM,
  AUTO_SUSPEND = 30
  INITIALLY_SUSPENDED = TRUE
  RESOURCE_MONITOR = KEEBO_KWI_MONITOR;

GRANT USAGE ON WAREHOUSE KEEBO_KWI_WAREHOUSE TO ROLE KEEBO_KWI_ROLE;

How Is Network Access Configured?​

KWI restricts access to necessary metadata only and encrypts all data communications. All credentials are encrypted at rest and in transit, and Keebo's infrastructure complies with SOC 2 security standards.

IP Allowlisting​

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

ALTER USER KEEBO_KWI_USER SET NETWORK_POLICY = keebo_user_np;

Encrypted Credentials​

KWI ensures all credentials are encrypted at rest and in transit, and Keebo’s infrastructure complies with SOC 2 security standards.