Create Snowflake User and Privileges

In this section, we'll take care of having the prerequisites for creating a Snowflake - Seemore integration.

Create a role

💭 Create a role in Snowflake using the following commands:

-- Switch to the ACCOUNTADMIN role to perform administrative tasks such as creating warehouses.
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS seemore_user_role;
GRANT OPERATE, USAGE ON WAREHOUSE "<warehouse_name>" TO ROLE seemore_user_role;

• Replace <warehouse_name> with the default warehouse to use when running the Seemore snowflake "metadata fetcher".

Create a user

To create a user with a password, replace <password> and run the following:

CREATE USER IF NOT EXISTS seemore_user password='<password>' default_role=seemore_user_role default_warehouse='<warehouse_name>' display_name='SeemoreData';

2 types of authentication supported - Basic (using password) or Key-pair (using private/public key pair)

Key-pair authentication (Recommended)

To use key-pair authentication instead of password:

Generate the private key

To start, open a terminal window and generate a private key. You can generate either an encrypted version of the private key or an unencrypted version of the private key. To generate an unencrypted version, use the following command:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

To generate an encrypted version, use the following command, which omits -nocrypt:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

Generate a public key

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Store the private and public keys securely !!!

Assign the public key to a Snowflake user

ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

For more details access official Snoflake documentation.

Grant role to user

To grant the user_role to the new user:

GRANT ROLE seemore_user_role TO USER seemore_user;

Grant Privileges

In order to fetch metadata from snowflake, Seemore needs access only to SNOWFLAKE using GRANT IMPORTED PRIVILEGES.

-- Grant the role permission to import metadata from the Snowflake database.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE seemore_user_role;

-- Grant the role permission to monitor usage on the entire account.
GRANT MONITOR USAGE ON ACCOUNT TO ROLE seemore_user_role;

Comprehensive Setup Script

This script encompasses the entire setup process, including creating a dedicated warehouse for Seemore, establishing roles, creating a user, and assigning the necessary privileges for operations and monitoring. Please make sure to replace <Your Password>. If you don't need the warehouse just comment out the (lines 8-19), and replace seemore_warehouse (line 26) with your warehouse.

-- Switch to the ACCOUNTADMIN role to perform administrative tasks such as creating warehouses.
USE ROLE ACCOUNTADMIN;
SET var_password = '<Your Password>';

-- Optionally create a new warehouse named "seemore_warehouse" if it does not already exist.
-- The warehouse is set to 'XSMALL' size, with auto-suspension after 60 seconds of inactivity.
-- It's initially suspended to avoid unnecessary costs. A comment is added for clarity.
CREATE WAREHOUSE IF NOT EXISTS seemore_warehouse
    WAREHOUSE_SIZE='XSMALL'
    AUTO_SUSPEND=60
    INITIALLY_SUSPENDED=TRUE
    COMMENT = 'Used by seemore';

CREATE OR REPLACE RESOURCE MONITOR seemore_monitor
    WITH CREDIT_QUOTA = 100
    FREQUENCY = 'MONTHLY'
    START_TIMESTAMP = 'IMMEDIATELY'
    TRIGGERS ON 100 PERCENT DO SUSPEND;

-- Link the warehouse to the monitor
ALTER WAREHOUSE seemore_warehouse SET RESOURCE_MONITOR = seemore_monitor;
              
-- Create a new role for the seemore user, only if it doesn't exist.
CREATE ROLE IF NOT EXISTS seemore_user_role;

-- Grant the new role permissions to operate and use the "seemore_warehouse".
GRANT OPERATE, USAGE ON WAREHOUSE seemore_warehouse TO ROLE seemore_user_role;

-- Create a new user named "seemore_user" only if it doesn't exist.
-- The user's default role and warehouse are set to "seemore_user_role" and "seemore_warehouse", respectively.
CREATE USER IF NOT EXISTS seemore_user 
    PASSWORD = $var_password
    DEFAULT_ROLE = seemore_user_role 
    DEFAULT_WAREHOUSE = 'seemore_warehouse' 
    DISPLAY_NAME = 'SeemoreData';

