Snowflake

Integrate Teleskope with Snowflake

Requirements

In order to integrate Teleskope with Snowflake, you must make sure the following prerequisites are met:

• Within Snowflake: You must have a Snowflake user with the ACCOUNTADMIN role

• Within Teleskope: A Teleskope admin account to complete the setup process.

Integration

1

Create a Teleskope Snowflake Role

-- Create role & user under ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS TELESKOPE;
CREATE USER IF NOT EXISTS TELESKOPE_USER
  PASSWORD = '<PASSWORD>'                -- replace with a secure password
  DEFAULT_WAREHOUSE = '<WAREHOUSE>'      -- replace with your warehouse
  DEFAULT_ROLE = TELESKOPE
  COMMENT = 'Teleskope integration user';

GRANT ROLE TELESKOPE TO USER TELESKOPE_USER;     

2

Run the Below Script to:

1. Grant Read Access to the Teleskope Role for each database

2. Grant Access History Access (optional)

-- 1. Grant SELECT on all current + future objects

DECLARE
  -- Cursor selecting all non-system databases
  c1 CURSOR FOR
    SELECT database_name
      FROM information_schema.databases
     WHERE database_name NOT IN (
       'exampleDB1',
       'exampleDB2'
     );
  warehouse   STRING := 'WAREHOUSE';    -- same warehouse as above
  role_name   STRING := 'TELESKOPE';
  sql_cmd     STRING;
BEGIN
  -- Loop through each database and grant privileges
  FOR rec IN c1 DO
    -- Warehouse usage
    sql_cmd := 
      'GRANT USAGE ON WAREHOUSE "' || warehouse || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Database usage
    sql_cmd := 
      'GRANT USAGE ON DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Schemas
    sql_cmd := 
      'GRANT USAGE ON ALL SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Tables
    sql_cmd := 
      'GRANT SELECT ON ALL TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT SELECT ON FUTURE TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Views
    sql_cmd := 
      'GRANT SELECT ON ALL VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT SELECT ON FUTURE VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- 2. Optional: account-wide monitoring
    sql_cmd := 
      'GRANT MONITOR USAGE ON ACCOUNT TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
  END FOR;
END;


SHOW GRANTS TO USER TELESKOPE_USER;
SHOW GRANTS TO ROLE TELESKOPE;
          

3

Grant write access to the Teleskope Role (optional)

grant delete on future tables in database identifier($database) to role identifier($role);
grant delete on all tables in database identifier($database) to role identifier($role);                           
grant delete on future views in database identifier($database) to role identifier($role);         
grant delete on all views in database identifier($database) to role identifier($role); 

4

Configure Key Pair Authentication (optional)

If you would like to setup key pair authentication instead of using a password, follow Snowflake's documentation

Enrollment

To enroll your Snowflake instance:

1. Navigate to Settings-> Connector Settings -> Snowflake -> + Enroll New Account

2. Enter your account information and click Next

3. Choose your Authentication Type.

4. Enter your user credentials or keypair and click Enroll