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

Last updated

Was this helpful?