-- Grant the previously created role to the seemore user.
GRANT ROLE seemore_user_role TO USER seemore_user;

-- Grant the role permission to import metadata from the Snowflake database.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE seemore_user_role;

-- Grant the role permission to monitor usage on the entire account.
GRANT MONITOR USAGE ON ACCOUNT TO ROLE seemore_user_role;

Add additional warehouse data permissions

Seemore needs additional permissions to read warehouse configurations such as timeouts.

Per warehouse we need USAGEpermissions.

DECLARE
  res RESULTSET DEFAULT (SHOW WAREHOUSES);
  warehouse_name STRING;
  sql_command STRING;
BEGIN
  FOR warehouse IN res DO
    warehouse_name := warehouse."name";
    sql_command := 'GRANT USAGE ON WAREHOUSE "' || warehouse_name || '" TO ROLE ' || 'seemore_user_role' || ';';
    EXECUTE IMMEDIATE sql_command;
  END FOR;
END;

Seemore needs additional permissions to modify warehouse configurations such as auto suspend.

Per warehouse we need MODIFY permissions.

DECLARE
  res RESULTSET DEFAULT (SHOW WAREHOUSES);
  warehouse_name STRING;
  sql_command STRING;
BEGIN
  FOR warehouse IN res DO
    warehouse_name := warehouse."name";
    sql_command := 'GRANT MODIFY ON WAREHOUSE "' || warehouse_name || '" TO ROLE ' || 'seemore_user_role' || ';';
    EXECUTE IMMEDIATE sql_command;
  END FOR;
END;

Monitoring Additional Snowflake Services

Streams, Dynamic tables & Materialized Views

• Create (or use) a dedicated database and schema to house the procedure:

  CREATE DATABASE IF NOT EXISTS seemore;
  CREATE SCHEMA IF NOT EXISTS seemore.utils;

• Grant the necessary privileges to seemore_user_role so it can execute the procedure and view the results:

  GRANT USAGE ON DATABASE seemore TO ROLE seemore_user_role;
  GRANT USAGE ON SCHEMA seemore.utils TO ROLE seemore_user_role;

  (Adjust privileges if additional permissions are required by your security model.)

• Use a role with `ACCOUNTADMIN` to create or replace the procedure. This high-level role is needed to grant the procedure enough access to list all streams.

• Create (or replace) the stored procedure with EXECUTE AS OWNER. This setting ensures the procedure runs with the privileges of the owner (the role that created it). That way, even if the caller’s role does not have the privileges to see all streams directly, the procedure can still return the entire list:

CREATE OR REPLACE PROCEDURE seemore.utils.list_object(OBJECT_KEY VARCHAR)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
    var sql_command;
    var key = OBJECT_KEY.toLowerCase();

    if (key === 'streams') {
        sql_command = 'SHOW STREAMS IN ACCOUNT';
    } else if (key === 'dynamic_tables') {
        sql_command = 'SHOW DYNAMIC TABLES IN ACCOUNT';
    } else if (key === 'materialized_views') {
        sql_command = 'SHOW MATERIALIZED VIEWS IN ACCOUNT';
    } else {
        throw 'Invalid object type. Use one of: "streams", "dynamic_tables", "materialized_views"';
    }

    var stmt = snowflake.createStatement({ sqlText: sql_command });
    var result = stmt.execute();

    var objectsList = [];
    while (result.next()) {
        var rowObj = {};
        for (var i = 1; i <= result.getColumnCount(); i++) {
            var colName = result.getColumnName(i);
            var colValue = result.getColumnValue(i);
            rowObj[colName] = colValue;
        }
        objectsList.push(rowObj);
    }

    return objectsList;
$$
;

GRANT USAGE ON PROCEDURE seemore.utils.list_object(VARCHAR) TO ROLE seemore_user_role;

Auto clustering recommendations

Permissions to Auto Clustering monitoring and recommendations

