Snowflake

Steps to prepare and connect to Snowflake

Lightup account setup

๐Ÿ“˜

You must have at least the ACCOUNTADMIN role to set up new accounts.

  1. Open the SnowSQL Classic Console, then paste the following commands at the prompt.

๐Ÿ“˜

The following SQL only grants access to one database. Consider creating a datawarehouse for the databases you want to monitor, and then creating a Lightup login there with the same role.

-- change role to ACCOUNTADMIN
use role ACCOUNTADMIN;
-- create role for lightup
create role if not exists lightup_role;
grant role lightup_role to role SYSADMIN;
-- Note that we are not making the lightup_role; a SYSADMIN,
-- but rather granting users with the SYSADMIN role 
-- power to modify the lightup_role.
-- create a user for lightup
create user if not exists lightup_user password = '<enter password here>';
grant role lightup_role to user lightup_user;
alter user lightup_user set default_role = lightup_role;
-- change role
use role SYSADMIN;
-- grant read only database access 
-- repeat for all database/schemas
grant usage on database <database> to role lightup_role;
grant usage on schema <database>.<schema> to role lightup_role;

-- rerun the following any time a table is added to the schema
grant select on all tables in schema <database>.<schema> to role lightup_role;
-- or
grant select on future tables in schema <database>.<schema> to role lightup_role;
  1. Select All Queries, and then RUN.

Connector setting

  • Host - The hostname for the datasource. You can find this in the URL when viewing Snowflake in a browserโ€” typically it is the first part of the URL, either just the first string after https:// or the string between//and the first /. (e.g., https://**nn12321**.snowflakecomputing.com or https://**nn12321.snowflakecomputing.com**).
  • Database Name - The name of the database you're connecting to.
  • Username - Enter lightup_user.
  • Password - The username account's password.

Optional settings

Advanced/Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query governance

Snowflake datasources support the Query history, Scheduling, and Enable data storage settings. For steps, see Set query governance settings for a datasource.