Connect to Snowflake

Connect to Snowflake to search through any of your tables and views

Overview

To connect to Snowflake, go to Data Sources > + New > Snowflake.

Dataland asks for the following fields to connect with your Snowflake instance:

Account Identifier
Username and Password/Key
Warehouse
Role (optional)

Remember to whitelist Dataland's IPs

In the Snowflake UI, navigate to Admin > Security. Whitelist these IP addresses.

Account Identifier

You can find your account identifier in the URL you use to access Snowflake, ex: <account_identifier>.snowflakecomputing.com.

Note that this URL sometimes includes a service name and region, such as: https://aws.us-east-1.wsp61726.snowflakecomputing.com/

In this case, your Snowflake account ID would include the provider and region and would be wsp61726.us-east-1.aws.

Use existing credentials for user, role, and warehouse

To display your data, Dataland requires a user with usage grants on any databases and schemas you want to access and select grants on any tables and views you want to access.

The Dataland UI allows you to see available roles for a given user.

Available Roles

If you're using an existing warehouse, we recommend it to be sized at least small to make indexing faster.

Create Dataland exclusive user, warehouse, and role

You can use the below script to create a new user, role, and warehouse exclusively used for Dataland. Go to the Snowflake UI, create a new worksheet, and run the script below.

Remember to set your own values for user_password and database_name.

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
set role_name = 'DATALAND_ROLE';
set user_name = 'DATALAND_USER';
set user_password = 'change_this_password';
set warehouse_name = 'DATALAND_WAREHOUSE';
set database_name = 'your_database_name';

begin;

   -- change role to securityadmin for user / role steps
   use role securityadmin;

   -- create role for dataland
   create role if not exists identifier($role_name);
   grant role identifier($role_name) to role SYSADMIN;

   -- create a user for dataland
   create user if not exists identifier($user_name)
   password = $user_password
   default_role = $role_name
   default_warehouse = $warehouse_name;

   grant role identifier($role_name) to user identifier($user_name);

   -- set binary_input_format to BASE64
   ALTER USER identifier($user_name) SET BINARY_INPUT_FORMAT = 'BASE64';

   -- change role to sysadmin for warehouse / database steps
   use role sysadmin;

   -- create a warehouse for dataland
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;

   -- grant dataland role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);

   -- change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)
   use role ACCOUNTADMIN;
   grant CREATE INTEGRATION on account to role identifier($role_name);

    -- to grant on entire database
    grant usage on database identifier($database_name) to role identifier($role_name);
    grant usage on all schemas in database identifier($database_name) to role identifier($role_name);
    grant select on all tables in database identifier($database_name) to role identifier($role_name);
    grant select on future tables in database identifier($database_name) to role identifier($role_name);
    grant select on all views in database identifier($database_name) to role identifier($role_name);
    grant select on future views in database identifier($database_name) to role identifier($role_name);

 commit;