By creating the below procedures and give Seemore the access to it we will be able to help you cluster your tables in a more efficient way and also improve Snowflake's auto-clustering feature, Without the need of exposing data at all.

• First, create the Seemore database and schema if not exists:

CREATE DATABASE IF NOT EXISTS seemore;
CREATE SCHEMA IF NOT EXISTS seemore.utils;

GRANT USAGE ON DATABASE seemore TO ROLE seemore_user_role;
GRANT USAGE ON SCHEMA seemore.utils TO ROLE seemore_user_role;

• Its important to create the following procedures using `ACCOUNTADMIN` role. This high-level role is needed to grant the procedure access to all the tables clustering information.

• GET_CLUSTERING_INFO - Create a procedure that gives Seemore an ability to access snowflake clustering information method, which allows us optimizing our recommendation of clustering columns candidates, based on actual cardinality stats of the tables.

CREATE OR REPLACE PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_INFO(
  table_fqn STRING,
  column_combinations ARRAY
)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
  // Validate inputs
  if (!TABLE_FQN || typeof TABLE_FQN !== 'string' || TABLE_FQN.trim() === '') {
    throw new Error('table_fqn must be a non-empty string');
  }
  
  if (!COLUMN_COMBINATIONS || !Array.isArray(COLUMN_COMBINATIONS) || COLUMN_COMBINATIONS.length === 0) {
    throw new Error('column_combinations must be a non-empty array');
  }
  
  const results = [];
  
  // Process each column combination
  for (let i = 0; i < COLUMN_COMBINATIONS.length; i++) {
    const combination = COLUMN_COMBINATIONS[i];
    
    // Validate combination is an array
    if (!Array.isArray(combination) || combination.length === 0) {
      // Skip invalid combinations but log them
      results.push({
        combination: [],
        error: 'Invalid combination: must be a non-empty array',
        metrics: null
      });
      continue;
    }
    
    try {
      // Escape column names for SQL (handle quotes and special characters)
      const escapedColumns = combination.map(col => {
        if (typeof col !== 'string') {
          throw new Error(`Column name must be a string, got: ${typeof col}`);
        }
        return col;
      });
      
      // Build column list string for SYSTEM$CLUSTERING_INFORMATION
      // Format: (col1, col2, col3)
      const columnList = '(' + escapedColumns.join(', ') + ')';
      
      // Build the SQL statement - use parameterized query to prevent SQL injection
      const sql = `SELECT SYSTEM$CLUSTERING_INFORMATION('${TABLE_FQN}', '${columnList}') AS clustering_info`;
      
      // Execute the statement
      const stmt = snowflake.createStatement({ sqlText: sql });
      const resultSet = stmt.execute();
      
      if (resultSet.next()) {
        const clusteringInfo = resultSet.getColumnValue(1);
        
        // Parse the JSON response from SYSTEM$CLUSTERING_INFORMATION
        let metrics;
        if (typeof clusteringInfo === 'string') {
          try {
            metrics = JSON.parse(clusteringInfo);
          } catch (parseErr) {
            throw new Error(`Failed to parse clustering info JSON: ${parseErr.message}`);
          }
        } else if (clusteringInfo && typeof clusteringInfo === 'object') {
          metrics = clusteringInfo;
        } else {
          throw new Error(`Unexpected clustering info format: ${typeof clusteringInfo}`);
        }
        
        // Build result object with all relevant metrics
        results.push({
          combination: combination,
          metrics: {
            average_depth: metrics.average_depth !== undefined ? metrics.average_depth : null,
            average_overlaps: metrics.average_overlaps !== undefined ? metrics.average_overlaps : null,
            total_partition_count: metrics.total_partition_count !== undefined ? metrics.total_partition_count : null,
            total_constant_partition_count: metrics.total_constant_partition_count !== undefined ? metrics.total_constant_partition_count : null,
            cluster_by_keys: metrics.cluster_by_keys || null,
            notes: metrics.notes || null,
            partition_depth_histogram: metrics.partition_depth_histogram || null
          }
        });
      } else {
        // No result returned
        results.push({
          combination: combination,
          error: 'No result returned from SYSTEM$CLUSTERING_INFORMATION',
          metrics: null
        });
      }
    } catch (err) {
      // Log error but continue with other combinations
      const errorMessage = err.message || String(err);
      results.push({
        combination: combination,
        error: errorMessage,
        metrics: null
      });
    }
  }
  
  // Return results as VARIANT (automatically converted from JavaScript array/object)
  return results;
$$;

GRANT USAGE ON PROCEDURE SEEMORE_DATABASE.UTILS.GET_CLUSTERING_INFO(VARCHAR, ARRAY) TO ROLE seemore_user_role;

• GET_CLUSTERING_COST_ESTIMATE Create a procedure that gives Seemore an access to auto clustering cost estimate method , So Seemore can use it for estimating the cost of turning on auto clustering based on out recommendations.

CREATE OR REPLACE PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_COST_ESTIMATE(
  table_fqn STRING,  
  column_combination ARRAY
)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
  // Validate inputs
  if (!TABLE_FQN || typeof TABLE_FQN !== 'string' || TABLE_FQN.trim() === '') {
    throw new Error('table_fqn must be a non-empty string');
  }
  
  if (!COLUMN_COMBINATION || !Array.isArray(COLUMN_COMBINATION) || COLUMN_COMBINATION.length === 0) {
    throw new Error('column_combination must be a non-empty array');
  }
  
  try {
    // Escape column names for SQL (handle quotes and special characters)
    const escapedColumns = COLUMN_COMBINATION.map(col => {
      if (typeof col !== 'string') {
        throw new Error(`Column name must be a string, got: ${typeof col}`);
      }
      // Escape double quotes by doubling them
      const escaped = col.replace(/"/g, '""');
      return `"${escaped}"`;
    });
    
    // Build column list string for SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS
    // Format: (col1, col2, col3)
    const columnList = '(' + escapedColumns.join(', ') + ')';
    
    // Build the SQL statement
    const sql = `SELECT SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS('${TABLE_FQN}', '${columnList}') AS cost_estimation`;
    
    // Execute the statement
    const stmt = snowflake.createStatement({ sqlText: sql });
    const resultSet = stmt.execute();
    
    if (resultSet.next()) {
      const costEstimationJson = resultSet.getColumnValue(1);
      
      // Parse the JSON response from SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS
      let costEstimation;
      if (typeof costEstimationJson === 'string') {
        try {
          costEstimation = JSON.parse(costEstimationJson);
        } catch (parseErr) {
          throw new Error(`Failed to parse cost estimation JSON: ${parseErr.message}`);
        }
      } else if (costEstimationJson && typeof costEstimationJson === 'object') {
        costEstimation = costEstimationJson;
      } else {
        throw new Error(`Unexpected cost estimation format: ${typeof costEstimationJson}`);
      }
      
      // Return result object with combination and costs
      return {
        combination: COLUMN_COMBINATION,
        costs: {
          initial: costEstimation.initial !== undefined ? costEstimation.initial : null,
          maintenance: costEstimation.maintenance !== undefined ? costEstimation.maintenance : null
        }
      };
    } else {
      // No result returned
      throw new Error('No result returned from SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS');
    }
  } catch (err) {
    // Return error information
    const errorMessage = err.message || String(err);
    return {
      combination: COLUMN_COMBINATION,
      error: errorMessage,
      costs: null
    };
  }
$$;


GRANT USAGE ON PROCEDURE SEEMORE_DATABASE.UTILS.GET_CLUSTERING_COST_ESTIMATE(VARCHAR, ARRAY) TO ROLE seemore_user_role;

Allowlist the Seemore IP

If you are using the IP allowlist in your Snowflake instance, you must add the Seemore IP to the allowlist.

(If you are not using the IP Allowlist in your Snowflake instance, you can skip this step.)

CREATE NETWORK POLICY seemore_production ALLOWED_IP_LIST=('3.78.110.132', '3.73.171.134');
ALTER USER seemore_user SET NETWORK_POLICY = seemore_production;